CLI

native : psql

Doc

general

Reminder:

• every not-escaped command should end with a semicolon (;) !!
• on <code>{=html} ERROR: current transaction is aborted, commands ignored until end of transaction block , you should rollback

setup

All settings (otherwise stated), go to your personal settings file ~/.psqlrc (%APPDATA%\postgresql\psqlrc.conf in Windows). You can supply a custom .psqlrc file, supplying its path in PSQLRC environment variable. You can disable .psqlrc, using --no-psqlrc argument.

Connect

https://stackoverflow.com/questions/6523019/postgresql-scripting-psql-execution-with-password

Several options:

• shortest: psql postgres://<USER_NAME>:$PASSWORD@<HOST_NAME>:<PORT_NUMBER>/<DATABASE_NAME>
• short: psql -h <HOST_NAME> -p >PORT_NUMBER> -U <USER_NAME> -d <DATABASE_NAME>
• human: psql --host <HOST_NAME> --port <PORT_NUMBER> --user <USER_NAME> --dbname <DATABASE_NAME>

You can shorten using an environment variable

export CONNECTION_STRING="host=<HOST_NAME> port=<PORT_NUMBER> dbname=<DATABASE_NAME> user=<USERNAME> password=<PASSWORD>";
psql --dbname $CONNECTION_STRING 

Using environment variable

Before

export PGHOST=localhost
export PGPORT=5433
export PGUSER=john
export PGPASSWORD=password123
export PGDATABASE=dummy

After

unset PGHOST
unset PGPORT
unset PGUSER
unset PGPASSWORD
unset PGDATABASE

pgpass file

touch ~/.pgpass
echo localhost:5432:example:postgres:password123 >> ~/.pgpass

customize prompt

https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PROMPTING

variables

m : host > : port n : user /: database p: server process id x: transaction status

sample

With

\set PROMPT1 '%n@%m # '

You'll get.

john@database :

With

\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] \n%x%# '

You'll get in yellow on black.

john@schema :

Cancel query

On Ctrl-C

https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-CANCELING-REQUESTS

On unexpected disconnection

Does not work, sse this.

Misc

• list database and exit: -l (--list)
• SQL script debug (ask before executing each statement): -s (--single-step)

Execute queries

• execute SQL command from argument: -command <SQL_QUERY>
• execute SQL command from file: --file <FILE>
• log query output in file: --output
• display only error message: --quiet

Interactive command

List:

• quit: \q
• list databases: \d
• connect: \c <DATABASE_NAME> or CONNECT <DATABASE_NAME>
• help:
  • for non-SQL command \? will be your guide,
  • for SQL command \help
• get version SELECT version();
• change current user password \password
• show procedure/function source (read-only): \ef <PROCEDURE_NAME>

variable

Simple

psql postgresql://postgres@localhost:5432/pix --variable user_count=10  

select * from  generate_series(1,:user_count);
 generate_series 
-----------------
               1
(..)
               9
              10
(10 rows)

Using bash environment

foo=id  
echo $foo
psql postgresql://postgres@localhost:5432/pix --variable foo=$foo

CREATE TABLE test(id int);
INSERT INTO test VALUES (1);
SELECT * FROM test WHERE :foo=1;
 id 
----
  1
(1 row)

Silent

Should be easier? I think so Doc

To display only messages whose level is ERROR

PGOPTIONS='--client-min-messages=error' psql --quiet

Hello, world

List

• connect to database psql -U <ROLE_NAME> -d <DB_NAME> -h <HOST_NAME>
• check the connection \conninfo
• create a table (see below)
• insert some data (see below)
• query it SELECT * FROM playground;
• query it SELECT * FROM playground WHERE color='blue';

Sample table creation statement

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

Sample table data

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2014-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2010-08-16');

external: pgcli

Doc

Install

sudo pip install pgcli

Test

pgcli postgresql://user@password:port/database

Feature:

• autocomplete
• template (named queries)

Template:

• list: n (named queries) \n
• define: s (save) \ns TEMPLATE_NAME QUERY
• use: \n TEMPLATE_NAME PARAMS
• delete: d