SQL INSERT without specifying columns. What's happening?

Question

I looked through my favorite W3schools and found this page and actually found out something interesting. I did not know that you could invoke the insert command without specifying value columns. For example:

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

Pulling out of my vague memory, I seem to remember how SQL prof mentions that you should process the fields as if they were not in any particular order (although there is on the RDB side, but this is not guaranteed).

My question is, how does the server know which values ​​are assigned to which fields? * I would experience it myself, but I’m not going to use the production server that I currently have access to.

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

Answer 1

Your professional was right - you must explicitly specify columns before naming values.

In this case, although the values ​​will be inserted in the order in which they appear in the table definition.

The problem is that if this order is changed or columns are deleted or added (even if they are zero), then the insert will break.

In terms of its usefulness, there is not much in production code. If you manually encode fast insertion, this may just help you print all column names.

Answer 2

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

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

Your professor was right, it is lazy and can break. But useful for quick and dirty lazy inserts.

Answer 3

I can not resist to put "RTFM" here.
The PostgreSQL tutorial details what happens in the INSERT chapter :

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

My bold accent.

Answer 4

Values ​​are simply added in the same order as the columns in the table. It is useful in situations where you do not know the names of the columns you are working with, but you know what data to enter. This is usually not a good idea, although of course it breaks if the order of the columns is changed or new columns are inserted in the middle.

Answer 5

This 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 important thing is that the columns in the sql table are always in the same order and depend on the definition of your table. The only thing that does not have an innate order in the sql table is the rows.

Answer 6

when a table is created, each column will have an order number in the system table. Therefore, each value will be inserted in order.

The value of firt will go to the first column ... so on

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