CODEWITHSUNDEEP
postgresqltypescastingcreate-table
Ferenc Deak
Ferenc Deak

Reputation: 35408

Postgres CREATE TABLE AS SELECT with unknown column type

I want to create a table with the following query:

create table something as
select 
      other_id, 
      other_title, 
      '0.0.0.0' as IP, 
      1 as used 
from
      other_table

But Postgres complains that:

column "ip" has type "unknown"

How can I specify it to be of type char?

Upvotes: 4

Views: 5575

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

At least since Postgres 9.4, this does not raise an EXCEPTION, just a WARNING. The table is created anyway, with a column of data type unknown if no type is given for the string literal:

dbfiddle for pg 9.4 here

Postgres 10 introduces more useful default behavior. String literals are cast to the default data type text unless cast explicitly. So you don't get columns of type unknown any more:

dbfiddle here

Introduced with this commit (with detailed explanation).

And the type unknown has been labeled a pseudo-type now. Details in this commit.

Upvotes: 1

Abdul Salam
Abdul Salam

Reputation: 238

You should specify the type , use '0.0.0.0'::text AS IP

Upvotes: 0

mvp
mvp

Reputation: 116177

You need to explicitly cast your column, like this:

'0.0.0.0'::INET as IP,

Upvotes: 7

Related Questions