Postgresql CREATE TABLE AS fails with timestamp columns

[curtisq]

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.

[maciejgrzybek]

This is a duplicate of #6737, though it's better described so I'm closing the other one.

[ShanBai6]

I will try to investigate this.

[anandantrendwise]

Hi,

I am facing the same issue while trying to insert into postgresql from presto with timestamp type field. Is there any workaround for this issue?

Thanks & Regards,
Anandan. N

[hudsondba]

any update ?

Thanks

[electrum]

This should be fixed in the latest release. Can you give it a try?

[hudsondba]

Hi David,

The problem persist in 0.194 version.

presto:public> select node_version from system.runtime.nodes;
node_version

0.194
0.194
0.194
0.194
0.194
0.194
(6 rows)

Query 20180214_232052_00051_5f6xt, FINISHED, 2 nodes
Splits: 17 total, 17 done (100.00%)
0:00 [6 rows, 444B] [150 rows/s, 10.9KB/s]

presto:public> desc postgres.table_presto_example;
Column | Type | Extra | Comment
--------+---------------+-------+---------
x | varchar(2000) | |
z | timestamp | |
(2 rows)

Query 20180214_232115_00053_5f6xt, FINISHED, 2 nodes
Splits: 18 total, 18 done (100.00%)
0:01 [2 rows, 138B] [1 rows/s, 111B/s]

presto:public> insert into postgres.table_presto_example select cast(lurker_id as varchar(2000)),load_ts from hive.tracker.lurker_web_2 where year=2018 and month=2 and day=14 limit 100;

Query 20180214_232127_00054_5f6xt, FAILED, 6 nodes
Splits: 635 total, 181 done (28.50%)
0:06 [71.7K rows, 99.4MB] [11.1K rows/s, 15.4MB/s]

Query 20180214_232127_00054_5f6xt failed: Unsupported column type: timestamp

presto:public>

[sturzhav]

I pulled current master, JdbcPageSink is missing timestamp in if/else block, so it can not possibly work.

Is anybody looking into it?

Here is the actual stack trace:

[13] Query failed (#20180227_185325_03234_9xbkg): Unsupported column type: timestamp
java.lang.RuntimeException: com.facebook.presto.spi.PrestoException: Unsupported column type: timestamp
at com.facebook.presto.plugin.jdbc.JdbcPageSink.appendColumn(JdbcPageSink.java:163)
at com.facebook.presto.plugin.jdbc.JdbcPageSink.appendPage(JdbcPageSink.java:94)
at com.facebook.presto.operator.TableWriterOperator.addInput(TableWriterOperator.java:205)
at com.facebook.presto.operator.Driver.processInternal(Driver.java:378)
at com.facebook.presto.operator.Driver.lambda$processFor$8(Driver.java:269)
at com.facebook.presto.operator.Driver.tryWithLock(Driver.java:652)
at com.facebook.presto.operator.Driver.processFor(Driver.java:263)

[wfaria]

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!

[noahkawasakigoogle]

+1 Presto 0.231, 0.240, 0.243, basically all

[iceted]

works for me

presto:default> create table some_table (ts) as select current_timestamp;
CREATE TABLE: 1 row

Query 20201106_035558_00024_nd6xn, FINISHED, 3 nodes
Splits: 25 total, 25 done (100.00%)
0.21 [0 rows, 0B] [0 rows/s, 0B/s]

presto:default> select * from some_table;
ts

2020-11-06 03:55:59.051 UTC
(1 row)

Query 20201106_035603_00025_nd6xn, FINISHED, 2 nodes
Splits: 5 total, 5 done (100.00%)
0.21 [1 rows, 0B] [4 rows/s, 0B/s]

[noahkawasakigoogle]

Probably because you explicitly define the (ts) column, or maybe CURRENT_TIMESTAMP is correctly parsed where as selecting an actual column is different. Other queries fail:

CREATE TABLE my_table AS SELECT * FROM users

Assume that users has a timestamp column.

presto:schema>  CREATE TABLE my_table (created_at) AS SELECT created_at FROM users;

Query 20201106_162420_00259_wybdb, FAILED, 1 node
Splits: 19 total, 1 done (5.26%)
0:00 [85 rows, 0B] [538 rows/s, 0B/s]

Query 20201106_162420_00259_wybdb failed: Unsupported column type: timestamp

[iceted]

presto> describe some_table;
Column | Type | Extra | Comment
--------+-----------------------------+-------+---------
ts | timestamp(3) with time zone | |

presto> create table another_table as select * from some_table;
CREATE TABLE: 1 row

Query 20201106_163325_00011_jmxz6, FINISHED, 3 nodes
Splits: 21 total, 21 done (100.00%)
0.21 [1 rows, 0B] [4 rows/s, 0B/s]

presto> describe another_table;
Column | Type | Extra | Comment
--------+-----------------------------+-------+---------
ts | timestamp(3) with time zone | |
(1 row)

[noahkawasakigoogle]

hm, interesting. What version of presto and postgres are you using?

[iceted]

presto 345 and postgres 9.5

[noahkawasakigoogle]

Then you must be using PrestoSQL, not PrestoDB?