CODEWITHSUNDEEP
sqldatabasepostgresqlhsqldb
Johanes Matian
Johanes Matian

Reputation: 123

Default value of column (when value is set to NULL) equal to other column

Part of my SQL definition (for ):

CREATE TABLE activities (
  id  BIGINT GENERATED BY DEFAULT AS IDENTITY,
  group_id  BIGINT NOT NULL
  CONSTRAINT pk_activities PRIMARY KEY (id)
);

The scenario during and insert is:

I target and (for tests).

Can this be done?

Upvotes: 2

Views: 1302

Answers (2)

mvp
mvp

Reputation: 116367

You cannot set default value to be that of another column.

However, you can create a TRIGGER that would accomplish this task.

This is an example for PostgreSQL. It automatically replaces column with anothercolumn if it is NULL before INSERT or UPDATE:

CREATE OR REPLACE FUNCTION mytable_fn()
RETURNS trigger AS
$body$
BEGIN
    IF NEW.column ISNULL THEN
        NEW.column := NEW.anothercolumn;
    END IF;
    RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql';

CREATE TRIGGER mytable_trigger
BEFORE INSERT OR UPDATE ON mytable
FOR EACH ROW
EXECUTE PROCEDURE mytable_fn();

Upvotes: 1

mtsiakiris
mtsiakiris

Reputation: 190

This can be done using a trigger or if you dont want to use triggers is to handle the NULL when selecting rows like this:

SELECT id, isnull(group_id, id) group_id FROM activities;

Upvotes: 1

Related Questions