psql – Filtering Databases by Owner

psql

I know I can list my postgres databases with the following command:

# list only the databases
psql -U openerp -q -t -c "select datname from pg_database;" template1

# show a full listing of databases
psql template1 -U openerp -l

How can I filter the databases by owner 'openerp'? datdba field is just a number… how can I map it?

Best Answer

For things like this, you'll want to query pg_database directly. The owner field is a reference to pg_catalog.pg_authid.oid, but this is a superuser-only accessible table. There's a view, pg_catalog.pg_roles that exposes the id as the oid attribute, so you can:

select 
  datname,
  rolname AS dbowner
from pg_database d 
     inner join pg_roles r on (d.datdba = r.oid);

Related Solutions

Postgresql – psql won’t list databases

According to the pg_hba.conf snippet, a password is required if you connect from ::1, which is the IPv6 address for localhost.

It may be that the box on which you have the problem has the resolver configured so that the name "localhost" refers to both 127.0.0.1 (IPv4) and ::1 (IPv6), so that the command psql -h localhost... may connect to one or the other address.

I'd suggest to check the /etc/hosts file for different localhost entries, or bypass the resolver by connecting to 127.0.0.1, or update the pg_hba.conf with "trust" for IPv6 local connections.

PostgreSQL – How to Conditionally Stop a psql Script Based on Variable Value

There is an option in psql which stops executing commands on error, this is ON_ERROR_STOP. If we could raise an error somehow, this would do what we want.

The problem is that we have to test the variable and produce an error somehow. Since one can't use control structures in psql (because there are none)*, my only idea was to use SQL for testing. Well, producing an error conditionally is something which pl/pgsql is quite good at, so I wrote a function which would generate an error. I can now call this function from a simple CASE structure. A simple example:

-- let's assume for clarity that there is no function with this name in the database
CREATE OR REPLACE FUNCTION error_generator()
RETURNS boolean AS
$body$
BEGIN
    RAISE 'Meaningful error message here';
    RETURN FALSE; -- just for aesthetical purposes
END;
$body$
LANGUAGE plpgsql;

\set ON_ERROR_STOP on

BEGIN;

-- test for the variable value
-- notice that if :var is not set, it fails as well (with a syntax error)
SELECT CASE WHEN 1 = :var THEN error_generator() ELSE TRUE END;

INSERT INTO test_table (integer_value, text_value)
VALUES (:var, 'something');

COMMIT;

*: You can use any shell commands after \! and conditionals of the shell, but since \! opens a new shell, executing anything there does not have any effect for the current psql script.

Best Answer

Related Solutions

Postgresql – psql won’t list databases

PostgreSQL – How to Conditionally Stop a psql Script Based on Variable Value

Related Question