cast column to_date with multiple date formats in original text column in postgresql

Question

I have a table with a event_date text column with values following one of two date formats:

1. 'YYYYMMDD'
2. 'YYYY-MM-DD'

For example:

event_date: text
1991-04-01
2009-02-11
20010101
NULL
20020101

How might I parse that column into Date format?

Especially considering that TO_DATE() will take only one possible format

Answer 1

Both formats would be converted to dates with:

SELECT event_date::date event_date
FROM tablename;

Or use a CASE expression to choose one of the two formats:

SELECT TO_DATE(
         event_date,
         CASE
           WHEN event_date LIKE '____-__-__' THEN 'YYYY-MM-DD'
           WHEN event_date LIKE '________' THEN 'YYYYMMDD'
         END
       ) event_date
FROM tablename;

See the demo.