Subquery returning null from count. (PostgreSQL)

Question

PostgreSQL 11.1

ERROR: null value in column "chart_number" violates not-null constraint

O.K., I'm clearly doing something wrong here, but I don't see it.

Table patients has

COLUMN chart_number integer NOT NULL;

The Error is coming from the trigger:

CREATE OR REPLACE FUNCTION phoenix.next_chart()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
AS $BODY$
BEGIN
  IF NEW.chart_number is null or NEW.chart_number = 0 THEN
        LOOP
            EXIT WHEN 0 = (select count(*) from patients where chart_number = NEW.chart_number);

            SELECT nextval('chartsequence') INTO NEW.chart_number;
        END LOOP;       
  END IF;

  RETURN NEW;
END;
$BODY$;

The sequence "chartsequence" is defined in the usual way:

CREATE SEQUENCE chartsequence START 1;

What I am trying to do is have the loop exit when the sequence value does NOT exist in table patients.

Any help is most appreciated. TIA

sticky bit · Accepted Answer · 2020-01-28 01:22:11Z

1

When new.chart_number is NULL then

SELECT count(*)
       FROM patients p
       WHERE p.chart_number = new.chart_number

always returns 0 because there cannot be any row in patients with a chart_number of NULL since the column is defined NOT NULL, let alone that = won't work on NULL but needs a IS NULL operation.

That means that the condition

0 = (SELECT count(*)
            FROM patients p
            WHERE p.chart_number = new.chart_number)

is always true in such cases, which in turn means that you immediately exit the loop at the beginning of its first iteration, yet before new.chart_number is even changed once. It stays NULL. And since it's constrained not to be NULL the error gets thrown.

I think you just need to change the order of the statements in the loop's body. First set new.chart_number according to the sequence, then check if it doesn't exist. Speaking of exists, you could also use EXISTS here and just the assignment operator rather than SELECT ... INTO.

...
IF new.chart_number IS NULL
    OR new.chart_number = 0 THEN
  LOOP
    new.chart_number := nextval('chartsequence');
    EXIT WHEN NOT EXISTS (SELECT *
                                 FROM patients p
                                 WHERE p.chart_number = new.chart_number);
  END LOOP;       
END IF;
...

answered Jan 28, 2020 at 1:22

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alan Wayne Over a year ago

Entirely correct. Thanks. In particular, thank you for clarifying when the statement following "EXIT" is run. My (wrong) thinking was that it was always executed following the block of statements within the loop. Apparently, it is executed immediately where it is encountered. Thanks.

Collectives™ on Stack Overflow

Subquery returning null from count. (PostgreSQL)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related