196

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?
2

13 Answers 13

84

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

3
  • 3
    Bigquery 'standard sql' does have except now
    – user433342
    Oct 12, 2021 at 21:47
  • 1
    The main issue with this approach is that you loose the order of the columns. As I need a resulting table with the exact same column order of the original one, this approach won't work :( Apr 20, 2023 at 21:21
  • ew, yeah I'll stick to listing columns... Dec 5, 2023 at 3:41
40

The real answer is that you just can not practically. This has been a requested feature for decades and the developers refuse to implement it.

The popular answer suggesting querying the schema tables will not be able to run efficiently because the Postgres optimizer considers dynamic functions a black box (see the test case below). That means that indexes will not be used and joins will not be done intelligently. You would be much better off with some sort of macro system like m4. At least it will not confuse the optimizer (but it may still confuse you.) Without forking the code and writing the feature yourself or using a programming language interface you are stuck.

I wrote a simple proof of concept below showing how bad performance would be with a very simple dynamic execution in plpgsql. Notice also, that below I have to coerce a function returning a generic record into a specific row type and enumerate the columns. So this method will not work for 'select all but' unless you want to remake this function for all your tables.

test=# create table atest (i int primary key);
CREATE TABLE
test=# insert into atest select generate_series(1,100000);
INSERT 0 100000

test=# create function get_table_column(name text) returns setof record as
$$
    declare r record;
    begin
    for r in execute 'select  * from ' || $1 loop
    return next r;
    end loop;
    return; 
    end; 
$$ language plpgsql; 

test=# explain analyze select i from atest where i=999999;
                                                      QUERY PLAN                                    
----------------------------------------------------------------------------------------------------
-------------------
 Index Only Scan using atest_pkey on atest  (cost=0.29..8.31 rows=1 width=4) (actual time=0.024..0.0
24 rows=0 loops=1)
   Index Cond: (i = 999999)
   Heap Fetches: 0
 Planning time: 0.130 ms
 Execution time: 0.067 ms
(5 rows)

test=# explain analyze
    select * from get_table_column('atest') as arowtype(i int) where i = 999999;
                                                        QUERY PLAN                                  
----------------------------------------------------------------------------------------------------
-----------------------
 Function Scan on get_table_column arowtype  (cost=0.25..12.75 rows=5 width=4) (actual time=92.636..
92.636 rows=0 loops=1)
   Filter: (i = 999999)
   Rows Removed by Filter: 100000
 Planning time: 0.080 ms
 Execution time: 95.460 ms
(5 rows)

As you can see the function call scanned the whole table while the direct query used the index (95.46 ms vs. 00.07ms.) These kinds of functions would tank any kind of complicated query that needed to use indexes or join tables in the right order.

5
  • 2
    Interesting perspective. This is definitely a feature for human users rather than code (or so I should hope!) so I can see the point about making the client responsible. Presumably things such as extended display (\x on) are implemented purely in the client and omitting columns should be implemented in a similar place.
    – Max Murphy
    Mar 31, 2016 at 20:08
  • The SQL statement in DrColossos' answer, though dynamically generated, doesn't depend on any variables so it would stay constant unlike your example. Surely Postgres could at least optimize a dynamically generated prepared statement?
    – Andy
    Mar 26, 2020 at 17:44
  • 2
    Why is this anyway? Static SQL starts out as text too and has to be compiled by Postgres, so I'm surprised it would use different machinery to compile dynamically generated SQL.
    – Andy
    Mar 26, 2020 at 17:47
  • @andy information_schema.columns is not constant although perhaps stable. But more to the point plpgsql Is interpreted at runtime. Postgres could 'optimize' but as I say in the beginning, they just do not want to work on a feature like this (unless things have changed.) This is just not about dynamic column selection, but all functions that do not use plain sql.
    – user17130
    Feb 24, 2021 at 4:24
  • Looking closer at the example, it seems like inefficient query plan is due to iterating over rows yielded from a plpgsql function. I assume if you have a function that just returns the dynamically generated SQL query string (could be a pure SQL function), then a client takes that string and sends it back like any other query, it would perform normally. I guess there's just no way to execute a generated string with a single pure SQL call?
    – Andy
    Feb 24, 2021 at 17:30
