pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBoolean without default declared
4+ messages / 3 participants
[nested] [flat]
* Boolean without default declared
@ 2007-08-15 18:46 Jon Collette <jon@etelos.com>
2007-08-15 19:23 ` Re: Boolean without default declared Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jon Collette @ 2007-08-15 18:46 UTC (permalink / raw)
To: pgsql-sql
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.23
4 | me | | 1324.23
3 | as | | 123.2
1 | 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: ""
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Boolean without default declared
2007-08-15 18:46 Boolean without default declared Jon Collette <jon@etelos.com>
@ 2007-08-15 19:23 ` Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Broersma Jr @ 2007-08-15 19:23 UTC (permalink / raw)
To: Jon Collette <jon@etelos.com>; pgsql-sql
--- 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Boolean without default declared
@ 2007-08-15 19:36 Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-15 21:48 ` Re: Boolean without default declared Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Richard Broersma Jr @ 2007-08-15 19:36 UTC (permalink / raw)
To: Jon Collette <jon@etelos.com>; +Cc: pgsql-sql
--- 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Boolean without default declared
2007-08-15 19:36 Re: Boolean without default declared Richard Broersma Jr <rabroersma@yahoo.com>
@ 2007-08-15 21:48 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2007-08-15 21:48 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: Jon Collette <jon@etelos.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-08-15 21:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-15 18:46 Boolean without default declared Jon Collette <jon@etelos.com>
2007-08-15 19:23 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-15 19:36 Re: Boolean without default declared Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-15 21:48 ` Tom Lane <tgl@sss.pgh.pa.us>
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