Postgres Pro
Downloads
Home   >   mailing lists

Thread: Boolean without default declared

Boolean without default declared

From
Jon Collette
Date:
If a column with a boolean datatype doesn't have a default value.  What 
type of value is set if nothing is inserted into that column?  Here is 
my test table and the queries I have tried.  I can't seem to be able to 
select the rows where happy has no value.
           Table "public.users"Column |         Type          | Modifiers
--------+-----------------------+-----------id     | character varying(32) |email  | text                  |happy  |
boolean              |money  | numeric               |
 


*select * from users;                *id | email  | happy |  money 
----+--------+-------+---------4  | me     |       | 1324.234  | me     |       | 1324.233  | as     |       |   123.21
| afjssd | t     |       
 

*select * from users where happy;*id | email  | happy | money
----+--------+-------+-------1  | afjssd | t     |     

*select * from users where not happy;*id | email | happy | money
----+-------+-------+-------
(0 rows)

*select * from users where happy = NULL;*id | email | happy | money
----+-------+-------+-------
(0 rows)

*select * from users where happy = '';*
ERROR:  invalid input syntax for type boolean: ""

Re: Boolean without default declared

From
Richard Broersma Jr
Date:
--- Jon Collette <jon@etelos.com> wrote:

> If a column with a boolean datatype doesn't have a default value.  What 
> type of value is set if nothing is inserted into that column?  Here is 
> my test table and the queries I have tried.  I can't seem to be able to 
> select the rows where happy has no value.
> 
> *select * from users where happy = '';*
> ERROR:  invalid input syntax for type boolean: ""

Databases have three value logic in it expressions.

is the expression true,
is the expression false,
is the expreassion unknown i.e. null.

SELECT * FROM users WHERE happy IS NULL;

UPDATE users SET happy = false WHERE happy IS NULL;

ALTER TABLE USER ALTER COLUMN happy SET NOT NULL;

Regards,
Richard Broersma Jr.

Re: Boolean without default declared

From
Richard Broersma Jr
Date:
--- Jon Collette <jon@etelos.com> wrote:

> Thanks that was it exactly.  I did notice another strange thing.  When 
> using IS NULL in an plpgsql IF statement you have to use ISNULL.  Same 
> with NOT NULL.  Does this mean that ISNULL is more proper than IS NULL?

Good question,  I am not sure about the proper syntax for plpgsql.  However, IS NULL is correct
for sql statements.

Also, don't forget to cc. the list so that other can chime in as well.

Regards,
Richard Broersma Jr.

Re: Boolean without default declared

From
Tom Lane
Date:
Richard Broersma Jr <rabroersma@yahoo.com> writes:
> --- Jon Collette <jon@etelos.com> wrote:
>> Thanks that was it exactly.  I did notice another strange thing.  When 
>> using IS NULL in an plpgsql IF statement you have to use ISNULL.  Same 
>> with NOT NULL.  Does this mean that ISNULL is more proper than IS NULL?

IS NULL is in the SQL standard, the other is not; and I dunno what you did
wrong but you certainly don't "have to" use ISNULL in plpgsql.
        regards, tom lane