CODEWITHSUNDEEP
sqlpostgresql
user3206440
user3206440

Reputation: 5049

SQL - conditionally select a column if exists

I need to select a column only if it exists in table, else it can be set to null.

Sample table below, lets say the marks col is not necessary be there, so need to be checked if it exists

Table1:

name marks
joe  10
john 11
mary 13

Query:

select
    name,
    marks if it exists else null as marks1 -- pseudo code
from 
    table1

What should go in line to select marks ?

Upvotes: 13

Views: 18751

Answers (5)

Simon Kissane
Simon Kissane

Reputation: 5258

It is possible to achieve this in PostgreSQL using JSON. Consider the following SQL query:

SELECT c.relname, c.relkind, c.relispartition
FROM pg_class c
WHERE c.relkind IN ('r','p') AND
      c.relnamespace=(SELECT oid FROM pg_namespace WHERE nspname='public')

In PostgreSQL 10+, that will show you the names of all the tables in public schema, including whether they are partitioned and if so whether the table is the partitioned table or one of the partitions of it. However, if you try to run the same query on PostgreSQL 9.6 or earlier, it will fail since the relispartition column does not exist on the pg_class table prior to PostgreSQL 10.

An obvious solution would be to dynamically generate the SQL based on a condition, or have two different versions of the SQL. However, suppose you don't want to do that, you want to have a single query which works on both versions – in other words, you want to conditionally select the relispartition column if it exists.

The core SQL language does not have any facility to conditionally select a column, but it is achievable in PostgreSQL using the row_to_json function, as follows:

SELECT c.relname, c.relkind,
       (row_to_json(c)->>'relispartition')::boolean AS relispartition
FROM pg_class c
WHERE c.relkind IN ('r','p') AND
      c.relnamespace=(SELECT oid FROM pg_namespace WHERE nspname='public')

If you try running that, you will find on PostgreSQL 10+ the relispartition column is returned as true/false, whereas in pre-10 versions it is NULL. You could make it return false instead of NULL in pre-10 versions by doing COALESCE((row_to_json(c)->>'relispartition')::boolean,false).

What this is doing, is row_to_json(c) turns all the data of the row into JSON. Next, ->>'relispartition' selects the value of the relispartition JSON object key as text, which will be the same as the value of the relispartition column; if there is no such key in the JSON, the result of that will be NULL. Then, ::boolean converts the string value true/false back into a PostgreSQL boolean value. (If your column is of some other type, use the appropriate cast for the type of your column.)

(Obviously this approach will not work in Postgres versions which are too old to have the necessary JSON support – I have tested it works in Postgres 9.4; while I haven't tested it in Postgres 9.3, it probably works there. However, I would not expect it to work in 9.2 or earlier – the ->> operator was added in 9.3, and the JSON type and row_to_json function was added in 9.2. However, I expect few people will need to support those old unsupported versions–9.3 was released in 2013, and 9.2 supported ended in 2017.)

Upvotes: 2

Lean Bravo
Lean Bravo

Reputation: 371

Replying to an old question yet again but here's my hacky solution to this problem since I don't know how to write SQL functions... yet! %I formats the string as an identifier, and if there is no such table the return value is NULL and the alias is used!

SELECT (SELECT format('%I', 'my_column')
 AS my_column_alias
 FROM information_schema.columns
 WHERE table_name='my_table'
 AND column_name='my_column') 
FROM source_table

Hope this helps everybody out there =)

Upvotes: 0

Evan Carroll
Evan Carroll

Reputation: 1

SQL Doesn't permit that. Your result set has two options:

  1. Static inclusion
  2. All from table or subquery through column-expansion with * and tbl.*

Perhaps this will suit your needs, SELECT * FROM table1; You'll always get that column, if it exists.

Upvotes: 5

Muhammad Usman
Muhammad Usman

Reputation: 10148

try this

IF COL_LENGTH('your_table_name','column_name_you_want_to_select') IS NULL BEGIN 
 --This means columns does not exist or permission is denied
 END
 else 
 --Do whatever you want

Upvotes: 1

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

Try this:

IF EXISTS( SELECT 1
           FROM information_schema.columns 
           WHERE table_name='your_table' and column_name='your_column') THEN
   SELECT your_column as 'some_column'
ELSE
   SELECT NULL as 'some_column'
END IF

Upvotes: 0

Related Questions