9

I search for a simple way to select all columns except one in psql.

With psql I mean the interactive command line.

I would be happy with a tool that expands to * to a list of quoted column names. Then I could remove the column to remove by hand.

My question is just about the interactive usage of psql. It is not a duplicate of questions of people unhappy with the sql standard and who want to execute something like "select *-foo".

  • 3
    There isn't one, sorry. You can query information_schema via a pl/pgsql function to generate a list pretty easily, but that way it'd be two queries. – Craig Ringer Sep 11 '15 at 12:50
  • 2
    Most GUI tools can do that. But if you want to (or have to) stick to the command line, maybe this will help: github.com/dbcli/pgcli – a_horse_with_no_name Sep 15 '15 at 9:54
  • @a_horse_with_no_name I would like to accept your "answer". Could you please post an answer and explain the usage of the pgcli tool solving the question. – guettli Sep 16 '15 at 11:15
  • except which column? the last one? the first one? Or random one? – BAE Sep 16 '15 at 14:02
  • 1
    @ChengchengPei: there is no desc command in psql – a_horse_with_no_name Sep 16 '15 at 20:30
4
+50

To get the list of columns in default order, without the bad column:

SELECT string_agg(quote_ident(attname), ', ' ORDER BY attnum)
FROM   pg_attribute
WHERE  attrelid = 'myschema.mytable'::regclass
AND    NOT attisdropped  -- no dropped (dead) columns
AND    attnum > 0        -- no system columns
AND    attname <> 'bad_column'  -- case sensitive!

Or just WHERE attrelid = 'mytable'::regclass if you trust the search path to resolve to the right schema.

quote_ident() adds double-quotes where necessary.

I asked the same question in 2007 on pgsql-general. It was Postgres 8.2 back then. Sweet memories ...

Related:

  • 1
    It sounds like a to-do item, to add a backslash command to psql which will emit a string which is a list of all the columns for a table, appropriately quoted. And you could have it omit any columns which were included as extra arguments to the command. So hypothetically \dq thetable bad_column. – jjanes Sep 17 '15 at 17:40
  • @jjanes: I am not sure it can generate enough demand to convince core developers to allocate the free letter q for the task. Maybe a general option to the whole family of \d commands to show a bare list of concatenated names? Like \d- tbl (mnemonic: the opposite of \d+, which shows more details). This would allow to get basically any bare list of objects. Tables in a schema: \dt- public.*, functions starting with "f_foo": \df- f_foo* etc. - or columns in a table: \d- mytbl. Similar to what the shell command ls does ... – Erwin Brandstetter Sep 17 '15 at 22:53
  • I give you the bounty. But something built-in into psql would be really a nice feature. Thank you Erwin :-) – guettli Sep 21 '15 at 15:32
0

I think I have found what you are looking for but I have not tested it myself.

There is a software called SequelPro that would allow you to select the fields from a given table but its only for MySQL. Please read from the below from somewhere in the middle of the page:

http://www.sequelpro.com/docs/Working_with_Query_Favorites

There is another software called PSequel which only runs on Mac and it claims it is of type SequelPro built for Postgres:

http://www.psequel.com/

Hope this helps.

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.