Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 786034762A0 for ; Thu, 30 May 2002 14:46:27 -0400 (EDT) Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id ED0C84762A7 for ; Thu, 30 May 2002 14:39:18 -0400 (EDT) Received: from [66.219.92.2] (HELO chocolate-mousse) by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.5.9) with ESMTP id 1494479; Thu, 30 May 2002 11:39:18 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: David Busby , "'pgsql-php@postgresql.org'" Subject: Re: UUID/GUID information Date: Thu, 30 May 2002 11:39:20 -0700 X-Mailer: KMail [version 1.4] References: <35363AB30BF2D311B73C0050DAB989F063C1@SEAPDC01> In-Reply-To: <35363AB30BF2D311B73C0050DAB989F063C1@SEAPDC01> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200205301139.20519.josh@agliodbs.com> X-Archive-Number: 200205/115 X-Sequence-Number: 1247 David, > I'm trying to migrate my MS-SQL(shit) to Postgre. My database > depends on having a uniqueidentifier for all objects stored. (20 or so > tables of these unique objects). In MS-SQL I can use this datatype called > "uniqueidentifier" to accomplish this. What would be a similar solution = in > Postgre? I've looked on through the MAN pages and also scoured the net f= or > this info...I don't necessarly need a UUID like the MS one but some unique > way to identifiy each object.=20=20 The best way to do this in PostgreSQL is to set up an independant sequence: CREATE SEQUENCE universal_sq; Then reference this in each table definition: CREATE TABLE blah ( UUID INT4 NOT NULL DEFAULT NEXTVAL('universal_sq'), etc ... ); CREATE TABLE neh ( UUID INT4 NOT NULL DEFAULT NEXTVAL('universal_sq'), etc ... ); You can even use the UUID as the primary key this way. Postgres Sequence= =20 manager insures that no sequence number is used twice, even in the event of= =20 aborted transactions. See the docs on sequences for more info. Please note that special measures need to be taken if you are likely to exc= eed=20 the limits of INT4 (2.4 billion objects). --=20 -Josh Berkus