Thread: 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, 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@ScienceTools.com, http://ScienceTools.com/
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@comcast.net
I'm guessing that is an Oracle-ism for the sql DISTINCT keyword. Regards, Shelby Cain ----- Original Message ---- From: Wm.A.Stafford <stafford@marine.rutgers.edu> To: pgsql-general@postgresql.org Sent: Monday, November 20, 2006 2:13:13 PM 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 ____________________________________________________________________________________ Sponsored Link Don't quit your job - take classes online www.Classesusa.com
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
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.
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@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Wm.A.Stafford > Sent: Monday, November 20, 2006 12:13 PM > To: pgsql-general@postgresql.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
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
Alvaro Herrera <alvherre@commandprompt.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
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.
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.gonzales@linuxlouis.net http://www.linuxlouis.net
Le mardi 21 novembre 2006 00:47, Tom Lane a écrit : > Alvaro Herrera <alvherre@commandprompt.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/