Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT
On Thu, Apr 28, 2005 at 02:04:29PM +0200, Marco Colombo <[EMAIL PROTECTED]> wrote a message of 146 lines which said: > No. NULL is NOT 'None', nor 'undef', and definitely not NULL as in > C. Thanks for the very good and detailed explanation of NULL in SQL. Curious people may note that the strange semantics of NULL are heavily criticized in C. J. Date and Hugh Darwen "A Guide to the SQL Standard" (Addison-Wesley) [Side note: I was perfectly aware for NULL in C, where it is just an ordinary zero, that's why I only mentioned Perl and Python.] > marco=# select 2 = NULL; > ?column? > -- > > (1 row) Even better, you can write; registry=> select NULL = NULL; ?column? -- (1 row) > Depending on what you're trying to achieve, you may need to split > the table (normalization the theorists call it). Yes, I noticed in similar organizations that the Hosts table was split in one table for names and one for addresses, may be for exactly that reason. > Review your design, maybe either the table schema or the choice of > the primary key is not natural for your database. At the present time, it seems that, practically speaking, the technique proposed by Sebastian Böck (two index) is perfectly fine. This technique can be summarized as "PostgreSQL, I tell you to treat all NULL addresses as being the same value". ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT
On Wed, Apr 27, 2005 at 05:04:07PM +0200,
Sebastian Böck <[EMAIL PROTECTED]> wrote
a message of 24 lines which said:
One is enough :-)
v
> CREATE TABLE table x (
> name TEXT NOT NULL,
> address INET
> );
>
> CREATE UNIQUE INDEX na ON x (name, address);
> CREATE UNIQUE INDEX n ON x (name) WHERE address IS NULL;
Great! It works fine. Many thanks.
tests=> select * from x;
name | address
--+-
foo |
foo | 1.2.3.4
foo | ::1
bar | ::1
bar |
(5 rows)
tests=> insert into x (name) values ('bar');
ERROR: duplicate key violates unique constraint "n"
tests=> insert into x (name, address) values ('bar', '::1');
ERROR: duplicate key violates unique constraint "na"
tests=> insert into x (name) values ('new');
INSERT 21128 1
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT
On Wed, Apr 27, 2005 at 09:36:57AM -0500, Scott Marlowe <[EMAIL PROTECTED]> wrote a message of 18 lines which said: > Often the best bet here, btw, is to declare it not null then use > something other than null to represent null, like the text > characters NA or something. Yes, but it defeats the purpose of NULL. And what should I use as a "pseudo-NULL" value for INET? 127.0.0.1? 0.0.0.0? Special values are well-known for the problems they raise. That's why many languages have NULL-like solutions (None in Python, undef in Perl, Maybe types in Haskell, etc). ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT
On Wed, Apr 27, 2005 at 04:50:23PM +0200, Sebastian Böck <[EMAIL PROTECTED]> wrote a message of 48 lines which said: > CREATE UNIQUE INDEX na ON x (name, address) WHERE address IS NULL; No, because it prevents two tuples with the same value of "name". ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT
Stephane Bortzmeyer <[EMAIL PROTECTED]> writes: > Yes, PostgreSQL is right and implement the standard. Now, what's the > rationale for the standard? I understand it for a single column but, > for several columns, it should be still possible to have different > tuples, such as (3, NULL) and (5, NULL) for instance. If that's what you want, declare it as UNIQUE not PRIMARY KEY. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT
On Wed, Apr 27, 2005 at 10:26:30AM -0400,
Tom Lane <[EMAIL PROTECTED]> wrote
a message of 9 lines which said:
> If that's what you want, declare it as UNIQUE not PRIMARY KEY.
As shown by Patrick TJ McPhee, it does not work:
tests=> create table x (
tests(>name TEXT NOT NULL,
tests(>address INET,
tests(>CONSTRAINT na UNIQUE (name, address)
tests(> );
NOTICE: CREATE TABLE / UNIQUE will create implicit index "na" for table "x"
CREATE TABLE
tests=> INSERT INTO x (name) values ('foobar');
INSERT 45380 1
tests=> INSERT INTO x (name) values ('foobar');
INSERT 45381 1
tests=> INSERT INTO x (name) values ('foobar');
INSERT 45382 1
tests=> INSERT INTO x (name) values ('foobar');
INSERT 45383 1
tests=> select * from x;
name | address
+-
foobar |
foobar |
foobar |
foobar |
(4 rows)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT
On Wed, Apr 27, 2005 at 05:19:32AM +, Patrick TJ McPhee <[EMAIL PROTECTED]> wrote a message of 37 lines which said: > but you should know that in SQL, unique constraints don't apply to > rows containing null values May be I should but I didn't. > your table definition will be as you want it, but the constraint you > want won't be there. OK, I will try to write a custom trigger, then. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT
In article <[EMAIL PROTECTED]>,
Stephane Bortzmeyer <[EMAIL PROTECTED]> wrote:
% But it does not apply to primary keys containing a group of
% columns. In that case (my case), columns do not have to be UNIQUE. But
% they have to be NOT NULL, which puzzles me.
It does apply to primary keys containing groups of columns.
You can get the table definition you want by using a unique constraint,
but you should know that in SQL, unique constraints don't apply to
rows containing null values in the constrained columns. If you
do this:
create table x (
name TEXT NOT NULL,
address INET,
CONSTRAINT na UNIQUE (name, address)
);
your table definition will be as you want it, but the constraint you
want won't be there.
$ INSERT INTO x VALUES ('alpha');
INSERT 194224 1
$ INSERT INTO x VALUES ('alpha');
INSERT 194225 1
$ INSERT INTO x VALUES ('alpha');
INSERT 194226 1
$ INSERT INTO x VALUES ('alpha');
INSERT 194227 1
--
Patrick TJ McPhee
North York Canada
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT
On Tue, Apr 26, 2005 at 03:48:44PM -0500, Scott Marlowe <[EMAIL PROTECTED]> wrote a message of 26 lines which said: > Here's a quote from the SQL1992 spec that's VERY clear: Yes, PostgreSQL is right and implement the standard. Now, what's the rationale for the standard? I understand it for a single column but, for several columns, it should be still possible to have different tuples, such as (3, NULL) and (5, NULL) for instance. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT
On Tue, Apr 26, 2005 at 03:22:40PM -0500, Guy Rouillier <[EMAIL PROTECTED]> wrote a message of 37 lines which said: > "The primary key constraint specifies that a column or columns of a > table may contain only unique (non-duplicate), nonnull values. > Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT > NULL" > > Primary key columns cannot contain null values. I read the above also. It is perfectly clear for primary key on one column. But it does not apply to primary keys containing a group of columns. In that case (my case), columns do not have to be UNIQUE. But they have to be NOT NULL, which puzzles me. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT NULL?
Stephane Bortzmeyer wrote: > If I define a primary key: > >name TEXT NOT NULL, >address INET, >PRIMARY KEY(name, address) > > the definition (seen by \d) becomes: > > name | text| not null > address | inet| not null > > "address" is now not null, which I do not want. It seems unnecessary: > I just want the tuple (name, address) to be unique, which seems > possible even if some 'address' values are NULL. > > It does not appear to be documented in > http://www.postgresql.org/docs/7.4/interactive/ddl-constraints.html#AEN1 975. > Is there a workaround? Per the SQL Commands Reference, under CREATE TABLE: "The primary key constraint specifies that a column or columns of a table may contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL" Primary key columns cannot contain null values. -- Guy Rouillier ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT NULL?
If I define a primary key: name TEXT NOT NULL, address INET, PRIMARY KEY(name, address) the definition (seen by \d) becomes: name | text| not null address | inet| not null "address" is now not null, which I do not want. It seems unnecessary: I just want the tuple (name, address) to be unique, which seems possible even if some 'address' values are NULL. It does not appear to be documented in http://www.postgresql.org/docs/7.4/interactive/ddl-constraints.html#AEN1975. Is there a workaround? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
