Postgresql – Setting up a cascading trigger on a parent table

Let's say I have started to keep a log of all the postcards that I send so I define the table postcards:

                                            Table "postcards"
       Column         |            Type             |                Modifiers                 | Storage
----------------------+-----------------------------+------------------------------------------+----------
postcard_code         | bigint                      | not null                                 | plain    
postcard_recipient    | character varying(750)      | not null                                 | extended 
date_posted           | timestamp without time zone |                                          | plain    

Triggers:
    insert_date_posted BEFORE INSERT ON postcards FOR EACH ROW EXECUTE PROCEDURE insert_date_posted()
    update_date_posted BEFORE UPDATE ON postcards FOR EACH ROW EXECUTE PROCEDURE update_date_posted()

Child tables:
    postcards_unsent,
    postcards_2018,
    postcards_2019,
    postcards_2020

Because of the amount of postcards that I plan to send I have set up partitions via table inheritance so that all the postcards that I send this year will be logged in postcards_2018 and so on. The child tables are defined as, for example:

                                            Table "postcards_2018"
       Column         |            Type             |                Modifiers                 | Storage  
----------------------+-----------------------------+------------------------------------------+----------
postcard_code         | bigint                      | not null                                 | plain    
postcard_recipient    | character varying(750)      | not null                                 | extended 
date_posted           | timestamp without time zone |                                          | plain    

Indexes:
    "postcards_2018_pkey" PRIMARY KEY, btree ( postcard_code, postcard_recipient )

Check constraints:
    "postcards_2018_date_posted_check" CHECK (
    date_posted >= '2018-01-01 00:00:00'::timestamp without time zone AND
    date_posted < '2019-01-01 00:00:00'::timestamp without time zone )

Inherits: postcards

Everytime I buy a new postcard and send it, I add it to the table using:

INSERT INTO postcards ( postcard_code, postcard_recipient, date_posted )
VALUES ( 1, 'Adam', current_date );

The trigger insert_date_posted calls the trigger procedure insert_date_posted() and writes the row in the correct partition.
The trigger procedure insert_date_posted() is:

CREATE OR REPLACE FUNCTION insert_date_posted()
    RETURNS trigger AS
$BODY$
BEGIN
    IF ( NEW.date_posted IS NULL ) THEN
        INSERT INTO postcards_unsent VALUES ( NEW.* );
    IF ( NEW.date_posted  >= '2018-01-01' AND NEW.date_posted < '2019-01-01') THEN
        INSERT INTO postcards_2018 values ( NEW.* );
    ELSIF ( NEW.date_posted  >= '2019-01-01' AND NEW.date_posted < '2020-01-01') THEN
        INSERT INTO postcards_2019 values ( NEW.* );
    ELSIF ( NEW.date_posted  >= '2020-01-01' AND NEW.date_posted < '2021-01-01') THEN
        INSERT INTO postcards_2020 values ( NEW.* );
    ELSE
        RAISE EXCEPTION 'Date out of range. Check postcards_insert_trigger() procedure.';
    END IF;
    RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

I add the trigger using:

CREATE TRIGGER insert_date_posted
BEFORE INSERT
ON postcards
FOR EACH ROW
EXECUTE PROCEDURE insert_date_posted();

I also have a backlog of postcards that I want to send when I get around to it. I add them to the table and set date_posted to NULL to keep track of them. When I eventually do get around to sending a postcard from the backlog, I want to update the date_posted column using:

UPDATE postcards
SET date_posted = current_date
WHERE postcard_code = x
AND postcard_recipient = y
AND date_posted IS NULL;

I want the trigger update_date_posted to call the trigger procedure update_date_posted() that will:

• copy the row to the correct partition
• delete the row from the postcards_unsent partition
• trigger the insert_date_posted trigger.

The trigger procedure update_date_posted() is:

CREATE OR REPLACE FUNCTION update_date_posted()
   RETURNS trigger AS
$BODY$
BEGIN
    IF ( OLD.date_posted IS NULL )
    THEN
        -- Copy this row to the right partition by assigning a non NULL date_posted value to it
        INSERT INTO postcards (
            postcard_code
            , postcard_recipient
            , date_posted )
        SELECT
            OLD.postcard_code
            , OLD.postcard_recipient
            , current_date
        FROM postcards
        WHERE ( date_posted IS NULL )
        AND postcard_code = OLD.postcard_code
        AND postcard_recipient = OLD.postcard_recipient
        -- Delete original entry to prevent duplication
        DELETE FROM postcards
        WHERE ( date_posted IS NULL )
        AND postcard_code = OLD.postcard_code
        AND postcard_recipient = OLD.postcard_recipient
    END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

I then add this trigger using:

CREATE TRIGGER update_date_posted
BEFORE UPDATE
ON postcards
FOR EACH ROW
EXECUTE PROCEDURE update_date_posted();

This does not work and the update_date_posted never gets called, the row is not copied to the postcards_2018 partition, it is not deleted from the postcards_unsent partition and the insert_date_posted trigger is not called either.

The error message I see is:

ERROR: new row for relation "postcards_unsent" violates check constraint "postcards_unsent_date_posted_check"
DETAIL:  Failing row contains ( x, y, current_date )

The postcards_unsent_date_posted_check constraint is:

Check constraints:
    "postcards_unsent_date_posted_check" CHECK ( date_posted  IS NULL )

It would appear as if the check constraint is executed first but I am not sure if that is the case and I don't see why this is not working.