agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedunique constraint definition within create table
6+ messages / 5 participants
[nested] [flat]
* unique constraint definition within create table
@ 2015-12-02 06:36 Andreas Kretschmer <akretschmer@spamfence.net>
2015-12-02 14:46 ` Re: unique constraint definition within create table Tom Lane <tgl@sss.pgh.pa.us>
2015-12-02 14:59 ` Re: unique constraint definition within create table Adrian Klaver <adrian.klaver@aklaver.com>
2015-12-02 18:42 ` Re: unique constraint definition within create table David G. Johnston <david.g.johnston@gmail.com>
2015-12-08 06:16 ` Re: unique constraint definition within create table Vik Fearing <vik@2ndquadrant.fr>
0 siblings, 4 replies; 6+ messages in thread
From: Andreas Kretschmer @ 2015-12-02 06:36 UTC (permalink / raw)
To: pgsql-sql
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°
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique constraint definition within create table
2015-12-02 06:36 unique constraint definition within create table Andreas Kretschmer <akretschmer@spamfence.net>
@ 2015-12-02 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2015-12-02 14:46 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique constraint definition within create table
2015-12-02 06:36 unique constraint definition within create table Andreas Kretschmer <akretschmer@spamfence.net>
@ 2015-12-02 14:59 ` Adrian Klaver <adrian.klaver@aklaver.com>
3 siblings, 0 replies; 6+ messages in thread
From: Adrian Klaver @ 2015-12-02 14:59 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique constraint definition within create table
2015-12-02 06:36 unique constraint definition within create table Andreas Kretschmer <akretschmer@spamfence.net>
@ 2015-12-02 18:42 ` David G. Johnston <david.g.johnston@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2015-12-02 18:42 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-sql
On Tue, Dec 1, 2015 at 11:36 PM, Andreas Kretschmer <
akretschmer@spamfence.net> 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 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique constraint definition within create table
2015-12-02 06:36 unique constraint definition within create table Andreas Kretschmer <akretschmer@spamfence.net>
@ 2015-12-08 06:16 ` Vik Fearing <vik@2ndquadrant.fr>
2015-12-08 12:05 ` Re: unique constraint definition within create table Andreas Kretschmer <akretschmer@spamfence.net>
3 siblings, 1 reply; 6+ messages in thread
From: Vik Fearing @ 2015-12-08 06:16 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique constraint definition within create table
2015-12-02 06:36 unique constraint definition within create table Andreas Kretschmer <akretschmer@spamfence.net>
2015-12-08 06:16 ` Re: unique constraint definition within create table Vik Fearing <vik@2ndquadrant.fr>
@ 2015-12-08 12:05 ` Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 0 replies; 6+ messages in thread
From: Andreas Kretschmer @ 2015-12-08 12:05 UTC (permalink / raw)
To: pgsql-sql
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°
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2015-12-08 12:05 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-12-02 06:36 unique constraint definition within create table Andreas Kretschmer <akretschmer@spamfence.net>
2015-12-02 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-12-02 14:59 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-12-02 18:42 ` David G. Johnston <david.g.johnston@gmail.com>
2015-12-08 06:16 ` Vik Fearing <vik@2ndquadrant.fr>
2015-12-08 12:05 ` Andreas Kretschmer <akretschmer@spamfence.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox