PostgreSQL 11 Administration Cookbook

How it works…

Moving from the integer to the date type uses a complex USING expression. Let's break that down step by step so that we can see why, as follows:

postgres=# ALTER TABLE birthdayALTER COLUMN dob SET DATA TYPE dateUSING date(to_date(dob::text, 'YYMMDD') -      (CASE WHEN dob/10000 BETWEEN 16 AND 69      THEN interval '100 years'      ELSE interval '0' END));

First, we can't move directly from integer to date. We need to convert it into text and then to date. The dob::text statement means cast to text.

Once we have text, we use the to_date() function to move to a date type.

This is not enough; our starting data was 690926, which we presume is a date in the YYMMDD format. When PostgreSQL converts this data into a date, it assumes that the two-digit ...