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?

Answer 1

The core problem is the special nature of the key word DEFAULT in a VALUES expression attached to an INSERT. Per documentation:

In a VALUES list appearing at the top level of an INSERT, an expression can be replaced by DEFAULT to indicate that the destination column's default value should be inserted. DEFAULT cannot be used when VALUES appears in other contexts.

Bold emphasis mine. Specifically, DEFAULT cannot be argument to a function:

COALESCE(function_parameter, DEFAULT)  -- not possible

Possible solution

There are various ways, depending on exact requirements.

This function doesn't need to know the actual default of person.id - which seems to be what you are after:

CREATE OR REPLACE FUNCTION create_person(_id UUID)
  RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER AS
$func$
BEGIN
   IF _id IS NULL THEN                 -- no UUID provided
      INSERT INTO myschema.person(id)  -- see below about schema name
      VALUES (DEFAULT);                -- use the key word DEFAULT
   ELSE                                -- UUID provided
      INSERT INTO myschema.person(id)
      VALUES (_id);
   END IF;

   RETURN FOUND;                       -- (return value pointless so far)
END
$func$;

Avoid using the same name for parameters and involved table columns. Since function parameters are visible inside every SQL command in the function body, it can lead to very confusing naming conflicts (even if target columns of INSERT are exempt from this in modern Postgres). I use _id as parameter name instead.

Default values for other columns not mentioned in the INSERT are filled in automatically. I use the key word DEFAULT because we are required to list at least one target column for the INSERT.

The boolean return value is pointless in this demo because it is always true (unless you have triggers that might skip the row).

Related answer with possible alternatives and a lot of explanation:

• Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3

Aside: You should schema-qualify all function and table names in a SECURITY DEFINER function - or (probably better if you aren't sure) explicitly set the search_path to defend against possible attacks. More:

• How does the search_path influence identifier resolution and the "current schema"

Answer 2

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

Answer 3

Overload the gen_random_uuid function:

create or replace function gen_random_uuid()
returns uuid as $$
    select 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid;
$$ language sql;


create or replace function gen_random_uuid(_id uuid)
returns uuid as $$
    select coalesce(_id, gen_random_uuid());
$$ language sql;

Create the table as usual:

create table if not exists person (
    id uuid default gen_random_uuid()
);

Call the gen_random_uuid function inside the create_person function:

create or replace function create_person(_id uuid)
returns boolean as $$
    insert into person (id) values (gen_random_uuid(_id))
    returning true;
$$ language sql;

Then all the insert variations will work:

insert into person (id) values (default);
select create_person(null);
select create_person('b1eebc99-9c0b-4ef8-bb6d-6bb9bd380a22'::uuid);