presto: Postgresql CREATE TABLE AS fails with timestamp columns

When trying to use CREATE TABLE postgres_catalog.schema.table_name AS SELECT timestamp_column from another_pg_catalog.schema.table Presto throws an error.


Query 20170405_151901_00163_hzzb8, FAILED, 3 nodes
Splits: 52 total, 34 done (65.38%)
0:02 [1 rows, 0B] [0 rows/s, 0B/s]

Query 20170405_151901_00163_hzzb8 failed: Batch entry 0 INSERT INTO "postgres"."my_schema"."tmp_presto_88aa4cf1e3724dd5a45010ab786fd074" VALUES (1443814801104) was aborted.  Call getNextException to see the cause.

When I go into the target Postgres logs to see more detail the error seems to be that its trying to insert a bigint instead of a timestamp.

2017-04-05 15:19:03.740 GMT,...,"PARSE",...,ERROR,42804,"column ""created_date"" is of type timestamp without time zone but expression is of type bigint",,"You will need to rewrite or cast the expression.",,,,"INSERT INTO ""postgres"".""my_schema"".""tmp_presto_88aa4cf1e3724dd5a45010ab786fd074"" VALUES ($1)",88,,"

Not sure if I’m doing something wrong but I appear to have this issue when trying to CREATE TABLE _ AS SELECT .. with any timestamp column in the select.

About this issue

  • Original URL
  • State: open
  • Created 7 years ago
  • Reactions: 5
  • Comments: 15 (2 by maintainers)

Most upvoted comments

Hello, any news about this? I am using Presto 0.227 and PostgreSQL 10.6 and still getting the same issue reported by others here.

Thanks!