How can I use the “psql” command for listing all databases with a particular name?

 In the context of SQL, here is how you can easily achieve this:-

First, you would need to connect to the Postgresql server by using the Psql.

Once you have connected to the required Postgresql, then you can run the following SQL query to list all the databases that can start with “e-commerce” and exclude those that start with the “test”

Here is a Java program that would connect to a Postgresql database would implement the query for listing all the databases that would start with “e-commerce” and exclude those which is starting with the “test” and print the results:-

Import java.sql.Connection;
Import java.sql.DriverManager;
Import java.sql.ResultSet;
Import java.sql.Statement;
Import java.sql.SQLException;

Public class ListDatabases {
    Public static void main(String[] args) {
        // Database credentials
        String url = “jdbc:postgresql://your_host:5432/”;
        String user = “your_username”;
        String password = “your_password”;
        // SQL query to list the databases
        String query = “SELECT datname FROM pg_database WHERE datname LIKE ‘ecommerce_%’ AND datname NOT LIKE ‘test_%’;”;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Try {
            // Establish the connection
            Conn = DriverManager.getConnection(url, user, password);
            Stmt = conn.createStatement();
            // Execute the query
            Rs = stmt.executeQuery(query);
            // Print the results
            System.out.println(“Databases matching criteria:”);
            While (rs.next()) {
                System.out.println(rs.getString(“datname”));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Close the resources
            Try {
                If (rs != null) rs.close();
                If (stmt != null) stmt.close();
                If (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Here is a python script given which would help in connecting to a postgresql database which would implement the query for listing all the database which would implement the query for listing all the database which would start with “ecommerce” and excluded those which is starting with the “test” and print the results. This script would use the “psycopg2” library which is very popular postgresql adapter for the python programming language:-

Import psycopg2
From psycopg2 import sql
Def list_databases():
    # Database connection parameters
    Conn_params = {
        ‘host’: ‘your_host’,
        ‘port’: ‘5432’,
        ‘user’: ‘your_username’,
        ‘password’: ‘your_password’,
        ‘dbname’: ‘postgres’  # Connect to the default ‘postgres’ database to list other databases
    }
    # SQL query to list databases matching the criteria
    Query = “””
    SELECT datname
    FROM pg_database
    WHERE datname LIKE ‘ecommerce_%’
      AND datname NOT LIKE ‘test_%’;
    “””
    Try:
        # Establish connection to the PostgreSQL server
        Conn = psycopg2.connect(**conn_params)
        Cur = conn.cursor()
        # Execute the query
        Cur.execute(query)
        # Fetch and print the results
        Print(“Databases matching criteria:”)
        For row in cur.fetchall():
            Print(row[0])
    Except Exception as e:
        Print(f”An error occurred: {e}”)
    Finally:
        # Close the cursor and connection
        If cur is not None:
            Cur.close()
        If conn is not None:
            Conn.close()
If __name__ == “__main__”:
    List_databases()