PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres Default column to own id

I want to have a column user.substitute_user_id where users can appoint someone being in charge instead of them.
If they are still are in charge, it should point to themself, so that you can always look into that field and see the correct user id of the user being in charge.

create table public.user (
  id bigserial primary key,
  is_superuser boolean not null,
  substitute_user_id bigint,
  foreign key (substitute_user_id) references public.user (id)
  match simple on update no action on delete no action
);
create index user_substitute_user_id_d012d5b2 on user using btree (substitute_user_id);

Now I can update the substitute_user_id to point to a row itself like the following:

UPDATE "user" SET "substitute_user_id" = "user"."id"

How can I put a default which does the same?

Can be a trigger too, updating the value if it is still set to null on insert,
however I couldn’t find a way to do it.

Requirements:

  • must work on insert, setting the inserted row.substitute_user_id := row.id automatically
  • must be possible to be changed to another user.id via UPDATE.
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    With currval being the missing part (thanks @Zegarek) in my earlier tries it is now possible to create two triggers, one for insert and one for update.

    1. The INSERT one will use NEW.substitute_user_id := currval(…), as the .id is not yet available
    2. The UPDATE one will use NEW.substitute_user_id := NEW.id, as the currval(…) might now point to some later added row.

    Here's the full fun:

    ALTER TABLE public.user ALTER COLUMN substitute_user_id SET DEFAULT NULL;

-- trigger function to set `substitute_user(_id)` to the own id, if NULL on INSERT (1/4)
CREATE OR REPLACE FUNCTION fn_set_user_substitute_user_id_crate_trigger()
RETURNS trigger AS
    $BODY$
        BEGIN
            IF NEW.substitute_user_id IS NULL THEN
                NEW.substitute_user_id := currval(pg_get_serial_sequence('piauser','id'));
            END IF;
            RETURN NEW;
        END;
    $BODY$
    LANGUAGE plpgsql
;
-- trigger function to set `substitute_user(_id)` to the own id, if NULL on UPDATE (2/4)
CREATE OR REPLACE FUNCTION fn_set_user_substitute_user_id_update_trigger()
RETURNS trigger AS
    $BODY$
        BEGIN
            IF NEW.substitute_user_id IS NULL THEN
                NEW.substitute_user_id := NEW.id;
            END IF;
            RETURN NEW;
        END;
    $BODY$
    LANGUAGE plpgsql
;

-- trigger function to set `substitute_user(_id)` to the own id, if NULL on INSERT (3/4)
CREATE TRIGGER trigger_set_user_substitute_user_insert
    BEFORE  -- needs to be before, so we can change the values before they are written to db.
        -- only on insert, not update, so we have the id in `currval(…)`
        -- the update is handled by the other trigger.
        INSERT
    ON user
    FOR EACH ROW
    EXECUTE FUNCTION fn_set_user_substitute_user_id_crate_trigger()
;
-- trigger function to set `substitute_user(_id)` to the own id, if NULL on UPDATE (4/4)
CREATE TRIGGER trigger_set_user_substitute_user_update
    BEFORE  -- needs to be before, so we can change the values before they are written to db.
        -- only on update, not insert, so we have the .id available.
        -- the insert is handled by the other trigger.
        UPDATE OF "substitute_user_id"
    ON user
    FOR EACH ROW
    EXECUTE FUNCTION fn_set_user_substitute_user_id_update_trigger()
;

-- trigger function to make sure it can't end up null in the database (1/2)
CREATE OR REPLACE FUNCTION fn_validate_user_substitute_user_id_trigger() RETURNS TRIGGER AS
    $BODY$
        BEGIN
          IF (NEW.substitute_user_id IS NULL) THEN
            RAISE not_null_violation;
          END IF;
          RETURN NULL;
        END;
    $BODY$
  LANGUAGE plpgsql
;
-- trigger function to make sure it can't end up null in the database (2/2)
CREATE CONSTRAINT TRIGGER zzz_trigger_validate_user_substitute_user  -- constraint, so it runs after normal triggers
    AFTER
        UPDATE OF "substitute_user_id"
    ON user
    FOR EACH ROW
    WHEN (NEW.substitute_user_id IS NULL)
    EXECUTE FUNCTION fn_validate_user_substitute_user_id_trigger()
;

  2. 0

    The same way you generate id, except you take the currval() which will be equal to what id column just got from nextval(). You don’t need to know the name of the sequence if you let postgres look it up with pg_get_serial_sequence().:
    demo at db<>fiddle

    create table public.user (
  id bigint generated by default as identity primary key,
  is_superuser boolean not null,
  substitute_user_id bigint 
     default currval(pg_get_serial_sequence('public.user','id')),
  foreign key (substitute_user_id) references public.user (id)
  match simple on update no action on delete no action
);
create index user_substitute_user_id_d012d5b2 on 
  public.user using btree (substitute_user_id);

UPDATE "user" SET "substitute_user_id" = "user"."id";

insert into public.user(is_superuser)values(false),(true)returning*;
    id is_superuser substitute_user_id
    1 f 1
    2 t 2

    In case that DDL was because you used serial – don’t use serial.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search