View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0004310SymmetricDSBugpublic2020-03-12 15:262023-05-26 13:29
Reporterjordanl010 Assigned To 
Priorityhigh 
Status closedResolutionfixed 
Product Version3.11.6 
Target Version3.14.7Fixed in Version3.14.7 
Summary0004310: Replicating to postgres to postgres timestamp with default
DescriptionReplicating local database with master database, postgres to postgres.

CREATE DATABASE SCRIPT:

CREATE TABLE public.merchant_item
(
merchant uuid,
item_date timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP(6),
item uuid NOT NULL,
serial_no text COLLATE pg_catalog."default",
mac_addr text COLLATE pg_catalog."default",
hdd_addr text COLLATE pg_catalog."default",
quantity integer,
cost integer,
document jsonb
)

<database name="dataextractor"> <column default="('now'::text)::timestamp(6) with time zone" type="TIMESTAMP" name="item_date" size="29,6"> <platform-column default="('now'::text)::timestamp(6) with time zone" type="timestamp" name="postgres95" decimaldigits="6" size="29"> </platform-column></column><column type="INTEGER" name="quantity" size="10"> <platform-column type="int4" name="postgres95" size="10"> </platform-column></column><column type="INTEGER" name="cost" size="10"> <platform-column type="int4" name="postgres95" size="10"> </platform-column></column><column type="LONGVARCHAR" name="merchant" size="2147483647"> <platform-column type="uuid" name="postgres95" size="2147483647"> </platform-column></column><column required="true" type="LONGVARCHAR" name="item" size="2147483647"> <platform-column type="uuid" name="postgres95" size="2147483647"> </platform-column></column><column type="LONGVARCHAR" name="serial_no" size="2147483647"> <platform-column type="text" name="postgres95" size="2147483647"> </platform-column></column><column type="LONGVARCHAR" name="mac_addr" size="2147483647"> <platform-column type="text" name="postgres95" size="2147483647"> </platform-column></column><column type="LONGVARCHAR" name="hdd_addr" size="2147483647"> <platform-column type="text" name="postgres95" size="2147483647"> </platform-column></column><column type="LONGVARCHAR" name="document" size="2147483647"> <platform-column type="jsonb" name="postgres95" size="2147483647"> </platform-column></column>
</database> org.jumpmind.db.sql.SqlException: ERROR: invalid input syntax for type timestamp: "('now'::text)::timestamp(6) with time zone" at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:305)
Steps To ReproduceSingle corp engine, single client engine.

PostgreSQL 11

CREATE TABLE public.merchant_item
(
merchant uuid,
item_date timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP(6),
item uuid NOT NULL,
serial_no text COLLATE pg_catalog."default",
mac_addr text COLLATE pg_catalog."default",
hdd_addr text COLLATE pg_catalog."default",
quantity integer,
cost integer,
document jsonb
)
Tagsddl/schema, dialect: postgresql

Activities

elong

2020-03-12 16:58

developer   ~0001691

Potential workaround for now is to set create.table.without.defaults=true or to create the tables manually. Thanks for reporting.

emiller

2022-08-08 15:38

developer   ~0002146

I used the same create table statement to attempt to reproduce the issue using SymmetricDS 3.14.0 and PostgreSQL 13.6. SymmetricDS was able to replicate the table to the target database without error. It's possible that this issue has already been resolved.

Can you upgrade to the latest version of SymmetricDS and see if you still encounter this issue?

Issue History

Date Modified Username Field Change
2020-03-12 15:26 jordanl010 New Issue
2020-03-12 16:56 elong Tag Attached: dialect: postgresql
2020-03-12 16:57 elong Tag Attached: ddl/schema
2020-03-12 16:58 elong Note Added: 0001691
2022-08-08 15:38 emiller Note Added: 0002146
2022-08-08 15:38 emiller Status new => feedback
2023-05-26 13:29 elong Status feedback => closed
2023-05-26 13:29 elong Resolution open => fixed
2023-05-26 13:29 elong Fixed in Version => 3.14.7