Hello I am using postgres 12. I've been learning for a few days now.
I wanted to know if it's possible to write into the CREATE TABLE stage: IF column_x is NULL, return 'alternative value'?
This is my current table:
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.modified = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE example (
id BIGSERIAL NOT NULL PRIMARY KEY,
entry_name VARCHAR(150) NOT NULL UNIQUE,
about VARCHAR(1500),
org_type VARCHAR(150),
category VARCHAR(150),
sub_categories VARCHAR(300),
website_link VARCHAR(300) UNIQUE,
membership VARCHAR(100),
instagram VARCHAR(150) UNIQUE,
twitter VARCHAR(150) UNIQUE,
link_lists VARCHAR(100) UNIQUE,
facebook VARCHAR(200) UNIQUE,
youtube VARCHAR(200) UNIQUE,
podcast VARCHAR(200) UNIQUE,
tags VARCHAR(150),
created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
modified TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TRIGGER set_timestamp BEFORE UPDATE ON bbdb FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
So in this scenario, I would like for any null entries in 'about' and 'website_link' to say 'Coming soon' and all the social media null entries to just say 'None'. I guess my UNIQUE constraint would not allow this? (I have them to avoid duplicates of entries in case the 'entry_name' is submitted with variations).
Thanks for any help.
As you defined all those columns as UNIQUE you can have multiple null values, but you can't have two rows with the same 'Coming soon' value.
But you can replace those NULL values during retrieval, with the desired replacement:
select id,
coalesce(website_link, 'Coming Soon') as website_link,
coalesce(about, 'Coming soon') as about,
coalesce(twitter, 'None') as twitter,
coalesce(instgram, 'None') as instagram
from the_table;
If you don't want to type that every time, create a view which does that for you.