Re: PostgreSQL equivalent of the Oracale 'unique' qualifier

I hope the subject says it all. I'm porting an Oracle-centric
application to PostgreSQL and the Oracle sql is full of the 'unique'
qualifier. I'm assuming PostgreSQL does not support 'unique' since
don't see a 'unique' anywhere in the PostgreSQL docs. Is there a
substitute or a technique to get the same result?

Thanks,
-=bill stafford

On Mon, 2006-11-20 at 15:13 -0500, Wm.A.Stafford wrote:
> I hope the subject says it all. I'm porting an Oracle-centric
> application to PostgreSQL and the Oracle sql is full of the 'unique'
> qualifier. I'm assuming PostgreSQL does not support 'unique' since
> don't see a 'unique' anywhere in the PostgreSQL docs. Is there a
> substitute or a technique to get the same result?

Distinct?

Joshua D. Drake

>
> Thanks,
> -=bill stafford
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Wm.A.Stafford wrote:
> I hope the subject says it all. I'm porting an Oracle-centric
> application to PostgreSQL and the Oracle sql is full of the 'unique'
> qualifier. I'm assuming PostgreSQL does not support 'unique' since
> don't see a 'unique' anywhere in the PostgreSQL docs. Is there a
> substitute or a technique to get the same result?

You gotta be kidding. Of course Postgres supports UNIQUE.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Wm.A.Stafford wrote:
> I hope the subject says it all. I'm porting an Oracle-centric
> application to PostgreSQL and the Oracle sql is full of the 'unique'
> qualifier. I'm assuming PostgreSQL does not support 'unique' since
> don't see a 'unique' anywhere in the PostgreSQL docs. Is there a
> substitute or a technique to get the same result?
>
> Thanks,
> -=bill stafford

DISTINCT, and PostgreSQL's proprietary extension, DISTINCT ON.
http://www.postgresql.org/docs/8.1/interactive/queries-select-lists.html#QUERIES-DISTINCT

UNIQUE index:
http://www.postgresql.org/files/documentation/books/aw_pgsql/node108.htm
l

UNIQUE constraint:
http://www.postgresql.org/files/documentation/books/aw_pgsql/node129.htm
l

Online manual information:
http://www.postgresql.org/docs/8.2/interactive/ddl-constraints.html#AEN2
058
http://www.postgresql.org/docs/8.2/interactive/indexes-unique.html

There is a little search box in the upper right hand corner that might
prove helpful for your cause.

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Wm.A.Stafford
> Sent: Monday, November 20, 2006 12:13 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] PostgreSQL equivalent of the Oracale 'unique'
qualifier
>
> I hope the subject says it all. I'm porting an Oracle-centric
> application to PostgreSQL and the Oracle sql is full of the 'unique'
> qualifier. I'm assuming PostgreSQL does not support 'unique' since
> don't see a 'unique' anywhere in the PostgreSQL docs. Is there a
> substitute or a technique to get the same result?
>
> Thanks,
> -=bill stafford
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

Are you talking about a SQL statement qualifier, where you may be
referring to "distinct"

On Mon, 20 Nov 2006, Wm.A.Stafford wrote:

> I hope the subject says it all. I'm porting an Oracle-centric application to
> PostgreSQL and the Oracle sql is full of the 'unique' qualifier. I'm
> assuming PostgreSQL does not support 'unique' since don't see a 'unique'
> anywhere in the PostgreSQL docs. Is there a substitute or a technique to get
> the same result?
>
> Thanks,
> -=bill stafford
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
Louis Gonzales
louis(dot)gonzales(at)linuxlouis(dot)net
http://www.linuxlouis.net

On Mon, 20 Nov 2006, Wm.A.Stafford wrote:
>
> I hope the subject says it all. I'm porting an Oracle-centric
> application to PostgreSQL and the Oracle sql is full of the 'unique'
> qualifier. I'm assuming PostgreSQL does not support 'unique' since
> don't see a 'unique' anywhere in the PostgreSQL docs. Is there a
> substitute or a technique to get the same result?
>
> Thanks,
> -=bill stafford
>

