Select all columns except for some PostgreSQL

Question

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.

I thought of something like:

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

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?

Answer 1

The other solutions do work but if we're talking about correcting your query, this would work:

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

This will give you the list of columns you need.

Answer 2

This works:

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

Then:

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')

Result:

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

Answer 3

In case anyone from the Rails world happens to end up here (as I have), using the example of a table with 10 columns where you want to SELECT all the columns except for one, you can accomplish this like so:

SomeModelName.select(SomeModelName.column_names - ['column_1'])

instead of having to do:

SomeModelName.select(:column_2, :column_3, :column_4, :column_5, :column_6, :column_7, :column_8, :column_9, :column_10)

Answer 4

I'm not sure if this helps, but I would write the query as:

SELECT 'SELECT ' || string_agg('o' || '.' || c.column_name, ', ') || ' FROM ' || table_name || ' o' As sqlstmt
FROM information_schema.columns As c
WHERE table_name = 'office' AND
      c.column_name NOT IN ('id', 'deleted')
GROUP BY c.table_name;