0

I am using Dbeaver to run queries for my database (postgresql). I am running two queries:

CREATE DATABASE example_db;

and before this query, I am running drop if exists query:

DROP DATABASE IF EXISTS example_db;

But, I am getting this error:

SQL Error [55006]: ERROR: cannot drop the currently open database

I know that I should disconnect the current database and connect to another database to run the drop query but I could not find any way to switch databases in postgresql.

I used \connect db_name but it didn't work because \connect command in an SQL script being executed in a context that does not recognize \connect as a valid command.

I researched use database but found out that it works in mysql but not in postgresql. I tried using "IF not exists" and then run ‘create database’ command but it also did not work out because create database cannot be run within a transaction block in postgresql.

In short, I couldn't find any way to connect to another database inside the script

What is the solution here?

Improve this question
6
  • Please read: How to ask, because it's unknow what errors you have, and what script your run. (and, please, do not comment on your own question, you better use edit for that!) Commented Dec 25, 2023 at 13:48
  • Thanks a lot, @Luuk for a polite explanation. I fixed my question and deleted my comment. Commented Dec 25, 2023 at 14:54
  • "In PostgreSQL you establish a connection to a specific database when setting up a session. CREATE DATABASE creates a new database, so you need to establish a new connection if you want to send SQL commands to this new database. This is different from MS SQL Server, where you always connect to the server, not a specific database, and can use the "use" command to send commands to any database of your server. There you can even use tables from several databases within a single SELECT statement by fully qualifying them. PostgreSQL regards databases as separate and closed entities.", Commented Dec 25, 2023 at 15:03
  • previous comment is a copy from: see: stackoverflow.com/questions/53663974/… Commented Dec 25, 2023 at 15:03
  • 2
    To be clear in MySQL a database is an alias for schema per MySQL Schema. Therefore you are creating new schemas in MySQL from a single connection. You can do that in Postgres also. Commented Dec 25, 2023 at 16:13
Related questions
1040
psql: FATAL: database "<user>" does not exist
638
"use database_name" command in PostgreSQL
736
Import SQL dump into PostgreSQL database
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.