Would "distinct" do it for you?

RT

--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy(at)ScienceTools(dot)com, http://ScienceTools.com/

On Nov 20, 2006, at 21:13 , Wm.A.Stafford wrote:

> I hope the subject says it all. I'm porting an Oracle-centric
> application to PostgreSQL and the Oracle sql is full of the
> 'unique' qualifier. I'm assuming PostgreSQL does not support
> 'unique' since don't see a 'unique' anywhere in the PostgreSQL
> docs. Is there a substitute or a technique to get the same result?

Which documentation? It's in the index:

http://www.postgresql.org/docs/8.1/interactive/bookindex.html

The entry points here:

http://www.postgresql.org/docs/8.1/interactive/ddl-
constraints.html#AEN2016

PostgreSQL does ANSI SQL:

create table foo (s text unique);

create table foo (s text, constraint s_unique unique (s));

alter table foo add constraint s_unique unique (s);

And the usual index syntax:

create unique index foo_s_index on foo (s);

Syntax reference:

http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html
http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html

Alexander.

On Monday 20 November 2006 12:13 pm, Wm.A.Stafford wrote:
> I hope the subject says it all. I'm porting an Oracle-centric
> application to PostgreSQL and the Oracle sql is full of the 'unique'
> qualifier. I'm assuming PostgreSQL does not support 'unique' since
> don't see a 'unique' anywhere in the PostgreSQL docs. Is there a
> substitute or a technique to get the same result?
>
> Thanks,
> -=bill stafford
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
Two places I found to specify a unique constraint.
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
http://www.postgresql.org/docs/8.1/interactive/sql-createindex.htm
--
Adrian Klaver
aklaver(at)comcast(dot)net

On Mon, 2006-11-20 at 15:13 -0500, Wm.A.Stafford wrote:
> I hope the subject says it all. I'm porting an Oracle-centric
> application to PostgreSQL and the Oracle sql is full of the 'unique'
> qualifier. I'm assuming PostgreSQL does not support 'unique' since
> don't see a 'unique' anywhere in the PostgreSQL docs. Is there a
> substitute or a technique to get the same result?
>

You mean a UNIQUE index on a column?

http://www.postgresql.org/docs/8.1/static/sql-createtable.html
http://www.postgresql.org/docs/8.1/static/sql-createindex.html

The postgresql docs are filled with the word "UNIQUE".

Regards,
Jeff Davis

On Mon, 2006-11-20 at 14:13, Wm.A.Stafford wrote:
> I hope the subject says it all. I'm porting an Oracle-centric
> application to PostgreSQL and the Oracle sql is full of the 'unique'
> qualifier. I'm assuming PostgreSQL does not support 'unique' since
> don't see a 'unique' anywhere in the PostgreSQL docs. Is there a
> substitute or a technique to get the same result?

Context is king.

Do you mean:

create unique index abc on table xyz(field1);

???

OR some other usage of unique. An example would really help us help
you. otherwise we're all blind men describing an elephant (the
postgresql elephant at that!)

So, is it in an index, or somewhere else. Cause in an index it's fine.
I've never used unique anywhere else in pgsql.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Wm.A.Stafford wrote:
>> I hope the subject says it all. I'm porting an Oracle-centric
>> application to PostgreSQL and the Oracle sql is full of the 'unique'
>> qualifier. I'm assuming PostgreSQL does not support 'unique' since
>> don't see a 'unique' anywhere in the PostgreSQL docs. Is there a
>> substitute or a technique to get the same result?

> You gotta be kidding. Of course Postgres supports UNIQUE.

