CODEWITHSUNDEEP
postgresqlgo
Alexey
Alexey

Reputation: 2812

PostgreSQL, allow to filter by not existing fields

I'm using a PostgreSQL with a Go driver. Sometimes I need to query not existing fields, just to check - maybe something exists in a DB. Before querying I can't tell whether that field exists. Example:

where size=10 or length=10

By default I get an error column "length" does not exist, however, the size column could exist and I could get some results.

Is it possible to handle such cases to return what is possible?

EDIT:

Yes, I could get all the existing columns first. But the initial queries can be rather complex and not created by me directly, I can only modify them.

That means the query can be simple like the previous example and can be much more complex like this:

WHERE size=10 OR (length=10 AND n='example') OR (c BETWEEN 1 and 5 AND p='Mars')

If missing columns are length and c - does that mean I have to parse the SQL, split it by OR (or other operators), check every part of the query, then remove any part with missing columns - and in the end to generate a new SQL query?

Any easier way?

Upvotes: 0

Views: 1054

Answers (3)

Laurenz Albe
Laurenz Albe

Reputation: 246188

There is no way to do what you want, except for constructing an SQL string from the list of available columns, which can be got by querying information_schema.columns.

SQL statements are parsed before they are executed, and there is no conditional compilation or no short-circuiting, so you get an error if a non-existing column is referenced.

Upvotes: 1

Illia
Illia

Reputation: 374

Why don't you get a list of columns that are in the table first? Like this

select column_name 
from information_schema.columns 
where table_name = 'table_name' and (column_name = 'size' or column_name = 'length');

The result will be the columns that exist.

Upvotes: 1

Carlos Condore
Carlos Condore

Reputation: 26

I would try to check within information schema first

"select column_name from INFORMATION_SCHEMA.COLUMNS where table_name ='table_name';"

And then based on result do query

Upvotes: 1

Related Questions