Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id D82D79FB4CC for ; Fri, 19 Jan 2007 23:42:03 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 32493-10 for ; Fri, 19 Jan 2007 23:41:54 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 7ED9F9FB477 for ; Fri, 19 Jan 2007 23:42:00 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id CFDD630E1A; Sat, 20 Jan 2007 04:41:58 +0100 (MET) From: "codeWarrior" X-Newsgroups: pgsql.sql Subject: Re: select based on multi-column primary keys Date: Fri, 19 Jan 2007 19:45:40 -0800 Organization: iDynaTECH, Inc. Lines: 78 Message-ID: References: <45B16602.2080508@furthernorth.net> Reply-To: "codeWarrior" X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.3028 X-RFC2646: Format=Flowed; Response X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.196 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_HOTMAIL_RCVD2 X-Spam-Level: * X-Archive-Number: 200701/88 X-Sequence-Number: 27240 -- 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" 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 >