Actually, there is a <unique predicate> in SQL92, which we've not gotten
around to implementing ... but from the subsequent discussion it seems
that what the OP is looking at is something else, ie, a gratuitously
nonstandard spelling of the DISTINCT modifier for aggregate functions :-(

A <unique predicate> is syntactically like EXISTS:

<unique predicate> ::= UNIQUE <table subquery>

General Rules

1) Let T be the result of the <table subquery>.

2) If there are no two rows in T such that the value of each column
in one row is non-null and is equal to the value of the cor-
responding column in the other row according to Subclause 8.2,
"<comparison predicate>", then the result of the <unique predi-
cate> is true; otherwise, the result of the <unique predicate>
is false.

(This matches up with the behavior of unique constraints/unique indexes
because the spec actually defines a unique constraint in terms of the
truth of a unique predicate.)

Hmm ... note that there is a difference between this definition of
uniqueness and the behavior of DISTINCT, which is that two rows
containing nulls can be "the same" according to DISTINCT, but they'll
never be "the same" according to UNIQUE. Is it possible that Oracle's
UNIQUE aggregate modifier is not just a relabeling of DISTINCT, but uses
a two-nulls-are-different-from-each-other definition unlike DISTINCT?
If so, and if this fine point is critical to the OP's code, he's gonna
have a bit of a problem.

regards, tom lane

Le mardi 21 novembre 2006 00:47, Tom Lane a écrit :
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Wm.A.Stafford wrote:
> >> I hope the subject says it all. I'm porting an Oracle-centric
> >> application to PostgreSQL and the Oracle sql is full of the 'unique'
> >> qualifier. I'm assuming PostgreSQL does not support 'unique' since
> >> don't see a 'unique' anywhere in the PostgreSQL docs. Is there a
> >> substitute or a technique to get the same result?
> >
> > You gotta be kidding. Of course Postgres supports UNIQUE.
>
> Actually, there is a <unique predicate> in SQL92, which we've not gotten
> around to implementing ... but from the subsequent discussion it seems
> that what the OP is looking at is something else, ie, a gratuitously
> nonstandard spelling of the DISTINCT modifier for aggregate functions :-(
>
> A <unique predicate> is syntactically like EXISTS:
>
> <unique predicate> ::= UNIQUE <table subquery>
>
> General Rules
>
> 1) Let T be the result of the <table subquery>.
>
> 2) If there are no two rows in T such that the value of each
> column in one row is non-null and is equal to the value of the cor-
> responding column in the other row according to Subclause 8.2, "<comparison
> predicate>", then the result of the <unique predi- cate> is true;
> otherwise, the result of the <unique predicate> is false.
>
> (This matches up with the behavior of unique constraints/unique indexes
> because the spec actually defines a unique constraint in terms of the
> truth of a unique predicate.)
>
> Hmm ... note that there is a difference between this definition of
> uniqueness and the behavior of DISTINCT, which is that two rows
> containing nulls can be "the same" according to DISTINCT, but they'll
> never be "the same" according to UNIQUE. Is it possible that Oracle's
> UNIQUE aggregate modifier is not just a relabeling of DISTINCT, but uses
> a two-nulls-are-different-from-each-other definition unlike DISTINCT?
> If so, and if this fine point is critical to the OP's code, he's gonna
> have a bit of a problem.

In a migration from Informix we just done at dalibo, we found an UNIQUE
constraint (and index) used where two rows containing NULL in a column where
to be considered the same row, and had to rewrite it:

- CREATE unique index pki_exception on exception (id_classement, domaine,
id_categorie);
+ CREATE unique index pki_exception on exception (coalesce(id_classement,-1),
coalesce(domaine,''), coalesce(id_categorie,-1));

Then we have a behavior similar as Informix one:
insert into exception (id_classement, domaine, id_categorie)
values (1, null, 1);

insert into exception (id_classement, domaine, id_categorie)
values (1, null, 1);

=> error

Without the coalesce's, no error would have risen.

Hope this helps,
--
Dimitri Fontaine
http://www.dalibo.com/