I want to execute the following query if the database and user does not exist.
CREATE DATABASE database;
CREATE ROLE user WITH LOGIN PASSWORD 'password';
REVOKE CONNECT ON DATABASE db1 FROM PUBLIC;
GRANT CONNECT ON DATABASE db1dev TO userbdev;
PostgreSQL does not allow IF NOT EXSIST in CREATE DATABASE like MySQL does, so I would like to do the branching on the application side, is there a better way?
Hi! There are three methods to do it. Of course you can put in and the user’s existence condition, and I’d avoid this kind of op between transaction statements.
You can use PostgreSQL’s built-in functions to check for the existence of a database and user. Here’s an example of how to check for the existence of a database and user and perform a branch process using a Bash script:
Enter your database and user information
Check if the database exists
if psql -lqt | cut -d | -f 1 | grep -qw “$DATABASE”; then
echo “The database $DATABASE exists.”
echo “The database $DATABASE does not exist.”
Check if the user exists
if psql postgres -tAc “SELECT 1 FROM pg_roles WHERE rolname=‘$USER’” | grep -q 1; then
echo “The user $USER exists.”
echo “The user $USER does not exist.”
If both the database and user exist, perform your branch process here
echo “Both the database and user exist. Performing branch process.”
In this script, the
psql command is used to check for the existence of the database and user. The
-lqt option is used to list all databases in a quiet, tabular format, and the
grep commands are used to filter the output and check if the specified database exists.
To check for the existence of the user, the
psql command is used again, this time to query the
pg_roles table for a user with the specified name. The
-tAc options are used to format the output, and the
grep command is used to check if the query returned a result.
If both the database and user exist, the script will continue with your branch process. If either the database or user does not exist, the script will exit with an error code of 1.
Click here more information: https://crecentech.com/
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.