Postgres CREATE TABLE AS SELECT with unknown column type

Question

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?

Answer 1

You need to explicitly cast your column, like this:

'0.0.0.0'::INET as IP,

Answer 2

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.