Database Reference
In-Depth Information
It works as expected. Now let's try moving to a
DATE
type:
postgres=# ALTER TABLE birthday
ALTER COLUMN dob SET DATA TYPE date
USING date(to_date(dob::text, 'YYMMDD') -
(case when dob/10000 < 15 then interval '0'
else interval '100 years' end);
It then gives what we were hoping to see:
postgres=# select * from birthday;
name | dob
-------+------------
simon | 26/09/1969
(1 row)
With PostgreSQL you can also set or drop default expressions, whether or not the
NOT
NULL
constraints are applied.
ALTER TABLE foo
ALTER COLUMN col DROP DEFAULT expression;
ALTER TABLE foo
ALTER COLUMN col SET DEFAULT 'expression';
ALTER TABLE foo
ALTER COLUMN col SET NOT NULL;
ALTER TABLE foo
ALTER COLUMN col DROP NOT NULL;
How it works...
Moving from the
integer
to the
date
type used a complex
USING
expression. Let's break
that down step-by-step so that we can see why, as follows:
postgres=# ALTER TABLE birthday
ALTER COLUMN dob SET DATA TYPE date
USING date(to_date(dob::text, 'YYMMDD') -
(case when dob/10000 <=15 then interval '0'
else interval '100 years' end);
First, we can't move directly from integer to date, we need to first convert to
text
and then
onto
date
.
dob::text
means "cast to text".
Once we have text, we use
to_date()
function to move into a date.
