Inserting a Coalesce(Null,Default)

Inserting a COALESCE(NULL,default)

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

Coalesce not working in insert statement for a null table

The correct way to solve this is with an auto_increment column:

create table PMS_CALC_SCHEDULE (
    PMS_CALC_SCHEDULE_UID int auto_increment primary key,
    . . .
);

If, for some reason, you want to do the calculation yourself, subject your code to race conditions, and have slower inserts, then you need to do the coalesce in the right place:

INSERT INTO PMS_CALC_SCHEDULE (PMS_CALC_SCHEDULE_UID, . . .) 
    SELECT COALESCE(MAX(PMS_CALC_SCHEDULE_UID), 0) + 1, 
           . . .
    FROM PMS_CALC_SCHEDULE ;

Coalesce sentence containing an insert into clause fails in PostgreSQL

Different method: chained CTEs:

---

CREATE TABLE test
        ( id          INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY
        , first_name VARCHAR UNIQUE
        );

WITH sel AS (
        SELECT id FROM test WHERE first_name = 'carlos'
        )
, ins AS (
        INSERT INTO test(first_name) 
        SELECT 'carlos'
        WHERE NOT EXISTS (SELECT 1 FROM test WHERE first_name = 'carlos')
        RETURNING id
        )
, omg AS (
        SELECT id FROM sel
        UNION ALL 
        SELECT id FROM ins
        )
SELECT id
FROM omg
        ;

Inserting DEFAULT value into a column when a parameter is NULL

As param_2 can only be one of null or not null only one of the selects will return a row to be inserted:

with i as (
    insert into my_table (val_1)
    select param_1
    where param_2 is null
)
insert into my_table (val_1, val_2)
select param_1, param_2
where param_2 is not null

If it is necessary to return the inserted values:

with i_null as (
    insert into my_table (val_1)
    select param_1
    where param_2 is null
    returning *
), i_notnull as (
    insert into my_table (val_1, val_2)
    select param_1, param_2
    where param_2 is not null
    returning *
)
select * from i_null
union all
select * from i_notnull

How to set default value while insert null value into not null column SQL Server?

First Solution,

   insert into t1
    select id,isnull(name,'Peter') from t2

Second solution

ALTER TABLE T1 ALTER COLUMN name varchar(255) NULL

insert into t1
select id,name from t2

ALTER TABLE T1 ALTER COLUMN name varchar(255) NOT NULL

how to replace NULL value during insertion in sql

For MySQL use:

insert into table values (CPI_id , Weight ,IFNULL(score_100_UB ,100), score_100_LB )

or:

insert into table values (CPI_id , Weight ,COALESCE(score_100_UB ,100), score_100_LB )

SQL Server:

insert into table values (CPI_id , Weight ,ISNULL(score_100_UB ,100), score_100_LB )

Oracle:

insert into table values (CPI_id , Weight ,NVL(score_100_UB ,100), score_100_LB )

COALESCE doesn't seem to fire in Postgres 12 SQL stored function

The lookup function doesn't return anything from the select statement inside it as the where clause fails. The coalesce is never executed therefore.
A null is returned by default.

You can rewrite the select as:

select coalesce((SELECT name_ FROM lookup_table WHERE id = id_in),'');

---

Related Topics