Re: [SQL] RFC: A brief guide to nulls
This should be added (From PostgreSQL integrated docs...): SQL uses a three-valued Boolean logic where the null value represents "unknown". Observe the following truth tables: a | b | a AND b | a OR b --+---+-+ TRUE | TRUE | TRUE| TRUE TRUE | FALSE | FALSE | TRUE TRUE | NULL | NULL| TRUE FALSE | FALSE | FALSE | FALSE FALSE | NULL | FALSE | NULL NULL | NULL | NULL| NULL So, if there's any trues in the chain of ORs, the whole expression will be true, not null. This conforms to NULL representing unknown value. If you have "true or unknown", of course whole result is true regardless of the "unknown". Let's check this example: > Subqueries and nulls > > Since tests always fail when testing against nulls you can have unexpected > results with sub-queries. > > Example: Assume we have a companies table and a diary table. Diary entries > are usually related to a particular company but not always. > SELECT co_name FROM companies WHERE co_id NOT IN (SELECT dy_company FROM > diary); > If any row in diary contains a null dy_company then you will get *no > results*. > We can expand the query like so: > WHERE co_id NOT IN (SELECT dy_company FROM diary) > WHERE co_id NOT IN (1, 2, null, 3...) > WHERE NOT (co_id=1 OR co_id=2 OR co_id=null OR co_id=3...) > WHERE NOT (... OR null OR ...) > WHERE NOT (null) (erm... actually not) template1=# select 5 in (1, 2, 9, null); ?column? -- (1 row) template1=# select 2 in (1, 2, 9, null); ?column? -- t (1 row) WHERE NOT (null/true) -> evaluates to WHERE null/false So the result was the same - but only for this example. Suppose you take the NOT away: template1=# select * from a where a.i in (1, 2, 9, null); i --- 1 2 (2 rows) I surely see two lines (not 0)... :) And this could be added too, for clarification: "SELECT clause lists lines for which the WHERE expression is certainly known to be true." ;) -- Antti Haapala ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] RFC: A brief guide to nulls
A few more comments... > It might be useful to be able to distinguish between these two cases - not > applicable and unknown, but there is only one option "Null" available to > us, so we can't. If we really need to distinguish between these two cases, I think null shouldn't be used as a N/A value but some other like empty string or 0. (IMHO it's preferable not to use null as N/A at all). For example sex could be classified as 'n' - not applicable 'f' - female 'm' - male null - yet unknown > Example: with the customer table above you could run the following queries: > SELECT * FROM customer WHERE sex='M'; > SELECT * FROM customer WHERE sex<>'M'; > Now you might think this returns all customers, but it will miss those > where sex is null. You've asked for all rows where the value of sex is 'M' > and all those with values not equal to 'M' but not rows with *no value at > all* these could be explained as select all customers who surely are men select all customers who surely aren't men if customers sex is unknown - null, we can't decide whether they're men or not. > The first case can be especially confusing. Concatenating a null string to > a string value will return null, not the original value. Isn't it null, not null string? ;) > Keys and nulls > == > No column that is part of a primary key can be null. When you define a > PRIMARY KEY, none of the columns mentioned can take a null value. > Postgresql makes sure of this by defining the columns as NOT NULL for you. ... because primary keys are to uniquelly identify rows in a table, and how's an unknown values going to do that :) -- Antti Haapala +358 50 369 3535 ICQ: #177673735 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] casting interval to time
On Mon, 16 Jun 2003, Tom Lane wrote:
> Tomasz Myrta <[EMAIL PROTECTED]> writes:
> > Dnia 2003-06-16 16:53, U¿ytkownik Tom Lane napisa³:
> > > Tomasz Myrta <[EMAIL PROTECTED]> writes:
> > >
> > > > psql (PostgreSQL) 7.3.2
> > > > SELECT cast(cast('1 day 12 hours' as interval) as time);
> > > > time
> > > > --
> > > > 00:00:00
> > > > (1 row)
> > >
> > >
> > > I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe?
>
> > Default Debian package. I got the same result even on a 7.3.3 Debian
> > package.
>
> What do you get from "pg_config --configure"? What's the hardware
> platform --- i386, or something else? Can anyone else reproduce this,
> on any platform?
template1=# select cast('25 hours'::interval as time);
time
--
00:00:00
(1 row)
template1=# select version();
version
PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)
It's also Debian (3.0).
% pg_config --configure '--host=i386-linux' '--build=i386-linux'
'--prefix=/usr' '--mandir=/usr/share/man' '--docdir=/usr/share/doc'
'--bindir=/usr/lib/postgresql/bin' '--libdir=/usr/lib'
'--includedir=/usr/include/postgresql' '--enable-recode' '--enable-nls'
'--enable-integer-datetimes' (could be this?)
'--enable-debug'
'--disable-rpath' '--with-tcl' '--with-perl' '--with-python' '--with-pam'
'--with-openssl' '--with-gnu-ld' '--with-krb5'
'--with-tclconfig=/usr/lib/tcl8.4' '--with-tkconfig=/usr/lib/tk8.4'
'--with-includes=/usr/include/tcl8.4' '--with-maxbackends=64'
'--with-pgport=5432'
'DOCBOOKSTYLE=/usr/share/sgml/docbook/stylesheet/dsssl/modular'
'build_alias=i386-linux' 'host_alias=i386-linux'
---
Not affected: GentooLinux 1.4 rc2 x86 w/ pg 7.3, gcc 3.2.1, glibc 2.3.1 w/
following configure options:
'--prefix=/usr' '--mandir=/usr/share/man' '--host=i586-pc-linux-gnu'
'--docdir=/usr/share/doc/postgresql-7.3' '--libdir=/usr/lib'
'--enable-syslog' '--enable-depend' '--with-gnu-ld' '--with-pam'
'--with-maxbackends=1024' '--with-python' '--with-perl' '--with-java'
'--with-openssl' '--enable-locale' '--enable-nls' '--enable-multibyte'
'--with-CXX' 'CC=gcc' 'CFLAGS=-march=pentium-mmx -O3 -pipe'
'host_alias=i586-pc-linux-gnu'
--
Antti Haapala
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
