Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0H611068052 for ; Wed, 17 Jan 2001 01:01:01 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.3) with HTTP id 1178138 for ; Tue, 16 Jan 2001 22:01:00 -0800 From: "Josh Berkus" Subject: Re: Boolean and Bit To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.3 Date: Tue, 16 Jan 2001 22:01:00 -0800 Message-ID: In-Reply-To: <3A652EF0.81863C62@heart.com.au> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200101/184 X-Sequence-Number: 848 Keith, > This is a compatibility issue. While I prefer to use > BOOLEAN, this is SQL3 > and not available on the (unfortunately must use) > MS-SQL/MSDE platform. > > My options are to use a CHAR field and re-write my code > for "T" and "F" or > an int field and re-write my code to use "field=0" and > "field<>0" Given that all the MS-SQL BIT field is, is INT1, using INT2 should not be much of a problem. Go ahead an create a custom type based on INT2 and add constraints to prevent any values outside of the range of 0 and 1. This is where the SQL92 DOMAIN (not, as far as I know, available in PGSQL) construction would be useful in PostgreSQL instead of TYPE (Tom?). The problem with TYPE is that you theoretically need to define a whole set of operators for your TYPE, while DOMAIN is a bit simpler. > I would like to distribute a script (SQL) file to our > users to update > databases to new versions... obstacles include > BOOLEAN/BIT and the > inconsistent use of BLOB/MEMO/[long]varchar(4096). Well, yes. This is beacause BLOBs are NOT part of the SQL standard and IMHO a bad idea relationally; thus their implementation is entirely proprietary to the RDBMS. The solution is not to use BLOBs. > Distributing schema patches is proving troublesome across > multiple > platforms. Yup. Yer in for a world of pain, sonny. Hope you get paid hourly. -Josh