Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE tbl (
  tbl_id int PRIMARY KEY
, source text NOT NULL DEFAULT 'COLUMN default source'
, data   text
);

CREATE TABLE


-- Step 1: Set source to NULL if unchanged:
CREATE OR REPLACE FUNCTION trg_tbl_upbef_step1()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.source = OLD.source THEN
      NEW.source := NULL;      -- "impossible" value (source is NOT NULL)
   END IF;

   RETURN NEW;
END
$func$;

CREATE TRIGGER upbef_step1
BEFORE UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_tbl_upbef_step1();

CREATE FUNCTION



CREATE TRIGGER


-- Step 2: Revert source to old value. Trigger is only fired if the value was actually updated:
CREATE OR REPLACE FUNCTION trg_tbl_upbef_step2()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.source IS NULL THEN
      NEW.source := OLD.source;
   END IF;

   RETURN NEW;
END
$func$;

-- The trigger for Step 2 is fired per column!
CREATE TRIGGER upbef_step2
BEFORE UPDATE OF source ON tbl                -- key element!
FOR EACH ROW EXECUTE PROCEDURE trg_tbl_upbef_step2();

CREATE FUNCTION



CREATE TRIGGER


-- Step 3: Now we can identify the lacking update and set a default value instead:
CREATE OR REPLACE FUNCTION trg_tbl_upbef_step3()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.source IS NULL THEN
      NEW.source := 'UPDATE default source';  -- optionally same as column default
   END IF;

   RETURN NEW;
END
$func$;

CREATE TRIGGER upbef_step3
BEFORE UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_tbl_upbef_step3();

CREATE FUNCTION



CREATE TRIGGER


-- plain inserts
INSERT INTO tbl(tbl_id, source, data) VALUES
  (1, 'source 1', 'data 1')
, (2, 'source 2', 'data 2')
, (3, 'source 3', 'data 3')
, (4, 'source 4', 'data 4')
, (5, DEFAULT, DEFAULT)     -- to show INPUT defaults
;

INSERT 0 5


-- UPDATE with different source
UPDATE tbl
SET    data   = 'new_data 1' -- different source
     , source = 'source NEW1'
WHERE  tbl_id = 1;

UPDATE 1


-- UPDATE with same source
UPDATE tbl
SET    data   = 'new_data 2'
     , source = 'source 2'   -- same source! (problem case to distinguish)
WHERE  tbl_id = 2;

UPDATE 1


-- UPDATE without source
UPDATE tbl
SET    data   = 'new_data 3'
                             -- no source! (problem case to distinguish)
WHERE  tbl_id = 3;

UPDATE 1


-- UPDATE to NULL should not occur (column is defined NOT NULL), and is reverted if it does
UPDATE tbl
SET    data   = 'new_data 4'
     , source = NULL   -- should not occur (?)
WHERE  tbl_id = 4;

UPDATE 1


TABLE tbl ORDER BY tbl_id;


tbl_id
source
data


1
source NEW1
new_data 1

2
source 2
new_data 2

3
UPDATE default source
new_data 3

4
source 4
new_data 4

5
COLUMN default source
null



SELECT 5

tbl_id	source	data
1	source NEW1	new_data 1
2	source 2	new_data 2
3	UPDATE default source	new_data 3
4	source 4	new_data 4
5	COLUMN default source	null