I am creating a table that will be populated with a COPY. Here's the format of that data:
6/30/2014 2:33:00 PM
MM-DD-YYYY HH:MM:SS ??
What would I use as the formatting for the CREATE TABLE statement?
CREATE TABLE practice (
Data_Time ????
)
One alternative might be to read as varchar() then format later. Seems convoluted tho.
Always store timestamps as timestamp (or timestamptz).
Never use string types (text, varchar, ...) for that.
CREATE TABLE practice (
practice_id serial PRIMARY KEY
, data_time timestamp NOT NULL
);
If your timestamp literals are clean and follow the standard MDY format, you can set the DateStyle temporarily for the transaction to read proper timestamp types directly:
BEGIN;
SET LOCAL datestyle = 'SQL, MDY'; -- works for your example
COPY practice (data_time) FROM '/path/to/file.csv';
COMMIT;
Else, your idea is not that bad: COPY to a temporary table with a text column, sanitize the data and INSERT timestamps from there possibly using to_timestamp(). Example:
You should pretty much never use vharchar() in postgres. Always use text. But it sounds to me like you want 2 columns
create table practice (date_time timestamp, format text)
