CODEWITHSUNDEEP
sqlpostgresql
sergzach
sergzach

Reputation: 6754

How to insert selected rows with another column sequence?

I have to change a database structure. We have several tables with the same columns (mostly), now I'd like to move it to children of one parent table, then move data from old scattered tables to new (inherited) ones.

So, the two tables (inherited comments_temp and primary comments) have identical structure, but different column order:

\d comments
  Column   |            Type             |                           Modifiers
------------+-----------------------------+---------------------------------------------------------------
 comment_id | bigint                      | not null default nextval('comments_comment_id_seq'::regclass)
 post_id    | bigint                      | not null
 auser_id   | bigint                      | not null
 dt         | timestamp without time zone | not null
 text       | text                        | not null
 is_deleted | smallint                    | default 0
 parent     | bigint                      | default 0
                     | default 0

\d comments_temp
   Column   |            Type             |                             Modifiers
------------+-----------------------------+--------------------------------------------------------------------
 comment_id | bigint                      | not null default nextval('comments_base_comment_id_seq'::regclass)
 auser_id   | bigint                      | not null
 dt         | timestamp without time zone | not null
 text       | text                        | not null
 is_deleted | smallint                    | default 0
 parent     | bigint                      | default 0
 post_id    | bigint                      | not null
Inherits: comments_base

The insertion fails because of another order of columns (despite the fact columns in a copied table and columns in a new table matches).

INSERT INTO comments_temp ( SELECT * FROM comments );

So, I've got a error on INSERT:

ERROR:  column "dt" is of type timestamp without time zone but expression is of type bigint
LINE 1: INSERT INTO comments_temp ( SELECT * FROM comments );
                                           ^
HINT:  You will need to rewrite or cast the expression.

How to insert by select when order of columns is different in PostgreSQL? I'd like not to use exclicit column names.

Upvotes: 0

Views: 175

Answers (1)

Racil Hilan
Racil Hilan

Reputation: 25351

Specify the columns in your insert:

INSERT INTO comments_temp(comment_id, auser_id, dt, text, is_deleted, parent, post_id)
SELECT comment_id, auser_id, dt, text, is_deleted, parent, post_id FROM comments;

Upvotes: 1

Related Questions