Inserting a COALESCE(NULL,default)

Question

I have tables that use UUIDs. I want to be able to insert a new row with or without a UUID as sometimes the client will generate the UUID other times it won't.

Each table has this at it's core:

CREATE TABLE IF NOT EXISTS person (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

I'm trying to use a function to insert rows. I'd like to be able to hand a NULL id and get a default value (a generated UUID). I have something like this:

CREATE OR REPLACE FUNCTION create_person(
    id UUID
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    INSERT INTO person( id )
    VALUES (
        COALESCE(id,default)
    );
    RETURN FOUND;
END;
$$;

I've tried this:

INSERT INTO person ( id ) VALUES (
    COALESCE(id, default),
);

and this:

INSERT INTO person ( id ) VALUES (
  CASE WHEN id IS NULL THEN default ELSE id END
);

This works, but it repeats the gen_random_uuid() code:

INSERT INTO person ( id ) VALUES (
  COALESCE(id, gen_random_uuid()),
);

similarly this works too but has the same problems:

INSERT INTO person ( id ) VALUES (
    CASE WHEN id IS NULL THEN gen_random_uuid() ELSE id END
);

Is there a way to do this where I don't have to repeat the gen_random_uuid() code?

Would this be better done with plpgsql?

As you are using PL/pgSQL, I would use an if id is not null then insert .. values (id) else insert ... values (default) — a_horse_with_no_name
What is this default? I cannot see where it's defined. Is that visible? Can you print it? Another angle is: I would try to name this default something else that's not an SQL keyword, like default_value. — Tamas Rev
@tamas-rev It's the default keyword that I want. I would like Postgres to insert a default value there (as defined in the CREATE TABLE). — Sambeau
I think triggers can help you. E.g they can run something before insert for you. I can't test it because I don't have postgres on my machine. However, this page is a good place to start. — Tamas Rev
Third time lucky I think tamas has the right answer. BEFORE INSERT Triggers let you change the values of any insert and swap out nulls for a new value. — Philip Couling

Philip Couling Philip Couling · Accepted Answer · 2016-08-26T12:49:43

There's no way to re-use the defined default on the column. The default is only there to define what happens if an INSERT doesn't specify a value. By this definition a null value is still "specified" and therefore default can't be used.

Your comment that someone might not use the function indicates that a trigger is better for your requirements than a simple function.

https://www.postgresql.org/docs/current/static/plpgsql-trigger.html

CREATE OR REPLACE FUNCTION default_id() RETURNS TRIGGER AS $default_id$
    BEGIN       
        IF (NEW.id IS NULL) THEN
            NEW.id := gen_random_uuid();
        END IF;
        RETURN NEW;
    END;
$default_id$ LANGUAGE plpgsql;

CREATE TRIGGER default_id_trigger
BEFORE INSERT OR UPDATE ON person
    FOR EACH ROW EXECUTE PROCEDURE default_id();

If you do want to do this with a function then the simplest way is just to assign the value before inserting:

CREATE OR REPLACE FUNCTION create_person(
    id UUID
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    IF id IS NULL THEN
        id := gen_random_uuid();
    END IF;
    -- OR
    -- id := coalesce(id, gen_random_uuid());
    INSERT INTO person( id )
    VALUES (id);
    RETURN FOUND;
END;
$$;

Inserting a COALESCE(NULL,default)

3 Answers

Possible solution