Re: [GENERAL] Unique index problem

2015-12-21 Thread John McKown
On Mon, Dec 21, 2015 at 1:00 PM, Scott Marlowe wrote: > On Sun, Dec 20, 2015 at 11:39 PM, Sterpu Victor wrote: > > Thank you. > > > > I used the syntax with 2 indexes, it works for me. > > But why does NULL != NULL? > > Because NULL literally means "an unknown, possibly unknowable value." > > Yo

Re: [GENERAL] Unique index problem

2015-12-21 Thread Scott Marlowe
On Sun, Dec 20, 2015 at 11:39 PM, Sterpu Victor wrote: > Thank you. > > I used the syntax with 2 indexes, it works for me. > But why does NULL != NULL? Because NULL literally means "an unknown, possibly unknowable value." You need to stop thinking of NULL as A value. It is not. -- Sent via pg

Re: [GENERAL] Unique index problem

2015-12-21 Thread Sterpu Victor
t;Sterpu Victor" Cc: "Marc Mamin" ; "PostgreSQL General" ; "Andreas Kretschmer" ; "Scott Marlowe" Sent: 12/21/2015 8:44:14 AM Subject: Re: [GENERAL] Unique index problem 2015-12-21 7:39 GMT+01:00 Sterpu Victor : Thank you. I used the synta

Re: [GENERAL] Unique index problem

2015-12-20 Thread Pavel Stehule
"Marc Mamin" > To: "Sterpu Victor" > Cc: "PostgreSQL General" ; "Andreas > Kretschmer" ; "Scott Marlowe" < > scott.marl...@gmail.com> > Sent: 12/20/2015 11:44:35 PM > Subject: AW: [GENERAL] Unique index problem >

Re: [GENERAL] Unique index problem

2015-12-20 Thread Sterpu Victor
/20/2015 11:44:35 PM Subject: AW: [GENERAL] Unique index problem pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org]" im Auftrag von "Scott Marlowe [scott.marl...@gmail.com] ndet: Sonntag, 20. Dezember 2015 17:02 Sterpu Victor Po

Re: [GENERAL] Unique index problem

2015-12-20 Thread Jim Nasby
On 12/20/15 10:18 AM, Andreas Kretschmer wrote: test=*# create unique index on foo(a,b,c) where a is not null and b is not null and c is not null; CREATE INDEX As you discovered, you'd have to build separate indexes for each of the nullable fields: UNIQUE ON (a,b) WHERE c IS NULL a,c WHERE b

Re: [GENERAL] Unique index problem

2015-12-20 Thread Marc Mamin
pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org]" im Auftrag von "Scott Marlowe [scott.marl...@gmail.com] ndet: Sonntag, 20. Dezember 2015 17:02 Sterpu Victor PostgreSQL General eff: Re: [GENERAL] Unique index problem un, De

Re: [GENERAL] Unique index problem

