CODEWITHSUNDEEP
sqlpostgresql
slesh
slesh

Reputation: 2017

Postgres raises error when I try filter information_schema.columns.column_name by non-existing column name

I need to filter out columns 'x' from my table and I don't know if this column exists at all. I wrote a query:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'T'
      AND column_name <> "X"

And it throws error:

[42703] ERROR: column "X" does not exist

Why? I tried to convert _column_name_ to string - it didn't work.

What the problem?

$ psql --version
psql (PostgreSQL) 10.4 (Ubuntu 10.4-2.pgdg16.04+1)

Thank in advance!

Upvotes: 0

Views: 596

Answers (2)

Sebastian Hildebrandt
Sebastian Hildebrandt

Reputation: 2781

The problem was the double quote ...

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'T'
    AND column_name <> 'X'

This should work ...

Upvotes: 2

slesh
slesh

Reputation: 2017

Just applied a workaround like this:

select t.column_name
from (SELECT column_name :: text
      FROM information_schema.columns
      WHERE table_name = 'T') t
where t.column_name <> 'X'

Upvotes: 0

Related Questions