Logics in Postgres queries

Question

This is the second attempt as I didn't add enough examples in the first one and it was closed.

I need to make a query in Postgres that inserts new item if existing data has invalid data. I want to do something like this

if not exists( select * from table where field='value' ) then
    insert into ...
end if;

Can I do this without functions?

Here's the table

create table if not exists test_md (
    id          serial  not null,
    partition   text    not null,
    version     int     not null,
    data        jsonb   not null,
    
    primary key (partition, version)
);

Here's some data

insert into test_md(partition, version, data) 
values
    ('part one', 1, '{"k1": "v1", "k2": "v2"}'),
    ('part one', 2, '{"k1": "v1", "k2": "v2"}'),
    ('part two', 4, '{"k1": "v1", "k2": "v2"}'),
    ('part two', 5, '{"k1": "v1", "k2": "v2"}')
;

For example I want to insert data set

{
    'partition': 'part one', 
    'data':      '{"k1": "v1", "k2": "v2"}'
}

I should do nothing if partition and data are the same with the v2 record. In any other case, I should add a new record with version max + 1.

Laurenz Albe · Accepted Answer · 2020-11-11 19:23:58Z

3

The transaction would look like this (written in a DO statement in PL/pgSQL):

DO
$$DECLARE
   r test_md;
   new_version integer;
BEGIN
   -- get the latest version and lock it
   SELECT * INTO r
   FROM test_md
   WHERE partition = 'part one'
   ORDER BY version DESC
   FOR UPDATE
   LIMIT 1;

   -- if "data" has not changed, we are done
   IF r.data = JSONB '{"k1": "v1", "k2": "v2"}' THEN
      RETURN;
   END IF;

   new_version := coalesce(r.version, 0) + 1;

   INSERT INTO test_md (partition, version, data)
   VALUES ('part one', new_version, '{"k1": "v1", "k2": "v2"}');
END;$$;

Of course you can write the same logic as a database function or using client code, but make sure that it is running in a single transaction.

edited Nov 11, 2020 at 19:23

user330315

answered Nov 11, 2020 at 19:07

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

3 Comments

IMSoP Over a year ago

It might be worth linking to the manual page for DO, since one of the key insights here is that you can use that to run procedural code outside of a function.

Yura Over a year ago

It reports an error ERROR: current transaction is aborted, commands ignored until end of transaction block. I think I will figure out what's the reason. The idea is clear. Thanks

Laurenz Albe Over a year ago

Close the broken transaction before running the code.

user330315 · Accepted Answer · 2020-11-11 19:26:21Z

I think this can be done with a single statement using a data modifying common table expression, although I'm not sure if this is more efficient that Laurenz' procedural code:

with new_data (partition, data) as (
  values ('part one', '{"k1": "v1", "k2": "v3"}'::jsonb)
), latest as (
  select t.*
  from test_md t
  where t.partition = (select nd.partition from new_data nd)
  order by version desc
  limit 1
)
insert into test_md (partition, data, version)
select nd.partition, 
       nd.data,
       (select coalesce(max(version), 0) + 1 
        from test_md md where md.partition = nd.partition)
from new_data nd
where not exists (select * 
                  from latest e
                  where e.partition = nd.partition 
                    and e.data = nd.data)

Collectives™ on Stack Overflow

Logics in Postgres queries

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related