Inserting Data manually into table in AWS Redshift, sql workbench

Question

enter image description here 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.

enter image description here

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

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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.

Link to manual

I am part of SQL Workbench/J support

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 20, 2018 at 18:14

user10047216

Sign up to request clarification or add additional context in comments.

2 Comments

user10047216 Over a year ago

@Adriaan I am not spamming. I am merely linking to the manual to explain how that option is enabled. But I can remove the link to the manual if that makes you happy. I was just trying to be helpful

CalvT Over a year ago

links can stay, but ideally they are not essential to the answer. This is to prevent link rot (ie: so that if a link ever changes, the answer stays relevant). And then also you need to disclose that you are part of the organisation behind the link (if you are). I have edited your answer to include all this so you can see what SO is after. Thanks for contributing!

Yusuf Hassan · Accepted Answer · 2018-01-17 09:42:14Z

0

It's just a temporary acquired lock.

Disconnect the workbench from the datasource
Restart the workbench
Reconnect to your datasource.

You'll be able to resume from here.

answered Jan 17, 2018 at 9:42

Yusuf Hassan

2,0131 gold badge14 silver badges21 bronze badges

Collectives™ on Stack Overflow

Inserting Data manually into table in AWS Redshift, sql workbench

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related