I am able to connect to redshift from SQL workbench and I am able to create a table but When I try to insert values into the table It throws me the below error.
Since I am using temp schema and the connectivity shows schema as public, is this still an issue even if my insert statement is Insert into tempschema.temp_staging values
Postgres (and thus Redshift which is based on an ancient version of Postgres) has a very strict transaction concept: either all statements work or none.
As soon as one statement in your transaction fails, the whole transaction needs to be rolled back.
So all you need to do is to issue a ROLLBACK command and you can continue. There is no need to restart SQL Workbench/J.
If you don't want to do that for every statement that throws an error, just enable autocommit in the connection profile:
7.3.5. Autocommit
This check box enables/disables the "auto commit" property for the connection. If autocommit is enabled, then each SQL statement is automatically committed on the DBMS. If this is disabled, any DML statement (UPDATE, INSERT, DELETE, ...) has to be committed in order to make the change permanent. Some DBMS require a commit for DDL statements (CREATE TABLE, ...) as well. Please refer to the documentation of your DBMS.
I am part of SQL Workbench/J support
