PostgreSQL psql - ghdrako/doc_snipets GitHub Wiki

psql commands

https://www.postgresql.org/docs/current/app-psql.html

psql

Install

sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf repolist
sudo yum search postgresql13
sudo dnf install postgresql13 postgresql13-server

sudo yum module list | grep postgresql                                                                                                                             
postgresql           10 [d][e]       client, server [d]                       PostgreSQL server and client module                                                                                                                               
postgresql           13              client [i], server [d]                   PostgreSQL server and client module                                            

# w 10 jest [e] i nie da sie zainstalowac 13
sudo yum module install postgresql:13/server
sudo yum module list | grep postgresql                                                                                                                             
postgresql           10 [d]          client, server [d]                       PostgreSQL server and client module                                                                                                                               
postgresql           13 [e]          client [i], server [d]                   PostgreSQL server and client module                                            
# w 13 jest [e] i da sie zainstalowac 13
sudo yum module install postgresql:13/client

Configuration

PGPORT=5432
PGUSER=postgres
PGPASSWORD=postgres
PGDATABASE=postgres

Psql client can read a file named .psqlr automatically.

$ cat .psqlrc
\set QUIET ON
\set PROMPT1 '%[%033[1;32;40m%]%M:%>; %n@%/%[%033[0m%]% # '
\set PAGER OFF
\set HISTSIZE 2000
\set ECHO_HIDDEN ON
\set COMP_KEYWORD_CASE upper
\timing
\encoding unicode
\pset null 'NULL'
\pset border 2
\set QUIET OFF
\echo '\nCurrent Host Server Date Time : '`date` '\n'
\echo 'Administrative queries:\n'
\echo '\t\t\t:settings\t-- Server Settings'
\echo '\t\t\t:conninfo\t-- Server connections'
\echo '\t\t\t:activity\t-- Server activity'
\echo '\t\t\t:locks\t\t-- Lock info'
\echo '\t\t\t:waits\t\t-- Waiting queires'
\echo '\t\t\t:uptime\t\t-- Server uptime'
\echo '\t\t\t:menu\t\t-- Help Menu'
\echo '\t\t\t\\h\t\t-- Help with SQL commands'
\echo '\t\t\t\\?\t\t-- Help with psql commands\n'
\echo 'Development queries:\n'
\echo '\t\t\t:sp\t\t-- Current Search Path'
\echo '\t\t\t:clear\t\t-- Clear screen'
\echo '\t\t\t:ll\t\t-- List\n'
-- Administration queries
\set menu '\\i ~/.psqlrc'
\set settings 'select name, setting,unit,context from pg_settings;'
\set locks 'SELECT bl.pid AS blocked_pid, a.usename AS
blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_
user, a.query AS blocked_statement FROM pg_catalog.pg_locks
bl JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid JOIN
pg_catalog.pg_locks kl JOIN pg_catalog.pg_stat_activity ka
ON kl.pid = ka.pid ON bl.transactionid = kl.transactionid AND
bl.pid != kl.pid WHERE NOT bl.granted;'
\set conninfo 'select usename, count(*) from pg_stat_activity
group by usename;'
\set activity 'select datname, pid, usename, application_
name,client_addr, client_hostname, client_port, query, state
from pg_stat_activity;'
\set waits 'SELECT datname, usename, wait_event_type, wait_
event, pg_blocking_pids(pid) AS blocked_by, query FROM pg_stat_
activity WHERE wait_event IS NOT NULL;'
\set uptime 'select now() - pg_postmaster_start_time() AS uptime;'
-- Development queries:
\set sp 'SHOW search_path;'
\set clear '\\! clear;'
\set ll '\\! ls -lrt;'

$ cat ~/.psqlrc
\set PROMPT1 '%~%x%# ' 
\x auto 
\set ON_ERROR_STOP on 
\set ON_ERROR_ROLLBACK interactive 
\pset null '¤' 
\pset linestyle 'unicode' 
\pset unicode_border_linestyle single 
\pset unicode_column_linestyle single 
\pset unicode_header_linestyle double 
set intervalstyle to 'postgres_verbose';
\setenv LESS '-iMFXSx4R' 
\setenv EDITOR '/Applications/Emacs.app/Contents/MacOS/bin/emacsclient -nw'

