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?

11 Answers 11


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


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);
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;
    for r in execute 'select  * from ' || $1 loop
    return next r;
    end loop;
$$ 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.

  • 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 '16 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 '20 at 17:44
  • 1
    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 '20 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 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 at 17: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
) foo

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


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?

  • 9
    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 '11 at 11:59
  • So only dynamic sql can save you from lot of typing :-). – Marian Mar 29 '11 at 13:07
  • 3
    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 '19 at 9:58

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

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

-- 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.


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
  toid oid;
  tname text;
  nname text;
  cols text;
  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;
$$ language plpgsql;
select * from f(null::my_table);
foo | bar | baz 
--: | --: | :---
  1 |   2 | null
  3 |   4 | null

dbfiddle here


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.

  • This approach is obviously programmatically wrong, but it is harmless and useful as a console query for SELECTs. – Adam Matan Mar 29 '11 at 13:58

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.

  • 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.


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 $$

 rec_row RECORD;
 curs1 refcursor ;

  --print column names:
  raise notice '%', ('|'|| ARRAY_TO_STRING(ARRAY(SELECT 
  TABLE_NAME=table_name1 AND COLUMN_NAME NOT IN (to_remove) ), 
  '|') ||'|') ; 

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

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

  -- exit when no more row to fetch

  -- build and print the row output

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


  -- Close the cursor

  CLOSE curs1;

  END; $$ LANGUAGE plpgsql;

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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