Postgres string formatting for record ID

Question

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)
);

From here CREATE TABLE: " DEFAULT default_expr ... The value is any variable-free expression (in particular, cross-references to other columns in the current table are not allowed). Subqueries are not allowed either. ... ". You will need a trigger to do this. — Adrian Klaver
– Adrian Klaver, Commented Jun 21, 2022 at 20:36

emomaliev · Accepted Answer · 2022-11-23 06:06:44Z

1

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

edited Nov 23, 2022 at 6:06

answered Jun 22, 2022 at 9:16

emomaliev

2,4233 gold badges12 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres string formatting for record ID

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related