Search code examples
postgresqlsql-insertauto-increment

PostgreSQL: Conditional insert default values


How do we conditionally insert a row with its default values into a table?

For example, we have a table with one column.

CREATE TABLE foo (
    id bigserial PRIMARY KEY
);

And, we want to do something like below:

INSERT INTO foo DEFAULT VALUES
WHERE random() >= 0.5;

But, we get:

ERROR:  syntax error at or near "WHERE"

Solution

  • INSERT INTO foo
    SELECT -- here is empty select, without any columns
    WHERE random() >= 0.5;
    

    Demo

    PS: There are several "curious" related things in PostgreSQL. For example, select; or even more create table t(); both are valid statements.