Using query to set the column type in PostgreSQL

Question

After the excellent answer by Alexandre GUIDET, I attempted to run 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 incorrect syntax. Specifically it says that I cannot write: create table egg (id (SELECT.
Are there any workarounds? Can't I convert the result of a query to text and reuse it as a query?

Community · Accepted Answer · 2017-05-23 12:07:22Z

There is a much simpler way to do that.

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

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

As Milen wrote, you need to EXECUTE dynamic DDL statements like this.
A much 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 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 look for a specific column any

format() requires Postgres 9.1+.

How to check if a table exists in a given schema

Milen A. Radev · Accepted Answer · 2010-12-02 15:48:02Z

1

You can either convert that query 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$$;

answered Dec 2, 2010 at 15:48

Milen A. Radev

63.1k22 gold badges111 silver badges112 bronze badges

Comments

nate c · Accepted Answer · 2010-12-03 00:29:10Z

0

You can either have a table a definition or a query, but not both. Maybe your thinking of the select into command.

answered Dec 3, 2010 at 0:29

nate c

9,0532 gold badges29 silver badges28 bronze badges

Collectives™ on Stack Overflow

Using query to set the column type in PostgreSQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related