Im using postgres and would like to add a condition to a column in my table schema.
Suppose if have the following table
CREATE TABLE people (
ID SERIAL PRIMARY KEY,
NAME varchar(20) NOT NULL,
ADDRESS varchar(50)
)
Im trying to figure out how I can update this schema so if an update happens, if the ADDRESS column is not null DON'T overwrite it. Basically only update that column ONLY if it's null or empty and ignore the new value being passed in with the next update.
Any help appreciated
What Iv tried so far
update people
set name = 'John', address = '12 Oak road, Hong Kong'
where id = 1;
CREATE OR REPLACE FUNCTION check_address() RETURNS TRIGGER AS
$BODY$
BEGIN
IF (TG_OP='UPDATE') THEN
IF OLD.address is not NULL THEN
NEW.address = OLD.address;
END IF;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER tr_check_address
BEFORE update
on people
EXECUTE PROCEDURE check_address();
The above trigger returns an error after I run the update...
SQL Error [55000]: ERROR: record "old" is not assigned yet
