2011/11/9 José Arthur Benetasso Villanova <jose.arthur@gmail.com>:
> postgres=# create table test1(id serial primary key, value text);
> NOTICE: CREATE TABLE will create implicit sequence "test1_id_seq" for
> serial column "test1.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "test1_pkey" for table "test1"
> CREATE TABLE
> postgres=# ALTER TABLE test1 ADD CONSTRAINT must_be_unique unique (value);
> NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index
> "must_be_unique" for table "test1"
> ALTER TABLE
> postgres=# insert into test1 values (default, 'Hello World');
> INSERT 0 1
> postgres=# insert into test1 values (default, 'Hello World');
> ERROR: duplicate key value violates unique constraint "must_be_unique"
> DETAIL: Key (value)=(Hello World) already exists.
It does this already, without this patch. This patch is about CHECK
constraints, not UNIQUE ones.
I believe we've previously rejected patches along these lines on the
grounds that the output could realistically be extremely long.
Imagine that you have a table with an integer primary key column and a
text column. The integer column has a check constraint on it. But
the text column might contain a kilobyte, or a megabyte, or even a
gigabyte worth of text, and we don't necessarily want to spit that all
out on an error. For unique constraints, we only emit the values that
are part of the constraint, which in most cases will be relatively
short (if they're more than 8kB, they won't fit into an index block);
but here the patch wants to dump the whole tuple, and that could be
really big.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company