Saturday, November 14, 2015

A record with all defaults

Fresh from yesterday's help file:

Say you want to insert a record into your table with all the default values set. Later on you'll update some of the values. You might want to do this so you can get the row's ID field. So you try this:
insert into mytable values () returning my_id;
but you find it doesn't work. VALUES isn't allowed to have an empty list. Here's what you do:
insert into mytable values (default) returning my_id;
This will insert the default value explicitly into first column in the table, and implicitly into all the other columns. It doesn't matter if any of the columns doesn't have an explicit default value set - in that case the default is NULL. Of course, that means this won't work if you have NOT NULL columns without a default set.
at

4 comments:

  1. johtopgNovember 14, 2015 at 10:44 AM

    Or, better: INSERT INTO foo DEFAULT VALUES;

    Works even if the table has no columns.

    ReplyDelete
  2. xocolatlNovember 14, 2015 at 10:47 AM

    As said above, "insert into mytable default values returning my_id;" is much cleaner.

    ReplyDelete
  3. UnknownNovember 14, 2015 at 11:00 AM

    Or in 9.4, INSERT INTO foo SELECT;

    But that’s just for fun. The DEFAULT VALUES solution above is the right way to go.

    ReplyDelete
  4. Andrew DunstanNovember 14, 2015 at 12:20 PM

    Well, there you go. I learn something new every day. Thanks for the info all.

    ReplyDelete