CODEWITHSUNDEEP
postgresql
cjslv
cjslv

Reputation: 377

select all except for a specific column

I have a table with more than 20 columns, I want to get all columns except for one which I'll use in a conditional expression. 

SELECT s.* (BUT NOT column1), 
   CASE WHEN column1 is null THEN 1 ELSE 2 END AS column1 
from tb_sample s;

Can I achieve it in postgresql given the logic above?

Upvotes: 5

Views: 7981

Answers (2)

Karl
Karl

Reputation: 1

I have created a request for my own use, that I use in 2 times :

  1. I use postgres to generate my request
  2. I copy the result of this request, change it if necessary

Don't know if I can change it into a function, because in most of time I need to change a column type (for exemple, transforming the SCR or 'the_geom")

WITH param AS (
    SELECT 
        'public' AS my_schema, -- enter the schema name
        'my_table' AS my_table, -- enter the table name
        ARRAY['the_geom'] AS excluded_fields -- enter the field(s) you want to exclude, separated with commas
)
SELECT format('SELECT %s FROM %I.%I;', 
              string_agg(quote_ident(column_name), ', '), 
              param.my_schema, 
              param.my_table)
FROM information_schema.columns, param
WHERE table_schema = param.my_schema
AND table_name = param.my_table
AND column_name <> ALL (param.excluded_fields )
GROUP BY param.my_schema, param.my_table;

Upvotes: 0

Ryan Wittrup
Ryan Wittrup

Reputation: 129

It may not be ideal, but you can use information_schema to get the columns and use the column to exclude in the where clause.

That gives you a list of all the column names you DO want, which you can copy/paste into your select query:

select textcat(column_name, ',')
from information_schema.columns
where table_name ='table_name' and column_name !='column_to_exclude';

Upvotes: 2

Related Questions