Clever Geek Handbook

How to create a column constant in Postgresql

create table test(
  t_id SERIAL primary key,
  t_date CONSTANT date default CURRENT_DATE
);
ERROR:  syntax error at or near "date"
LINE 3:   t_date CONSTANT date default CURRENT_DATE
                      ^
********** Error **********

ERROR: syntax error at or near "date"
SQL state: 42601
+3


source to share


2 answers


For the default, you can use the function,

CREATE TABLE test(
    t_id SERIAL primary key,
    t_date date DEFAULT now()
);

about constant

, I've never used, not even other SQL (!), only in the context of PL / SQL ...

If you need a no refresh constraint, you can use a trigger. Example:

CREATE FUNCTION correct_update() RETURNS trigger AS $$ 
    BEGIN
    NEW.t_date=OLD.t_date;
    RETURN NEW; 
    END
$$ LANGUAGE plpgsql;

CREATE TRIGGER no_date_update
  BEFORE BEFORE UPDATE ON test
  FOR EACH ROW
  WHEN (OLD.t_date IS DISTINCT FROM NEW.t_date)
  EXECUTE PROCEDURE correct_update();

For full control, you also need to fire the INSERT event (and no longer need the default as the insert trigger will be involved):

create table test(
    t_id SERIAL primary key,
    t_date date  -- a default will be redundant
);

CREATE FUNCTION correct_date() RETURNS trigger AS $$ 
    BEGIN
      IF TG_OP = 'INSERT' THEN
         NEW.t_date=now();         -- default value
      ELSIF TG_OP = 'UPDATE' THEN  -- optional AND OLD.t_date != NEW.t_date
         NEW.t_date=OLD.t_date;    -- "constant" behaviour
      END IF;
      RETURN NEW;
    END
$$ LANGUAGE plpgsql;

CREATE TRIGGER constant_date
  BEFORE INSERT OR UPDATE ON test
  FOR EACH ROW
  EXECUTE PROCEDURE correct_date();

The comparison OLD.t_date != NEW.t_date

is optional as it does not affect performance ... But it is good practice to use it. Another way is to test the trigger for WHEN

, but only update triggers can use OLD

... So, the best create triggers for the same function correct_date()

(no old / new comparison):

CREATE TRIGGER constant_date_ins
  BEFORE INSERT  ON test
  FOR EACH ROW
  EXECUTE PROCEDURE correct_date();
CREATE TRIGGER constant_date_upd
  BEFORE UPDATE ON test
  FOR EACH ROW
  WHEN (OLD.t_date IS DISTINCT FROM NEW.t_date)
  EXECUTE PROCEDURE correct_date();


Contextualization in a script

As noted above in the question, there is a lack of contextualization for example. explaining why you think it should work and what it should do.

Scenario-1: db-master needs to block unwary programmers

We can imagine a framework like CakePHP with a "crafted" field and a database wizard that wants this field to have "persistent behavior", preventing careless programmers from affecting this "expected constraint".

This script was used by anwser.

Scenario-2: project decision should be notified by mistake

This is @IgorRomanchenko's suggestion ...

 ... now here as a Wiki, you can EDIT and add new solution/example ...
+2


source


Restriction required check 

create table test(
  t_id SERIAL primary key,
  t_date date default CURRENT_DATE check(t_date = current_date)
);

insert into test(t_date) values (default);
INSERT 0 1

insert into test(t_date) values ('2014-01-01');
ERROR:  new row for relation "test" violates check constraint "test_t_date_check"
DETAIL:  Failing row contains (2, 2014-01-01).

Or there might be a constraint foreign key

that allows multiple possible values ​​and can be updated without changing the table schema



create table date_constraint (
    date_constraint date primary key
);
insert into date_constraint (date_constraint) values (current_date);

create table test(
  t_id SERIAL primary key,
  t_date date
    default CURRENT_DATE
    references date_constraint(date_constraint)
);

insert into test(t_date) values (default);
INSERT 0 1

insert into test(t_date) values ('2014-01-01');
ERROR:  insert or update on table "test" violates foreign key constraint "test_t_date_fkey"
DETAIL:  Key (t_date)=(2014-01-01) is not present in table "date_constraint".

http://www.postgresql.org/docs/current/static/ddl-constraints.html

-1


source






More articles: