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"
INSERT INTO foo
SELECT -- here is empty select, without any columns
WHERE random() >= 0.5;
PS: There are several "curious" related things in PostgreSQL. For example, select; or even more create table t(); both are valid statements.
Conditional insert requires a query and, as you can see from the INSERT documentation, query and DEFAULT VALUES are in a exclusive group.
When explicitly defining what columns should be populated, you need to specify at least one. So something like this is also invalid:
INSERT INTO foo ()
SELECT 1
WHERE random() >= 0.5;
The only way I can think of to achieve the same effect is by explicitly defining the "default" value:
INSERT INTO foo (id)
SELECT nextval('foo_id_seq'::regclass)
WHERE random() > 0.8;
Or by adding another column
CREATE TABLE foo (
id bigserial PRIMARY KEY,
ignored SMALLINT
);
INSERT INTO foo (ignored)
SELECT 1
WHERE random() >= 0.5;
Edit:
Completely missed that you can do empty selects:
INSERT INTO foo
SELECT
WHERE random() >= 0.5;
