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

guettli
  • 1,521
  • 5
  • 24
  • 49
  • 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: https://github.com/dbcli/pgcli –  Sep 15 '15 at 09: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
  • @ChengchengPei the column name could be foo_col :-) – guettli Sep 16 '15 at 14:31
  • Why not just do DESC on your table, cut/paste the column list and then eliminate the one you want? That's the easiest I think.

    http://stackoverflow.com/questions/11341192/return-all-the-columns-in-a-mysql-table-in-a-string-format can help...

     – BAE Sep 16 '15 at 20:22
  • 1
    @ChengchengPei: there is no desc command in psql –  Sep 16 '15 at 20:30
  • Sorry for that. But there should be similar syntax in psql. – BAE Sep 16 '15 at 21:30

2 Answers2

5

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:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • 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.

Unbound
  • 1
  • 1