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:
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 () 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.insert into mytable values (default) returning my_id;
Or, better: INSERT INTO foo DEFAULT VALUES;
ReplyDeleteWorks even if the table has no columns.
As said above, "insert into mytable default values returning my_id;" is much cleaner.
ReplyDeleteOr in 9.4, INSERT INTO foo SELECT;
ReplyDeleteBut that’s just for fun. The DEFAULT VALUES solution above is the right way to go.
Well, there you go. I learn something new every day. Thanks for the info all.
ReplyDelete