Using a query to set a column type in PostgreSQL

Question

After a great answer from Alexandre GUIDET, I tried to execute the following query:

create table egg (id (SELECT pg_catalog.format_type(a.atttypid, a.atttypmod) as Datatype FROM pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = ( SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(TABLENAME)$' AND pg_catalog.pg_table_is_visible(c.oid) ) and a.attname = 'COLUMNNAME')); 

PostgreSQL, however, complains about the wrong syntax. In particular, he says that I cannot write: create table egg (id (SELECT .
Are there any workarounds? Can I convert the result of a query to text and reuse it as a query?

Answer 1

There is a much easier way.

 SELECT pg_typeof(col)::text FROM tbl LIMIT 1 

Only the precondition is that the template table contains at least one row. See the manual at pg_typeof ()

As Mylene wrote, you need EXECUTE dynamic DDL statements like this.
A simpler DO statement:

 DO $$BEGIN EXECUTE 'CREATE TABLE egg (id ' || (SELECT pg_typeof(col)::text FROM tbl LIMIT 1) || ')'; END$$; 

Or, if you are not sure that the template table has any rows:

 DO $$BEGIN EXECUTE ( SELECT format('CREATE TABLE egg (id %s)' , format_type(atttypid, atttypmod)) FROM pg_catalog.pg_attribute WHERE attrelid = 'tbl'::regclass -- name of template table AND attname = 'col' -- name of template column AND attnum > 0 AND NOT attisdropped ); END$$; 

These conditions seem redundant since you are looking for a specific column any

format() requires Postgres 9.1 +.

on this topic:

• How to check if a table exists in a given schema

Answer 2

You can convert this request to a function or (if you have Postgres 9.0) to an anonymous code block :

 DO $$DECLARE the_type text; BEGIN SELECT ... AS datatype INTO the_type FROM <the rest of your query>; EXECUTE 'create table egg ( id ' || the_type || <the rest of your create table statement>; END$$; 

Answer 3

You may have a definition or query table, but not both. Perhaps you are thinking of the select into command.