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

Asked

Viewed 876 times

1

We have the following simplified table for the example:

create table teste(
        id serial not null
        );

The commands below 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

Provisionally use the following:

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

The objective is to inform the DEFAULT value of the field, whether serial or not, conditionally without informing the function or the value of the constant defined in the table, because this value can change in the future and will cause an inconsistency in the commands that call the INSERT. That’s possible?

postgresql sql-insert value default

  • The word DEFAULT should be entered in the INSERT VALUES part and not in the field list.

  • @anonimo Thank you, but note that the above example DEFAULT is not in the field declaration, INSERT is done only with the value, which is one of the ways to do this without necessarily giving the field names, since values that are in the same order of types accepted by the record type of the table.

  • I don’t understand. It makes no sense that you want to specify the term DEFAULT if in the creation of the table you have not informed what is the DEFAULT value.

  • @anonymity:"...when creating the table you did not specify what is the DEFAULT value". Implicitly define yes. Note that the table statement looks like this: ...id integer **default** nextval('teste_id_seq'::')

1 answer

1

The default value of the field must be informed at the time of table creation, as follows:

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

More details on Postgresql documentation.

  • Thank you, but that doesn’t answer the question. The DEFAULT value can also be implicitly reported using the DEFAULT reserved word in the INSERT command. [link] https://www.postgresql.org/docs/9.6/static/sql-insert.html [link]

  • Why do you need to pass INSERT? Why does DEFAULT change? Changing is not easier to control in the application? You cannot adjust DEFAULT by changing what was set in create table?

  • The full code is called a Trigger in an "UPSERT" (ON CONFLICT no postgresql). As the id is incremented under certain condition, if the id is not found to deliver to the "ON CONFLICT UPDATE" it must enter the DEFAULT.

  • So if the ID is not found, move NULL that it will use the default as defined in CREATE.

  • I’m sorry, I don’t understand what you mean by "move NULL".

  • Pass NULL or do not pass the field in Insert!

  • Passing NULL does not call Default, since the field is explicitly called. The database will return "ERROR: null value in column "code" violates not-null Constraint". Not passing the field does not meet the requirement to explicitly inform the code under a certain condition, stated above.

  • From the documentation: "To Insert the next value of the Sequence into the serial column, specify that the serial column should be Assigned its default value. This can be done either by excluding the column from the list of Columns in the INSERT statement, or through the use of the DEFAULT key word.". https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

Show 3 more comments

Browser other questions tagged postgresql sql-insert value default

You are not signed in. Login or sign up in order to post.