How to perform Ternary operation in an SQL Insert statement

user_9401029 0
Viewed 1428

Is there a way to perform an insert into Postgres database by using a ternary operation?

I am trying not to write an SQL function nor pass in current_timestamp values via code. Trying to achieve this directly in the SQL statement itself. Is this possible?

Seen examples for select queries where I could use CASE and WHEN but nothing for inserts and I don't see a WHERE clause being applicable here.

Advice if the following is achievable. Thanks.

For the value of check_time in the following, I want to pass in current_timestamp or null depending on the value of the status in the same query.

INSERT INTO user(name, age, status, check_time) VALUES (?, ?, ?, current_timestamp);

I wish to modify the above to the following.

This is not a valid SQL statement but trying to achieve what this ternary would do where it inserts current_timestamp when status is 1 else insert a null value.

INSERT INTO user(name, age, status, check_time) VALUES (?, ?, ?, status == 1 ? current_timestamp : null);
postgresql sql
1 Answers

You can use a CASE expression where you pass again the value passed for the column status:

INSERT INTO "user"(name, age, status, check_time) 
VALUES (?, ?, ?, CASE WHEN ? = 1 THEN current_timestamp END);

An alternative with the use of INSERT ... SELECT where you don't have to pass the status twice:

INSERT INTO "user"(name, age, status, check_time) 
SELECT t.name, t.age, t.status, CASE WHEN t.status = 1 THEN current_timestamp END
FROM (SELECT ? "name", ? age, ? status) t;
user_104988280
Related