I'm new to PostgreSQL and am having trouble correctly coding an AFTER INSERT trigger (and have not been able to find an example that matches my specific need). I have two tables that each contain several columns, and one of which is shared between the tables (sampleid). When new rows are inserted into table1 and sampleid in those rows IS NOT NULL, I want the trigger to fire and copy the inserted sampleids to table2. When table1.sampleid IS NULL, and don't want the trigger to fire.
My (simplified) tables are as follows:
CREATE TABLE table1 (
a_id SERIAL PRIMARY KEY,
species CHAR(4),
sampleid TEXT);
CREATE TABLE table2 (
sampleid TEXT PRIMARY KEY,
replicate INTEGER,
bd DOUBLE PRECISION);
And here is the trigger function and trigger:
CREATE OR REPLACE FUNCTION func_sampleid_copy()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.sampleid IS NOT NULL THEN
INSERT INTO table2 (sampleid)
VALUES(NEW.sampleid);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER trig_sampleid_copy
AFTER INSERT ON table1
FOR EACH ROW
EXECUTE PROCEDURE func_sampleid_copy();
If I insert two rows into table1, one in which sampleid is not null and one in which it is null
INSERT INTO table1 (species, sampleid)
VALUES ('RACA', 'XXX100'), ('AMMA', '');
I expected that a single value of sampleid would be copied to table2 (i.e., XXX100). Instead a query of table2 (SELECT * FROM table2) shows two rows, one in which sampleid is not null, and one in which it is null). What do I need to change in my trigger/trigger function to ensure that null values of sampleid are not copied to table2? The related question suggested by @dmfay was not particularly clear and I'd suggest that it is not really a duplicate.
The comment by @Abelisto provided the answer to my question. When I added NULL values (instead of empty strings) to table1, the trigger and trigger functions worked as expected.