pg.ddx.io pgsql-interfaces@postgresql.org mailing list archivehelp / color / mirror / Atom feed
binding a variable to NULL in perl-DBD 3+ messages / 2 participants [nested] [flat]
* binding a variable to NULL in perl-DBD @ 2013-08-21 21:06 Max Pyziur <pyz@brama.com> 0 siblings, 1 reply; 3+ messages in thread From: Max Pyziur @ 2013-08-21 21:06 UTC (permalink / raw) To: pgsql-interfaces Greetings, I'm trying to determine how to pass "NULL" to a variable, specifically in the conditional section of a SQL statement: SELECT moo FROM foo aa WHERE field1 = ? AND field2 = ? Sometimes at the command line, when running the perl-DBD script, I would like the select statement to reflect: SELECT moo FROM foo aa WHERE field1 = 'goo' AND field2 IS NULL Much thanks in advance, Max Pyziur pyz@brama.com -- Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-interfaces ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: binding a variable to NULL in perl-DBD @ 2013-08-22 20:48 Daniel Verite <daniel@manitou-mail.org> parent: Max Pyziur <pyz@brama.com> 0 siblings, 1 reply; 3+ messages in thread From: Daniel Verite @ 2013-08-22 20:48 UTC (permalink / raw) To: Max Pyziur <pyz@brama.com>; +Cc: pgsql-interfaces Max Pyziur wrote: > I'm trying to determine how to pass "NULL" to a variable, specifically in > the conditional section of a SQL statement: > > SELECT moo > FROM foo aa > WHERE field1 = ? > AND field2 = ? Perl's undef is used to pass NULL as a literal but field=NULL will never be true. > SELECT moo > FROM foo aa > WHERE field1 = 'goo' > AND field2 IS NULL You may use: WHERE field1 = ? AND field2 IS NOT DISTINCT FROM ? which conveys the idea that field2 must be equal to the value passed, and works as expected with both non-NULL literals and NULL (undef). Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-interfaces ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: binding a variable to NULL in perl-DBD @ 2013-08-24 21:52 Max Pyziur <pyz@brama.com> parent: Daniel Verite <daniel@manitou-mail.org> 0 siblings, 0 replies; 3+ messages in thread From: Max Pyziur @ 2013-08-24 21:52 UTC (permalink / raw) To: Daniel Verite <daniel@manitou-mail.org>; +Cc: pgsql-interfaces On Thu, 22 Aug 2013, Daniel Verite wrote: > Max Pyziur wrote: > >> I'm trying to determine how to pass "NULL" to a variable, specifically in >> the conditional section of a SQL statement: >> >> SELECT moo >> FROM foo aa >> WHERE field1 = ? >> AND field2 = ? > > Perl's undef is used to pass NULL as a literal but field=NULL > will never be true. > >> SELECT moo >> FROM foo aa >> WHERE field1 = 'goo' >> AND field2 IS NULL > > You may use: > WHERE field1 = ? > AND field2 IS NOT DISTINCT FROM ? Much thanks for your help. The way that I'm making this work is that on the command line for the argument of interest I use the word NULL, then in the perl code I have: if ($FIELD2_CODE eq "NULL") { undef($FIELD2_CODE); } and then in the SQL: "AND field2 IS NOT DISTINCT FROM ?" seems to work Do I have a correct understanding of Postgres SQL's vernacular and application here? > which conveys the idea that field2 must be equal to the value passed, > and works as expected with both non-NULL literals and NULL (undef). > > Best regards, > Thank you again, Max Pyziur pyz@brama.com -- Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-interfaces ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-08-24 21:52 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2013-08-21 21:06 binding a variable to NULL in perl-DBD Max Pyziur <pyz@brama.com> 2013-08-22 20:48 ` Daniel Verite <daniel@manitou-mail.org> 2013-08-24 21:52 ` Max Pyziur <pyz@brama.com>
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