April 19th, 2010 by depesz | Tags: , , , , , | 13 comments »
Did it help? If yes - maybe you can help me?

One of the most common questions is “how do I get select * from table, but without one of the column".

Short answer is of course – name your columns, instead of using *. Or use a view.

But I decided to take a look at the problem.

I haven't found a way to hide column – although it might be possible to do it, but would require a bit heavier wizardry. But, I have found a way to hide values in a column.

For example – let's assume that I'd like to view pg_proc table, but without prosrc column which is usually too big.

So, I wrote this function:

CREATE OR REPLACE FUNCTION hide_column( in_table regclass, VARIADIC in_column TEXT[] ) RETURNS setof record as $$
DECLARE
    v_sql TEXT;
    v_table record;
    v_table_name TEXT;
    temprec record;
BEGIN
    SELECT c.relname, n.nspname INTO v_table FROM pg_class c join pg_namespace n on c.relnamespace = n.oid WHERE c.oid = in_table;
 
    for temprec in SELECT a.attname, t.typname FROM pg_attribute a join pg_type t on a.atttypid = t.oid WHERE a.attrelid = in_table AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum loop
        v_sql := coalesce( v_sql || ', ', 'SELECT ' );
        IF temprec.attname = ANY( in_column ) THEN
            v_sql := v_sql || 'NULL::' || quote_ident( temprec.typname ) || ' as ' || quote_ident( temprec.attname );
        ELSE
            v_sql := v_sql || quote_ident( temprec.attname );
        END IF;
    END LOOP;
    v_table_name := quote_ident( v_table.nspname ) || '.' || quote_ident( v_table.relname );
    v_sql := v_sql || ' FROM ' || v_table_name;
    v_sql := 'SELECT x::' || v_table_name || ' as rec FROM (' || v_sql || ') as x';
    RETURN QUERY EXECUTE v_sql;
END;
$$ language plpgsql;

No, I will not explain how it works – mostly because I don't really want anybody to use it. But if you have this function you can:

SELECT (rec).*
FROM hide_column('pg_proc', 'prosrc', 'probin', 'proowner', 'proargnames' ) as q (rec pg_proc);

Of course this is still to wide to show in here, so let's instead test it on some smaller table:

CREATE TABLE test_it (
    a TEXT,
    b TEXT,
    c TEXT,
    d TEXT,
    e TEXT
);
INSERT INTO test_it
    SELECT
        'column a : ' || i,
        'column b : ' || i,
        'column c : ' || i,
        'column d : ' || i,
        'column e : ' || i
    FROM
        generate_series(1,3) i;

It looks like this:

SELECT * FROM test_it;
      a       |      b       |      c       |      d       |      e       
--------------+--------------+--------------+--------------+--------------
 column a : 1 | column b : 1 | column c : 1 | column d : 1 | column e : 1
 column a : 2 | column b : 2 | column c : 2 | column d : 2 | column e : 2
 column a : 3 | column b : 3 | column c : 3 | column d : 3 | column e : 3
(3 rows)

And now let's use my magic function:

# select (rec).* from hide_column('test_it', 'b', 'd') as i(rec test_it);
      a       | b |      c       | d |      e       
--------------+---+--------------+---+--------------
 column a : 1 |   | column c : 1 |   | column e : 1
 column a : 2 |   | column c : 2 |   | column e : 2
 column a : 3 |   | column c : 3 |   | column e : 3
(3 rows)

Of course it doesn't look nice (invocation of the function), but that's not the point. The point is that it's possible.

  1. 13 comments

  2. Apr 20, 2010

    It would be better if normal SELECT syntax could just say what you don’t want; eg:

    select * except b, d from foo

    Surely Postgres could support such a syntax extension, even if the SQL standard had allegedly refused to.

  3. # Miguel A.
    Apr 20, 2010

    On the other side I think it would be better to remove “select *” from the SQL standard. πŸ˜€

  4. Apr 20, 2010

    It is nice example of hard coded idiocy :). You remove useless reading, but you store complete result in MEMORY – so it is useful only for small sets (10000 rows).

    to Duncan: Your idea is wrong. Wrong designers have to suffer when he designs too wide tables.

  5. Apr 20, 2010

    @MIGUEL A.

    Yes, having to say “select * from foo” is very stupid; one should just be able to say “foo” to mean “just get foo”. And in Muldis D that’s just how it works. But that’s an old SQL problem.

  6. Apr 20, 2010

    @PAVEL STEHULE

    The point of that one should be able to say something like “select all but a, b from baz” is simply a matter of language completeness.

    When selecting rows, you can put a “not” in front of any filter expression in a where clause to get the complement of rows.

    Similarly, one should be able to effectively put “not” in the select list to get the complement of the named rows.

    Arguing against this being useful is like arguing against “not” being useful.

  7. Apr 20, 2010

    In my second to last sentence I meant to say “complement of the named columns”.

  8. Apr 20, 2010

    @Darren Duncan:
    It could be nice addition – especially for people who design too wide tables, but it’s not yet here, so there is no point in arguing over it.

  9. Apr 20, 2010

    @Pavel:
    Thank you. πŸ™‚

    Well, sure it’s idiotic. Of course you could further modify the function to allow addition of custom where/order/limit/… clauses πŸ™‚

    And no, I will not do it.

  10. Apr 20, 2010

    Depesz: The are two problems: a) RETURN QUERY implementation (use only memory), b) planner – plpgsql is black box for planner, c) application stability – using a explicit list of columns add some order to returned data. Without it, the column’s order is “random” (you can change it via ALTER TABLE, CREATE TABLE, ..). What I know, the advice of not using * is based first on source code maintainability. With PostgreSQL is more important, using TOAST columns can slowness query.

  11. Neat for tables with few rows and a couple of columns you want to exclude, which sadly is a bit to specific for me to have any use for me. I have a 200K row table with a column with ~20K of binary in each row in it that I often want to leave out when peeking at it. And I’m happy you didn’t explain that thing, if I ever catch anyone using something like this in production I’ll throw them out the nearest window. Don’t worry, I work on the ground floor. πŸ™‚

    What I’d rather want is an option for psql to truncate or replace wide columns of a type without size restriction with their size or a hash, not to mention something like that for DML statement logging in PostgreSQL. Oh well.

  12. # someguy
    Apr 20, 2010

    @Thor

    Oracle’s SQL*Plus has similar functionality to what you describe in your last sentence. Probably hackable into psql.

  13. Apr 22, 2010

    @depesz much nicer than similar function I wrote on 7.3 once πŸ˜‰

    @darren in sql spec, you can do “table foo”, which will return all of foo. better than select * I guess.

    @pavel I don’t understand how you can miss the point so much when depesz gives a perfect example. this really isnt about too wide table definitions, its often more about 1 column which has large text data in it which makes result display harder. pg_proc.prosrc is great example.

    btw, i’ve often though select *, !bar from baz would be simple enough syntax to remove unwanted columns, but sadly we’re probably unlikey to see anything until sql std defines it.

  14. # Pete Yunker
    May 13, 2010

    This function would actually be extremely useful for PostGIS users. Surely every developer runs numerous SELECT * queries throughout the day to view sample data from a table. Unfortunately, geometry columns have an extremely wide internal representation. This doesn’t present an issue when running a SELECT * in any good graphical SQL client as column widths can be set and long data values are not wrapped. However, when run in psql, the value in the geometry field will wrap and generally fill up the entire terminal window. This type of function would be quite useful to me for quickly viewing data in psql for spatially enabled tables minus the geometry field.

Leave a comment