postgres - insert gen_random_uuid() for the column "key" if the count(key) < 1

Question

Generate gen_random_uuid() for the column key if there count(key) < 1

My table name is keytable and column is key.

I am using the below insert statement to generate the uuid : that is the only value in the table.

INSERT INTO keytable VALUES(gen_random_uuid());

key 
--------------------------------------- 
5686473e-add1-4ab1-be85-7e62152ce539

I wanted to run this insert statement only when i dont have any values in my "key" column.

in other words, if count(key) < 1 then i want to run the INSERT INTO keytable VALUES(gen_random_uuid()); Please help.

is your provided key a wanted value for `gen_random_uuid`?.. what is it?.. — Vao Tsun, Sep 11 '17 at 07:44

score 0 · Answer 1 · answered Sep 11 '17 at 08:00

use INSERT ... SELECT construct instead

t=# begin; insert into keytable select gen_random_uuid() where (select count(key) from keytable) < 1;
BEGIN
INSERT 0 1
t=# insert into keytable select gen_random_uuid() where (select count(key) from keytable) < 1;
INSERT 0 0
t=# rollback;
ROLLBACK

Sahil Jalan · Answer 2 · 2017-09-11T08:09:48.713

0

Already Answered By Clodoaldo Neto (Link Below)

here is according to your perspective

insert into keytable
select gen_random_uuid()
where not exists ( select key from keytable );

It will only insert the result of select statement when

select key from keytable

this does not return anything!

for more info: https://stackoverflow.com/a/15710598/8506841

edited Sep 11 '17 at 08:09

answered Sep 11 '17 at 08:00

Sahil Jalan

111
6

@Aby If your problem is solved! please accept it as your answer. – Sahil Jalan Oct 29 '17 at 11:32

postgres - insert gen_random_uuid() for the column "key" if the count(key) < 1

2 Answers2