CODEWITHSUNDEEP
sqlpostgresql
code writer 3000
code writer 3000

Reputation: 354

Postgres string formatting for record ID

I am trying to create a table to keep an archive of dad jokes in Postgres. For the title record, I would like the value to by-default be the joke ID, but formatted in a way where if the id is 7, the record's title is Joke #7. Here is my query to create the table:

CREATE TABLE public.jokes (
    id int NOT null primary KEY,
    user_id int NOT NULL DEFAULT 1,
    title varchar NULL DEFAULT FORMAT("Joke #%s", ), -- the title that I would like to be formatted
    body varchar NOT NULL,
    CONSTRAINT jokes_fk FOREIGN KEY (user_id) REFERENCES public."Users"(id)
);

Upvotes: 0

Views: 155

Answers (1)

emomaliev
emomaliev

Reputation: 2383

You need to create a trigger function that will change the title if it is not set

create function change_title() returns trigger as $$
  begin 
     if new.title is null then
         new.title := format('joke #%s', new.id);
     end if;     
     return new; 
  end; $$ language plpgsql; 

create trigger change_title_jokes before insert on jokes for each row execute procedure change_title();

Demo in sqldaddy.io

Upvotes: 1

Related Questions