X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 1A10C9DC878 for ; Wed, 8 Feb 2006 15:59:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 29970-09 for ; Wed, 8 Feb 2006 15:59:34 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web32515.mail.mud.yahoo.com (web32515.mail.mud.yahoo.com [68.142.207.225]) by postgresql.org (Postfix) with SMTP id 087549DC815 for ; Wed, 8 Feb 2006 15:59:31 -0400 (AST) Received: (qmail 6995 invoked by uid 60001); 8 Feb 2006 19:59:33 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=VesmCyAiEHMIB5385/H7qCbgOMcOfTvqGMmDjP19XPQ6PJrnGrlu2dy+RkKVd76/JUki/1QTtBxP0cHiN4E5e7Bpy7acPCvsgo7OjAPazPcFWE2atnw3xka4GppBe1p4SG3SCP5XgLpQ5nBJN4dRcpDb0e+gbLpt5loTxrKoYs4= ; Message-ID: <20060208195933.6993.qmail@web32515.mail.mud.yahoo.com> Received: from [216.173.173.66] by web32515.mail.mud.yahoo.com via HTTP; Wed, 08 Feb 2006 11:59:33 PST Date: Wed, 8 Feb 2006 11:59:33 -0800 (PST) From: CG Reply-To: cgg007@yahoo.com Subject: Cross schema Primary Key Indexes problem with datatype in the public schema To: pgsql-admin@postgresql.org In-Reply-To: <43EA13B6.6050403@vianet.ca> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.548 required=5 tests=[AWL=0.069, DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.548 X-Spam-Level: X-Archive-Number: 200602/84 X-Sequence-Number: 20505 PostgreSQL 8.1 I'm using the uniqueidentifier datatype for a primary key in these tables. The uniqueidentifier datatype is specified in the public schema. There is a default operator class defined for "btree" in the public schema. These table_# tables are in a different schema, second on the list in the search path. I can issue the command from psql "ALTER TABLE ONLY table_1 ADD CONSTRAINT table_1_luuid_pkey PRIMARY KEY (luuid);" and the SQL statement runs fine. Here's the output from pg_restore... pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 2982; 2606 44309156 CONSTRAINT table_1_luuid_pkey postgres pg_restore: [archiver (db)] could not execute query: ERROR: data type public.uniqueidentifier has no default operator class for access method "btree" HINT: You must specify an operator class for the index or define a default operator class for the data type. Command was: ALTER TABLE ONLY table_1 ADD CONSTRAINT table_1_luuid_pkey PRIMARY KEY (luuid); pg_restore: creating CONSTRAINT table_2_luuid_pkey pg_restore: [archiver (db)] Error from TOC entry 2988; 2606 44309158 CONSTRAINT table_2_luuid_pkey postgres pg_restore: [archiver (db)] could not execute query: ERROR: data type public.uniqueidentifier has no default operator class for access method "btree" HINT: You must specify an operator class for the index or define a default operator class for the data type. Command was: ALTER TABLE ONLY table_2 ADD CONSTRAINT table_2_luuid_pkey PRIMARY KEY (luuid); pg_restore: creating CONSTRAINT table_3_luuid_pkey pg_restore: [archiver (db)] Error from TOC entry 2990; 2606 44309160 CONSTRAINT table_3_luuid_pkey postgres pg_restore: [archiver (db)] could not execute query: ERROR: data type public.uniqueidentifier has no default operator class for access method "btree" HINT: You must specify an operator class for the index or define a default operator class for the data type. Command was: ALTER TABLE ONLY table_3 ADD CONSTRAINT table_3_luuid_pkey PRIMARY KEY (luuid); pg_restore: creating CONSTRAINT table_3_puuid_pkey pg_restore: [archiver (db)] Error from TOC entry 2960; 2606 44309162 CONSTRAINT table_4_puuid_pkey postgres pg_restore: [archiver (db)] could not execute query: ERROR: data type public.uniqueidentifier has no default operator class for access method "btree" HINT: You must specify an operator class for the index or define a default operator class for the data type. Command was: ALTER TABLE ONLY table_4 ADD CONSTRAINT table_4_puuid_pkey PRIMARY KEY (puuid); pg_restore: creating CONSTRAINT table_5_puuid_pkey pg_restore: [archiver (db)] Error from TOC entry 2998; 2606 44309164 CONSTRAINT table_5_puuid_pkey postgres pg_restore: [archiver (db)] could not execute query: ERROR: data type public.uniqueidentifier has no default operator class for access method "btree" HINT: You must specify an operator class for the index or define a default operator class for the data type. Command was: ALTER TABLE ONLY table_5 ADD CONSTRAINT table_5_puuid_pkey PRIMARY KEY (puuid); pg_restore: creating CONSTRAINT table_6_puuid_pkey pg_restore: [archiver (db)] Error from TOC entry 2962; 2606 44309166 CONSTRAINT table_6_puuid_pkey postgres pg_restore: [archiver (db)] could not execute query: ERROR: data type public.uniqueidentifier has no default operator class for access method "btree" HINT: You must specify an operator class for the index or define a default operator class for the data type. Command was: ALTER TABLE ONLY table_6 ADD CONSTRAINT table_6_puuid_pkey PRIMARY KEY (puuid); I'm not sure what to make of this. I'm puzzled and need some help figuring this out! CG __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com