-----Original Message-----
From: Tom Lane <tgl@xxxxxxxxxxxxx>
Sent: Thursday, August 15, 2019 6:13 PM
To: stan <stanb@xxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: Variable constants ?
stan <stanb@xxxxxxxxx> writes:
> Failing a better way is there some way I can limit this table to only
> allow one row to exist?
I was recently reminded of a cute trick for that: make a unique index on a constant.
regression=# create table consts(f1 int, f2 int); CREATE TABLE regression=# create unique index consts_only_one on consts((1)); CREATE INDEX regression=# insert into consts values(1,2); INSERT 0 1 regression=# insert into consts values(3,4);
ERROR: duplicate key value violates unique constraint "consts_only_one"
DETAIL: Key ((1))=(1) already exists.
Shepard's nearby advice about keeping some history is probably a better plan though.
regards, tom lane
____________________________________________________________________________
Here is another trick to keep a table to just one row:
reg#create table test(c1 int check(c1=1) default 1, c2 int);
CREATE TABLE
reg#create unique index one_row on test(c1);
CREATE INDEX
reg#insert into test (c2) values(3);
INSERT 01
reg# insert into test (c2) values(4);
ERROR: duplicate key value violates unique constraint "one_row"
DETAIL: Key (c1)=(1) already exists.
SQL state: 23505
Regards,
Igor Neyman
