CODEWITHSUNDEEP
sqlpostgresql
user187676
user187676

Reputation:

Conditional Value

I have a table that uses UUIDs as primary keys. New rows are inserted like this

INSERT INTO a ( id, ... ) VALUES ( uuid_generate_v4(), ...)

Now I actually only want to generate the UUID when no ID is provided in the insert (either NULL or an empty string)

Is it possible to write something like this?

INSERT INTO a ( id, ... ) VALUES ( $1 || uuid_generate_v4(), ...)

Upvotes: 1

Views: 163

Answers (3)

Tim Child
Tim Child

Reputation: 3012

You can alter the table or create the table with a default value for the column.

CREATE TABLE thing (
  id uuid DEFAULT uuid_generate_v4(),
  updated timestamp DEFAULT now()
);

Upvotes: 1

Ondra Žižka
Ondra Žižka

Reputation: 46796

Check the IF function, available in some RDBMS.

IF( $1 = NULL, $1, uuid_generate_v4())

$1 being a placeholder.

Also you could set a trigger, if you like them, but mostly they are frowned upon.

And also there are IFNULL( $1, uuid_generate_v4() ).

Update: I'm quite surprised that PostgreSQL doesn't suport IF which I considered standard.

So as mentioned in other answers, COALESCE( $1, uuid_generate_v4() ) is probably the best option.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95522

The coalesce() function will use the first non-null. If $1 is null, coalesce() will use uuid_generate_v4(). Otherwise, it will try to use $1.

insert into a (id, ... ) values 
(coalesce($1, uuid_generate_v4()), ... );

Upvotes: 4

Related Questions