选择除某些 PostgreSQL 之外的所有列 [英] Select all columns except for some PostgreSQL

问题描述

我必须比较表格，但有些列我不需要比较，我只知道它们(不是我必须比较的那些)所以我想从表中选择所有列，除了我不知道的列不需要比较.

I have to compare tables but there are some columns which I don't need to compare and I only know them (not the ones I have to compare) so I want to select all columns from table except the ones that I don't need to compare.

我想到了类似的东西:

SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
    FROM information_schema.columns As c
        WHERE table_name = 'office' 
        AND  c.column_name NOT IN('id', 'deleted')
), ',') || ' FROM officeAs o' As sqlstmt

然而输出是 SELECT * FROM office As o而不是 select a,b,c from office 没有 id and deleted 列.

however the output was SELECT * FROM office As o instead of being select a,b,c from office without id and deleted columns.

有人知道这个查询有什么问题吗?

Does anyone have any ideas what's wrong with this query?

推荐答案

这有效:

CREATE TABLE public.office(id INTEGER, a TEXT, b TEXT, 删除 BOOLEAN, c TEXT)

那么:

SELECT 'SELECT ' || STRING_AGG('o.' || column_name, ', ') || ' FROM office AS o'
FROM information_schema.columns
WHERE table_name = 'office'
AND table_schema = 'public'
AND column_name NOT IN ('id', 'deleted')

结果:

SELECT o.a, o.b, o.c FROM office AS o

这篇关于选择除某些 PostgreSQL 之外的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！