CODEWITHSUNDEEP
sqlnetezza
Victor
Victor

Reputation: 17097

How netezza matches columns while doing insert into select from

When I use:

insert into table2 select * from table1;

Does it match up table 1 and table2 by the relative order of columns or by the column names?

Upvotes: 1

Views: 10861

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The statement:

insert into table2
    select *
    from table1;

Aligns columns by position, not by column name. In most cases, you should be explicit about the names:

insert into table2(col1, . . . coln)
    select col1, . . . coln
    from table1;

Netezza is based on Postgres. Here is the relevant documentation in Postgres:

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.

However, I believe this behavior is part of the ANSI standard. All databases that I know of operate this way.

Upvotes: 1

Related Questions