Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (event_date varchar(10));
    
INSERT INTO tablename (event_date) VALUES
    ('1991-04-01'),
    ('2009-02-11'),
    ('20010101'),
    (NULL),
    ('20020101');

CREATE TABLE

INSERT 0 5

SELECT event_date::date event_date
FROM tablename;

event_date

1991-04-01

2009-02-11

2001-01-01

null

2002-01-01

SELECT 5

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;

event_date

1991-04-01

2009-02-11

2001-01-01

null

2002-01-01

SELECT 5