30

It actually is somewhat possible with PostgreSQL starting with 9.4 where JSONB was introduced. I was pondering about similar question on how to show all available attributes in Google Map (via GeoJSON).

johto on irc channel suggested to try to delete element from JSONB.

Here is the idea

select the_geom,
  to_jsonb(foo) - 'the_geom'::text attributes
from (
  select * from
  segments
) foo

While you get json instead of individual columns, it was exactly what I wanted. Perhaps json can be expanded back into individual columns.

2
  • Yeah, maybe something from here, but I haven't gotten this to work yet- stackoverflow.com/questions/36174881/…
    – chrismarx
    May 22, 2018 at 16:38
  • 2
    Expanding json back to individual columns require mentioning all column names. One can also use jsonb_agg which makes it easier to parse as similar format of returning all columns: select jsonb_agg(to_jsonb(q) - 'col3' - 'col4') ara from (select * from table1) q returns as format {'ara': [{'col1': xyz, 'col2': yzx'}, {'col1': wxy, 'col2': uvw}]} Jun 13, 2021 at 5:41
6

The only way you can (don't say you should) do that is by using dynamic sql statements. It's easy (like DrColossos wrote) to query the system views and find the structure of the table and build proper statements.

PS: Why would you want to select all/some columns without knowing/writing exactly your table structure?

4
  • 15
    Regarding your PS: Sometimes I want to query a table with geometric column, without displaying the very long geometry string which garbles the output. I don't want to specify all the columns, because there might be some dozens.
    – Adam Matan
    Mar 29, 2011 at 11:59
  • So only dynamic sql can save you from lot of typing :-).
    – Marian
    Mar 29, 2011 at 13:07
  • 10
    Everybody assumes that the one who makes the query is the one who designed the database. :-) Suppose that you need to query an old database with a lot of fields (more than 30) in order to generate an excel, but there is one or two fields that have sensitive information that you don't want to deliver.
    – yucer
    Feb 28, 2019 at 9:58
  • Yes yes, what @Adam Matan said. I also wish to exclude the columns that contain geometry.
    – Mike Finch
    Feb 5 at 16:33
5

You never see * in SQL-VIEWS... check \d any_view at your psql. There are a (introspective) preprocessing for internal representation.


All discussion here shows that the issue proposal (implicit in the question and discussions) is a syntax sugar for programmers, not a real "SQL optimization issue"... Well, my guess, it is for 80% of programmers.

So can be implemented as "pre-parsing with introspection"... See what PostgreSQL do when you declare a SQL-VIEW with SELECT *: the VIEW-constructor transforms * into a list of all columns (by introspection and at the moment that you run the CREATE VIEW source-code).

Implementation for CREATE VIEW and PREPARE

It is a viable implementation. Suppose table t with fields (id serial, name text, the_geom geom).

CREATE VIEW t_full AS SELECT * FROM t;
-- is transformed into SELECT id,name,the_geom FROM t;

CREATE VIEW t_exp_geom AS SELECT * -the_geom FROM t;
-- or other syntax as EXCEPT the_geom
-- Will be transformed into SELECT id,name FROM t;

Same for PREPARE statement.

... so, that is possible, and that is what 80% of programmers need, a syntax sugar for PREPARE and VIEWS!


NOTE: of course the viable syntax perhaps is not - column_name, if there are some conflict in PostgreSQL, so we can suggest EXCEPT column_name,
EXCEPT (column_name1, column_name2, ..., column_nameN) or other.

4

If your goal is to remove clutter from the screen during debugging by not displaying columns with large data values, then you can use the following trick:

(install "hstore" contrib package if you don't already have it:"CREATE EXTENSION hstore;")

For a table "test" with col1,col2,col3, you can set the value of "col2" to null before displaying:

select (r).* from (select (test #= hstore('col2',null)) as r from test) s;

Or, set two columns to null before displaying:

select (r).* from (select (test #= hstore('col2',null) #= hstore('col1',null)) as r from test) s;

the caveats is that "test" has to be a table (an alias or subselect won't work) since the record type feeding into hstore must be defined.

4

There's a workaround I have just discovered, but it requires to send SQL queries from within R. It may be of use to R users.

Basically the dplyr package sends SQL (and specifically PostgreSQL) queries and accepts the -(column_name) argument.

So your example could be written as follows:

select(segments, -(the_geom))
4

In a comment you explain that your motive is to have the convenience of not displaying the contents of columns with long content, rather than not displaying the column itself:

…Sometimes I want to query a table with geometric column, without displaying the very long geometry string which garbles the output. I don't want to specify all the columns, because there might be some dozens.

This is possible, with the aid of a helper function which replaces the long content with null (any text column in my example, but you would modify that for the types you want to suppress):

create table my_table(foo integer, bar integer, baz text);
insert into my_table(foo,bar,baz) values (1,2,'blah blah blah blah blah blah'),(3,4,'blah blah');
select * from my_table;
foo | bar | baz                          
--: | --: | :----------------------------
  1 |   2 | blah blah blah blah blah blah
  3 |   4 | blah blah                    
create function f(ttype anyelement) returns setof anyelement as
$$
declare
  toid oid;
  tname text;
  nname text;
  cols text;
begin
  --
  select pg_type.oid, pg_namespace.nspname, pg_type.typname
  into toid, nname, tname
  from pg_type join pg_namespace on pg_namespace.oid=pg_type.typnamespace
  where pg_type.oid=pg_typeof(ttype);
  --
  select string_agg((case when data_type<>'text' 
                          then column_name 
                          else 'null::'||data_type||' "'||column_name||'"' end)
                   ,', ' order by ordinal_position)
  into cols
  from information_schema.columns 
  where table_schema=nname and table_name=tname;
  --
  return query execute 'select '||cols||' from '||nname||'.'||tname;
  --
end
$$ language plpgsql;
select * from f(null::my_table);
foo | bar | baz 
--: | --: | :---
  1 |   2 | null
  3 |   4 | null

dbfiddle here

3

Dynamically as stated above is the only answer but I won't recommend it. What if you add more columns in the long run but they are not necessarily required for that query?

You would start pulling more column than you need.

What if the select is part of an insert as in

Insert into tableA (col1, col2, col3.. coln) Select everything but 2 columns FROM tableB

The column match will be wrong and your insert will fail.

It's possible but I still recommend writing every needed column for every select written even if nearly every column is required.

1
  • This approach is obviously programmatically wrong, but it is harmless and useful as a console query for SELECTs.
    – Adam Matan
    Mar 29, 2011 at 13:58
3
  • From an application perspective, this is a lazy solution. An application is unlikely to automatically know what to do with the new column(s).

    Data browser applications may query the metadata for the data and exclude the columns from the queries being run, or select a subset of the column's data. New BLOBs can be excluded when added. BLOB data for particular rows can be selected on demand.

  • In any SQL variant which supports dynamic queries, the query can be built using a query on the tables meta data. For your intent, I would exclude columns based on type rather than name.

3

drop if the temporary table exists

DROP TABLE IF EXISTS temp_tb;

create a temporary table named temp_tb using 'as'

CREATE TEMPORARY TABLE temp_tb AS

select the whole table from which you want to delete certain columns

  TABLE public.nowcast_data_table_with_data_as_date_type;

drop all the columns which you want to drop like here I have removed geometry and state_id

  ALTER TABLE temp_tb DROP geometry, DROP state_id ;

select the data if it is the desired table which you wanted or not?

  SELECT * FROM temp_tb;

whole SQL will be

DROP TABLE IF EXISTS temp_tb;
CREATE TEMPORARY TABLE temp_tb AS
  TABLE public.nowcast_data_table_with_data_as_date_type;
  ALTER TABLE temp_tb DROP geometry, DROP state_id ;
  SELECT * FROM temp_tb;
1

This is my function to select all columns expect one. I combined ideas from postgresonline.com and postgresql tuturial and from other sources.

CREATE TABLE phonebook(phone VARCHAR(32), firstname VARCHAR(32),
lastname VARCHAR(32), address VARCHAR(64));
INSERT INTO phonebook(phone, firstname, lastname, address) 
VALUES ('+1 123 456 7890', 'John', 'Doe', 'North America'), 
('+1 321 456 7890', 'Matti', 'Meikeläinen', 'Finland'), 
('+1 999 456 7890', 'Maija', 'Meikeläinen', 'Finland'), 
('+9 123 456 7890', 'John', 'Doe', 'Canada'), 
('+1 123 456 7890', 'John', 'Doe', 'Sweden'), 
('+1 123 456 7890', 'John', 'Doe2', 'North America');

drop function all_except_one(text,text);
CREATE OR REPLACE FUNCTION all_except_one(to_remove TEXT, table_name1 TEXT) 
RETURNS void AS $$

 DECLARE 
 rec_row RECORD;
 curs1 refcursor ;

 BEGIN
  --print column names:
  raise notice '%', ('|'|| ARRAY_TO_STRING(ARRAY(SELECT 
  COLUMN_NAME::CHAR(20) FROM INFORMATION_SCHEMA.COLUMNS WHERE
  TABLE_NAME=table_name1 AND COLUMN_NAME NOT IN (to_remove) ), 
  '|') ||'|') ; 

  OPEN curs1 FOR
  EXECUTE 'select table_1  from (SELECT ' || ARRAY_TO_STRING(ARRAY(
  SELECT COLUMN_NAME::VARCHAR(50) FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME=table_name1 AND COLUMN_NAME NOT IN (to_remove)    
  ), ', ') || ' FROM ' || table_name1 || ' limit 30)   table_1 ';

  LOOP
  -- fetch row into the rec_row
  FETCH curs1 INTO rec_row;

  -- exit when no more row to fetch
  EXIT WHEN NOT FOUND;

  -- build and print the row output

  raise notice '%',(select'| '|| regexp_replace( array_to_string(
  array_agg(a::char(20)),'|'),'["\(.*\)]+',   '','g') ||'|'  from 
  unnest(string_to_array(replace(replace(replace(trim(rec_row::text,
  '()'),'"',''), ', ','|'),')',' '),',')) as a);

  END LOOP;

  -- Close the cursor

  CLOSE curs1;

  END; $$ LANGUAGE plpgsql;

select  all_except_one('phone','phonebook');

--output:
--NOTICE:  |firstname           |lastname            |address             |
--NOTICE:  | John               |Doe                 |North America       |
--NOTICE:  | Matti              |Meikeläinen         |Finland             |
--NOTICE:  | Maija              |Meikeläinen         |Finland             |
--NOTICE:  | John               |Doe                 |Canada              |
--NOTICE:  | John               |Doe                 |Sweden              |
--NOTICE:  | John               |Doe2                |North America       |
-- all_except_one 
-- ----------------
-- (1 row)
1

This is possible using DuckDB, as described here. DuckDB is an in-process analytics database, something like SQLite, but optimized for analytical queries.

DuckDB can be used to query other databases (similar to the foreign server protocol in Postgres), hence you can use its new convenient syntax for executing queries against your underlying Postgres database.

Here is an example:

-- establish connection to your Postgres database
ATTACH '<you_database_uri>' AS my_db (TYPE postgres);

-- select all columns except `etl_date`
SELECT * EXCLUDE (etl_date)
FROM my_db.my_schema.my_table;

Actually, DuckDB is not just limited to the EXCLUDE logic shown above, you can select all columns matching a regular expression a lambda function.

More convenient query helpers are documented here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.