Posts tagged "psql"

Get granted roles with psql

With psql, there is an easy way to check which roles are granted to a role: \du

henk=> CREATE ROLE accounting;
CREATE ROLE
henk=> CREATE ROLE alex;
CREATE ROLE
henk=> GRANT accounting TO alex;
GRANT ROLE
henk=> \du accounting
             List of roles
 Role name  |  Attributes  | Member of
------------+--------------+-----------
 accounting | Cannot login | {}

henk=> \du alex
              List of roles
 Role name |  Attributes  |  Member of
-----------+--------------+--------------
 alex      |              | {accounting}

PostgreSQL: Include files by relative path

You can include files in psql using the relative path like so:

\ir <filename>

Or, as a longer alternative:

\include_relative <filename>

Note:

When using \ir from a file that was just included using \ir, the path will be interpreted relative to the nearest file in the include tree.


PostgreSQL CSV Import: missing data for column "..."

Problem

The following .sql script will error out prematurely:

create table if not exists foo (
	bar text not null,
	baz text not null
);

copy foo (bar, baz) from stdin (format csv, delimiter ';', header true);
bar;baz
Lorem;ipsum
dolor;sit
amet,;consectetur
\.

Here's the accompanying psql output:

CREATE TABLE
psql:<stdin>:11: ERROR:  missing data for column "baz"
CONTEXT:  COPY foo, line 5: "\."

Solution

Adding a newline after the \. termination sequence fixes the error.


Using dynamic variables in psql

When developing in PostgreSQL, you may run into the fringe situation of being unable to use PL/pgSQL and instead having to fallback to plain psql (as was my case when writing tests with pgTap, since PL/pgSQL insists on using PERFORM over SELECT for void-returning functions).

Writing these tests in plain SQL quickly results in bloated code, since you have to reuse certain IDs over and over again, even when using CTEs.
While psql supports variables in the form of \set {name} {value}, these can not be dynamically set (i.e. using the result of a query).

However, it is possible to abuse runtime parameters (SET {name} TO {value}) for this purpose by making use of PL/pgSQLs EXECUTE, as shown in the following example:

DO $$
BEGIN
	EXECUTE format('SET %I TO %L', 'var.my_test_variable', (SELECT 1));
END $$;

Then, once you have returned to your plain SQL block, you may use SELECT current_setting('var.my_test_variable') to retrieve the value.

If used often, you could even move the EXECUTE block into its own function, receiving both name and value of the runtime parameter, and thus further removing unnecessary boilerplate code.


Disable pager for psql

PostgreSQL's CLI psql offers a myriad of helpful features.

For example, psql detects whenever a large result-set is returned and uses a pager to display the content.

While this is great for viewing your data, it is really inconvenient for automating tasks, as the pager needs user input to be terminated.
So, how can we circumvent / deactivate the pager?

# original, shows the pager
psql -h localhost -U postgres -c "SELECT * FROM pg_class"

# just pipe the output to `cat`
psql -h localhost -U postgres -c "SELECT * FROM pg_class" | cat

# if you are not interested in the output, you can also write to /dev/null
psql -h localhost -U postgres -c "SELECT * FROM pg_class" > /dev/null

# alternatively, you can use the environment variable `PAGER` to choose which pager should be used
PAGER=cat psql -h localhost -U postgres -c "SELECT * FROM pg_class"

# best method: completely turn off the pager
psql -h localhost -U postgres -P pager=off -c "SELECT * FROM pg_class"

Additionally, if you want to disable the pager while in interactive mode, just type \pset pager off.