Errors with subqueries and joins in SQL workbench/j

Question

I am new to redshift and sql workbench/j and I'm coming from using bigquery so maybe I am unaware of slight code style changes. I have two very simple queries I am trying to execute in sql workbench/j that is connect to my redshift db and I keep getting error messages

Amazon Invalid operation: current transaction is aborted, commands ignored until end of transaction block; [SQL State=25P02, DB Errorcode=500310] 1 statement failed.

Documentation around workbench/j is very limited

Query 2

SELECT
report_date,
campaign,
ad_group,
ad_type,
data_source,
channel,
device,
impressions,
clicks,
conversions,
cost,
conversion_type
FROM
( 
SELECT
report_date,
campaign,
ad_group,
ad_type,
'Bing Ads' as data_source,
'Paid Search' as Channel,
device,
impressions,
clicks,
cost,
NULL AS conversions,
NULL as conversion_type,
FROM t1

new columns that would create null conversion types and conversions and data_source and channel variables both extremely simple queries I can execute in BQ but not in workbench/j

any ideas of why this might not be working? And general insight into nuances so I'm aware in the future.

SQL WorkbenchJ SQL WorkbenchJ · Accepted Answer · 2019-07-18T15:58:21

That has nothing to do with SQL Workbench/J. That is the default transactional behaviour with Postgres and thus with Redshift as well, as it is a fork of Postgres.

If any statement fails in a transaction, the transaction fails. You probably executed different queries before that, that failed and now you have to rollback your transaction before you continue (which is what the error message tells you).

The easiest way to deal with that in SQL Workbench/J (or any other SQL client), is to enable autocommit in the connection profile, so that each query automatically ends the transaction it starts.

Errors with subqueries and joins in SQL workbench/j

1 Answers