使用查询在PostgreSQL中设置列类型 [英] Using query to set the column type in PostgreSQL

问题描述

优秀的答案通过Alexandre GUIDET，我尝试运行以下查询:

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抱怨语法不正确.具体来说，它说我不能写:create table egg (id (SELECT.
有什么解决方法吗?我不能将查询结果转换为文本并将其重新用作查询吗?

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?

推荐答案

有一种更简单的方法.

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

唯一的前提是模板表至少包含一行.请参见pg_typeof()上的手册

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

正如Milen所写，您需要EXECUTE这样的动态DDL语句.
一个更简单的DO语句:

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() 需要Postgres 9.1 +.

format() requires Postgres 9.1+.

相关:

• 如何检查如果表存在于给定的模式中

这篇关于使用查询在PostgreSQL中设置列类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！