Thread: unique constraint definition within create table
Hi @ll,
i'm trying to create a table with 2 int-columns and a constraint that a
pair of (x,y) cannot be as (y,x) inserted:
test=# create table foo(u1 int,u2 int, unique (least(u1,u2),greatest(u1,u2)));
ERROR: syntax error at or near "("
LINE 1: create table foo(u1 int,u2 int, unique (least(u1,u2),greates...
I know, i can solve that in this way:
test=*# create table foo(u1 int,u2 int);
CREATE TABLE
test=*# create unique index idx_foo on foo(least(u1,u2),greatest(u1,u2));
CREATE INDEX
But is there a way to define the unique constraint within the create table - command?
Thx.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Andreas Kretschmer <akretschmer@spamfence.net> writes:
> i'm trying to create a table with 2 int-columns and a constraint that a
> pair of (x,y) cannot be as (y,x) inserted:
> test=# create table foo(u1 int,u2 int, unique (least(u1,u2),greatest(u1,u2)));
> ERROR: syntax error at or near "("
> LINE 1: create table foo(u1 int,u2 int, unique (least(u1,u2),greates...
> I know, i can solve that in this way:
> test=*# create table foo(u1 int,u2 int);
> CREATE TABLE
> test=*# create unique index idx_foo on foo(least(u1,u2),greatest(u1,u2));
> CREATE INDEX
> But is there a way to define the unique constraint within the create table - command?
No. Per SQL standard, the argument of a UNIQUE (or PRIMARY KEY)
constraint can only be a list of column names.
We would consider relaxing that, except it would break the
information_schema views for such constraints.
regards, tom lane
On 12/01/2015 10:36 PM, Andreas Kretschmer wrote:
> Hi @ll,
>
> i'm trying to create a table with 2 int-columns and a constraint that a
> pair of (x,y) cannot be as (y,x) inserted:
>
> test=# create table foo(u1 int,u2 int, unique (least(u1,u2),greatest(u1,u2)));
> ERROR: syntax error at or near "("
> LINE 1: create table foo(u1 int,u2 int, unique (least(u1,u2),greates...
>
>
> I know, i can solve that in this way:
>
> test=*# create table foo(u1 int,u2 int);
> CREATE TABLE
> test=*# create unique index idx_foo on foo(least(u1,u2),greatest(u1,u2));
> CREATE INDEX
>
>
> But is there a way to define the unique constraint within the create table - command?
http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html
Shows that expressions are not allowed in UNIQUE constraints.
"UNIQUE ( column_name [, ... ] ) index_parameters
whereas
http://www.postgresql.org/docs/9.4/interactive/sql-createindex.html
"CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table_name [ USING
method ] ( { column_name | ( expression ) } ..."
does.
So no there is not a way to do that in the CREATE TABLE command. You can
bundle the commands though:
BEGIN;
create table foo(u1 int,u2 int);
create unique index idx_foo on foo(least(u1,u2),greatest(u1,u2));
COMMIT;
to make sure they either both succeed or fail.
>
> Thx.
>
> Andreas
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi @ll,
i'm trying to create a table with 2 int-columns and a constraint that a
pair of (x,y) cannot be as (y,x) inserted:
test=# create table foo(u1 int,u2 int, unique (least(u1,u2),greatest(u1,u2)));
ERROR: syntax error at or near "("
LINE 1: create table foo(u1 int,u2 int, unique (least(u1,u2),greates...
I know, i can solve that in this way:
test=*# create table foo(u1 int,u2 int);
CREATE TABLE
test=*# create unique index idx_foo on foo(least(u1,u2),greatest(u1,u2));
CREATE INDEX
But is there a way to define the unique constraint within the create table - command?
You might consider adding a table constraint "u1 <= u2". You could also consider adding a trigger that munges data on INSERT/UPDATE so that this constraint is met regardless of the order supplied by the user.
David J.
On 12/02/2015 07:36 AM, Andreas Kretschmer wrote:
> Hi @ll,
>
> i'm trying to create a table with 2 int-columns and a constraint that a
> pair of (x,y) cannot be as (y,x) inserted:
>
> test=# create table foo(u1 int,u2 int, unique (least(u1,u2),greatest(u1,u2)));
> ERROR: syntax error at or near "("
> LINE 1: create table foo(u1 int,u2 int, unique (least(u1,u2),greates...
>
>
> I know, i can solve that in this way:
>
> test=*# create table foo(u1 int,u2 int);
> CREATE TABLE
> test=*# create unique index idx_foo on foo(least(u1,u2),greatest(u1,u2));
> CREATE INDEX
>
>
> But is there a way to define the unique constraint within the create table - command?
You can use exclusion constraints for this.
CREATE TABLE foo ( u1 integer, u2 integer, EXCLUDE USING btree ( least(u1, u2) WITH =, greatest(u1,
u2)WITH =)
);
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Vik Fearing <vik@2ndquadrant.fr> wrote: > > But is there a way to define the unique constraint within the create table - command? > > You can use exclusion constraints for this. Yeah! Great. I love this exclusion constraint, but it doesn't occurred to me. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°