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.

Answer 1

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.

Answer 2

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.