Thread: select * from mytable where myfield=null;
Hi, I'm using Postgresql 7.2.1, with JBoss2.4.4. and jdk1.4 Here is my table : id | date | value -----+-------------------------+--------- 1 | 2002-05-10 10:00:00:+02 | 5 2 | (null) | 10 ... With the jdbc driver, this query doesn't any result : Stmt = con.prepareStatement(select * from mytable where date=?) Stmt.setNull(1,java.sql.Types.TIMESTAMP); Stmt.executeQuery(); I think the jdbc driver transform the query on "select * from mytable where date=null" and not "select * from mytable where date is null". Il seems that select * from mytable where date=null was supported by postgres before 7.0 version, but not with the 7.2.1 How can i do ? thanks Bruno.
This isn't really a jdbc question and would probably better be addressed to pgsql-general. The behavior you are now seeing is ANSI Standard behavior. '= null' should always return false according to the standard. In 7.2 this non-standard behavior was fixed. You will see this behavior in all of the interfaces to postgres, not just jdbc. There is a parameter in the postgresql.conf file that will revert back to the old buggy behavior (transform_null_equals = true). thanks, --Barry blamouret wrote: > Hi, > I'm using Postgresql 7.2.1, with JBoss2.4.4. and jdk1.4 > > Here is my table : > id | date | value > -----+-------------------------+--------- > 1 | 2002-05-10 10:00:00:+02 | 5 > 2 | (null) | 10 > ... > > With the jdbc driver, this query doesn't any result : > Stmt = con.prepareStatement(select * from mytable where date=?) > Stmt.setNull(1,java.sql.Types.TIMESTAMP); > Stmt.executeQuery(); > > I think the jdbc driver transform the query on "select * from mytable > where date=null" and not "select * from mytable where date is null". > > Il seems that select * from mytable where date=null was supported by > postgres before 7.0 version, but not with the 7.2.1 > > How can i do ? > thanks > Bruno. > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Bruno, No, the driver should never alter your SQL statement. If you said '... = ? ' it would be wrong to change this to ' ... is ? '. How does the driver know that you wanted this behavior. (I know for example that code I have written would break if the driver suddenly decided to try to out guess what I intended). This is the responsibility of the client code to handle nulls appropriately for their application. You will not find the jdbc drivers for Oracle or MSSQL doing anything like this either. --Barry Bruno Lamouret wrote: > Hi, I agree with you when you say that '= null' isn't ANSI Standard. > But the trouble is that the jdbc driver does this error. > A query such as "select * from mytable where date=?" becomes "select * > from mytable where date=null" > with the jdbc driver when we put a null value in the preparedStatement > while it should become > "select * from mytable where date is null". > > am I right ? > > Thanks Bruno > > > Barry Lind wrote: > >> This isn't really a jdbc question and would probably better be >> addressed to pgsql-general. The behavior you are now seeing is ANSI >> Standard behavior. '= null' should always return false according to >> the standard. In 7.2 this non-standard behavior was fixed. You will >> see this behavior in all of the interfaces to postgres, not just jdbc. >> >> There is a parameter in the postgresql.conf file that will revert back >> to the old buggy behavior (transform_null_equals = true). >> >> thanks, >> --Barry >> >> >> blamouret wrote: >> >>> Hi, >>> I'm using Postgresql 7.2.1, with JBoss2.4.4. and jdk1.4 >>> >>> Here is my table : >>> id | date | value >>> -----+-------------------------+--------- >>> 1 | 2002-05-10 10:00:00:+02 | 5 >>> 2 | (null) | 10 >>> ... >>> >>> With the jdbc driver, this query doesn't any result : >>> Stmt = con.prepareStatement(select * from mytable where date=?) >>> Stmt.setNull(1,java.sql.Types.TIMESTAMP); >>> Stmt.executeQuery(); >>> >>> I think the jdbc driver transform the query on "select * from mytable >>> where date=null" and not "select * from mytable where date is null". >>> >>> Il seems that select * from mytable where date=null was supported by >>> postgres before 7.0 version, but not with the 7.2.1 >>> >>> How can i do ? >>> thanks >>> Bruno. >>> >>> >>> >>> >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 4: Don't 'kill -9' the postmaster >>> >> >> > > > >
Hi, I agree with you when you say that '= null' isn't ANSI Standard. But the trouble is that the jdbc driver does this error. A query such as "select * from mytable where date=?" becomes "select * from mytable where date=null" with the jdbc driver when we put a null value in the preparedStatement while it should become "select * from mytable where date is null". am I right ? Thanks Bruno Barry Lind wrote: > This isn't really a jdbc question and would probably better be > addressed to pgsql-general. The behavior you are now seeing is ANSI > Standard behavior. '= null' should always return false according to > the standard. In 7.2 this non-standard behavior was fixed. You will > see this behavior in all of the interfaces to postgres, not just jdbc. > > There is a parameter in the postgresql.conf file that will revert back > to the old buggy behavior (transform_null_equals = true). > > thanks, > --Barry > > > blamouret wrote: > >> Hi, >> I'm using Postgresql 7.2.1, with JBoss2.4.4. and jdk1.4 >> >> Here is my table : >> id | date | value >> -----+-------------------------+--------- >> 1 | 2002-05-10 10:00:00:+02 | 5 >> 2 | (null) | 10 >> ... >> >> With the jdbc driver, this query doesn't any result : >> Stmt = con.prepareStatement(select * from mytable where date=?) >> Stmt.setNull(1,java.sql.Types.TIMESTAMP); >> Stmt.executeQuery(); >> >> I think the jdbc driver transform the query on "select * from mytable >> where date=null" and not "select * from mytable where date is null". >> >> Il seems that select * from mytable where date=null was supported by >> postgres before 7.0 version, but not with the 7.2.1 >> >> How can i do ? >> thanks >> Bruno. >> >> >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Don't 'kill -9' the postmaster >> > >
Hi,
I understand that this behavior is a standard but agree with Bruno that it's nonsense. I have trouble with it in my own application. I use sql command like "select * from A where A.a=? and A.b=? and A.c=? and A.d=? and A.e=?". There are five parameters in my query and each of them can be null! I can not create SQL command string for all possible combinations of null values so I have to build the query dynamically. Why then use prepared statement?
I doubt that anybody ever needed x=null in SQL command but it seems that my example is very common. There SHOULD be some workaround for that. But I agree that this is not topic for this list.
Hoanui
--- On Mon 05/13, Barry Lind wrote:
> Bruno,
>
> No, the driver should never alter your SQL statement. If you said '...
> = ? ' it would be wrong to change this to ' ... is ? '. How does the
> driver know that you wanted this behavior. (I know for example that
> code I have written would break if the driver suddenly decided to try to
> out guess what I intended). This is the responsibility of the client
> code to handle nulls appropriately for their application. You will not
> find the jdbc drivers for Oracle or MSSQL doing anything like this
> either.
>
> --Barry
>
> Bruno Lamouret wrote:
> > Hi, I agree with you when you say that '= null' isn't ANSI Standard.
> > But the trouble is that the jdbc driver does this error.
> > A query such as "select * from mytable where date=?"
> becomes "select *
> > from mytable where date=null"
> > with the jdbc driver when we put a null value in the
> preparedStatement
> > while it should become
> > "select * from mytable where date is null".
> >
> > am I right ?
> >
> > Thanks Bruno
> >
> >
> > Barry Lind wrote:
> >
> >> This isn't really a jdbc question and would probably better be
> >> addressed to pgsql-general. The behavior you are now seeing is
> ANSI
> >> Standard behavior. '= null' should always return false according
> to
> >> the standard. In 7.2 this non-standard behavior was fixed. You
> will
> >> see this behavior in all of the interfaces to postgres, not just
> jdbc.
> >>
> >> There is a parameter in the postgresql.conf file that will revert
> back
> >> to the old buggy behavior (transform_null_equals = true).
> >>
> >> thanks,
> >> --Barry
> >>
> >>
> >> blamouret wrote:
> >>
> >>> Hi,
> >>> I'm using Postgresql 7.2.1, with JBoss2.4.4. and jdk1.4
> >>>
> >>> Here is my table :
> >>> id | date | value
> >>> -----+-------------------------+---------
> >>> 1 | 2002-05-10 10:00:00:+02 | 5
> >>> 2 | (null) | 10
> >>> ...
> >>>
> >>> With the jdbc driver, this query doesn't any result :
> >>> Stmt = con.prepareStatement(select * from mytable where
> date=?)
> >>> Stmt.setNull(1,java.sql.Types.TIMESTAMP);
> >>> Stmt.executeQuery();
> >>>
> >>> I think the jdbc driver transform the query on "select *
> from mytable
> >>> where date=null" and not "select * from mytable
> where date is null".
> >>>
> >>> Il seems that select * from mytable where date=null was
> supported by
> >>> postgres before 7.0 version, but not with the 7.2.1
> >>>
> >>> How can i do ?
> >>> thanks
> >>> Bruno.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> ---------------------------(end of
> broadcast)---------------------------
> >>> TIP 4: Don't 'kill -9' the postmaster
> >>>
> >>
> >>
> >
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
I understand that this behavior is a standard but agree with Bruno that it's nonsense. I have trouble with it in my own application. I use sql command like "select * from A where A.a=? and A.b=? and A.c=? and A.d=? and A.e=?". There are five parameters in my query and each of them can be null! I can not create SQL command string for all possible combinations of null values so I have to build the query dynamically. Why then use prepared statement?
I doubt that anybody ever needed x=null in SQL command but it seems that my example is very common. There SHOULD be some workaround for that. But I agree that this is not topic for this list.
Hoanui
--- On Mon 05/13, Barry Lind wrote:
> Bruno,
>
> No, the driver should never alter your SQL statement. If you said '...
> = ? ' it would be wrong to change this to ' ... is ? '. How does the
> driver know that you wanted this behavior. (I know for example that
> code I have written would break if the driver suddenly decided to try to
> out guess what I intended). This is the responsibility of the client
> code to handle nulls appropriately for their application. You will not
> find the jdbc drivers for Oracle or MSSQL doing anything like this
> either.
>
> --Barry
>
> Bruno Lamouret wrote:
> > Hi, I agree with you when you say that '= null' isn't ANSI Standard.
> > But the trouble is that the jdbc driver does this error.
> > A query such as "select * from mytable where date=?"
> becomes "select *
> > from mytable where date=null"
> > with the jdbc driver when we put a null value in the
> preparedStatement
> > while it should become
> > "select * from mytable where date is null".
> >
> > am I right ?
> >
> > Thanks Bruno
> >
> >
> > Barry Lind wrote:
> >
> >> This isn't really a jdbc question and would probably better be
> >> addressed to pgsql-general. The behavior you are now seeing is
> ANSI
> >> Standard behavior. '= null' should always return false according
> to
> >> the standard. In 7.2 this non-standard behavior was fixed. You
> will
> >> see this behavior in all of the interfaces to postgres, not just
> jdbc.
> >>
> >> There is a parameter in the postgresql.conf file that will revert
> back
> >> to the old buggy behavior (transform_null_equals = true).
> >>
> >> thanks,
> >> --Barry
> >>
> >>
> >> blamouret wrote:
> >>
> >>> Hi,
> >>> I'm using Postgresql 7.2.1, with JBoss2.4.4. and jdk1.4
> >>>
> >>> Here is my table :
> >>> id | date | value
> >>> -----+-------------------------+---------
> >>> 1 | 2002-05-10 10:00:00:+02 | 5
> >>> 2 | (null) | 10
> >>> ...
> >>>
> >>> With the jdbc driver, this query doesn't any result :
> >>> Stmt = con.prepareStatement(select * from mytable where
> date=?)
> >>> Stmt.setNull(1,java.sql.Types.TIMESTAMP);
> >>> Stmt.executeQuery();
> >>>
> >>> I think the jdbc driver transform the query on "select *
> from mytable
> >>> where date=null" and not "select * from mytable
> where date is null".
> >>>
> >>> Il seems that select * from mytable where date=null was
> supported by
> >>> postgres before 7.0 version, but not with the 7.2.1
> >>>
> >>> How can i do ?
> >>> thanks
> >>> Bruno.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> ---------------------------(end of
> broadcast)---------------------------
> >>> TIP 4: Don't 'kill -9' the postmaster
> >>>
> >>
> >>
> >
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
The solution is pretty straight forward here. For those who like the behavior then just set the transform_null_equals = true option in the postgresql.conf The jdbc driver should never change the sql Dave On Tue, 2002-05-14 at 05:50, Hoanui Morangie wrote: > Hi, > > I understand that this behavior is a standard but agree with Bruno that it's nonsense. I have trouble with it in my ownapplication. I use sql command like "select * from A where A.a=? and A.b=? and A.c=? and A.d=? and A.e=?". There are fiveparameters in my query and each of them can be null! I can not create SQL command string for all possible combinationsof null values so I have to build the query dynamically. Why then use prepared statement? > > I doubt that anybody ever needed x=null in SQL command but it seems that my example is very common. There SHOULD be someworkaround for that. But I agree that this is not topic for this list. > > Hoanui > > > > --- On Mon 05/13, Barry Lind wrote: > > Bruno, > > > > No, the driver should never alter your SQL statement. If you said '... > > = ? ' it would be wrong to change this to ' ... is ? '. How does the > > driver know that you wanted this behavior. (I know for example that > > code I have written would break if the driver suddenly decided to try to > > out guess what I intended). This is the responsibility of the client > > code to handle nulls appropriately for their application. You will not > > find the jdbc drivers for Oracle or MSSQL doing anything like this > > either. > > > > --Barry > > > > Bruno Lamouret wrote: > > > Hi, I agree with you when you say that '= null' isn't ANSI Standard. > > > But the trouble is that the jdbc driver does this error. > > > A query such as "select * from mytable where date=?" > > becomes "select * > > > from mytable where date=null" > > > with the jdbc driver when we put a null value in the > > preparedStatement > > > while it should become > > > "select * from mytable where date is null". > > > > > > am I right ? > > > > > > Thanks Bruno > > > > > > > > > Barry Lind wrote: > > > > > >> This isn't really a jdbc question and would probably better be > > >> addressed to pgsql-general. The behavior you are now seeing is > > ANSI > > >> Standard behavior. '= null' should always return false according > > to > > >> the standard. In 7.2 this non-standard behavior was fixed. You > > will > > >> see this behavior in all of the interfaces to postgres, not just > > jdbc. > > >> > > >> There is a parameter in the postgresql.conf file that will revert > > back > > >> to the old buggy behavior (transform_null_equals = true). > > >> > > >> thanks, > > >> --Barry > > >> > > >> > > >> blamouret wrote: > > >> > > >>> Hi, > > >>> I'm using Postgresql 7.2.1, with JBoss2.4.4. and jdk1.4 > > >>> > > >>> Here is my table : > > >>> id | date | value > > >>> -----+-------------------------+--------- > > >>> 1 | 2002-05-10 10:00:00:+02 | 5 > > >>> 2 | (null) | 10 > > >>> ... > > >>> > > >>> With the jdbc driver, this query doesn't any result : > > >>> Stmt = con.prepareStatement(select * from mytable where > > date=?) > > >>> Stmt.setNull(1,java.sql.Types.TIMESTAMP); > > >>> Stmt.executeQuery(); > > >>> > > >>> I think the jdbc driver transform the query on "select * > > from mytable > > >>> where date=null" and not "select * from mytable > > where date is null". > > >>> > > >>> Il seems that select * from mytable where date=null was > > supported by > > >>> postgres before 7.0 version, but not with the 7.2.1 > > >>> > > >>> How can i do ? > > >>> thanks > > >>> Bruno. > > >>> > > >>> > > >>> > > >>> > > >>> > > >>> ---------------------------(end of > > broadcast)--------------------------- > > >>> TIP 4: Don't 'kill -9' the postmaster > > >>> > > >> > > >> > > > > > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > ------------------------------------------------ > Join Excite! - http://www.excite.com > The most personalized portal on the Web!
----- Original Message -----From: Hoanui MorangieHi,
I understand that this behavior is a standard but agree with Bruno that it's nonsense. I have trouble with it in my own application. I use sql command like "select * from A where A.a=? and A.b=? and A.c=? and A.d=? and A.e=?". There are five parameters in my query and each of them can be null! I can not create SQL command string for all possible combinations of null values so I have to build the query dynamically. Why then use prepared statement?
I doubt that anybody ever needed x=null in SQL command but it seems that my example is very common. There SHOULD be some workaround for that. But I agree that this is not topic for this list.
don't use NULLs - introduce a dummy value that bears the semantic of NULL so u can use "= XXX" instead of " is NULL"
hth,
Marin
----
"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "
I need NULL value, some of these columns are foreign keys. And using dummy values is not good practice anyway. Hoanui --- On Tue 05/14, Marin Dimitrov wrote: > > > > > > > > > style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; > BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> > ----- Original Message ----- > style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: > black">From: > Hoanui > Morangie > > > Hi, I understand that this behavior is a standard but agree > with > Bruno that it's nonsense. I have trouble with it in my own application. > I use > sql command like "select * from A where A.a=? and A.b=? and A.c=? and > A.d=? > and A.e=?". There are five parameters in my query and each of them can > be > null! I can not create SQL command string for all possible combinations > of > null values so I have to build the query dynamically. Why then use > prepared > statement? I doubt that anybody ever needed x=null in SQL > command but > it seems that my example is very common. There SHOULD be some workaround > for > that. But I agree that this is not topic for this list. > > > > don't use NULLs - introduce a dummy value > that bears the > semantic of NULL so u can use "= XXX" instead of " is > NULL" > > hth, > > Marin > > ----"...what you > brought > from your past, is of no use in your present. When you must choose a > new > path, do not bring old experiences with you. Those who strike out > afresh, > but who attempt to retain a little of the old life, end up torn apart > by > their own memories. " > > ------------------------------------------------ Join Excite! - http://www.excite.com The most personalized portal on the Web!
I need NULL value, some of these columns are foreign keys. And using dummy values is not good practice anyway. Hoanui --- On Tue 05/14, Marin Dimitrov wrote: > > > > > > > > > style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; > BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> > ----- Original Message ----- > style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: > black">From: > Hoanui > Morangie > > > Hi, I understand that this behavior is a standard but agree > with > Bruno that it's nonsense. I have trouble with it in my own application. > I use > sql command like "select * from A where A.a=? and A.b=? and A.c=? and > A.d=? > and A.e=?". There are five parameters in my query and each of them can > be > null! I can not create SQL command string for all possible combinations > of > null values so I have to build the query dynamically. Why then use > prepared > statement? I doubt that anybody ever needed x=null in SQL > command but > it seems that my example is very common. There SHOULD be some workaround > for > that. But I agree that this is not topic for this list. > > > > don't use NULLs - introduce a dummy value > that bears the > semantic of NULL so u can use "= XXX" instead of " is > NULL" > > hth, > > Marin > > ----"...what you > brought > from your past, is of no use in your present. When you must choose a > new > path, do not bring old experiences with you. Those who strike out > afresh, > but who attempt to retain a little of the old life, end up torn apart > by > their own memories. " > > ------------------------------------------------ Join Excite! - http://www.excite.com The most personalized portal on the Web!
Is it good practice to use NULL values for foreign key fields? Thanks, Noel --- Hoanui Morangie <hoanui@excite.com> wrote: > > I need NULL value, some of these columns are foreign > keys. And using dummy values is not good practice > anyway. > > Hoanui > > > > --- On Tue 05/14, Marin Dimitrov wrote: > > > > > > > > > > > > > > > > > > style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; > MARGIN-LEFT: 5px; > > BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: > 0px"> > > ----- Original Message ----- > > style="BACKGROUND: #e4e4e4; FONT: 10pt arial; > font-color: > > black">From: > > Hoanui > > Morangie > > > > > > Hi, I understand that this behavior is a > standard but agree > > with > > Bruno that it's nonsense. I have trouble with it > in my own application. > > I use > > sql command like "select * from A where A.a=? > and A.b=? and A.c=? and > > A.d=? > > and A.e=?". There are five parameters in my > query and each of them can > > be > > null! I can not create SQL command string for > all possible combinations > > of > > null values so I have to build the query > dynamically. Why then use > > prepared > > statement? I doubt that anybody ever needed > x=null in SQL > > command but > > it seems that my example is very common. There > SHOULD be some workaround > > for > > that. But I agree that this is not topic for > this list. > > > > > > > > don't use NULLs - introduce a dummy value > > that bears the > > semantic of NULL so u can use "= XXX" instead of " > is > > NULL" > > > > hth, > > > > Marin > > > > ----"...what you > > brought > > from your past, is of no use in your present. When > you must choose a > > new > > path, do not bring old experiences with you. Those > who strike out > > afresh, > > but who attempt to retain a little of the old > life, end up torn apart > > by > > their own memories. " > > > > > > ------------------------------------------------ > Join Excite! - http://www.excite.com > The most personalized portal on the Web! > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to majordomo@postgresql.org > so that your > message can get through to the mailing list cleanly __________________________________________________ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com
Ofcourse Noel! How would you design the following example:
Table ORDER:
------------
order_id (PK) not null
date not null
...
invoice_id (FK) null
Table INVOICE:
--------------
invoice_id (PK) not null
...
Column invoice_id in the ORDER table can not be defined as "not null" because we don't know invoice_id yet and there can not be "dummy" value because of FK constraint. This is 1:N (or 1:1) relationship where the other object is not mandatory.
_________ _______
| | | |
| INVOICE |o----------o<| ORDER |
|_________| |_______|
Hoanui
--- On Tue 05/14, Noel Yap wrote:
> Is it good practice to use NULL values for foreign key
> fields?
>
> Thanks,
> Noel
> --- Hoanui Morangie wrote:
> >
> > I need NULL value, some of these columns are foreign
> > keys. And using dummy values is not good practice
> > anyway.
> >
> > Hoanui
> >
> >
> >
> > --- On Tue 05/14, Marin Dimitrov wrote:
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px;
> > MARGIN-LEFT: 5px;
> > > BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT:
> > 0px">
> > > ----- Original Message -----
> > > style="BACKGROUND: #e4e4e4; FONT: 10pt arial;
> > font-color:
> > > black">From:
> > > Hoanui
> > > Morangie
> > >
> > >
> > > Hi, I understand that this behavior is a
> > standard but agree
> > > with
> > > Bruno that it's nonsense. I have trouble with it
> > in my own application.
> > > I use
> > > sql command like "select * from A where A.a=?
> > and A.b=? and A.c=? and
> > > A.d=?
> > > and A.e=?". There are five parameters in my
> > query and each of them can
> > > be
> > > null! I can not create SQL command string for
> > all possible combinations
> > > of
> > > null values so I have to build the query
> > dynamically. Why then use
> > > prepared
> > > statement? I doubt that anybody ever needed
> > x=null in SQL
> > > command but
> > > it seems that my example is very common. There
> > SHOULD be some workaround
> > > for
> > > that. But I agree that this is not topic for
> > this list.
> > >
> > >
> > >
> > > don't use NULLs - introduce a dummy value
> > > that bears the
> > > semantic of NULL so u can use "= XXX" instead of
> "
> > is
> > > NULL"
> > >
> > > hth,
> > >
> > > Marin
> > >
> > > ----"...what you
> > > brought
> > > from your past, is of no use in your present. When
> > you must choose a
> > > new
> > > path, do not bring old experiences with you. Those
> > who strike out
> > > afresh,
> > > but who attempt to retain a little of the old
> > life, end up torn apart
> > > by
> > > their own memories. "
> > >
> > >
> >
> > ------------------------------------------------
> > Join Excite! - http://www.excite.com
> > The most personalized portal on the Web!
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please
> > send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org
> > so that your
> > message can get through to the mailing list cleanly
>
>
> __________________________________________________
> Do You Yahoo!?
> LAUNCH - Your Yahoo! Music Experience
> http://launch.yahoo.com
>
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
Table ORDER:
------------
order_id (PK) not null
date not null
...
invoice_id (FK) null
Table INVOICE:
--------------
invoice_id (PK) not null
...
Column invoice_id in the ORDER table can not be defined as "not null" because we don't know invoice_id yet and there can not be "dummy" value because of FK constraint. This is 1:N (or 1:1) relationship where the other object is not mandatory.
_________ _______
| | | |
| INVOICE |o----------o<| ORDER |
|_________| |_______|
Hoanui
--- On Tue 05/14, Noel Yap wrote:
> Is it good practice to use NULL values for foreign key
> fields?
>
> Thanks,
> Noel
> --- Hoanui Morangie wrote:
> >
> > I need NULL value, some of these columns are foreign
> > keys. And using dummy values is not good practice
> > anyway.
> >
> > Hoanui
> >
> >
> >
> > --- On Tue 05/14, Marin Dimitrov wrote:
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px;
> > MARGIN-LEFT: 5px;
> > > BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT:
> > 0px">
> > > ----- Original Message -----
> > > style="BACKGROUND: #e4e4e4; FONT: 10pt arial;
> > font-color:
> > > black">From:
> > > Hoanui
> > > Morangie
> > >
> > >
> > > Hi, I understand that this behavior is a
> > standard but agree
> > > with
> > > Bruno that it's nonsense. I have trouble with it
> > in my own application.
> > > I use
> > > sql command like "select * from A where A.a=?
> > and A.b=? and A.c=? and
> > > A.d=?
> > > and A.e=?". There are five parameters in my
> > query and each of them can
> > > be
> > > null! I can not create SQL command string for
> > all possible combinations
> > > of
> > > null values so I have to build the query
> > dynamically. Why then use
> > > prepared
> > > statement? I doubt that anybody ever needed
> > x=null in SQL
> > > command but
> > > it seems that my example is very common. There
> > SHOULD be some workaround
> > > for
> > > that. But I agree that this is not topic for
> > this list.
> > >
> > >
> > >
> > > don't use NULLs - introduce a dummy value
> > > that bears the
> > > semantic of NULL so u can use "= XXX" instead of
> "
> > is
> > > NULL"
> > >
> > > hth,
> > >
> > > Marin
> > >
> > > ----"...what you
> > > brought
> > > from your past, is of no use in your present. When
> > you must choose a
> > > new
> > > path, do not bring old experiences with you. Those
> > who strike out
> > > afresh,
> > > but who attempt to retain a little of the old
> > life, end up torn apart
> > > by
> > > their own memories. "
> > >
> > >
> >
> > ------------------------------------------------
> > Join Excite! - http://www.excite.com
> > The most personalized portal on the Web!
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please
> > send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org
> > so that your
> > message can get through to the mailing list cleanly
>
>
> __________________________________________________
> Do You Yahoo!?
> LAUNCH - Your Yahoo! Music Experience
> http://launch.yahoo.com
>
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
----- Original Message -----
From: "Hoanui Morangie"
>
> I need NULL value, some of these columns are foreign keys.
introduce dummy tupples too, f.e. if u consider 0 as the NULL for numbers,
then have a dummy row with ID=0, so that the FK won't be violated and u'll
still have NULL semantics
>And using dummy values is not good practice anyway.
>
indeed, 3-valued logic (nulls) is often considered as a flaw and NULL usage
is not recommended.
And in your case u won't make use of any indexes for the queries, because
these null values can't be indexed
Marin
----
"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "
The "proper" and portable approach would to solve it on the client (as Dave previously mentioned) - i.e. convert the statement into something like select * from mytable where date=? or ((coalesce(?,'x')='x' and date is null) or using Oracle syntax select * from mytable where date=? or ((nvl(?,'x')='x' and date is null) Well, you have another parameter, but there is not that much for free in this world - only important things ;-) Cheers, Jorg Janke (203) 445-9503 http://www.compiere.org Smart ERP & CRM Business Solution for Distribution and Service globally ------------------------------------------------------------------------ Porting to PostgreSQL: http://www.compiere.org/technology/pg/index.html General questions/issues: http://sourceforge.net/forum/?group_id=29057 Support via: http://sourceforge.net/tracker/?group_id=29057&atid=410216 ------------------------------------------------------------------------ -----Original Message----- From: Dave Cramer [mailto:Dave@micro-automation.net] Sent: 14 May, 2002 05:57 To: hoanui@excite.com Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] select * from mytable where myfield=null; The solution is pretty straight forward here. For those who like the behavior then just set the transform_null_equals = true option in the postgresql.conf The jdbc driver should never change the sql Dave On Tue, 2002-05-14 at 05:50, Hoanui Morangie wrote: > Hi, > > I understand that this behavior is a standard but agree with Bruno > that it's nonsense. I have trouble with it in my own application. I use sql command like "select * from A where A.a=? and A.b=? and A.c=? and A.d=? and A.e=?". There are five parameters in my query and each of them can be null! I can not create SQL command string for all possible combinations of null values so I have to build the query dynamically. Why then use prepared statement? > > I doubt that anybody ever needed x=null in SQL command but it seems > that my example is very common. There SHOULD be some workaround for that. But I agree that this is not topic for this list. > > Hoanui > > > > --- On Mon 05/13, Barry Lind wrote: > > Bruno, > > > > No, the driver should never alter your SQL statement. If you said > > '... > > = ? ' it would be wrong to change this to ' ... is ? '. How does the > > driver know that you wanted this behavior. (I know for example that > > code I have written would break if the driver suddenly decided to try to > > out guess what I intended). This is the responsibility of the client > > code to handle nulls appropriately for their application. You will not > > find the jdbc drivers for Oracle or MSSQL doing anything like this > > either. > > > > --Barry > > > > Bruno Lamouret wrote: > > > Hi, I agree with you when you say that '= null' isn't ANSI > > > Standard. But the trouble is that the jdbc driver does this error. > > > A query such as "select * from mytable where date=?" > > becomes "select * > > > from mytable where date=null" > > > with the jdbc driver when we put a null value in the > > preparedStatement > > > while it should become > > > "select * from mytable where date is null". > > > > > > am I right ? > > > > > > Thanks Bruno > > > > > > > > > Barry Lind wrote: > > > > > >> This isn't really a jdbc question and would probably better be > > >> addressed to pgsql-general. The behavior you are now seeing is > > ANSI > > >> Standard behavior. '= null' should always return false according > > to > > >> the standard. In 7.2 this non-standard behavior was fixed. You > > will > > >> see this behavior in all of the interfaces to postgres, not just > > jdbc. > > >> > > >> There is a parameter in the postgresql.conf file that will revert > > back > > >> to the old buggy behavior (transform_null_equals = true). > > >> > > >> thanks, > > >> --Barry > > >> > > >> > > >> blamouret wrote: > > >> > > >>> Hi, > > >>> I'm using Postgresql 7.2.1, with JBoss2.4.4. and jdk1.4 > > >>> > > >>> Here is my table : > > >>> id | date | value > > >>> -----+-------------------------+--------- > > >>> 1 | 2002-05-10 10:00:00:+02 | 5 > > >>> 2 | (null) | 10 > > >>> ... > > >>> > > >>> With the jdbc driver, this query doesn't any result : Stmt = > > >>> con.prepareStatement(select * from mytable where > > date=?) > > >>> Stmt.setNull(1,java.sql.Types.TIMESTAMP); > > >>> Stmt.executeQuery(); > > >>> > > >>> I think the jdbc driver transform the query on "select * > > from mytable > > >>> where date=null" and not "select * from mytable > > where date is null". > > >>> > > >>> Il seems that select * from mytable where date=null was > > supported by > > >>> postgres before 7.0 version, but not with the 7.2.1 > > >>> > > >>> How can i do ? > > >>> thanks > > >>> Bruno. > > >>> > > >>> > > >>> > > >>> > > >>> > > >>> ---------------------------(end of > > broadcast)--------------------------- > > >>> TIP 4: Don't 'kill -9' the postmaster > > >>> > > >> > > >> > > > > > > > > > > > > > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > ------------------------------------------------ > Join Excite! - http://www.excite.com > The most personalized portal on the Web! ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Well Marin, it is a solution but I'm afraid it would bring more troubles then benefits. You have dummy values and dummy tupples and have to be careful all the time to prevent mess in your data. I prefer using NULL value - it's interpretation unambiguous.
Hoanui
--- On Tue 05/14, Marin Dimitrov wrote:
>
> ----- Original Message -----
> From: "Hoanui Morangie"
>
> >
> > I need NULL value, some of these columns are foreign keys.
>
> introduce dummy tupples too, f.e. if u consider 0 as the NULL for
> numbers,
> then have a dummy row with ID=0, so that the FK won't be violated and
> u'll
> still have NULL semantics
>
> >And using dummy values is not good practice anyway.
> >
>
> indeed, 3-valued logic (nulls) is often considered as a flaw and NULL
> usage
> is not recommended.
> And in your case u won't make use of any indexes for the queries, because
> these null values can't be indexed
>
>
> Marin
>
> ----
> "...what you brought from your past, is of no use in your present.
> When
> you must choose a new path, do not bring old experiences with you.
> Those who strike out afresh, but who attempt to retain a little of the
> old life, end up torn apart by their own memories. "
>
>
>
>
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
Hoanui
--- On Tue 05/14, Marin Dimitrov wrote:
>
> ----- Original Message -----
> From: "Hoanui Morangie"
>
> >
> > I need NULL value, some of these columns are foreign keys.
>
> introduce dummy tupples too, f.e. if u consider 0 as the NULL for
> numbers,
> then have a dummy row with ID=0, so that the FK won't be violated and
> u'll
> still have NULL semantics
>
> >And using dummy values is not good practice anyway.
> >
>
> indeed, 3-valued logic (nulls) is often considered as a flaw and NULL
> usage
> is not recommended.
> And in your case u won't make use of any indexes for the queries, because
> these null values can't be indexed
>
>
> Marin
>
> ----
> "...what you brought from your past, is of no use in your present.
> When
> you must choose a new path, do not bring old experiences with you.
> Those who strike out afresh, but who attempt to retain a little of the
> old life, end up torn apart by their own memories. "
>
>
>
>
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
"Marin Dimitrov" <marin.dimitrov@sirma.bg> writes:
> don't use NULLs
I agree with Marin. If you think you need x = NULL to act like x IS
NULL, then you should rethink the way you are using NULL in your data
design. You are really swimming against the tide of the way that SQL
specifies NULL to work.
regards, tom lane
To my order-invoice example: there are not many choices, I know only three:
1) use NULL value and build proper query (x=? or x is null),
2) use dummy tupples and values (Marin's solution),
3) give up foreign key constraints on the database level and rely on the middle tier.
I have chosen 1).
Hoanui
--- On Tue 05/14, Tom Lane wrote:
> "Marin Dimitrov" writes:
> > don't use NULLs
>
> I agree with Marin. If you think you need x = NULL to act like x IS
> NULL, then you should rethink the way you are using NULL in your data
> design. You are really swimming against the tide of the way that SQL
> specifies NULL to work.
>
> regards, tom lane
>
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
1) use NULL value and build proper query (x=? or x is null),
2) use dummy tupples and values (Marin's solution),
3) give up foreign key constraints on the database level and rely on the middle tier.
I have chosen 1).
Hoanui
--- On Tue 05/14, Tom Lane wrote:
> "Marin Dimitrov" writes:
> > don't use NULLs
>
> I agree with Marin. If you think you need x = NULL to act like x IS
> NULL, then you should rethink the way you are using NULL in your data
> design. You are really swimming against the tide of the way that SQL
> specifies NULL to work.
>
> regards, tom lane
>
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
Tom Lane wrote: > "Marin Dimitrov" <marin.dimitrov@sirma.bg> writes: > >>don't use NULLs > > > I agree with Marin. If you think you need x = NULL to act like x IS > NULL, then you should rethink the way you are using NULL in your data > design. You are really swimming against the tide of the way that SQL > specifies NULL to work. > > regards, tom lane Has this change affected the behavior of the isnull operator/keyword/whatever it is... select * from tablename where foo isnull; I use that on occasion (in 7.1.2)...did this change in 7.2 also? --Jeremy