3

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.

Lebene
  • 35
  • 4

1 Answers1

6

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.

  • Thanks a lot for this. Sorry, I just need clarification on the part 'a unique constraint does not change any data'. Are you saying even if I have unique constraints on those fields, this trigger supersedes that and the non-unique entries of 'Coming soon' and 'None' will be allowed? – Lebene Jul 17 '20 at 20:26
  • @Lebene: what I meant is that a unique constraint won't magically turn `null` into `'Coming soon'` - but I did overlook that nearly all columns are defined as `unique` (which is kind of strange) –  Jul 17 '20 at 20:28
  • Ok, that's fine, I never had that assumption. I wanted the social media links to be unique because unfortunately the 'entry_name' submissions keep being submitted with mistakes, typos or variations. So for example 'McDonalds', 'MacDonalds', McDonald's' etc.. they're all the same but for some reason there'd be unique entries for almost all SM links and then just ONE would have a duplicate. I don't know how to overcome that. Perhaps what I actually need is an action if there is a duplicate entry? – Lebene Jul 17 '20 at 20:31
  • thank you for the solution. I tried the trigger without the unique constraints but it's not working for me, but I'll keep trying! – Lebene Jul 17 '20 at 20:43
  • 1
    A unique constraint will NOT detect McDonalds, MacDonalds,'McDonald's as being the same. While a person might very well recolonize each as variants of the same thing a computer will not, at least with out a lot of work on your part. The computer is quite literal so MacDonalds contains an "a" as the 2nd letter, the others do not and McDonald's contains an apostrophe, the others do not; so they all pass the **unique**ness criteria. Seems you have 3 choices: write considerable code (or find a lib), create a lookup table (but that would be huge maintenance problem), don't worry about it. – Belayer Jul 18 '20 at 01:51