PostgreSQL psql Commnads - ghdrako/doc

Alternativ to pgsql: https://github.com/dbcli/pgcli

Using psql variables

\set foo 1
SELECT :foo;

The meta-commands you run in psql perform SQL queries behind the scenes. You can see them by setting this variable

\set  ECHO_HIDDEN  on

Once set, run any meta-command to see the SQL query printed out.

Meta-Commands

Description	Meta-Command
`\l`	List databases. This is the letter “l” and not the number 1.
`\d`	Describe all objects (tables,views, sequences ...)
`\dn+`	Describe schemas (namespaces)
`\dt`	Describe all tables
`\dv`	Describe views
`\d+`	Describe definition when used with a view
`\x`	Toggle expanded display
`\! clear`	Clear the screen
`\! <shell command>`	Execute shell command
`\o <file-name>`	Ustawienie zbioru do ktorego zapisze sie wynika nastepnych kommend a zdo wpisanie `\o`
`\set`	Set a psql variable e.g. `\set foo 1` and use in statement `SELECT :foo;`
`\e`	Edit queries in EDITOR
`\df`	Describe functions
`\dx`	Describe extensions
`\di *`	Describe indexes that match pattern, e.g. `\di index_users*`
`\dRs+`	Describe subscriptions
`\! clear`	Clear the screen
`\set`	Set a psql variable
`\g`	Execute the last command again
`\s`	Display command history
`\s filename`	Save the command history to a file
`\i filename`	Execute psql commands from a file - sql
`\?`	Know all available psql commands
`\h`	Get help
`\e`	Edit command in your own editor
`\a`	Switch from aligned to non-aligned column output
`\H`	Switch the output to HTML format
`watch <numsec>`	repeats the previous command at the specified interval
`\q`	Exit psql shell

watch

tom=# select count(*);
  count 
--------
    726
(726 rows)

tom=# \watch 5
Mon Nov 16 13:50:36 2020 (every 2s)

  count 
--------
    726
(726 rows)

Mon Nov 16 13:50:38 2020 (every 2s)

  count 
--------
    726
(726 rows)

Mon Nov 16 13:50:40 2020 (every 2s)

  count 
--------
    726
(726 rows)

ECHO_HIDDEN option

turn on the ECHO_HIDDEN option in psql to see the queries used to retrieve information from system catalogs when you use meta-commands (\d, \dt, \dn, etc).

=> \set ECHO_HIDDEN on
=> \dn
************ Query**************
...
********************************
List of schemas
....

Databases

\l           -- list database
\l+          -- List databases with size, tablespace, and description

\l+ <db_name>  -- show info about db ex size
select pg_database_size('<db_name>');
SELECT datname FROM pg_database;

\c <db_name> -- connect to db or if not exist create 

\conninfo  --  display information about current connection


CREATE DATABASE my_database WITH OWNER my_user;
CREATE DATABASE test_db WITH OWNER = test_usr ENCODING = 'UTF8' TABLESPACE = test_tbs LC_COLLATE = 'pl_PL.utf8' LC_CTYPE = 'pl_PL.utf8' CONNECTION LIMIT = -1;

$ createdb <db_name> -- Note that outside the PostgreSQL console you can also create a database with createdb

ALTER USER my_user CREATEDB; -- give a user the ability to create new databases

ALTER DATABASE name RENAME TO new_name;     # change db name - nie moze byc polaczen do bazy
ALTER DATABASE moja_baza OWNER TO postgres; # change db owner


select pg_size_pretty(pg_database_size('dvdrental'));  # specified db size
SELECT pg_database_size('dvdrental');

select current_database();

\x Expand/narrow table lists

Use \x (X for eXpanded listing) to control whether table listings use a wide or narrow format.

Command	Effect
`\x off`	Show table listings in wide format
`\x on`	Show table listings in narrow format
`\x`	Reverse the previous state
`\x auto`	Use terminal to determine format
Example

/* List all databases. */
postgres=# \l

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

/* Turn on narrow listings. */
postgres=# \x on
postgres=# \l

-[ RECORD 1 ]-----+----------------------
Name              | postgres
Owner             | postgres
Encoding          | UTF8
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
Access privileges | 
...

Tablespace

----------------
CREATE TABLESPACE test_tbs OWNER test_usr LOCATION '/pgqdata/cdl/PostgreSQL/9.2/data';
ALTER DATABASE test_baza SET TABLESPACE test_tbs;

Schemas

CREATE SCHEMA test_schema;
\dn
\dn+
select current_schema();

List objecs in db:

\dn          - list schemas

\du          - list users