we have three di?ferent settings to play with here:

\set [ name [ value [ ... ] ] ] - sets the psql variable name to value, or if more than one value is given, to the concatenation of all of them. If only one argument is given, the variable is set with an empty value. To unset a variable, use the \unset command.
\setenv name [ value ] - sets the environment variable name to value, or if the value is not supplied, unsets the environment variable.
\pset [ option [ value ] ] - command sets options affecting the output of query result tables. option indicates which option is to be set. The semantics ofvaluevary de-pending on the selected option. For some options, omittingvaluecauses theoptiontobetoggledorunset, as describedunder theparticularoption. If no such behavior is mentioned, then omittingvaluejust results in the current setting being displayed.

# Client version
psql --version
psql -V
locate bin/psql

# Server version
postgres --version
postgres -V
locate bin/postgres
sudo -u postgres psql
SELECT version();
SHOW server_version;

psql -d mydb -U myuser              - connect to postgresql on local host
psql -h myhost -d mydb -U myuser    - connect to postgresql on remote host
psql "sslmode=verify-ca sslrootcert=server-ca.pem \
      sslcert=client-cert.pem sslkey=client-key.pem \
      hostaddr=01.23.45.67 \
      user=postgres dbname=postgres"

Reporting

~ psql --tuples-only \
       --set n=1 \ 
       --set name=Alesi \ 
       --no-psqlrc \
       -P format=html \ 
       -d f1db \ 
       -f report.sql 
<table border="1"> 
 <tr valign="top"> 
 <td align="left">Alesi</td> 
 <td align="left">Canadian Grand Prix</td> 
 <td align="right">1995</td> 
 <td align="right">1</td> 
 </tr> 
</table>

$cat report.sql
select surname, races.name, races.year, results.position 
  from results 
  join drivers using(driverid) 
  join races using(raceid) 
 where drivers.surname = :'name' 
   and position between 1 and 3 
 order by position 
 limit :n;

The query in the report.sql??le uses the:'name'variable syntax. Using:name would be missing the quotes around the literal value injected, and:''allows one to remedy this even with values containing spaces. psqlalso supports :"variable"notation for double-quoting values, which is used for dynamic SQL when identi??ers are a parameter (column name or table names).

When running psql for reports, it might be good to have a specifc setup. In this example, you can see I’ve been using the --no-psqlrc switch to be sure we’re not loading my usual interactive setup

Connection string

~ psql -d dbname -h hostname -p port -U username

~ psql -d postgresql://dim@localhost:5432/f1db 
f1db# 
~ psql -d "user=dim host=localhost port=5432 dbname=f1db" 
f1db#

Execute scripy as single transaction

psql -1 -f myscript.sql
psql --single-transaction -f myscript.sql

or in script:

BEGIN
...
COMMIT

Execute script to exit in first error

The default mode for the psql script tool is to continue processing when it finds an error.

psql -f test.sql -v ON_ERROR_STOP=on

or in initial line of script:

\set ON_ERROR_STOP on

When you run psql, a startup file will be executed, sometimes called a profile file. You can place your psql commands in that startup file to customize your environment. Adding ON_ERROR_STOP to your profile will ensure that this setting is applied to all psql sessions:

$ $EDITOR ~/.psqlrc

\set ON_ERROR_STOP

You can forcibly override this and request psql to execute without a startup file using -X

ON_ERROR_STOP is one of some special variables that affects the way psql behaves. The full list is available at the following URL: https://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-VARIABLES.

https://stackoverflow.com/questions/4480381/how-can-i-stop-a-postgres-script-when-it-encounters-an-error

Using psql variables - user defined

\set tabname mytable
\set colname mycol
\set colval 'myval'

ALTER TABLE :tabname ADD COLUMN :colname text;
UPDATE :tabname SET :colname = :'colval';

