Postgres query error

Question

I have a query in postgres

insert into c_d (select * from cd where ak = '22019763');

And I get the following error

ERROR:  column "region" is of type integer but expression is of type character varying
HINT:  You will need to rewrite or cast the expression.

are you sure the c_d and and cd table have the same fields order? otherwise explicitly indicate the fields in your query — Michael Buen
– Michael Buen, Commented Nov 6, 2009 at 9:55
There are more fields in the c_d table than in the cd table. Are there any other ways of doing this? — Elitmiar
– Elitmiar, Commented Nov 6, 2009 at 10:10

Soviut · Accepted Answer · 2018-04-11 06:44:13Z

19

An INSERT INTO table1 SELECT * FROM table2 depends entirely on order of the columns, which is part of the table definition. It will line each column of table1 up with the column of table2 with the same order value, regardless of names.

The problem you have here is whatever column from cd with the same order value as c_d of the table "region" has an incompatible type, and an implicit typecast is not available to clear the confusion.

INSERT INTO SELECT * statements are stylistically bad form unless the two tables are defined, and will forever be defined, exactly the same way. All it takes is for a single extra column to get added to cd, and you'll start getting errors about extraneous extra columns.

If it is at all possible, what I would suggest is explicitly calling out the columns within the SELECT statement. You can call a function to change type within each of the column references (or you could define a new type cast to do this implicitly -- see CREATE CAST), and you can use AS to set the column label to match that of your target column.

If you can't do this for some reason, indicate that in your question.

edited Apr 11, 2018 at 6:44

Soviut

92.2k53 gold badges210 silver badges285 bronze badges

answered Nov 6, 2009 at 10:44

Ed Carrel

4,2741 gold badge27 silver badges17 bronze badges

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

2 Comments

hamx0r Over a year ago

To clarify further, here's how to describe column names in such a statement (Postgres, copying rows into a table from another table): INSERT INTO tablename (col1, col2, col3) SELECT * FROM ...

Noah Gary Over a year ago

just to help y'all out if you really need to do this:

SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_catalog = 'my_db' AND table_name = 'table1'  EXCEPT SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_catalog = 'my_db' AND table_name = 'table2';

That will give you columns in table 1 that arent in table2. Be sure to run in reverse too. Add those columns to each table to make the columns the same. I have a tabel with 900+ columns and this helped sooooo much.

Soviut · Accepted Answer · 2018-04-11 15:13:42Z

1

Check out the PostgreSQL insert documentation. The syntax is:

INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

which here would look something like:

INSERT INTO c_d (column1, column2...) select * from cd where ak = '22019763'

This is the syntax you want to use when inserting values from one table to another where the column types and order are not exactly the same.

edited Apr 11, 2018 at 15:13

Soviut

92.2k53 gold badges210 silver badges285 bronze badges

answered Nov 9, 2009 at 14:47

Amanda Nyren

4952 silver badges6 bronze badges

Collectives™ on Stack Overflow

Postgres query error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related