\d           - list object in curent database
\dt          - list tables (technically, relations) in current db in public schema
\dt+         - get more information on tables
\dt <schema> - list tables in schema
\dt <schema>.*
\dt *.*      - List tables from all schemas
\dt <scema>.<table> - view the schema of a table
\dt+ <scema>.<table> - view the schema of a table with mode details

\df <schema> - list stored procedures
\df+

\dv <schema> - list views

\di          - list indexes

select * from pg_indexes where tablename not like 'pg%';
select tablename,indexname,tablespace,indexdef  from pg_indexes where tablename = 'your_table_name';
SELECT * FROM pg_indexes WHERE  schemaname = 'public';

\dx+ pg_trgm - show info about extension

Auxilary commands

psql -V           - Check psql Client version

Select version(); - Check Postgres server version

\g           - seeing previous command

\timing

\e           - execute last command 

\x           - Show query output in the pretty-format - extended mode

\h CREATE DATABASE - show help for command

\?             - list postgres commands

\q           - quit console

Timing SQL operations

Use \t to show timing for all SQL operations performed.

Command	Effect
`\timing off`	Disable timing of SQL operations
`\timing on`	Show timing after all SQL operations
`\timing`	Toggle (reverse) the setting

Example

tom=# insert into todo values ('Retry on Android before app submission,'8.x and earlier');
INSERT 0 1
tom=# \timing on
Timing is on.
tom=# insert into todo values ('Correct footer bug','Mobile version only');
INSERT 0 1
Time: 1.067 ms
tom=# insert into todo values ('Retry on Android before app submission', '8.x and earlier');
INSERT 0 1
Time: 23.312 ms
tom=# \timing
Timing is off.

Watch

The \watch command repeats the previous command at the specified interval. To use it, enter the SQL command you want repeated, then use \watch followed by the number of seconds you want for the interval between repeats, for rexample, \watch 1 to repeat it every second.

tom=# select count(*);
  count 
--------
    726
(726 rows)

tom=# \watch 5
Mon Nov 16 13:50:36 2020 (every 2s)

  count 
--------
    726
(726 rows)

Mon Nov 16 13:50:38 2020 (every 2s)

  count 
--------
    726
(726 rows)

Mon Nov 16 13:50:40 2020 (every 2s)

  count 
--------
    726
(726 rows)

Output query to CSV

\pset format csv - format outpu as csv
select ...

Export select to csv file

\o output.csv
\pset format csv
SELECT ....

psql -c "SELECT ..." --csv > output.csv

Tables

\d <table>   - describe table
\d+ <table>  - Get detailed information on a table

\copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV  - Copy table data to CSV file

SELECT * FROM pg_indexes WHERE tablename='<table>' AND schemaname='<schema>'; - Check indexes for a table 

ANALYZE [<table>] - Collects statistics about the contents of tables in pg_statistic system catalog

SELECT reltuples AS card FROM pg_class WHERE relname = '<table_name>'; - Approximate Table Row count / Table Cardinality


SELECT 
   table_name, 
   column_name, 
   data_type 
FROM 
   information_schema.columns
WHERE 
   table_schema = 'schema_name' 
   AND
   table_name = 'city';

Service - instancja

service postgresql <stop|start|restart>

show all - Display configuration parameters
select * from pg_settings; - Display configuration parameters using sql

SELECT current_setting('max_connections'); - Show current setting from “max_connections”

show config_file; - Show Postgres config file location

Functions

\df+            - List functions
\ef myfunction  - Edit function

$$
CREATE FUNCTION add(integer, integer) RETURNS integer
 AS 'select $1 + $2;'
 LANGUAGE SQL
 IMMUTABLE
 RETURNS NULL ON NULL INPUT;
$$ 	

select add(5,9);

Users - in Postgres called Roles

\connect opensim Opensim_Tester localhost;

\password username  - Set/Reset postgres user password

\du                     - Show all users
select * from pg_user;

psql -U testuser mytest - Login & enter postgres terminal

CREATE ROLE my_user WITH LOGIN PASSWORD 'my_password';

DROP ROLE [ IF EXISTS ] <user>;
# if some object block drop 
drop owned by <user> cascade;
# or
reassign owned by <user> to <other user>;

ALTER USER user_name WITH PASSWORD 'new_password';

# list users
SELECT usename AS role_name,
 CASE
  WHEN usesuper AND usecreatedb THEN
    CAST('superuser, create database' AS pg_catalog.text)
  WHEN usesuper THEN
    CAST('superuser' AS pg_catalog.text)
  WHEN usecreatedb THEN
    CAST('create database' AS pg_catalog.text)
  ELSE
    CAST('' AS pg_catalog.text)
 END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

