0

I have created table with query:

CREATE TABLE Z (
A varchar(30),
B varchar(30),
C timestamp,
D numeric,
E varchar(30),
F varchar(30),
G varchar(30),
H numeric
);

Now, I have a csv file with delimiter '|' and entries in this file are like

1313131|131313131|20130103115041|20.0000000|ABCD|EFGH||0.0000000

I am trying following command to import the data in the table:

\copy Z from filename WITH (FORMAT CSV , DELIMITER '|', NULL '');

But I am getting following error:

ERROR:  invalid input syntax for type timestamp: "20130103115041"
CONTEXT:  COPY Z, line 1, column C: "20130103115041"

I guess I have to specify the timestamp format while creating table so that it can accept the input in "YYYYMMDDHHMMSS" format.

Any ideas?

Thanks in advance.

user3176082
  • 1
  • 1

1 Answers1

2

You can convert such a timestamp with:

SELECT to_timestamp('20130103115041','YYYYMMDDHHMISS');

However, COPY doesn't offer input filters. So you'll probably want to CREATE UNLOGGED TABLE ... to store the COPY data, then do an INSERT INTO ... SELECT ... to insert and transform the data.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • And any implicit or explicit casts from `timestamp with time zone` to `timestamp` would drop the session's current time zone from the `timestampz` without butchering anything, right? Time zones and DST have triggered many extended swearing sessions over the years so I'm always paranoid about them. – mu is too short Jan 09 '14 at 05:36
  • @muistooshort If in doubt, `SET TimeZone = 'UTC'` first. Similarly, plenty of swearing. I always have to look the rules up as I can't ever remember off the top of my head, so unsure. – Craig Ringer Jan 09 '14 at 06:06