Redshift table creates are not durable/persistent

I'm using SqlWorkbenchJ to connect to my Redshift cluster and create a table:

CREATE TABLE mydb_dev.Widget (
  fizz BIGINT,
  buzz BIGINT,
  lastRanOn timestamp with time zone NOT NULL DEFAULT (current_timestamp AT TIME ZONE 'UTC')
);  

select * from mydb_dev.Widget;

When I run the above SELECT statement, it shows an empty table. So far so good.

However, if I disconnect and then reconnect to the cluster, and then run the same exact SELECT again, I get:

An error occurred when executing the SQL command:
select * from mydb_dev.Widget

[Amazon](500310) Invalid operation: relation "mydb_dev.Widget" does not exist;
1 statement failed.

Execution time: 0.06s

So it looks like table creates are not durable or persisting the current connection...whats the fix here?!?

Solution

In SQL Workbench, the default setting for autocommit when creating a new connection profile is "off". So when you create a new connection and don't change anything, autocommit will be disabled and you need to commit every transaction.

Alternatively, autocommit can be turned off/on interactively by executing:

SET autocommit ON/OFF

After creating the table you need to commit it, otherwise, when the current session ends, the created table is removed.

execute COMMIT after creating the table.

CREATE TABLE mydb_dev.Widget (
  fizz BIGINT,
  buzz BIGINT,
  lastRanOn timestamp with time zone NOT NULL DEFAULT (current_timestamp AT TIME ZONE 'UTC')
);  
COMMIT;