Database Administration - PostgreSQL 9 Administration - page 161

That's not enough. Our starting data was 690926 , which we presume is a date in the form
'YYMMDD'. When PostgreSQL converts to a date, it assumes that two-digit year 69 is in the
current century. So, it outputs 2069 rather than 1969. So, the case statement is added to
reduce any year more than 15 to be a date in the previous century, by explicitly subtracting an
interval of 100 years.
I recommend very strongly that you test this conversion by performing a SELECT first.
Converting datatypes, especially to/from dates always causes some problems, so don't try to
do this quickly. Always take a backup of the data first.
There's more...
The USING clause can also be used to handle complex expressions involving other columns.
This could be used for data transformations, which might be useful for DBAs in some
circumstances, such as migrating to a new database design on a production database server.
Let's put everything together into an example, look at a full working example. For example, if
you wished to transform the following table:
postgres=# select * from cust;
customerid | firstname | lastname | age
------------+-----------+----------+-----
1 | Philip | Marlowe | 38
2 | Richard | Hannay | 42
3 | Holly | Martins | 25
4 | Harry | Palmer | 36
(4 rows)
Into a table design like the following:
postgres=# select * from cust;
customerid | custname | age
------------+----------------+-----
1 | Philip Marlowe | 38
2 | Richard Hannay | 42
3 | Holly Martins | 25
4 | Harry Palmer | 36
(4 rows)
You might decide to do it using the following four simple steps.
ALTER TABLE cust ADD COLUMN custname text not null default '';
UPDATE cust SET custname = firstname || ' ' || lastname;
ALTER TABLE cust DROP COLUMN firstname;
ALTER TABLE cust DROP COLUMN lastname;