#11727: Import fail for different datatypes in PostgreSQL Database.

raghavsingh reported 2014-02-18T13:25:29Z · last modified 2014-06-27T18:26:18Z

Import fail for different datatypes in PostgreSQL Database.

Dev	tomconrad
QA	JennyNishimura

Priority	Major
Complexity	Unknown

Component	Tools - Import Tool
Version	15.0

Product: Aqua Data Studio

Version: 15.0.0-dev-73

Build #: 35575

Build Date: 2014-Jan-17 05:39:06 PM

Operating Environment: Windows NT (unknown) (6.2, amd64) / Cp1252

Database : PostgreSQL 9.2

Import fail for different datatypes in PostgreSQL Database.

Steps to reproduce :

1. Connect to PostgreSQL and use given below create script for creating table

CREATE TABLE test.faildatatypes (

bit_col bit(1) NULL,

box_col box NULL,

bytea_col bytea NOT NULL,

lseg_col lseg NULL,

path_col path NULL,

point_col point NULL,

polygon_col polygon NULL,

smallserial_col smallserial NOT NULL,

text_col text NULL,

tsquery_col tsquery NULL,

tsvector_col tsvector NULL,

xml_col xml NULL,

uuid_col uuid NOT NULL,

money_col money NOT NULL,

cidr_col cidr NOT NULL,

inet_col inet NULL,

macaddr_col macaddr NOT NULL

)

Now go to "faildatatypes" table and Import file,It will give error.

Execute batch failed: Batch entry 0 INSERT INTO "test"."faildatatypes"("bit_col", "box_col", "bytea_col", "lseg_col",

"path_col", "point_col", "polygon_col", "smallserial_col", "text_col", "tsquery_col", "tsvector_col", "xml_col",

"uuid_col", "money_col", "cidr_col", "inet_col", "macaddr_col") VALUES('1', '(3.0,3.0),(2.0,2.0)', '44',

'[(6.0,6.0),(2.0,2.0)]', '((0.0,0.0),(7.0,7.0),(3.0,3.0))', '(12.0,12.0)', '((59.0,59.0),(4.0,4.0),(67.0,67.0))',

'57', 'P', '''fAYrKCskIWC''', '''CQxokhQ''', 'org.postgresql.jdbc4.Jdbc4SQLXML@17369331', '5ffc952f-bd63-19c0-64b0-4f450907598a',

'''434672034861548.40''::money', '191.174.1.154/32', '112.115.1.118', '08:00:2b:01:02:03') was aborted. Call getNextException to see the cause.

We have try all datatypes seperately it shown same types of error message in view logs.

org.postgresql.util.PSQLException: ERROR: column "bit_col" is of type bit but expression is of type character varying

Hint: You will need to rewrite or cast the expression.

Position: 268

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2752)

at \\...\\ .\\??ñçêC???????\\.n???? interface try.executeBatch(Unknown Source)

at \\...\\ .\\??ñçêC???????\\.T????.executeBatch(Unknown Source)

at \\...\\ .\\??ñçêC???????\\.K???? case.process(Unknown Source)

at \\...\\ .\\??ñçêC???????\\.K???? case.run(Unknown Source)

Now we have try this ADS-15.0.0-dev-52 its working without any error.

2 attachments

Postgres_faildatatypes.png

2014-02-18T13:25:29Z

145 KB
faildatatypes.csv

2014-02-18T13:25:29Z

880 B

All Comments (5) Change History

JennyNishimura 2014-02-18T18:11:44Z

Now we have try this ADS-15.0.0-dev-52 its working without any error.

Issue #11135 reported that import failed for bit data type in PostgreSQL using dev-52. However, you report that it is working in dev-52.

Product: Aqua Data Studio

Version: 15.0.0-dev-73

Issue #11135 was fixed in dev-91. However, you are testing with dev-73.

Please run your test cases using the latest available build.

raghavsingh 2014-02-20T11:29:43Z

This issue reproduce in ADS- Version: 15.0.0-dev-122.

I have try all datatypes import seperately, it will thrown same exception.

" Hint: You will need to rewrite or cast the expression".

tomconrad 2014-02-26T19:06:07Z

Added support for true/false using a binary column for Batch and Full. From issue 11135, batch will still not work as Postgres JDBC doesn't work correctly for the prepared statements. It doesn't handle bit arrays correctly. This is a known problem. If customer gets the following error using Batch suggest that they use Full as this piece works.

Import started ...

Execute batch failed: Batch entry 0 INSERT INTO "dbschema"."bit_string_datatype"("bit_col", "bit_col1", "varbit_col") VALUES('011', '1', '111') was aborted. Call getNextException to see the cause.

ERROR: column "bit_col" is of type bit but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 91

Import completed.

raghavsingh 2014-02-27T11:19:22Z

Only Full Transaction is working

Verified ADS-15.0.0-dev-130

tariqrahiman 2014-06-27T18:26:18Z

Verified in ADS v15.0.0-dev-350. Full transaction mode works. Batch and Threshold, user gets a message to Cast or Rewrite Expression in the Import dialog.

Search Tips

Aqua Data Studio / nhilam

Import fail for different datatypes in PostgreSQL Database.

2 attachments

Issue #11727

Completion

1 issue link

Issue #13558

Search Tips

Aqua Data Studio / nhilam

Title

Import fail for different datatypes in PostgreSQL Database.

2 attachments

Issue #11727

Completion

1 issue link

Issue #13558