Solved: column has type unknown when using ‘SELECT INTO’

For my latest assignment, I have to work with PostGIS/Postgres a lot. I tried a query that inserts the result of a SELECT statement into a new table. However, I ran into a peculiar error.

The error I ran into was:

WARNING:  column XXXXXXXXXXX has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
SELECT 1

A scenario where this error can arise is when you are SELECTing … INTO a destination table.

An example. In the example below, from population_table, I write the population of all Asian countries into destination_table. On top of that, I’m also passing the name of the source table as a string into the column source_table.

SELECT
	'population_table' as source_table,
    s.country
	s.population
INTO destination_table
FROM
	population_table s
WHERE
	s.my_date = '2021-07-12'
	AND s.continent = 'Asia'

In this case, the error will be the following.

WARNING:  column "source_table" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
SELECT 1

This is caused by the fact that the Postgres engine can’t know for sure what the data type is of the string ‘population_table’. Is it a CHAR, TEXT or VARCHAR(X)?

To solve the problem, you should CAST() the string to the desired data type, so that the table can be adequately created, in line with your exact needs. Casting can be done in two straightforward notations.

First notation: the CAST function.

...
CAST('population_table' as VARCHAR(255)) as source_table,
...

Second notation: double-colon.

...
'population_table'::VARCHAR(255) as source_table,
...

Now, you won’t run into the error anymore. Great success!