Postgres: insert new row in table if the table does not have records where column_name = 'abc' occurs N times? I would like to do this in one transaction
So let's take table with columns:
user -&gt; [first_name, last_name]

The use case is that we don't want to insert a new record into a table, if the table already has records where user.first_name = 'ABC' occurs 5 times;
INSERT INTO user VALUES 
('john', 'doe') ...

Apologies if this is simple, am a developer and SQL is not my strong point.
You can do something with triggers
Or you do somewhat complexer INSERT
Which allows only 5 John in your table


CREATE TABLE &quot;user&quot; (&quot;first_name&quot; varchar(16),&quot;last_name&quot; varchar(50) )


✓




insert into &quot;user&quot; 
select 'john', 'doe' 
where (select count(*) from &quot;user&quot; where &quot;first_name&quot; = 'john') &lt; 5




1 rows affected




insert into &quot;user&quot; 
select 'john', 'doe2' 
where (select count(*) from &quot;user&quot; where &quot;first_name&quot; = 'john') &lt; 5




1 rows affected




insert into &quot;user&quot; 
select 'john', 'doe3' 
where (select count(*) from &quot;user&quot; where &quot;first_name&quot; = 'john') &lt; 5


1 rows affected




insert into &quot;user&quot; 
select 'john', 'doe4' 
where (select count(*) from &quot;user&quot; where &quot;first_name&quot; = 'john') &lt; 5


1 rows affected




insert into &quot;user&quot; 
select 'john', 'doe5' 
where (select count(*) from &quot;user&quot; where &quot;first_name&quot; = 'john') &lt; 5


1 rows affected




insert into &quot;user&quot; 
select 'john', 'doe6' 
where (select count(*) from &quot;user&quot; where &quot;first_name&quot; = 'john') &lt; 5


✓




SELECT * FROM &quot;user&quot;


first_name | last_name
:--------- | :--------
john       | doe      
john       | doe2     
john       | doe3     
john       | doe4     
john       | doe5     


db&lt;&gt;fiddle here