In PostgreSQL, I want to check for the existence of a database and user and perform a branch process

[rice1gou (namimori)]

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?

[Metalymph (lorenzo pirro)]

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.

[ethanklein]

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:

#!/bin/bash

Enter your database and user information

DATABASE=“your_database_name”
USER=“your_user_name”

Check if the database exists

if psql -lqt | cut -d | -f 1 | grep -qw “$DATABASE”; then
echo “The database $DATABASE exists.”
else
echo “The database $DATABASE does not exist.”
exit 1
fi

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.”
else
echo “The user $USER does not exist.”
exit 1
fi

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 cut and 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/

[stevediaz]

Hello @rice1gou
You can refer stackoverflow & msbi online training