Postgresql - in a function after a trigger a NULL column makes all payload void

Question

I've a table with various columns - some of them can be NULL (there is no default value for them).

So I've created a trigger and each time I've a new value inserted in the table I want to trigger a pg_notify. (Hint: I'm a total noob about SQL).

The problem is that if just one of the column that can be null have a void value, then all the payload emitted by pg_notify is null.

A really simple example:

postgres=# create table example(id serial, name varchar); 
CREATE TABLE
postgres=# create function new_example()  RETURNS trigger AS $$
DECLARE
BEGIN
PERFORM pg_notify('example', 'id: ' || NEW.id || ' name: ' || NEW.name);
RETURN new;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# create trigger new_ex AFTER INSERT ON example FOR EACH ROW EXECUTE PROCEDURE new_example();
CREATE TRIGGER
postgres=# LISTEN example;
LISTEN
postgres=# INSERT into example(name) VALUES ('a');
INSERT 0 1
Asynchronous notification "example" with payload "id : 1 name: a" received from server process with PID 22349.

This is correct - I've inserted a new row and the notify is exactly what I expect

postgres=# INSERT into example(name) VALUES (NULL);
INSERT 0 1
Asynchronous notification "example" received from server process with PID 22349.

This does not make any sense. I would expect something like Asynchronous notification "example" with payload "id : 1 name: NULL" received from server process with PID 22349. or Asynchronous notification "example" with payload "id : 1 name:" received from server process with PID 22349.

What do I do wrong?

Kamil Gosciminski · Accepted Answer · 2016-09-22 22:58:55Z

1

When you are concatenating strings with || operator and any of the argument is NULL then the entire expression evaluates to NULL.

To get around that use CONCAT() function that handles NULL values by simply removing them from string.

So, to the code:

PERFORM pg_notify('example', CONCAT('id: ', NEW.id, ' name: ', NEW.name));

This will however leave the following message to be empty, so you may want to use COALESCE() function and come up with some fancy string that will tell you the value is really NULL and not a "null" string for example. One way to do that would be:

PERFORM pg_notify('example', CONCAT('id: ', NEW.id, ' name: ', COALESCE(NEW.name, '[#NULL#]')));

But you would still not distinguish string [#NULL#] from a real null value because they would look the same in the message returned from NOTIFY. See below for different approach.

Personally, I'd probably go within a slightly different approach by creating an audit table and inserting ids with schema and table name and this would serve your purpose for multiple tables checking for null values in various columns.

edited Sep 22, 2016 at 22:58

answered Sep 22, 2016 at 22:34

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

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

1 Comment

rpadovani Over a year ago

Thank you very much - actually the notification it will be used by a nodejs application, so I think I'll go with COALESCE(NEW.description, '') and I'll consider the '' as NULL on nodejs side

Collectives™ on Stack Overflow

Postgresql - in a function after a trigger a NULL column makes all payload void

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