CODEWITHSUNDEEP
postgresqlunionsql-insert
sad
sad

Reputation: 3

How can I insert union tables to table in PostgreSQL?

I have this query and insert rows to MYSQl database and work perfect.

insert int test(id,user)
select null,user from table2
union 
select null,user from table3

But when run the above query in PostgreSQL not work. And I get this error column "id" is of type integer but expression is of type text, But when I run two query below as shown as worked.

When I run below query in PostgreSQL it works properly:

insert into test(id,user)
select null,user from table2

Or below query in PostgreSQL it works properly:

insert int test(id,user)
select null,user from table3

Or below query in PostgreSQL it works properly:

select null,user from table2
union 
select null,user from table3

Upvotes: 0

Views: 540

Answers (1)

user330315
user330315

Reputation:

null is not a real value and thus has no data type. The default assumed data type is text, that's where the error message comes from. Just cast the value to int in the first SELECT:

insert into test(id, "user")
select null::int, "user" from table2
union 
select null, "user" from table3

Or even better, leave out the id completely so that any default defined for the id column is used. It sounds strange to try and insert null into a column named id

insert into test("user")
select "user" from table2
union 
select "user" from table3

Note that user is a reserved keyword and a built-in function, so you will have to quote it to avoid problems. In the long run I recommend to find a different name for that column.

Upvotes: 1

Related Questions