CODEWITHSUNDEEP
postgresqlsql-insert
zhughes3
zhughes3

Reputation: 477

Insert new record if count of records with certain field is less than N in PostgreSQL

I have a table called Foos where each record references another record in table Bars.

When I attempt to insert a new record with bar_id = x into Foo: If the count of existing Foos with bar_id = x is > 2, cancel the insert. Else: insert the new record.

I am trying to get this done in one PostgreSQL query, if possible.

Here is my first attempt that doesn't work:

WITH c AS (SELECT COUNT(*) FROM foos WHERE bar_id = 3),
INSERT INTO foos(bar_id, title) VALUES(3, "Some thing") WHERE c < 2

Upvotes: 1

Views: 810

Answers (1)

Bohemian
Bohemian

Reputation: 425043

Insert from a subquery that returns rows only if there are less than 2 like rows:

insert into foos(bar_id, title)
select *
from (select 3, 'Some thing') x
where (select count(*) from foos where bar_id = 3) < 2

Upvotes: 1

Related Questions