In PostgreSQL, what's data type you pass to a create table call when dealing with timestamp values?

Question

When creating a table how do you deal with a timestamp in csv file that has the following syntax - MM/DD/YY HH:MI? Here's an example: 1/1/16 19:00

I have tried the following script in PostgreSQL:

create table timetable (
     time timestamp
);

copy table from '<path>' delimiter ',' CSV;

But, I receive an error message saying:

ERROR: ERROR: invalid input syntax for type timestamp: "visit_datetime" Where: COPY air_reserve, line 16, column visit_datetime: "visit_datetime"

One solution I have considered is first creating the timestamp column in char then run a separate query that converts it to the appropriate timestamp datatype using the function call 'to_char(time, MM/DD/YY HH:MI). But, I'm looking for a solution that would enable to load the data in the correct datatype in a single query.

Possible duplicate of [Importing .csv with timestamp column (dd.mm.yyyy hh.mm.ss) using psql \copy](https://stackoverflow.com/questions/14042452/importing-csv-with-timestamp-column-dd-mm-yyyy-hh-mm-ss-using-psql-copy) — Dmitry, Dec 25 '17 at 09:52

score 0 · Accepted Answer · answered Dec 25 '17 at 11:50

You may find a datestyle that enables you to load the data you have, but sooner or later someone will deliver to you something that doesn't fit.

The solution you have considered is probably the best.

We use this as a standard pattern for loading data warehouses. We take today's data, load it into a staging table using varchar columns for any data that will not load directly into its target data type. We then run whatever scripts we need to to get the data into a good state, raising warnings for anything that is broken in a way we haven't seen before. Then we add the cleaned version of today's data into the table containing cleaned data for all previous days.

We don't mind if this takes several steps; we put them all in a script and run it as an automated job.

I'm working on documenting the techniques we use. You can see the beginnings of this at http://www.thedatastudio.net.

Thank you for a very comprehensive and thoughtful response! – daniel lee Dec 25 '17 at 20:13 — daniel lee, Dec 25 '17 at 20:13

In PostgreSQL, what's data type you pass to a create table call when dealing with timestamp values?

1 Answers1