12

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?

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
Chad Harrison
  • 2,836
  • 4
  • 33
  • 50

6 Answers6

10

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.

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129
  • I agree with you. Production code must have tight coupling b/w POJO field names and column names. – nabster Nov 01 '19 at 18:09
5

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.

podiluska
  • 50,950
  • 7
  • 98
  • 104
2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • And yet... I got my answer here, and not from the FM... even so, thank you, Erwin. I see your PostgreSQL wisdom everywhere I go. The manual can be stale, whereas collective wisdom as seen in responses on this web page... they make my next move quite clear. I will continue my T-SQL habit of specifying every field both on the insert side and the select side of the statement. – Wellspring Jul 25 '20 at 14:46
  • @Wellspring: That's a good habit to get into, for any RDBMS that complies to the SQL standard in this respect. (Rare exceptions apply, with dynamic SQL for instance.) – Erwin Brandstetter Aug 08 '21 at 22:11
0

The values are just added in the same order as the columns appear in the table. It's useful in situations where you don't know the names of the columns you're working with but know what data needs to go in. It's generally not a good idea to do this though as it of course breaks if the order of the columns change or new columns are inserted in the middle.

PhonicUK
  • 13,486
  • 4
  • 43
  • 62
0

That syntax only works without specifying columns if and only if you provide with the same number of values as the number of columns. The second more important thing is columns in a sql table are always in the same order and that depends on your table definition. The only thing that has no innate order in a sql table are rows.

Florin Stingaciu
  • 8,085
  • 2
  • 24
  • 45
0

when the Table is created , each column will have an order number in the system table. So each value would be inserted as per the order..

Firt value will go to first column ... an so on

In sql server , system table syscolumn maintains this order. Postgresql should have something similar to this..

Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58