Insert conditionally on number of rows matching condition in Postgres

Question

I have a table like this:

  create table if not exists features (
     username text not null,
     feature text not null,
     attributes text[] not null,

     primary key(username, feature)
  );

I want to insert a new feature for a user but only if they have less than 2 features currently in the table. I also want to update the feature's attributes if the user already has that feature in the table.

This is what I've got so far:

with f (username, feature, attributes, fcount) as (
    values (
        'bob', 'dashboard', '{"theme=dark"}'::text[], 
        (select count(*) from features where username = 'bob' and feature = 'dashboard')
    )
)
insert into features (
    username,
    feature,
    attributes
)
select username, feature, attributes 
from f 
where fcount < 2
on conflict (username, feature)
do update set attributes = excluded.attributes
;

When I run this three times with different feature names it adds three rows instead of 2.

username    feature     attributes
bob         dashboard   theme=dark
bob         dashboard1  theme=dark
bob         dashboard2  theme=dark

How can I achieve this? Is it possible to do this with a single insert query or do I need to use a transaction and multiple queries?

http://sqlfiddle.com/#!17/dcc7c/2

klin · Accepted Answer · 2017-12-04 23:15:48Z

3

The main problem - the select list in brackets gives a tuple, not individual columns. Moreover, attributes in the values clause should be explicitly casted to text[]. Also note the proper use of the special record excluded in the on conflict section.

with f (username, feature, attributes, fcount) as (
    values (
        'bob', 'dashboard', '{"theme=dark"}'::text[], 
        (select count(*) from features where username = 'bob')
    )
)
insert into features (
    username,
    feature,
    attributes
)
select username, feature, attributes 
from f 
where fcount < 2
on conflict (username, feature)
do update set attributes = excluded.attributes;

edited Dec 4, 2017 at 23:15

answered Dec 2, 2017 at 1:53

klin

123k15 gold badges240 silver badges262 bronze badges

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

4 Comments

voutasaurus Over a year ago

Thanks @klin! To be clear are you saying I don't need that final where clause?

klin Over a year ago

Yes, find examples with excluded in the documentation.

voutasaurus Over a year ago

I got around to testing the limit part of this and this doesn't prevent the record from being created. I changed the limit from 10 to 2 here: sqlfiddle.com/#!17/dcc7c/13

klin Over a year ago

Well, it's a logical mistake, you should count rows only for username and not for feature. See the updated answer.

Collectives™ on Stack Overflow

Insert conditionally on number of rows matching condition in Postgres

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related