Postgresql – psql: SELECT * … except one column

postgresqlpsql

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

Best Answer

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

How to check if a table exists in a given schema

Related Solutions

Postgresql – the purpose of the keyword “ID” in PostgreSQL

In PostgreSQL, inserting a column named

"ID"

is one thing. Inserting a column named

ID

is another.

create table test (
  "ID" integer not null
);

insert into test values (1);

select *
from test
where ID = 1;

ERROR: column "id" does not exist

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case.

Postgresql – psql asks for password, CREATE USER didn’t specify one

A user having a password or not won't affect if you're asked for a password when trying to connect. That is controlled by the pg_hba.conf file.

If you're being asked for a password that indicates that a password is required. If you try to connect to an account which doesn't have a password set that will simply fail. You need to either set a password for the account and use that, use a different type of connection or modify the pg_hba.conf file to use a different authentication method.

Best Answer

Related Solutions

Postgresql – the purpose of the keyword “ID” in PostgreSQL

Postgresql – psql asks for password, CREATE USER didn’t specify one

Related Question