pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Keary Suska <hierophant@pcisys.net>
To: Postgres-PHP <pgsql-php@postgresql.org>
Subject: Re: Booleans
Date: Mon, 04 Nov 2002 12:40:37 -0700
Message-ID: <B9EC1B45.14F90%hierophant@pcisys.net> (raw)
In-Reply-To: <200211041838.56754.JTSMailing@gmx.net>

on 11/4/02 10:38 AM, JTSMailing@gmx.net purportedly said:

>> This would work only if the fields are boolean. Unfortunately, bit fields
>> are a poor choice to store boolean values, and you don't get any real
>> benefit from them.
>> 
> 
> I havent found any better yet... (i will try shortint next)
> 
>> You should also use proper bit string constant syntax, prefacing each value
>> with "B":
>> insert into table (a,b,c) values (B'$a',B'$b',B'$c')
> 
> 
> I would like to use the same queries for mysql and postgres. I don't think
> mysql will handle with that.

Which is why bit fields are a bad idea, because they aren't very portable.
Both Postgres and MySQL understand the BOOL type, although Postgres is more
flexible at what kinds of constants are allowed. In MySQL, BOOL (as well as
BIT) is just a synonym for TINYINT, but the net effect is that both DB's use
the same storage space (1 byte).

The problem is that your code relies on the failure of MySQL to understand
column constraints. '' is not a valid constant for numeric types in either
PG or MySQL, however MySQL will not reject the malformed SQL and instead use
the closest numeric equivalent which is 0. If MySQL ever becomes truly SQL
compliant your code will break, so you will get better mileage out of
correcting this issue now, and making sure that your boolean values are '1'
or '0' only, which is portable across PG and MySQL without issue. This would
also allow your code to e more portable to other RDBMSes.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"




view thread (6+ messages)

Message-ID: <B9EC1B45.14F90%hierophant@pcisys.net>
Permalink:  ../B9EC1B45.14F90%25hierophant@pcisys.net/
Also on:    postgresql.org/message-id/B9EC1B45.14F90%hierophant@pcisys.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: hierophant@pcisys.net
  Subject: Re: Booleans
  In-Reply-To: <B9EC1B45.14F90%hierophant@pcisys.net>
  References: <200211041838.56754.JTSMailing@gmx.net> <B9EC1B45.14F90%hierophant@pcisys.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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