View Issue Details

IDProjectCategoryView StatusLast Update
0005001SymmetricDSBugpublic2021-05-28 20:40
Reporterelong Assigned Toelong  
Status closedResolutionfixed 
Product Version3.12.0 
Target Version3.12.10Fixed in Version3.12.10 
Summary0005001: Postgres converts json and uuid types to text for create table
DescriptionWhen using Postgres 9.5 and newer, tables with data types for uuid and json get converted to use text data type when it creates the table on the target. The XML for table has longvarchar for the cross-platform type, but has a platform column that is correct. On the target, the DDL builder is looking for a platform name of just "postgres" instead of "postgres95". Using platform names with version in them is maybe not a good idea, since these types exist on versions of Postgres before and after 9.5. Short term, we'll change DDL builder to look for "postgres95" as a low-risk change, which is the pattern in the code, but that won't work between different versions of Postgres, so we need a better long term fix.

<!DOCTYPE database SYSTEM "">
<database name="dataextractor">
    <table name="uuidjson">
        <column name="id" primaryKey="true" primaryKeySeq="1" required="true" type="INTEGER" size="10">
            <platform-column name="postgres95" type="int4" size="10"/>
        <column name="guid" type="LONGVARCHAR" size="2147483647">
            <platform-column name="postgres95" type="uuid" size="2147483647"/>
        <column name="notes" type="LONGVARCHAR" size="2147483647">
            <platform-column name="postgres95" type="json" size="2147483647"/>
Steps To ReproduceCREATE TABLE "public"."uuidjson"(
    "id" int4 NOT NULL,
    "guid" uuid,
    "notes" json,
    PRIMARY KEY ("id")

- Use Postgres 9.5 or newer
- Send a load that creates the table
Tagsddl/schema, dialect: postgresql


There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.12 24753da2

2021-05-21 20:40:10


Details Diff
0005001: Postgres converts json and uuid types to text for create table Affected Issues
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/ Diff File
add - symmetric-db/src/main/java/org/jumpmind/db/platform/postgresql/ Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/ Diff File

Issue History

Date Modified Username Field Change
2021-05-21 20:19 elong New Issue
2021-05-21 20:19 elong Status new => assigned
2021-05-21 20:19 elong Assigned To => elong
2021-05-21 20:19 elong Tag Attached: ddl/schema
2021-05-21 20:19 elong Tag Attached: dialect: postgresql
2021-05-21 20:40 elong Status assigned => resolved
2021-05-21 20:40 elong Resolution open => fixed
2021-05-21 20:40 elong Fixed in Version => 3.12.10
2021-05-21 21:00 admin Changeset attached => SymmetricDS 3.12 24753da2
2021-05-28 20:40 admin Status resolved => closed