Re: The BOOLEAN data type

On Mon, 31 Mar 2003 10:11:15 -0800, --CELKO-- wrote:

> It gets to be a mess really fast.

As I understand you, the primary reason for it being messy is the eternal NULL-problem.

But then, making it optional in SQL:1999, was that perhaps a compromise?

> Aside from the destruction of the foundations of SQL, Booleans are
> usually, but not always, a sign of bad programming.

OK; good to think about.

Still, I think that there are many conceivable relations were true/false/unknown makes good sense. So my question then becomes "what's a good, cross-DBMS-product way of doing it; it there perhaps a de-facto standard for doing it?". The ways I see:

1. As a boolean field

   ---

   Fails in important SQL implemetations like Oracle and MSSQL. Works in PostgreSQL. Fails in Mimer SQL.
2. As a one-char bit string

   ---

   Fails in Oracle and Mimer SQL.

Works differently between PostgreSQL and MSSQL:

MSSQL: troels_at_jtds:windows> create table test(id int not null primary key, truth bit);
  affected 0 rows (6 msec)
troels_at_jtds:windows> insert into test values(42,0);   affected 1 rows (7 msec)
troels_at_jtds:windows> insert into test values(43,1);   affected 1 rows (2 msec)
troels_at_jtds:windows> insert into test values(44,B'1');   FAILURE: The name 'B' is not permitted in this context. Only   constants, expressions, or variables allowed here. Column   names are not permitted.
troels_at_jtds:windows> select * from test; ----+-------+
 id | truth |
----+-------+
 42 | false |
 43 | true |
----+-------+
2 rows in result (first row: 7 msec; total: 8 msec)

PostgreSQL:

troels=> create table test(id int not null primary key,truth bit);  NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index  'test_pkey' for table 'test'
CREATE TABLE
 troels=> insert into test values(42,0);  ERROR: column "truth" is of type bit but expression is of type  integer
 You will need to rewrite or cast the expression troels=> insert into test values(43,1);
 ERROR: column "truth" is of type bit but expression is of type  integer
 You will need to rewrite or cast the expression troels=> insert into test values(44,B'1'); INSERT 19180 1 troels=> select * from test;
 id | truth
----+-------
 44 | 1
(1 row)

c) As a magic char/value
In any database that I know of (except for MySQL[1]), one may use an ordinary type like char(1) or int combined with a CHECK in order to restrict the domain of values.

The portability of this solution makes it nice, but magic values are a bad habit in my opinion, primarily because of the lack consensus this results in (is it 0 or 'F' or 'f' or 'FALSE' or 'false' or 'n' or 'no' or 'N' or 'NO', ...).

I wonder if DB2 supports the boolean or bit types.

/Troels

Note 1: In MySQL, one may use ENUMs to achieve the same situation, but it ties you to MySQL which makes it a bad choise. Received on Tue Apr 01 2003 - 16:00:43 CEST