1

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Pat Stroh
  • 189
  • 1
  • 3
  • 10

2 Answers2

2

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
-3

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)
Falmarri
  • 47,727
  • 41
  • 151
  • 191