What is the difference between "psql -c" and "psql -f" when executing multiple queries?

user_10913934 0
Viewed 3028

I'm trying to execute two sql commands (create a new schema and table), in a way that would enable a rollback of both commands if the execution fails. The database I'm connecting to is AWS Redshift.

create schema if not exists test_schema;
create table test_schema.test_table as select 1;

Initially I tried to execute these commands programatically with python, using both psycopg2 and pyodbc, and got the following error:

ERROR:  schema "test_schema" does not exist

I realised that it fails because the first command isn't being comitted, so to fix that , I tried setting the autocommit mode on, and wrapping the statements with "begin/end" block, which didn't help.

When I used psql CLI and ran the following, everything worked as intended (there was no "schema does not exist" error, and after the rollback, both schema and table were gone):

dev=# begin;
BEGIN
dev=# create schema test_schema;
CREATE SCHEMA
dev=# create table test_schema.test_table as select 1;
SELECT
dev=# rollback;
ROLLBACK

I tried to get the same results by running the following in the command line:

psql -c "begin; create schema test_schema; create table test_schema.test_table as select 1;"

This results in the same error:

ERROR: schema "test_schema" does not exist

However, when I put the above code in a file and ran the same command, this time using -f, it worked:

psql -f create_schema_and_table.sql

My questions are:

  1. What is the difference between executing queries with "psql -c" and "psql -f"?

  2. How can the same result be achieved programatically, with python?

Thanks a lot!

amazon-redshift psql pyodbc psycopg2 sql
1 Answers

I don't know what you are doing wrong, your "psql -c" command works perfectly fine:

ads@diamond:~$ psql -c "begin; create schema test_schema; create table test_schema.test_table as select 1;" postgres
SELECT 1

psql will send the entire string to the server, and execute it in one single transaction. Your problem is that you start a transaction using "begin", but never commit it. Therefore at the end of the psql run, all your changes are rolled back. The next psql command will not find the schema, nor the table. But as long as everything stays in a single psql call, subsequent queries in the same command can see newly created objects.

Your query string should instead look like:

begin; create schema test_schema; create table test_schema.test_table as select 1; commit;

Or, more easy:

create schema test_schema; create table test_schema.test_table as select 1;

Both will work.

user_53742050
Related