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
  • 539,169
  • 125
  • 977
  • 1,137
Pat Stroh
  • 183
  • 1
  • 2
  • 8

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
  • 539,169
  • 125
  • 977
  • 1,137
-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
  • 46,415
  • 39
  • 146
  • 189