Thread: problem selecting rows with null value
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
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. On March 12, 2003 07:47 pm, 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
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: > 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 >
Oops! I misread the original problem sorry... Try this for selecting afterwards. SELECT * FROM pleah WHERE bar IS NULL; Josh :) On March 12, 2003 07:47 pm, 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
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
Attachment
--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