Postgresql – Drop Schema from Postgresql

postgresql

I am trying to execute a few postgres commands from a bash file.

I am trying to drop a schema by writing the following line

DROP SCHEMA "xxx" IF EXISTS

I have two issues here

  1. I am not sure how to specify the db from which the schema should be deleted

  2. I am getting the following error message — "DROP: command not found"

Can anyone help me with this issue?

Best Answer

You cannot execute SQL statements directly from a bash script. Use psql to send the command to the database server. If you have a single (or a few) SQL statements to execute you can do it like this

psql -d YOUR_DB_NAME -c "YOUR_SQL_STATEMENT"

Make sure that your SQL statement does not include quotes or escape them appropriately. If you have many SQL statements in a file you can execute all of them like this

psql -d YOUR_DB_NAME -f FILENAME

In the above YOUR_DB_NAME should be replaced with your database name, YOUR_SQL_STATEMENT with the SQL statement you want to execute and FILENAME is the name of the file with SQL statements to be executed.

If you need to specify extra parameters like username, check the command's usage message displayed with psql -?.

Also, note that your syntax for DROP SCHEMA is incorrect. It should be

DROP SCHEMA IF EXISTS schema_name;

See also this documentation.

Related Topic