postgresql - 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.