pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stuart Rison <stuart@ludwig.ucl.ac.uk>
To: pgsql-sql@postgresql.org
Cc: stuart@ludwig.ucl.ac.uk
Subject: [SQL] Typecasting datetype as date.  How do I cope with NULLs?
Date: Thu, 26 Nov 1998 15:55:42 +0000
Message-ID: <l03110700b2831665de7a@[128.40.242.190]> (raw)

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              |
+-------------------------+--------------------------------------+





view thread (2+ messages)  latest in thread

Message-ID: <l03110700b2831665de7a@[128.40.242.190]>
Permalink:  ../l03110700b2831665de7a@%5B128.40.242.190%5D/
Also on:    postgresql.org/message-id/l03110700b2831665de7a@[128.40.242.190]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: stuart@ludwig.ucl.ac.uk
  Subject: Re: [SQL] Typecasting datetype as date.  How do I cope with NULLs?
  In-Reply-To: <l03110700b2831665de7a@[128.40.242.190]>
  References: <l03110700b2831665de7a@[128.40.242.190]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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