Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
Restrictions
You can ask the system catalog pg_database
- accessible from any database in the same database cluster. The tricky part is that CREATE DATABASE
can only be executed as a single statement. The manual:
CREATE DATABASE
cannot be executed inside a transaction block.
So it cannot be run directly inside a function or DO
statement, where it would be inside a transaction block implicitly. SQL procedures, introduced with Postgres 11, cannot help with this either.
Workaround from within psql
You can work around it from within psql by executing the DDL statement conditionally:
SELECT 'CREATE DATABASE mydb'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec
The manual:
\gexec
Sends the current query buffer to the server, then treats each column of each row of the query's output (if any) as a SQL statement to be executed.
Workaround from the shell
With \gexec
you only need to call psql once:
echo "SELECT 'CREATE DATABASE mydb' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec" | psql
You may need more psql options for your connection; role, port, password, ... See:
- Run batch file with psql command without password
The same cannot be called with psql -c "SELECT ...\gexec"
since \gexec
is a psql meta‑command and the -c
option expects a single command for which the manual states:
command
must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands within a-c
option.
Workaround from within Postgres transaction
You could use a dblink
connection back to the current database, which runs outside of the transaction block. Effects can therefore also not be rolled back.
Install the additional module dblink for this (once per database):
- How to use (install) dblink in PostgreSQL?
Then:
DO
$do$
BEGIN
IF EXISTS (SELECT FROM pg_database WHERE datname = 'mydb') THEN
RAISE NOTICE 'Database already exists'; -- optional
ELSE
PERFORM dblink_exec('dbname=' || current_database() -- current db
, 'CREATE DATABASE mydb');
END IF;
END
$do$;
Again, you may need more psql options for the connection. See Ortwin's added answer:
- Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
Detailed explanation for dblink:
- How do I do large non-blocking updates in PostgreSQL?
You can make this a function for repeated use.
Postgres database create if not exists
No but you could query the pg_catalog.pg_database table to see if it exists.
Why is the CREATE DATABASE command not working?
You have to terminate SQL commands with ;
.
In your case:
CREATE DATABASE test;
what is the equivalent of CREATE VIEW IF NOT EXISTS in postresql
You could use CREATE OR REPLACE
:
CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be completely different.
CREATE OR REPLACE VIEW complete_user_profile ...
PostgreSQL Table exists but python says it doesn't
You should try this.
import psycopg2
# Connect to the Database Server, now with the created DB
try:
connection = psycopg2.connect(user="postgres", password="postgres", host="127.0.0.1", port="5432", database="dashboard")
except (Exception, psycopg2.Error) as error:
print("Connection not established", error)
# Check if test_run Table Exists
cursor = connection.cursor()
cursor.execute("SELECT EXISTS(SELECT * FROM information_schema.tables WHERE table_name='test_run')")
if bool(cursor.fetchone()[0]):
print('test_run table exists. Moving On.')
else:
print('test_run does not exist. Creating the Table now.')
cursor.execute("CREATE TABLE test_run (run_id serial PRIMARY KEY, date date, status varchar(255), "
"total_time integer, project_name varchar(255));")
connection.commit()
Create New Database and a Schema in new database using Sql Script in PostgreSQL
Try:
\connect dezdaz
.
You did not do CREATE DATABASE "DEZDAZ"
so the database name is folded to lower case dezdaz
per Identifiers. Looks like \connect
is doing "DEZDAZ"
and not finding the upper case version of the name. This is called out in src/bin/psql/command.c
:
/*
* Read and interpret an argument to the \connect slash command.
...
/*
* Ideally we should treat the arguments as SQL identifiers. But for
* backwards compatibility with 7.2 and older pg_dump files, we have to
* take unquoted arguments verbatim (don't downcase them). For now,
* double-quoted arguments may be stripped of double quotes (as if SQL
* identifiers). By 7.4 or so, pg_dump files can be expected to
* double-quote all mixed-case \connect arguments, and then we can get rid
* of OT_SQLIDHACK.
*/
Related Topics
Effect of Nolock Hint in Select Statements
How to Return Multiple Values in One Column (T-Sql)
Custom Serial/Autoincrement Per Group of Values
Hive Select Count(*) Non Null Returns Higher Value Than Select Count(*)
Combining Union and Limit Operations in MySQL Query
Get Join Table as Array of Results With Postgresql/Nodejs
Ora-00918: Column Ambiguously Defined in Select *
What Are the Most Common SQL Anti-Patterns
Difference Between Primary Key and Unique Key
Performing SQL Queries on an Excel Table Within a Workbook With Vba Macro
Generate a Resultset of Incrementing Dates in Tsql
Why No Windowed Functions in Where Clauses
Creating a "Numbers Table" in MySQL
Create a Pivot Table With Postgresql
What Are Best Practices For Multi-Language Database Design