Postgresql COPY FROM file with "timestamp with timezone" - getting "malformed array literal"

Question

I have a table with a column of type "timestamp with timezone"

I'm importing data from a file with this command

db=# COPY "temperature"  FROM 'd:\data\PostgresImport\TEMPLOG.CSV' DELIMITER ',' CSV;

I get this error

ERROR:  malformed array literal: "2009-01-05 18:40:00 z"
DETAIL:  Array value must start with "{" or dimension information.
CONTEXT:  COPY temperature, line 1, column timestamp: "2009-01-05 18:40:00 z"

I tried re-formatting the source data to show the timezone as +0000, but I still get the same error:

ERROR:  malformed array literal: "2009-01-05 18:40:00+0000"
DETAIL:  Array value must start with "{" or dimension information.
CONTEXT:  COPY temperature, line 1, column timestamp: "2009-01-05 18:40:00+0000"

tim11g · Accepted Answer · 2020-04-26 17:37:32Z

1

The column name cannot be "timestamp" - apparently that is a reserved word. Rather than flagging it as reserved when the table is created, it causes strange errors like this when importing data.

By changing the column name to "datetimestamp", the COPY FROM command works perfectly. (using the timestamp in the "2009-01-05 18:40:00+0000" format)

db=# COPY "temperature"  FROM 'd:\data\PostgresImport\TEMPLOG.CSV' DELIMITER ',' CSV;
COPY 595896
db=#

Further note: when I created the table with column "timestamp", apparently the type was created as an array:

                    Table "public.temperature"
     Column      |            Type            | Collation | Nullable |
-----------------+----------------------------+-----------+----------+
timestamp        | timestamp with time zone[] |           |          |

When I recreated the table with "datetimestamp" the column is no longer an array:

                         Table "public.temperature"
      Column      |           Type           | Collation | Nullable | 
------------------+--------------------------+-----------+----------+--
 datetimestamp    | timestamp with time zone |           |          |

edited Apr 26, 2020 at 17:37

answered Apr 26, 2020 at 16:14

tim11g

2,0336 gold badges32 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Belayer Over a year ago

It has nothing to do with your name change, it is the type definition. When the type definition format is x[] it means create array of x. So "timestamp timestamp with time zone" while is a very bad choice of names it is syntactically valid.

Collectives™ on Stack Overflow

Postgresql COPY FROM file with "timestamp with timezone" - getting "malformed array literal"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related