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
– Parfait, Commented Jul 12, 2019 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
– Nikhil Badami, Commented Jul 12, 2019 at 21:59
You are not selecting any columns in SELECT pt FROM ...? Is this a typo in post? — Parfait
– Parfait, Commented Jul 13, 2019 at 0: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
– Erwin Brandstetter, Commented Jul 13, 2019 at 2:08

Erwin Brandstetter · Accepted Answer · 2019-07-13 02:43:00Z

2

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:

edited Jul 13, 2019 at 2:43

answered Jul 13, 2019 at 2:22

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nikhil Badami Over a year ago

Thanks this was exactly what I was looking for!

Collectives™ on Stack Overflow

Insert into Postgres table only if record exists

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related