How to make an INSERT in postgresql with conditional default value?

Question

How to make an INSERT in postgresql with conditional default value?

Navigation

#1 by (0 votes)

0

We have the following simplified table for the example:

create table teste(
        id serial not null
        );

The following commands cause syntax error:

insert into teste(
        select (case when 1=1 then DEFAULT end)
        );

or:

insert into teste(
        select coalesce(null::integer, DEFAULT)
        );

ERROR:  syntax error at or near "DEFAULT"
LINE 2:         select (case when 1=1 then DEFAULT end)
                                           ^
********** Error **********
ERROR: syntax error at or near "DEFAULT"
SQL state: 42601

I provisionally use the following:

   insert into teste(
    select coalesce(null::integer, nextval('teste_id_seq'::regclass))
    );

The purpose is to inform the DEFAULT value of the field, whether serial or not, conditionally without informing the function or value of the constant defined in the table, as this value may change in the future and will cause an inconsistency in the commands that call the INSERT. Is this possible?

postgresql sql-insert default value

asked by anonymous 15.02.2017 / 15:13

1 answer

Strip Status Label does not display image in Image Margin (Context Menu Strip) Composite name in template does not generate plural in table name

score 0 · Answer 1

The default value of the field must be informed when the table is created, as follows:

CREATE TABLE products (
    product_no integer DEFAULT nextval('products_product_no_seq'),
    price numeric DEFAULT 9.99,
    ...
);

More details on PostgreSQL documentation .