Insert into Postgres table only if record exists

Question

I am writing some sql in Postgres to update an audit table. My sql will update the table being audited based on some criteria and then select that updated record to update information in an audit table. This is what I have so far:

DO $$
  DECLARE
    jsonValue json;
    revId int;
    template RECORD;
    BEGIN
        jsonValue = '...Some JSON...'
        UPDATE projectTemplate set json = jsonValue where type='InstallationProject' AND account_id IS NULL;

        template := (SELECT pt FROM ProjectTemplate pt WHERE pt.type='InstallationProject' AND pt.account_id IS NULL);

        IF EXISTS (template) THEN
        (
            revId := nextval('hibernate_sequence');
            insert into revisionentity (id, timestamp) values(revId, extract(epoch from CURRENT_TIMESTAMP));

            insert into projectTemplate_aud (rev, revtype, id, name, type, category, validfrom, json, account_id)
                VALUES (revId, 1, template.id, template.name, template.type, template.category, template.validfrom, jsonValue, template.account_id);
        )
END $$;

My understanding is that template will be undefined if there is nothing in the table that matches that query (and there isn't currently). I want to make it so my query will not attempt to update the audit table if template doesn't exist.

What can I do to update this sql to match what I am trying to do?

What does the current code do right now that does not work? Please explain *I want to make it so my query will not attempt to update the audit table if template doesn't exist.* By *update*, you do not mean the `insert`? — Parfait, Jul 12 '19 at 20:36
Yes I mean I want to prevent the code from inserting anything into the `revisionentity` and `projectTemplate_aud` tables if there are no records matching `SELECT pt FROM ProjectTemplate pt WHERE pt.type='InstallationProject' AND pt.account_id IS NULL`. Originally I did not have the if block and I was getting an error saying `template` was not assigned yet. I believe this is because there are currently no records in the table I am querying. I want to make it so that if template is not assigned the code in the IF block does not execute — Nikhil Badami, Jul 12 '19 at 21:59
You are not selecting any columns in `SELECT pt FROM ...`? Is this a typo in post? — Parfait, Jul 13 '19 at 00:44
`and then select that updated record` .. What makes you think that only ***1*** row will be updated? Your predicates do not seem to guarantee anything of the sort: `where type='InstallationProject' AND account_id IS NULL;` — Erwin Brandstetter, Jul 13 '19 at 02:08

Erwin Brandstetter · Accepted Answer · 2019-07-13T02:43:00.803

You cannot use EXISTS like that, it expects a subquery expression. Plus some other issues with your code.

This single SQL DML statement with data-modifying CTEs should replace your DO command properly. And faster, too:

WITH upd AS (
   UPDATE ProjectTemplate
   SET    json = '...Some JSON...'
   WHERE  type = 'InstallationProject'
   AND    account_id IS NULL
   RETURNING *
   )
, ins AS (
   INSERT INTO revisionentity (id, timestamp)
   SELECT nextval('hibernate_sequence'), extract(epoch FROM CURRENT_TIMESTAMP)
   WHERE  EXISTS (SELECT FROM upd)  -- minimal valid EXISTS expression!
   RETURNING id
   )
INSERT INTO ProjectTemplate_aud
      (rev , revtype,   id,   name,   type,   category,   validfrom,   json,   account_id)
SELECT i.id, 1      , u.id, u.name, u.type, u.category, u.validfrom, u.json, u.account_id
FROM   upd u, ins i;

Inserts a single row in revisionentity if the UPDATE found any rows.

Inserts as many rows projectTemplate_aud as rows have been updated.

About data-modifying CTEs:

Insert data in 3 tables at a time using Postgres

Aside: I see a mix of CaMeL-case, some underscores, or just lowercased names. Consider legal, lower-case names exclusively (and avoid basic type names as column names). Most importantly, though, be consistent. Related:

Thanks this was exactly what I was looking for! – Nikhil Badami Jul 15 '19 at 18:19 — Nikhil Badami, Jul 15 '19 at 18:19

Insert into Postgres table only if record exists

1 Answers1