0

I want to update all columns named with suffix '[]' in the table geochem if they have zeros (0) with NULL.

This query returns all relevant column names:

select c.COLUMN_NAME 
  from information_schema.COLUMNS as c 
 where c.TABLE_NAME = 'geochem' 
   and c.COLUMN_NAME like '%\[%\]'

Example of results:

Result

Then update values in these columns e.g. column SiO2[%{wt}]

UPDATE geochem
SET "SiO2[%{wt}]" = NULL
WHERE "SiO2[%{wt}]" = 0;

How can I combine these queries to update all columns returned by Q1, instead of updating column by column manually?

1
  • 1
    I'd recommend you to normalize the structure to (geochem_entity_id, formula, value). How can I combine the queries above Dynamic SQL may solve.
    – Akina
    Feb 26 at 9:37

1 Answer 1

1

Fist ask yourself if a table structure with a single value column like suggested by Akina in the comments wouldn't be more practical.

While stuck with your current design, you need dynamic SQL to target a dynamic set of columns. But first, optimize the command(s).

Every UPDATE writes a new row version for every row that is not excluded from the UPDATE. Your approach writes another new row version for every single column.

Combine all of these:

UPDATE geochem
SET    "SiO2[%{wt}]" = NULL
WHERE  "SiO2[%{wt}]" = 0;

UPDATE geochem
SET    "TiO2[%{wt}]" = NULL
WHERE  "TiO2[%{wt}]" = 0;

-- more?

... into a single UPDATE:

UPDATE geochem
SET    "SiO2[%{wt}]" = NULLIF("SiO2[%{wt}]", 0)
,      "TiO2[%{wt}]" = NULLIF("TiO2[%{wt}]", 0)
 -- more?
WHERE  0 = ANY ("SiO2[%{wt}]", "TiO2[%{wt}]");  -- more?

Note the adapted WHERE clause to check if any of the target columns holds 0. NULLIF per column only nullifies if it is 0.

To do this dynamically, concatenate the statement safely, then execute:

DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          'UPDATE public.geochem
SET    '
       || string_agg(format('%1$s = NULLIF(%1$s, 0)', c.col), E'\n,      ')
       || E'\nWHERE  0 = ANY ('
       || string_agg(c.col, ', ')
       || ');'
   FROM  (
      SELECT quote_ident(c.column_name) AS col  -- ! to be safe
      FROM   information_schema.columns AS c
      WHERE  c.table_name = 'geochem'
      AND    c.table_schema = 'public'  -- ! to be safe
      AND    c.column_name LIKE '%\[%\]'
      ) c;
      
   IF _sql IS NULL THEN
      RAISE WARNING 'Did not find any matching columns!';
   ELSE
      RAISE NOTICE '%', _sql;
      -- EXECUTE _sql  -- uncomment to actually execute
   END IF;
END
$do$

I added a child-safety device, so this only raises a notice with the statement to execute. Un-comment the EXECUTE line (and comment the RAISE NOTICE line) to execute it immediately.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.