PostgreSQL - check if column exists and nest condition statement

Question

Transactions column's names in below code are dynamicaly generated (so it means that sometimes particular name/column doesn't exist). Using this select it finishes successfully only in case when every of those names exists, if not, I got error like this (example):

Error(s), warning(s): 42703: column "TransactionA" does not exist

SELECT  
             *,
               ((CASE WHEN "TransactionA" IS NULL THEN 0 ELSE "TransactionA" END) - 
               (CASE WHEN "TransactionB" IS NULL THEN 0 ELSE "TransactionB" END) +
               (CASE WHEN "TransactionC" IS NULL THEN 0 ELSE "TransactionC" END)) AS "Account_balance"
        FROM Summary ORDER BY id;

Could you tell me please how can I check first if the column exists and then how can I nest another CASE statement or other condition statement to make it working in a correct way?

Answer 1

You can build any query dynamically with information from the Postgres catalog tables. pg_attribute in your case. Alternatively, use the information schema. See:

• Query to return output column names and data types of a query, table or view
• How to check if a table exists in a given schema

Basic query to see which of the given columns exist in a given table:

SELECT attname
FROM   pg_attribute a
WHERE  attrelid = 'public.summary'::regclass  --  tbl here
AND    NOT attisdropped
AND    attnum > 0
AND    attname IN ('TransactionA', 'TransactionB', 'TransactionC');  -- columns here

Building on this, you can have Postgres generate your whole query. While being at it, look up whether columns are defined NOT NULL, in which case they don't need COALESCE:

CREATE OR REPLACE FUNCTION f_build_query(_tbl regclass, _columns json)
  RETURNS text AS
$func$
DECLARE
   _expr text;
BEGIN

SELECT INTO _expr
       string_agg (op || CASE WHEN attnotnull
                              THEN quote_ident(attname)
                              ELSE format('COALESCE(%I, 0)', attname) END
                 , '')
FROM  (
   SELECT j->>'name' AS attname
        , CASE WHEN j->>'op' = '-' THEN ' - ' ELSE ' + ' END AS op
   FROM   json_array_elements(_columns) j
   ) j
JOIN   pg_attribute a USING (attname)
WHERE  attrelid = _tbl
AND    NOT attisdropped
AND    attnum > 0;

IF NOT FOUND THEN
   RAISE EXCEPTION 'No column found!'; -- or more info
END IF;

RETURN
'SELECT *,' || _expr || ' AS "Account_balance"
FROM   ' || _tbl || '
ORDER  BY id;';

END
$func$  LANGUAGE plpgsql;

The table itself is parameterized, too. May or may not be useful for you. The only assumption is that every table has an id column for the ORDER BY. Related:

• Table name as a PostgreSQL function parameter

I pass columns names and the associated operator as JSON document for flexibility. Only + or - are expected as operator. Input is safely concatenated to make SQL injection impossible.About json_array_elements():

• Query for element of array in JSON column

Example call:

SELECT f_build_query('summary', '[{"name":"TransactionA"}
                                , {"name":"TransactionB", "op": "-"}
                                , {"name":"TransactionC"}]');

Returns the according valid query string, like:

SELECT *, + COALESCE("TransactionA", 0) - COALESCE("TransactionB", 0) AS "Account_balance"
FROM   summary
ORDER  BY id;

"TransactionC" isn't there in this case. If both existing columns happen to be NOT NULL, you get instead:

SELECT *, + "TransactionA" - "TransactionB" AS "Account_balance"
FROM   summary
ORDER  BY id;

db<>fiddle here

You could execute the generated query in the function immediately and return result rows directly. But that's hard as your return type is a combination of a table rows (unknown until execution time?) plus additional column, and SQL demands to know the return type in advance. For just id and sum (stable return type), it would be easy ...

It's odd that your CaMeL-case column names are double-quoted, but the CaMeL-case table name is not. By mistake? See:

• Are PostgreSQL column names case-sensitive?

How to pass column names containing single quotes?

Addressing additional question from comment.

If someone used column names containing single quotes by mistake:

CREATE TABLE madness (
  id int PRIMARY KEY
, "'TransactionA'" numeric NOT NULL  -- you wouldn't do that ...
, "'TransactionC'" numeric NOT NULL
);

For the above function, the JSON value is passed as quoted string literal. If that string is enclosed in single-quotes, escape contained single-quotes by doubling them up. This is required on top of valid JSON format:

SELECT f_build_query('madness', '[{"name":"''TransactionA''"}
                                , {"name":"TransactionB", "op": "-"}
                                , {"name":"TransactionC"}]');  -- 

("''TransactionA''" finds a match, "TransactionC" does not.)

Or use dollar quoting instead:

SELECT f_build_query('madness', $$[{"name":"'TransactionA'"}
                                , {"name":"TransactionB", "op": "-"}
                                , {"name":"TransactionC"}]$$);

db<>fiddle here with added examples

See:

• Insert text with single quotes in PostgreSQL

Answer 2

Assuming that id is a unique id in summary, then you can use the following trick:

SELECT s.*,
       (COALESCE("TransactionA", 0) -
        COALESCE("TransactionB", 0) +
        COALESCE("TransactionC", 0)
       ) AS Account_balance
FROM (SELECT id, . . .   -- All columns except the TransactionX columns
FROM (SELECT s.*, 
             (SELECT TransactionA FROM summary s2 WHERE s2.id = s.id) as TransactionA,
             (SELECT TransactionB FROM summary s2 WHERE s2.id = s.id) as TransactionB,
             (SELECT TransactionC FROM summary s2 WHERE s2.id = s.id) as TransactionC
            FROM Summary s
           ) s CROSS JOIN
           (VALUES (NULL, NULL, NULL)) v(TransactionA, TransactionB, TransactionC)
      ) s
ORDER BY s.id;

The trick here is that the correlated subqueries do not qualify the TransactionA. If the value is defined for summary, then that will be used. If not, it will come from the values() clause in the outer query.

This is a bit of a hack, but it can be handy under certain circumstances.

Answer 3

Check this example:

UPDATE yourtable1 
SET yourcolumn = (
    CASE 
        WHEN setting.value IS NOT NULL
            THEN CASE WHEN replace(setting.value,'"','') <> '' THEN replace(setting.value,'"','') ELSE NULL END
        ELSE NULL
    END
)::TIME FROM (SELECT value FROM yourtable2 WHERE key = 'ABC') AS setting;