SQL INSERT without specifying columns. What happens?

Question

Was looking through the beloved W3schools and found this page and actually learned something interesting. I didn't know you could call an insert command without specifying columns to values. For example;

INSERT INTO table_name
 VALUES (value1, value2, value3,...)

Pulling from my hazy memory, I seem to remember the SQL prof mentioning that you have to treat fields as if they are not in any particular order (although there is on the RDB side, but it's not guaranteed).

My question is, how does the server know which values get assigned to which fields?* I would test this myself, but am not going to use a production server to do which is all I a have access to at the moment.

If this technology specific, I am working on PostgresSQL. How is this particular syntax even useful?

Answer 1

Your prof was right - you should name the columns explicitly before naming the values.

In this case though the values will be inserted in the order that they appear in the table definition.

The problem with this is that if that order changes, or columns are removed or added (even if they are nullable), then the insert will break.

In terms of its usefulness, not that much in production code. If you're hand coding a quick insert then it might just help save you typing all the column names out.

Answer 2

They get inserted into the fields in the order they are in the table definition.

So if your table has fields (a,b,c), a=value1, b=value2, c=value3.

Your professor was right, this is lazy, and liable to break. But useful for a quick and dirty lazy insert.

Answer 3

I cannot resist to put a "RTFM" here.
The PostgreSQL manual details what happens in the chapter on INSERT:

The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order; or the first N column names, if there are only N columns supplied by the VALUES clause or query. The values supplied by the VALUES clause or query are associated with the explicit or implicit column list left-to-right.

Bold emphasis mine.