RE: Variable constants ?

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Thursday, August 15, 2019 6:13 PM
To: stan <stanb(at)panix(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Variable constants ?

stan <stanb(at)panix(dot)com> 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