2015-12-20 Thread Andreas Kretschmer
Andreas Kretschmer wrote: > Andreas Kretschmer wrote: > > > > > Maybe there are better solutions, it's a quick hack ;-) > > better solution: sorry, doesn't work =:( Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect.

Re: [GENERAL] Unique index problem

2015-12-20 Thread Andreas Kretschmer
Andreas Kretschmer wrote: > > Maybe there are better solutions, it's a quick hack ;-) better solution: test=*# create unique index on foo(a,b,c) where a is not null and b is not null and c is not null; CREATE INDEX (partial index) Andreas -- Really, I'm not out to destroy Microsoft. That w

Re: [GENERAL] Unique index problem

2015-12-20 Thread Andreas Kretschmer
Sterpu Victor wrote: > Hello > > I created a unique index that doesn't seem to work when one column is NULL. > Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON > lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from, > id_lab_sample_types); > Now I

Re: [GENERAL] Unique index problem

2015-12-20 Thread Scott Marlowe
On Sun, Dec 20, 2015 at 9:00 AM, Scott Marlowe wrote: > On Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor wrote: >> Hello >> >> I created a unique index that doesn't seem to work when one column is NULL. >> Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON >> lab_tests_gro

Re: [GENERAL] Unique index problem

2015-12-20 Thread Scott Marlowe
On Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor wrote: > Hello > > I created a unique index that doesn't seem to work when one column is NULL. > Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON > lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from, >

[GENERAL] Unique index problem

2015-12-20 Thread Sterpu Victor
Hello I created a unique index that doesn't seem to work when one column is NULL. Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from, id_lab_sample_types); Now I can run this insert twice and I w

Re: [GENERAL] unique index on embedded json object

2014-09-21 Thread Peter Geoghegan
On Sun, Sep 21, 2014 at 5:23 AM, Lee Jason wrote: > {"id": "12345", > "bags": [{ > "sku": "abc123", > "price": 0, > }, > { > "sku": "abc123", > "price": 0, > }] > } That's invalid JSON - there are stray commas. > However, I want sku of bags to be unique. It means the json can't

Re: [GENERAL] unique index on embedded json object

2014-09-21 Thread John R Pierce
On 9/21/2014 5:23 AM, Lee Jason wrote: > * > * > Any suggestions, please? Thank you. store your structured data in conventional SQL tables. -- john r pierce 37N 122W somewhere on the middle of the left coast

[GENERAL] unique index on embedded json object

2014-09-21 Thread Lee Jason
Hi forks, I am testing postgresql 9.4 beta2 right now. I am wondering if it is possible to create a unique index on embedded json object? For example, I create a table names "products" CREATE TABLE products (oid serial primary key, data jsonb) Now, I try to insert json object into data column.

Re: [GENERAL] unique index corruption

2013-07-24 Thread Sergey Konoplev
On Wed, Jul 24, 2013 at 11:50 AM, pg noob wrote: > In PostgreSQL 8.4... > > I am wondering if autovacuum will periodically rebuild indexes? It doesn't rebuild indexes, it marks empty index pages for reuse. > If not, how advisable is it to reindex periodically? Here described the recommendations

Re: [GENERAL] unique index corruption

2013-07-24 Thread Merlin Moncure
On Wed, Jul 24, 2013 at 1:50 PM, pg noob wrote: > > Hi all, > > In PostgreSQL 8.4... > > I am wondering if autovacuum will periodically rebuild indexes? it will not. REINDEX requires a heavy lock and for most applications it would be just plain untenable to be run without some type of application

[GENERAL] unique index corruption

2013-07-24 Thread pg noob
Hi all, In PostgreSQL 8.4... I am wondering if autovacuum will periodically rebuild indexes? If not, how advisable is it to reindex periodically? We recently had a case of unique index corruption which ended up allowing duplicate primary key IDs to get inserted and caused widespread data model

Re: [GENERAL] unique index for periods

2009-08-20 Thread Jeff Davis
On Thu, 2009-08-20 at 13:35 +0200, Harald Fuchs wrote: > Have a look at http://pgfoundry.org/projects/temporal The temporal project on pgfoundry only provides the time period type, which is (hopefully) useful, but it does not help with a non-overlapping constraint. Please see my other project her

Re: [GENERAL] unique index for periods

2009-08-20 Thread Tom Lane
Greg Stark writes: > On Thu, Aug 20, 2009 at 3:14 PM, Tom Lane wrote: >> I don't believe it is possible to use a btree index for this purpose, >> because there just isn't a way to express "overlaps" as a total order. > That's true for the general case of indexing ranges but I don't think > that's

Re: [GENERAL] unique index for periods

2009-08-20 Thread Greg Stark
On Thu, Aug 20, 2009 at 3:14 PM, Tom Lane wrote: > I don't believe it is possible to use a btree index for this purpose, > because there just isn't a way to express "overlaps" as a total order. That's true for the general case of indexing ranges but I don't think that's true for the case where ove

Re: [GENERAL] unique index for periods

2009-08-20 Thread Tom Lane
Gerhard Heift writes: > I try to create an unique index for a (time)period, and my goal is to > prevent two overlapping periods in a row. > To use the btree index I added a compare function: >return > CASE > WHEN $1.next <= $2.first THEN -1 > WHEN $2.next <= $1.first

Re: [GENERAL] unique index for periods

2009-08-20 Thread Harald Fuchs
In article <20090820065819.ga2...@gheift.kawo1.rwth-aachen.de>, Gerhard Heift writes: > Hello, > I try to create an unique index for a (time)period, and my goal is to > prevent two overlapping periods in a row. > ... > Is there another solution to solve my problem? Have a look at http://pgfoun

[GENERAL] unique index for periods

2009-08-19 Thread Gerhard Heift
Hello, I try to create an unique index for a (time)period, and my goal is to prevent two overlapping periods in a row. For this I created a type with following command: CREATE TYPE period AS ("first" timestamp with time zone, "next" timestamp with time zone); To use the btree index I add

Re: [GENERAL] UNIQUE INDEX and PRIMARY KEY

2007-10-20 Thread Tom Lane
"Kynn Jones" <[EMAIL PROTECTED]> writes: > In short, my question is: is there a way to designate a pre-existing > UNIQUE INDEX (based on data contained in NOT NULL fields) as the basis > for a table's PRIMARY KEY? No. If there were, that client software you mention would very likely still get con

[GENERAL] UNIQUE INDEX and PRIMARY KEY

2007-10-20 Thread Kynn Jones
This is a follow-up to a question I asked earlier. On 10/19/07, Gregory Stark <[EMAIL PROTECTED]> wrote: > What you need is: > > CREATE UNIQUE INDEX foo_uniq_x_y on foo (canonicalize(x,y)); > > > LOCATION: base_yyerror, scan.l:795 OK, now, what if instead of this -> ALTER TABLE foo ADD CONSTRAI

Re: [GENERAL] unique index on variable time

2006-04-16 Thread Tom Lane
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Sun, Apr 16, 2006 at 07:07:11PM +0300, [EMAIL PROTECTED] wrote: >> I am interested to know if I can define an unique index on a timestamp >> column to reject values within one hour. Perhaps CREATE UNIQUE INDEX foo ON tab (date_trunc('hour', co

Re: [GENERAL] unique index on variable time

2006-04-16 Thread Michael Fuhr
On Sun, Apr 16, 2006 at 07:07:11PM +0300, [EMAIL PROTECTED] wrote: > I am interested to know if I can define an unique index on a timestamp > column to reject values within one hour. Last month I posted an idea for enforcing unique constraints on date ranges by using a composite type and a custom

[GENERAL] unique index on variable time

2006-04-16 Thread [EMAIL PROTECTED]
hello, I am interested to know if I can define an unique index on a timestamp column to reject values within one hour. insert into table(timestamp_col) values(LOCALTIMESTAMP); insert into table(timestamp_col) values(LOCALTIMESTAMP + '5 minutes'::INTERVAL); I want the second insert to fail w

Re: [GENERAL] Unique index with Null value in one field

2005-10-20 Thread Chris Travers
Hrishi Joshi wrote: Hi, I need to define a Unique index on 3 non-PK fields (composite key) on my table in PostgreSQL 8.0.3. The problem is, if any of those 3 fields is Null, PostgreSQL allows duplicate rows to be inserted. While searching through archives, I found more information about this.

Re: [GENERAL] Unique index with Null value in one field

2005-10-12 Thread Jaime Casanova
On 11 Oct 2005 17:36:59 -0500, Hrishi Joshi <[EMAIL PROTECTED]> wrote: > Hi, > > I need to define a Unique index on 3 non-PK fields (composite key) on my > table in PostgreSQL 8.0.3. > > The problem is, if any of those 3 fields is Null, PostgreSQL allows > duplicate rows to be inserted. While searc

Re: [GENERAL] Unique index with Null value in one field

2005-10-11 Thread Stephan Szabo
On Tue, 11 Oct 2005, Hrishi Joshi wrote: > I need to define a Unique index on 3 non-PK fields (composite key) on my > table in PostgreSQL 8.0.3. > > The problem is, if any of those 3 fields is Null, PostgreSQL allows > duplicate rows to be inserted. While searching through archives, I found > mor

Re: [GENERAL] Unique index with Null value in one field

2005-10-11 Thread Tom Lane
Hrishi Joshi <[EMAIL PROTECTED]> writes: > I need to define a Unique index on 3 non-PK fields (composite key) on my > table in PostgreSQL 8.0.3. > The problem is, if any of those 3 fields is Null, PostgreSQL allows > duplicate rows to be inserted. That is the behavior defined by the SQL standard.

[GENERAL] Unique index with Null value in one field

2005-10-11 Thread Hrishi Joshi
Hi, I need to define a Unique index on 3 non-PK fields (composite key) on my table in PostgreSQL 8.0.3. The problem is, if any of those 3 fields is Null, PostgreSQL allows duplicate rows to be inserted. While searching through archives, I found more information about this. But I need to know how

Re: [GENERAL] unique index with bool

2005-05-24 Thread Stuart Bishop
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: > CREATE UNIQUE INDEX name on table(param1,param2); > > How to create such unique index when param2 is bool type, and this param2 > should be accepted only in case of true ? > > I tried: CREATE UNIQUE INDEX name on table(par

Re: [GENERAL] unique index with bool

2005-05-19 Thread Scott Marlowe
On Thu, 2005-05-19 at 09:49, [EMAIL PROTECTED] wrote: > CREATE UNIQUE INDEX name on table(param1,param2); > > How to create such unique index when param2 is bool type, and this param2 > should be accepted only in case of true ? > > I tried: CREATE UNIQUE INDEX name on table(param1,(param2 = 'tru

Re: [GENERAL] unique index with bool

2005-05-19 Thread Alban Hertroys
[EMAIL PROTECTED] wrote: CREATE UNIQUE INDEX name on table(param1,param2); How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ? I tried: CREATE UNIQUE INDEX name on table(param1,(param2 = 'true')); but it's not working. CREATE UNIQUE I

Re: [GENERAL] unique index with bool

2005-05-19 Thread Richard Huxton
[EMAIL PROTECTED] wrote: CREATE UNIQUE INDEX name on table(param1,param2); How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ? I tried: CREATE UNIQUE INDEX name on table(param1,(param2 = 'true')); but it's not working. Something like:

[GENERAL] unique index with bool

2005-05-19 Thread tmpmac
CREATE UNIQUE INDEX name on table(param1,param2); How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ? I tried: CREATE UNIQUE INDEX name on table(param1,(param2 = 'true')); but it's not working. Regards, Mac -

Re: [GENERAL] Unique Index

2005-01-20 Thread Tino Wildenhain
Am Donnerstag, den 20.01.2005, 06:09 -0800 schrieb J. Greenlees: > Tino Wildenhain wrote: > > Hi, > > > > Am Mittwoch, den 19.01.2005, 15:02 -0800 schrieb J. Greenlees: > > > >>Roman Neuhauser wrote: > >> > >>># [EMAIL PROTECTED] / 2005-01-20 01:35:32 +1100: > >>> > >>> > i have a unique inde

Re: [GENERAL] Unique Index

2005-01-20 Thread Dann Corbit
Yes. I was wrong. Sorry about the noise. -Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 12:01 PM To: Dann Corbit Cc: Tom Lane; Greg Stark; pgsql-general@postgresql.org Subject: RE: [GENERAL] Unique Index On Thu, 20 Jan 2005, Dann

Re: [GENERAL] Unique Index

2005-01-20 Thread Stephan Szabo
On Thu, 20 Jan 2005, Dann Corbit wrote: > Would the constraint not be satisfied if each combination (including > NULL) were not also forced to be unique? The constraint would be satisfied, however cases that the constraint is satisfied for would not be allowed. The case I gave below is one for w

Re: [GENERAL] Unique Index

2005-01-20 Thread Greg Stark
"Dann Corbit" <[EMAIL PROTECTED]> writes: > Would the constraint not be satisfied if each combination (including > NULL) were not also forced to be unique? > > I maintain that the constraint is still satisfied. > > So, it is satisfied if I stuff thousands of NULL values in there. > > And it is

Re: [GENERAL] Unique Index

2005-01-20 Thread Frank D. Engel, Jr.
tiple null values would not violate it. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Thursday, January 20, 2005 8:03 AM To: Greg Stark Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Unique Index Greg Stark <[EMAIL PROTECTED]>

Re: [GENERAL] Unique Index

2005-01-20 Thread Dann Corbit
them. -Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 11:14 AM To: Dann Corbit Cc: Tom Lane; Greg Stark; pgsql-general@postgresql.org Subject: Re: [GENERAL] Unique Index On Thu, 20 Jan 2005, Dann Corbit wrote: > It is clear to me t

Re: [GENERAL] Unique Index

2005-01-20 Thread Stephan Szabo
On Thu, 20 Jan 2005, Dann Corbit wrote: > It is clear to me that only allowing a single null value will not > violate the explanation below. Given two rows in T with one column each (NULL), (NULL) Find two rows such that the value of each column in one row is non-null and equal to the value of

Re: [GENERAL] Unique Index

2005-01-20 Thread Dann Corbit
, January 20, 2005 8:03 AM To: Greg Stark Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Unique Index Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> Not for UNIQUE constraints. SQL92 section 4.10 "Integrity constraints"

Re: [GENERAL] Unique Index

2005-01-20 Thread Greg Stark
"Frank D. Engel, Jr." <[EMAIL PROTECTED]> writes: > I'm sure this won't work for some reason, but something similar might; why not > create a unique index on a constant where all three are null; something along > these lines (in addition to the others): > > CREATE UNIQUE INDEX foo_trio_index ON f

Re: [GENERAL] Unique Index

2005-01-20 Thread Scott Marlowe
On Wed, 2005-01-19 at 22:20, Alex wrote: > > Maybe there could be an option in the creation of the index to indicate > on how to use NULL values. > > How do other DBMS handle this? http://troels.arvin.dk/db/rdbms/ ---(end of broadcast)--- TIP 9:

Re: [GENERAL] Unique Index

2005-01-20 Thread Frank D. Engel, Jr.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm sure this won't work for some reason, but something similar might; why not create a unique index on a constant where all three are null; something along these lines (in addition to the others): CREATE UNIQUE INDEX foo_trio_index ON foo (1) WHERE

Re: [GENERAL] Unique Index

2005-01-20 Thread Greg Stark
Dawid Kuroczko <[EMAIL PROTECTED]> writes: > Don't worry about "index bloat". These additional indexes will be used > only when your main (foo_abc_index) is not used, so there won't be > any duplicate data in them. The main index will have _all_ the tuples in them, even where some of the columns

Re: [GENERAL] Unique Index

2005-01-20 Thread Tom Lane
Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> Not for UNIQUE constraints. SQL92 section 4.10 "Integrity constraints": >> >> A unique constraint is satisfied if and only if no two rows in >> a table have the same non-null values in the unique columns. > That's

Re: [GENERAL] Unique Index

2005-01-20 Thread Greg Stark
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > Tom Lane <[EMAIL PROTECTED]> writes: > >> Direct your complaints to the ISO SQL standards committee. > > > The SQL standard generally treats NULLs as a escape hatch for constraints. Huh? I thought I was agreeing wi

Re: [GENERAL] Unique Index

2005-01-20 Thread J. Greenlees
Tino Wildenhain wrote: Hi, Am Mittwoch, den 19.01.2005, 15:02 -0800 schrieb J. Greenlees: Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2005-01-20 01:35:32 +1100: i have a unique index on a table over multiple columns. If now one of the records has a null value in one of the indexed columns i can

Re: [GENERAL] Unique Index

2005-01-20 Thread Tom Lane
Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> Direct your complaints to the ISO SQL standards committee. > The SQL standard generally treats NULLs as a escape hatch for constraints. Not for UNIQUE constraints. SQL92 section 4.10 "Integrity constraints":

Re: [GENERAL] Unique Index

2005-01-20 Thread Martijn van Oosterhout
On Thu, Jan 20, 2005 at 04:32:37PM +0900, Michael Glaesemann wrote: > > On Jan 20, 2005, at 16:03, David Garamond wrote: > > >Dann Corbit wrote: > >>True, but the standard says nothing about the creation of an index, so > >>you can make it behave in any way that you see fit. > > > >But I thought

Re: [GENERAL] Unique Index

2005-01-20 Thread Dawid Kuroczko
On Thu, 20 Jan 2005 15:20:26 +1100, Alex <[EMAIL PROTECTED]> wrote: > I actually just wanted to know if there is a way around this problem. > Obviously it is implemented that way for whatever reason. Well, if you really need it, partial indexes are your friends! :) For clarity, let's say you have

Re: [GENERAL] Unique Index

2005-01-19 Thread Vincent Hikida
I actually just wanted to know if there is a way around this problem. Obviously it is implemented that way for whatever reason. I still though think some arguments given in some of the replies, while probably correct, are besides the point. Sorry. I was hoping someone else would answer. I use a

Re: [GENERAL] Unique Index

2005-01-19 Thread Michael Glaesemann
On Jan 20, 2005, at 16:03, David Garamond wrote: Dann Corbit wrote: True, but the standard says nothing about the creation of an index, so you can make it behave in any way that you see fit. But I thought we are talking about unique _constraint_ here (which is certainly regulated by the standard).

Re: [GENERAL] Unique Index

2005-01-19 Thread Tino Wildenhain
Hi, Am Mittwoch, den 19.01.2005, 15:02 -0800 schrieb J. Greenlees: > Roman Neuhauser wrote: > > # [EMAIL PROTECTED] / 2005-01-20 01:35:32 +1100: > > > >>i have a unique index on a table over multiple columns. If now one of > >>the records has a null value in one of the indexed columns i can inse

Re: [GENERAL] Unique Index

2005-01-19 Thread David Garamond
Dann Corbit wrote: True, but the standard says nothing about the creation of an index, so you can make it behave in any way that you see fit. But I thought we are talking about unique _constraint_ here (which is certainly regulated by the standard). -- dave ---(end of broa

Re: [GENERAL] Unique Index

2005-01-19 Thread Greg Stark
Alex <[EMAIL PROTECTED]> writes: > I actually just wanted to know if there is a way around this problem. > Obviously > it is implemented that way for whatever reason. The way around is to make all the columns NOT NULL. For most applications unique indexes don't make much sense on nullable column

Re: [GENERAL] Unique Index

2005-01-19 Thread Greg Stark
Tom Lane <[EMAIL PROTECTED]> writes: > "Dann Corbit" <[EMAIL PROTECTED]> writes: > > > Even at that, I think that being able to insert more than one null value > > into a unique index should be considered as a bug (or diagnosed as an > > error). > > Direct your complaints to the ISO SQL standard

Re: [GENERAL] Unique Index

2005-01-19 Thread Alex
I actually just wanted to know if there is a way around this problem. Obviously it is implemented that way for whatever reason. I still though think some arguments given in some of the replies, while probably correct, are besides the point. I use a unique index that may contain null values. On

Re: [GENERAL] Unique Index

2005-01-19 Thread Stephan Szabo
On Wed, 19 Jan 2005, Stephan Szabo wrote: > > On Wed, 19 Jan 2005, Dann Corbit wrote: > > > True, but the standard says nothing about the creation of an index, so > > you can make it behave in any way that you see fit. > > The unique index is however used to model the unique constraint in > Postgr

Re: [GENERAL] Unique Index

2005-01-19 Thread Stephan Szabo
anuary 19, 2005 4:27 PM > To: Dann Corbit > Cc: [EMAIL PROTECTED]; J. Greenlees; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Unique Index > > > On Wed, 19 Jan 2005, Dann Corbit wrote: > > > Even at that, I think that being able to insert more than one null >

Re: [GENERAL] Unique Index

2005-01-19 Thread Stephan Szabo
On Wed, 19 Jan 2005, Dann Corbit wrote: > Even at that, I think that being able to insert more than one null value > into a unique index should be considered as a bug (or diagnosed as an > error). AFAICT the UNIQUE constraint that it's used to model explicitly allows multiple NULLs in the spec s

Re: [GENERAL] Unique Index

2005-01-19 Thread Dann Corbit
-general@postgresql.org Subject: Re: [GENERAL] Unique Index "Dann Corbit" <[EMAIL PROTECTED]> writes: > Or (perhaps better yet, violating trichotomy) ... > If has a null numeric value, then ALL of the following are > FALSE for that case: > Some_column < 0 >

Re: [GENERAL] Unique Index

2005-01-19 Thread Dann Corbit
-general@postgresql.org Subject: Re: [GENERAL] Unique Index On Wed, 19 Jan 2005, Dann Corbit wrote: > Even at that, I think that being able to insert more than one null value > into a unique index should be considered as a bug (or diagnosed as an > error). AFAICT the UNIQUE constraint

Re: [GENERAL] Unique Index

2005-01-19 Thread Tom Lane
"Dann Corbit" <[EMAIL PROTECTED]> writes: > Or (perhaps better yet, violating trichotomy) ... > If has a null numeric value, then ALL of the following are > FALSE for that case: > Some_column < 0 > Some_column > 0 > Some_column = 0 > Some_column <> 0 // This is the one that many find surprising

Re: [GENERAL] Unique Index

2005-01-19 Thread Dann Corbit
ndex should be considered as a bug (or diagnosed as an error). -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, January 19, 2005 3:30 PM To: J. Greenlees Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Unique Index

Re: [GENERAL] Unique Index

2005-01-19 Thread vhikida
According to Date you should never use NULLs. This is because a NULL can mean many different things. It can mean not known (e.g. I know he has an age but I don't know what it is), It can be not applicable (e.g. in a Party table of organizations and people, people would be of a certain sex but an or

Re: [GENERAL] Unique Index

2005-01-19 Thread J. Greenlees
Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2005-01-20 01:35:32 +1100: i have a unique index on a table over multiple columns. If now one of the records has a null value in one of the indexed columns i can insert the same record multiple times. Is this a problem within postgres or expected?

Re: [GENERAL] Unique Index

2005-01-19 Thread Roman Neuhauser
# [EMAIL PROTECTED] / 2005-01-20 01:35:32 +1100: > i have a unique index on a table over multiple columns. If now one of > the records has a null value in one of the indexed columns i can insert > the same record multiple times. > > Is this a problem within postgres or expected? In SQL, NUL

Re: [GENERAL] Unique Index

2005-01-19 Thread Stephan Szabo
On Thu, 20 Jan 2005, Alex wrote: > i have a unique index on a table over multiple columns. If now one of > the records has a null value in one of the indexed columns i can insert > the same record multiple times. > > Is this a problem within postgres or expected? Expected. NULLs are effectively

[GENERAL] Unique Index

2005-01-19 Thread Alex
Hi, i have a unique index on a table over multiple columns. If now one of the records has a null value in one of the indexed columns i can insert the same record multiple times. Is this a problem within postgres or expected? Example: index unique, btree (colA, colB, colC); would still allow me t

[GENERAL] unique index creation failure in 7.4.1 - bug?

2004-01-18 Thread Ben Marklein
Did a pg_dump of a 7.2 DB and am now trying to restore to 7.4.1. Index creation on a table fails: db=# CREATE UNIQUE INDEX person_info_username_ix ON person_info USING btree (username); ERROR: could not create unique index DETAIL: Table contains duplicated values. Of course, this index existed

Re: [GENERAL] Unique Index vs. Unique Constraint

2003-10-10 Thread scott.marlowe
On Fri, 10 Oct 2003, Thomas LeBlanc wrote: > Does a Unique Constraint build a unique index? > > What is the difference? None that I know of. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.post

Re: [GENERAL] Unique Index vs. Unique Constraint

2003-10-10 Thread Manfred Koizar
On Fri, 10 Oct 2003 13:59:38 -0500, "Thomas LeBlanc" <[EMAIL PROTECTED]> wrote: >Does a Unique Constraint build a unique index? Yes. >What is the difference? A constraint is an abstract concept, an index is an implementation detail. Servus Manfred ---(end of broadcast)

[GENERAL] Unique Index vs. Unique Constraint

2003-10-10 Thread Thomas LeBlanc
Does a Unique Constraint build a unique index? What is the difference? Thanks, Thomas LeBlanc _ Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -