Postgresql – SQL: SELECT All columns except some

postgresqlsql-standard

Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table.

Something like:

SELECT * -the_geom FROM segments;
  • I once heard that this functionality was deliberately excluded from the SQL standard because changing adding columns to the table will alter the query results. Is this true? Is the argument valid?
  • Is there a workaround, especially in PostgreSQL?

Best Answer

Such a feature exists in neither Postgres nor the SQL Standard (AFAIK). I think this is a quite interesting question so I googled a little bit and came across an interesting article on postgresonline.com.

They show an approach that selects the columns directly from the schema:

SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
        FROM information_schema.columns As c
            WHERE table_name = 'officepark' 
            AND  c.column_name NOT IN('officeparkid', 'contractor')
    ), ',') || ' FROM officepark As o' As sqlstmt

You could create a function that does something like that. Such topics were also discussed on the mailing lists, but the overall consensus was pretty much the same: query the schema.

I'm sure that there are other solutions but I think they will all involve some kind of magic schema-queriying-foo.

BTW: Be carefull with SELECT * ... as this can have performance penalties

Related Question