11

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

Improve this question
8
  • 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
    Commented Sep 11, 2015 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
    – user1822
    Commented Sep 15, 2015 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
    Commented Sep 16, 2015 at 11:15
  • except which column? the last one? the first one? Or random one?
    – BAE
    Commented Sep 16, 2015 at 14:02
  • 1
    @ChengchengPei: there is no desc command in psql
    – user1822
    Commented Sep 16, 2015 at 20:30

2 Answers 2

Reset to default
5
+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:

Improve this answer
3
  • 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
    Commented Sep 17, 2015 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
    Commented Sep 17, 2015 at 22:53
  • I give you the bounty. But something built-in into psql would be really a nice feature. Thank you Erwin :-)
    – guettli
    Commented Sep 21, 2015 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.

Improve this answer

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.