problem selecting rows with null value

try this

CREATE TABLE pleah (
foo int NOT NULL,
bar timestamp );

INSERT INTO pleah ( foo ) VALUES ( 1 );

If the NOT NULL statement is missing, a NULL will automatically be inserted.
no need to specify a DEFAULT NULL.

Josh.

Oops! I misread the original problem sorry...

Try this for selecting afterwards.

SELECT * FROM pleah
WHERE bar IS NULL;

Josh :)

Ok I already figured it out:
select * from pleah where bar IS NULL

** IS instead of = **

no need to point out what a bonehead I am.
thanks for your patience.

matthew

Matthew Phillips wrote:

Use 'IS NULL' or 'IS NOT NULL' like this:

select * from pleah where bar IS NULL;

'=' does not work for null values.

On Wed, 2003-03-12 at 16:47, Matthew Phillips wrote:

I am sure that I am doing something subtly wrong with my sql syntax
here. I thought that it was a timestamp null column issue, but it
doesn't work with int either. Is there a special way to denote the null
value in a situation like this that I don't know about?

tsc=# create table pleah(
tsc(# foo INT NOT NULL,
tsc(# bar timestamp DEFAULT NULL );
CREATE TABLE
tsc=# insert into pleah (foo) values (1);
INSERT 206475246 1

###ok here I would just like to select that row that I just inserted...
but no!

tsc=# select * from pleah where bar = NULL;
foo | bar
-----+-----
(0 rows)

tsc=# select * from pleah;
foo | bar
-----+-----
1 |
(1 row)

thanks muchly
matthew

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Jord Tanner <jord@indygecko.com>

On Wed, Mar 12, 2003 at 04:55:31PM -0800, Jord Tanner wrote:

Use 'IS NULL' or 'IS NOT NULL' like this:

select * from pleah where bar IS NULL;

'=' does not work for null values.

I was pretty sure there was supposed to be an automatic rewrite for that
since "= NULL" is so common...

--
Taral <taral@taral.net>
This message is digitally signed. Please PGP encrypt mail to me.
"Most parents have better things to do with their time than take care of
their children." -- Me

--On Wednesday, March 12, 2003 19:55:00 -0600 Taral <taral@taral.net> wrote:

On Wed, Mar 12, 2003 at 04:55:31PM -0800, Jord Tanner wrote:

Use 'IS NULL' or 'IS NOT NULL' like this:

select * from pleah where bar IS NULL;

'=' does not work for null values.

I was pretty sure there was supposed to be an automatic rewrite for that
since "= NULL" is so common...

There is a GUC (read postgresql.conf) parameter for that. I don't know
what it is
off hand, however.

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749