PK Constraints in schema

 SELECT conrelid::regclass AS table_name,
       conname AS primary_key, 
       pg_get_constraintdef(oid) 
FROM   pg_constraint 
WHERE  contype = 'p' -- p - primary key u - unique f -foreign key 
AND    connamespace = '<schema_name>'::regnamespace   
ORDER  BY conrelid::regclass::text, contype DESC;


ALTER TABLE ONLY TEST
    ADD CONSTRAINT con_id PRIMARY KEY (con_id);
alter table TEST drop constraint IF exists con_id;

gset

https://stackoverflow.com/questions/31454108/defining-multi-line-strings-in-psql/31457183#31457183 \gset is used to create a dollar quoted literały to display an example metrics entry as JSON, formatted vertically.

SELECT    
$$  '{     
"ride_details": {     
"bags_in_trunk": 1,     
"music_on": true,     
"water_offered": true     
}    
}'$$ AS json_string \gset

The dollar quoted literal here in SQL is similar to a Heredoc or multiline string in Ruby.

To store multiline string \gset in conjunction with a dollar-quoted literal:

SELECT
$$123
456
789$$ AS str \gset

gexec

Use quote_ident(), or format() with %I, instead. These apply correct escaping as necessary.

postgres=# SELECT format('GRANT SELECT ON TABLE %I TO someuser;', tablename) FROM pg_tables WHERE tablename~'^pgbench';
format 
-----------------------------------------------------
GRANT SELECT ON TABLE pgbench_accounts TO someuser;
GRANT SELECT ON TABLE pgbench_branches TO someuser;

postgres=# gexec
GRANT
GRANT

obraz

help

# \help create table
Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] 
TABLE [ IF NOT EXISTS ] table_name ( [
   { column_name data_type [ COMPRESSION compression_method ]  
[ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
URL: https://www.PostgreSQL.org/docs/14/sql-createtable.html

"Expanded" output

What is expanded?

\x [on|off|auto]       toggle expanded output (currently auto)
equivalent

\pset expanded

psql wraps lines by default. To prevent wrapped, toggle this command.

\pset format wrapped

Save result

\o <file-name>

// example
\o query_results
...run the psql commands...
\o - stop the process and output the results to the terminal again

\o wynik.txt
\dt
\dx
\d test
\du
\d+ users
\o

Note: To stop saving results to the file, you need to run the \o command again without the file name.

Run commands from a file - \i

psql_commands.sql:
\l
\dt
\du
select 'Hello'

\i <file-name>

// example
\i psql_commands.sql

Format output

Table column borders

\pset linestyle unicode
┌────┬───────┬─────┐
│ id │ name  │ age │
├────┼───────┼─────┤
│  1 │ Alice │  30 │
│  2 │ Bob   │  25 │
└────┴───────┴─────┘

Show query run times

-- Always show query time
\timing

Show null value as utf char

-- Set Null char output to differentiate it from empty string
\pset null '☘️'

psql history

-- Creates a history file for each database in your config directory CHECK IF THIS IS RIGHT
\set HISTFILE ~/.config/psql/psql_history-:DBNAME

-- Number of commands to save in history
\set HISTSIZE 2000

expand display

\x on
Expanded display is on.
\du
List of roles
-[ RECORD 1 ]----------------------------------------------------------
Role name  | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS
Member of  | {}
-[ RECORD 2 ]----------------------------------------------------------
Role name  | test
Attributes | Create role, Create DB
Member of  | {}

\x auto
Expanded display is used automatically.
-- Now if there's screen space for the horizontal output, it'll be used. Otherwise, vertical stacking will be used.

PostgreSQL psql Commnads - ghdrako/doc_snipets GitHub Wiki

Meta-Commands

watch

ECHO_HIDDEN option

Databases

\x Expand/narrow table lists

Tablespace

Schemas

List objecs in db:

Auxilary commands

Timing SQL operations

Watch

Output query to CSV

Tables

Service - instancja

Functions

Users - in Postgres called Roles

gset

gexec

help

"Expanded" output

Save result

Run commands from a file - \i

Format output

⚠️ GitHub.com Fallback ⚠️

PostgreSQL psql Commnads - ghdrako/doc_snipets GitHub Wiki

Meta-Commands

watch

ECHO_HIDDEN option

Databases

\x Expand/narrow table lists

Tablespace

Schemas

List objecs in db:

Auxilary commands

Timing SQL operations

Watch

Output query to CSV

Tables

Service - instancja

Functions

Users - in Postgres called Roles

gset

gexec

help

"Expanded" output

Save result

Run commands from a file - \i

Format output

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️