PostgreSQL Snaps

Scenario

Error/Output from the old JDBC Driver v9.4.1207

Error/Output from the new JDBC Driver v42.7.2 (Java 8)

PostgreSQL-Execute

When you provide multiple statements in the SQL Statement field, the Snap fails.

Status: 0

Output message: Success

Status: -3

Error message: Batch operation failed

PostgreSQL-Select

PostgreSQL-Lookup

PostgreSQL-Execute

Table columns of the Array type now output the data as a list instead of a string.

Output as a string:

{array_col_value={a, b, c}} 

Output message is now in an array:

{array_col_value=[a, b, c]}

PostgreSQL-Delete

PostgreSQL-Execute

PostgreSQL-Insert

PostgreSQL-Update

The various invalid SQL operation scenarios that result in failure include (but are not limited to) the following:

• When the input value you provide for a column is invalid for the column type.

• When you perform a delete operation that references a non-existent column.

• When you use an operator that does not exist or does not match the specific argument types.

• When the query references some relation (schema, table, etc.) that does not exist.

• When the query references a column that does not exist.

Error: SQL operation failed

Reason: ERROR: <scenario-specific message> Position: <number>, <error code and SQL state information>

For example: column "unknown" of relation "insertTests" does not exist Position: 55, error code: 0, SQL state: 42703

Error: Batch operation failed

Batch entry 0 <query> was aborted: ERROR: <scenario-specific message> Position: <number> Call getNextException to see other errors in the batch., <error code and SQL state information>, next exception: ERROR: <scenario-specific message>

For example: Batch entry 0 insert into "snapteam1"."insertTests" ("id", "c_int", "unknown") values (('1'::int4), ('1'::int4), ('99'::numeric)) was aborted: ERROR: column "unknown" of relation "insertTests" does not exist Position: 55 Call getNextException to see other errors in the batch., error code: 0, SQL state: 42703, next exception: ERROR: column "unknown" of relation "insertTests" does not exist Position: 55

PostgreSQL-Select

When the metadata is returned from the Snap’s second output view.

The original number of columns in the input data is displayed in the output.

In the Select Snap, the second output view, some new columns are introduced,
for example:

IS_GENERATEDCOLUMN , REF_GENERATION, SELF_REFERENCING_COL_NAME, TYPE_CAT, TYPE_NAME, TYPE_SCHEM

PostgreSQL-Delete

When the delete condition has an unterminated quote string.

Error message: SQL operation failed
Reason : ERROR: unterminated quoted string at or near "' )"\n Position: <number>, error code: 0, SQL state: 42601

Error message: SQL operation failed
Reason: "SQL [<query>]; Unterminated string literal started at position <number> in SQL <query>. Expected char"

PostgreSQL-Execute

When you provide the following query:

"create table <name> as <query>"

Status : 0
Message : Success

Status: 5
The <number> for Status indicates the number of rows used from the source to create the new table.
Message: Success

All PostgreSQL Snaps

When the Dynamic Account has incorrect credentials.

Error : SQL operation failed
Reason : "java.lang.RuntimeException: Driver org.postgresql.Driver claims to not accept jdbcUrl, jdbc:postgresql://??postgresql-v10.nia.snaplogic.com:5432/snapteam?OpenSourceSubProtocolOverride=true, Driver org.postgresql.Driver claims to not accept jdbcUrl, jdbc:postgresql://??postgresql-v10.nia.snaplogic.com:5432/snapteam?OpenSourceSubProtocolOverride=true"

Error message: SQL operation failed
Reason : "Failed to retrieve a database connection."