bpchar convertion to date during import

Question

I would like to convert bpchar to date during import from the other database. To create an external table it should be matched with the parameters of the table in the other database. Finally, I need the simple table with the column with date type, not bpchar

For example:

1. table to be imported

CREATE TABLE example (
    mouse bpchar(4),
    "date" bpchar(10),
    quantity int4
);

2. external table should be like the first one
3. the final table should be

CREATE TABLE example (
    mouse bpchar(4),
    "date" date,
    quantity int4
);

date format 31.01.2021

i tried simple conversion to date ::date

but it was an error in the create statement

Answer 1

Providing all contents of the field "date" has valid dates, you can create a new table like this:

CREATE TABLE newexample AS 
SELECT mouse, to_date("date",'DD.MM.YYYY'), quantity FROM example;

see: DBFIDDLE