psql -v tabname=mytab2 -f vartest.sql

\set beginning '2017-04-01'
\set months 3

select date, name, drivers.surname as winner 
 from races 
 left join results 
 on results.raceid = races.raceid 
 and results.position = 1 
 left join drivers using(driverid) 
where date >= date :'beginning' 
  and date < date :'beginning' 12 + :months * interval '1 month';

Password file .pass

password file contains the usual five fields that we require when connecting, as shown here:

host:port:dbname:user:password

The password file is located using an environment variable named PGPASSFILE. If PGPASSFILE is not set, a default filename and location must be searched for, as follows:

On *nix systems, look for ~/.pgpass.
On Windows systems, look for %APPDATA%\postgresql\pgpass.conf, where %APPDATA% is the application data subdirectory in the path (for me, that would be C:). On *nix systems, you must issue the following command: chmod 0600 ~/.pgpass. If you forget to do this, the PostgreSQL client will ignore the .pgpass file. While the psql tool will issue a clear warning, many other clients will just fail silently.

The password file can contain multiple lines. Each line is matched against the requested host:port:dbname:user combination until we find a line that matches. Each item can be a literal value or *, a wildcard that matches anything. There is no support for partial matching. With appropriate permissions, a user can potentially connect to any database.

The following are a few examples of wildcards:

myhost:5432:*:sriggs:moresecurepw
myhost:5432:perf:hannu:okpw
myhost:*:perf:gianni:sicurissimo

Using a connection service file

The connection service file allows you to give a single name to a set of connection parameters. This can be accessed centrally to avoid the need for individual users to know the host and port of the database, and it is more resistant to future change. You can set up a system-wide file as well as individual per-user files. The default file paths for these files are /etc/pg_service.conf and ~/.pg_service.conf respectively.

A system-wide connection file controls service names for all users from a single place, while a per-user file applies only to that particular user. Keep in mind that the per-user file overrides the system-wide file – if a service is defined in both files, then the definition in the per-user file will prevail.

Example

$ cat pg_service.conf 
[dbservice1]
host=postgres1
port=5432
dbname=postgres

You can then copy above file to either /etc/pg_service.conf or another agreed-upon central location. You can then set the PGSYSCONFDIR environment variable to that directory location. Alternatively, you can copy it to ~/.pg_service.conf. If you want to use a different name, indicate it using PGSERVICEFILE. Either way, you can then specify the name of the service in a connection string, such as in the following example:

psql "service=dbservice1=cookbook user=gciolli"

The service can also be set using an environment variable named PGSERVICE.

The pg_service.conf and .pgpass files can work together, or you can use just one of the two. The per-user connection service file is not shared, but in any case, it seems best to keep things separate and confine passwords to .pgpass.

psql meta-commands

A meta-command is a command for the psql client, which may (or may not) send SQL to the database server, depending on what it actually does, whereas an SQL command is always sent to the database server. An example of a meta-command is \q, which tells the client to disconnect. All lines that begin with \ (a backslash) as the first non-blank character are presumed to be me- ta-commands of some kind.

Informational metacommands, such as \d, \dn, and more
Formatting, for output, such as \x
Execution timing using the \timing command
Input/output and editing commands, such as \copy, \i, and \o
Automatic startup files, such as .psqlrc
Substitutable parameters (variables), such as \set and \unset
Access to the OS command line using \!
Crosstab views with \crosstabview
Conditional execution, such as \if, \elif, \else, and \endif

Show sql behind meta-command

~# \set ECHO_HIDDEN true 
~# \l+ 
********* QUERY ********** 
SELECT d.datname as "Name", 
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner", 
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", 
       d.datcollate as "Collate", 
       d.datctype as "Ctype", 
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges", 
       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') 
       THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) 
       ELSE 'No Access' 
       END as "Size", 
       t.spcname as "Tablespace", 
       pg_catalog.shobj_description(d.oid, 'pg_database') as "Description" 
  FROM pg_catalog.pg_database d 
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid 
 ORDER BY 1;
**************************
~# \set ECHO_HIDDEN false