How to insert selected rows with another column sequence?

Question

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.

Racil Hilan · Accepted Answer · 2014-04-08 14:00:51Z

1

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;

answered Apr 8, 2014 at 14:00

Racil Hilan

25.4k13 gold badges56 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sergzach Over a year ago

Excuse me. Probably I made the edit after your answer: I'd like not to use exclicit column names. There are many tables. Of course it's the solution, but I need a simplier one.

Racil Hilan Over a year ago

@sergzach I don't think there is a way to do it without specifying the columns if the order is not the same. You can use dynamic SQL and generate the column-names automatically, or use a tool to generate the queries for you. As for the second part, it is only unnecessary if the first part specifies ALL the columns in the SAME order as the table in the second part.

Collectives™ on Stack Overflow

How to insert selected rows with another column sequence?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related