108

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?
  • Which is the use-case for which you want to know all columns except some? Is it just to show on-screen while doing some manual queries? Is it part of a program? – joanolo Dec 26 '16 at 23:05
  • 2
    A table with 6 meaningful, short columns (a-la name, age, sid) that fits nicely into the screen width, alongwith a long binary geom column. I want to query all fields except the geometry binary, and writing their names one by one is tedious. – Adam Matan Dec 27 '16 at 7:28
  • In that case, this might be more something to do with the tool you're using with the interactive query than with SQL itself... – joanolo Dec 27 '16 at 7:49
  • 1
    @joanolo Plain PostgreSQL shell. – Adam Matan Dec 27 '16 at 8:04
  • 3
    This looks so obvious. Sometimes you don't want to print one or two columns because they are not interesting or you just want the result table to fit the screen (especially if a command line client is used). I would expect a syntax like select (!coluns2,!column5) from sometable; – gumkins Jun 22 '17 at 10:41

11 Answers 11

54

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

  • How to create such function? I cannot find any way to create a function that returns an unknown query I always would have to declare a table beforehand. – ePascoal Jun 4 '18 at 23:27
17

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.

  • 1
    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
13

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,
  row_to_json(foo)::jsonb - '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.

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?

  • 7
    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
  • 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 at 9:58
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.

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

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.

3

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

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

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

2

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.

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)

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.