Received: from mail-b.bcc.ac.uk (mail-b.bcc.ac.uk [144.82.100.22]) by hub.org (8.9.1/8.9.1) with ESMTP id LAA18468 for ; Thu, 26 Nov 1998 11:22:32 -0500 (EST) (envelope-from stuart@ludwig.ucl.ac.uk) Received: from kestrel.ludwig.ucl.ac.uk by mail-b.bcc.ac.uk with SMTP (XT-PP); Thu, 26 Nov 1998 16:22:10 +0000 Received: from [128.40.242.190] by kestrel.ludwig.ucl.ac.uk (SMI-8.6/SMI-SVR4) id QAA00350; Thu, 26 Nov 1998 16:23:25 GMT X-Sender: stuart@kestrel.ludwig.ucl.ac.uk Message-Id: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Thu, 26 Nov 1998 15:55:42 +0000 To: pgsql-sql@postgresql.org From: Stuart Rison Subject: [SQL] Typecasting datetype as date. How do I cope with NULLs? Cc: stuart@ludwig.ucl.ac.uk Dear All, For those of you who don't want to wade through the details, here's the question: "How do I get the date portion of a datetime field for ALL ENTRIES in a table regardless of whether the entry is NULL or not? (N.B. Typecasting a datetime NULL as date generates an error)" details... I have a table which stores, among other information, people's Date of Birth as a datetime datatype called dob (I don't use the 'time' part but the datatype is supported by many more functions). Of course, I don't always know the birthday of the person involved so the filed sometimes has a NULL value. patients=> \d patients Table = patients +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | patient_id | char() not null | 16 | | surname | text | var | | firstname | text | var | | othernames | text | var | | dob | datetime | 8 | | sex | char() | 1 | +----------------------------------+----------------------------------+-------+ Index: patients_new_pkey patients=> I have a select on this table which typecasts the dob (Date of Birth) as date i.e.: patients=> SELECT surname,firstname,othernames,dob::date FROM patients; ERROR: Unable to convert null datetime to date patients=> I find myself having to do two selects to get all the people in the table. i.e.: patients=> SELECT surname,firstname,othernames,dob::date FROM patients WHERE dob IS NOT NULL; surname|firstname|othernames | date -------+---------+--------------------+---------- Goose |Mother |Lay Golden Eggs |11-01-1923 One |Un |Uno Ein |11-11-1111 Light |Dee |Full |22-01-1933 (3 rows) patients=> SELECT surname,firstname,othernames FROM patients WHERE dob IS NULL; surname|firstname|othernames -------+---------+---------- Rison |Stuart | Rison |This |Pal Rison |Mark | (3 rows) My question is, how do I get surname,firstname,othername and the date portion of ALL people in table people regardless of whether the entry has an actual dob or a NULL dob. The best I have managed so far is: patients=> SELECT surname,firstname,othernames,date_part('day',dob) as dd, patients-> date_part('month',dob) as mm, date_part('year',dob) as yyyy patients-> FROM patients; surname|firstname|othernames |dd|mm|yyyy -------+---------+--------------------+--+--+---- Goose |Mother |Lay Golden Eggs |11| 1|1923 One |Un |Uno Ein |11|11|1111 Light |Dee |Full |22| 1|1933 Rison |Stuart | | | | Rison |This |Pal | | | Rison |Mark | | | | (6 rows) patients=> But I would like to have the date of birth as one field rather than 3. I have tried concatenating with || (no joy, date_part returns a float8 so can't cat) and textcat (same problem). Typecasting the return value of date_part does not work (e.g.: patients=> select date_part('day',dob)::text || date_part('month',dob)::text fro m patients; ?column? -------------------------------------------------------- Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:01 2000 GMT Sat 01 Jan 00:00:01 2000 GMTSat 01 Jan 00:00:04 2000 GMT Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:11 2000 GMT Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:11 2000 GMT Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:11 2000 GMT Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:11 2000 GMT (6 rows) patients=> select textcat(date_part('day',dob)::text,date_part('month',dob)::text); same output! CAN IT BE DONE??? cheers, Stuart. +-------------------------+--------------------------------------+ | Stuart Rison | Ludwig Institute for Cancer Research | +-------------------------+ 91 Riding House Street | | Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. | | Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk | +-------------------------+--------------------------------------+