pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect based on multi-column primary keys
7+ messages / 5 participants
[nested] [flat]
* select based on multi-column primary keys
@ 2007-01-20 00:44 mawrya <mawrya@furthernorth.net>
0 siblings, 2 replies; 7+ messages in thread
From: mawrya @ 2007-01-20 00:44 UTC (permalink / raw)
To: pgsql-sql
I have set up a table with a multi-column primary key constraint:
CREATE TABLE iopoints
(
enclosureid numeric(3) NOT NULL,
pointid char(4) NOT NULL,
equipmentgroup varchar(64) NOT NULL,
deviceid varchar(8),
devicetype varchar(24),
operationdesc varchar(64) NOT NULL,
entrytime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with
time zone,
systemid numeric(3) NOT NULL,
CONSTRAINT "ID" PRIMARY KEY (systemid, enclosureid, pointid)
)
WITHOUT OIDS;
If I had a row in the table where systemid=123, enclosureid=ab,
pointid=56, I would have a Primary Key ("ID") of 123ab56 for that row.
I now want to run a select based on the Primary Key, something like:
SELECT * FROM iopoints WHERE ID = 123ab56
Is something like this even possible? Or am I forced to do:
SELECT * FROM iopoints WHERE systemid=123 AND enclosureid=ab AND pointid=56
I have searched high and low but can not find a syntax example of how to
select based on a multi-column primary key, any tips?
Thanks,
mawrya
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select based on multi-column primary keys
@ 2007-01-20 01:45 Bruno Wolff III <bruno@wolff.to>
parent: mawrya <mawrya@furthernorth.net>
1 sibling, 1 reply; 7+ messages in thread
From: Bruno Wolff III @ 2007-01-20 01:45 UTC (permalink / raw)
To: mawrya <mawrya@furthernorth.net>; +Cc: pgsql-sql
On Fri, Jan 19, 2007 at 16:44:50 -0800,
mawrya <mawrya@furthernorth.net> wrote:
> I have set up a table with a multi-column primary key constraint:
>
> If I had a row in the table where systemid=123, enclosureid=ab,
> pointid=56, I would have a Primary Key ("ID") of 123ab56 for that row.
>
> I now want to run a select based on the Primary Key, something like:
>
> SELECT * FROM iopoints WHERE ID = 123ab56
>
> Is something like this even possible? Or am I forced to do:
>
> SELECT * FROM iopoints WHERE systemid=123 AND enclosureid=ab AND pointid=56
SELECT * FROM iopoints WHERE systemid=123 AND enclosureid='ab' AND pointid=56
While in theory you could concatenate the columns and test that against a
particular value, you probably don't want to do that. (If you do, use a
functional index.)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select based on multi-column primary keys
@ 2007-01-20 03:45 codeWarrior <gpatnude@hotmail.com>
parent: mawrya <mawrya@furthernorth.net>
1 sibling, 1 reply; 7+ messages in thread
From: codeWarrior @ 2007-01-20 03:45 UTC (permalink / raw)
To: pgsql-sql
--
AFAIK: You cannot have multiple primary keys. How would you know which one
is the actual key ?
FYI: What you are really talking about are table contraints... When you have
multiple unique column constraints -- they are generally referred to as
"table constraints" not multiple primary keys... Unique nmulti-column table
constraints generally use an internal set of rules and triggers and an
index.
My advice would be to alter your table structure so that you have a "real"
PK not table constraints -- that would make it searchable....
There's this nifty little thing called a "sequence"... they make great
PK's.... BTW: If you implement a true PK... you can still retain your
UNIQUE(col1, col2, col3, ...) table constraints.
Regards,
Gregory P. Patnude
Vice President - Applications & Innovations Group
iDynaTECH, Inc
120 North Pine Street
STC - Suite 162
Spokane, WA 99202
(509) 343-3104 [voice]
http://www.idynatech.com
"mawrya" <mawrya@furthernorth.net> wrote in message
news:45B16602.2080508@furthernorth.net...
>I have set up a table with a multi-column primary key constraint:
>
> CREATE TABLE iopoints
> (
> enclosureid numeric(3) NOT NULL,
> pointid char(4) NOT NULL,
> equipmentgroup varchar(64) NOT NULL,
> deviceid varchar(8),
> devicetype varchar(24),
> operationdesc varchar(64) NOT NULL,
> entrytime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with
> time zone,
> systemid numeric(3) NOT NULL,
> CONSTRAINT "ID" PRIMARY KEY (systemid, enclosureid, pointid)
> )
> WITHOUT OIDS;
>
> If I had a row in the table where systemid=123, enclosureid=ab,
> pointid=56, I would have a Primary Key ("ID") of 123ab56 for that row.
>
> I now want to run a select based on the Primary Key, something like:
>
> SELECT * FROM iopoints WHERE ID = 123ab56
>
> Is something like this even possible? Or am I forced to do:
>
> SELECT * FROM iopoints WHERE systemid=123 AND enclosureid=ab AND
> pointid=56
>
> I have searched high and low but can not find a syntax example of how to
> select based on a multi-column primary key, any tips?
>
> Thanks,
>
> mawrya
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select based on multi-column primary keys
@ 2007-01-20 04:57 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 7+ messages in thread
From: Richard Broersma Jr @ 2007-01-20 04:57 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; mawrya <mawrya@furthernorth.net>; +Cc: pgsql-sql
> SELECT * FROM iopoints WHERE systemid=123 AND enclosureid='ab' AND pointid=56
A slight variation of the syntax would be:
select * from iopoints where (systemid, enclosureid, pointid) = (123,'ab',56);
this table and fields sounds alot like a control system be being modeled. :-)
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select based on multi-column primary keys
@ 2007-01-21 15:28 Andrew Sullivan <ajs@crankycanuck.ca>
parent: codeWarrior <gpatnude@hotmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Andrew Sullivan @ 2007-01-21 15:28 UTC (permalink / raw)
To: pgsql-sql
On Fri, Jan 19, 2007 at 07:45:40PM -0800, codeWarrior wrote:
> AFAIK: You cannot have multiple primary keys. How would you know which one
> is the actual key ?
You can have a multi-column primary key, though. That's a perfectly
legitimate approach.
> FYI: What you are really talking about are table contraints... When you have
No, it's a multi-column primary key.
> My advice would be to alter your table structure so that you have a "real"
> PK not table constraints -- that would make it searchable....
This is already searchable. What you are talking about is not a real
primary key, but an artificial one. The OP already has a real
primary key. SQL purists think artificial primary keys mean that you
haven't done enough normalisation. I'm going to remain silent on
that topic, though, so that we don't get a Thread That Does Not End
:)
A
--
Andrew Sullivan | ajs@crankycanuck.ca
If they don't do anything, we don't need their acronym.
--Josh Hamilton, on the US FEMA
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select based on multi-column primary keys
@ 2007-01-23 21:07 codeWarrior <gpatnude@hotmail.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 7+ messages in thread
From: codeWarrior @ 2007-01-23 21:07 UTC (permalink / raw)
To: pgsql-sql
No... its a table constraint using a unique, implicit index on the listed
columns. .
PRIMARY KEY ( column [, ...] )
The PRIMARY KEY table constraint is similar to the PRIMARY KEY column
constraint. As a table constraint, PRIMARY KEY allows multiple columns to be
defined in a parenthetical expression, separated by commas. An implicit
index will be created across columns. The combination of values for each
column specified must therefore amount to only unique and non-NULL values,
as with the PRIMARY KEY column constraint.
hehehe....
"Andrew Sullivan" <ajs@crankycanuck.ca> wrote in message
news:20070121152838.GB3533@phlogiston.dyndns.org...
> On Fri, Jan 19, 2007 at 07:45:40PM -0800, codeWarrior wrote:
>> AFAIK: You cannot have multiple primary keys. How would you know which
>> one
>> is the actual key ?
>
> You can have a multi-column primary key, though. That's a perfectly
> legitimate approach.
>
>> FYI: What you are really talking about are table contraints... When you
>> have
>
> No, it's a multi-column primary key.
>
>> My advice would be to alter your table structure so that you have a
>> "real"
>> PK not table constraints -- that would make it searchable....
>
> This is already searchable. What you are talking about is not a real
> primary key, but an artificial one. The OP already has a real
> primary key. SQL purists think artificial primary keys mean that you
> haven't done enough normalisation. I'm going to remain silent on
> that topic, though, so that we don't get a Thread That Does Not End
> :)
>
> A
>
>
> --
> Andrew Sullivan | ajs@crankycanuck.ca
> If they don't do anything, we don't need their acronym.
> --Josh Hamilton, on the US FEMA
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select based on multi-column primary keys
@ 2007-01-23 21:26 Andrew Sullivan <ajs@crankycanuck.ca>
parent: codeWarrior <gpatnude@hotmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Andrew Sullivan @ 2007-01-23 21:26 UTC (permalink / raw)
To: pgsql-sql
On Tue, Jan 23, 2007 at 01:07:19PM -0800, codeWarrior wrote:
> No... its a table constraint using a unique, implicit index on the listed
> columns. .
Sure. But since these get added as table constraints after dump
anyway, I'm not sure I see why it makes a difference.
In any case, the point is that what you call a "real" primary key is
an artificial one, and what SQL weenies would think is a "real"
primary key is in fact, a natural primary key based on the data in
the table. That may well be multiple columns of data. (I know of
one really nasty case where it was five. Yes, the application needed
rewriting. But it was normalised :)
A
--
Andrew Sullivan | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-01-23 21:26 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-20 00:44 select based on multi-column primary keys mawrya <mawrya@furthernorth.net>
2007-01-20 01:45 ` Bruno Wolff III <bruno@wolff.to>
2007-01-20 04:57 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-01-20 03:45 ` codeWarrior <gpatnude@hotmail.com>
2007-01-21 15:28 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-01-23 21:07 ` codeWarrior <gpatnude@hotmail.com>
2007-01-23 21:26 ` Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox