Postgresql date formatting -

Postgresql date formatting -

i have dataset (in .tsv format) 1 of columns date of birth. however, info old, , not dates in yyyy-mm-dd format. entries have year of birth (month , day of birth missing) , in format yyyy-##-## (literally ##'s inserted in info wherever year known). wish load dataset postgres database, date of birth column info type date , not string, can comparing on dates. little sample shown below. (irrelevant columns of info not shown)

1924-##-## 1965-09-04 1944-11-05 1951-##-## -388-##-## 1893-01-26 1037-##-##

directly mass loading dataset obviously gives error

error: invalid input syntax type date: "1924-##-##" line 1: insert d values ('1924-##-##'); ^

the dataset quite large, having around 6 crore entries. thinking of running script replacing these ##'s 01 , inserting modified info database. don't thought -

this time consuming. this disk-space consuming (as maintain original "umtampered" data) also, not info genuine in database.

is there way can inquire postgres somehow take dates is, ignoring `##'s (and keeping year months , days missing) ? or can there improve solution problem?

you can create in table 2 columns, 1 entered value (type of varchar), , 1 calculation (type date).

create table your_table ( id int, -- other details dob_entered varchar, dob_parsed date );

you can utilize on insert trigger automatically populate date field varchar, update trigger handle changes.

create or replace function evaluate_dob_date() returns trigger $$ begin new.dob_parsed = cast(replace(new.dob_entered,'##','01') date); homecoming new; end; $$ language plpgsql; create trigger parse_dob before insert or update on your_table each row execute procedure evaluate_dob_date();

this mean store both entered info untouched verification, while still having date field in database suitable sorting , comparing etc. also, extending evaluate_dob_date() function can match against different cases find them, while still beingness able reject records genuinely invalid.

postgresql triggers

postgresql date formatting postgresql-8.4