sql - PostgreSQL:选择给定类型的表列

Question

假设我有一个表 table1，我可以得到所有列的列表，比方说，double precision 类型:

SELECT column_name 
FROM information_schema.columns
WHERE data_type = 'double precision' 
  AND table_name = 'table1';

我怎样才能只选择这些列？

Answer 1

这个问题类似于 SELECT * 但 * 也应该识别类型。

CREATE TABLE table1(ID SERIAL,col1 INT, col2 double precision,
                    col3 VARCHAR(100), col4 double precision);

INSERT INTO table1(col1,col2,col3,col4)
VALUES (1,2.0,'a',4.0), (10, 3.0, 'b', NULL);

在我看来，正确的方法是在 SELECT 中指定列名:

SELECT col2, col4
FROM table1;

为了使这更容易，您可以编写简单的查询来为您生成列列表:

;WITH cte(data_type, tab_name) AS
(
   SELECT 'double precision'::text, 'table1'::text
)
SELECT *
    ,FORMAT('SELECT %s FROM %s',
         COALESCE((SELECT string_agg(column_name, ',')
          FROM information_schema.columns
          WHERE data_type = c.data_type
            AND table_name = c.tab_name), '*')
          ,c.tab_name) AS result_query
FROM cte c;    

SqlFiddleDemo

输出:

╔═══════════════════╦═══════════╦══════════════════════════════╗
║    data_type      ║ tab_name  ║         result_query         ║
╠═══════════════════╬═══════════╬══════════════════════════════╣
║ double precision  ║ table1    ║ SELECT col2,col4 FROM table1 ║
╚═══════════════════╩═══════════╩══════════════════════════════╝

当然你可以玩动态sql，甚至用函数包装它，但我不会走这条路。