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,591
  • 5
  • 25
  • 51

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
  • 185,527
  • 28
  • 463
  • 633
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