PostgreSQL¶
- PostgreSQL manuals
- psql
- Debian overview
Basics¶
psql - Command-Line Interface (CLI)¶
psql - start¶
root@ic-tpl:~$ su - postgres
postgres@ic-tpl:~$ psql
psql (9.4.3)
Type "help" for help.
postgres=#
psql - SQL help¶
# get help on SQL instructions
postgres=# \h
Available help:
ABORT ALTER TYPE CREATE SCHEMA
ALTER AGGREGATE ALTER USER CREATE SEQUENCE
[...]
postgres=# \h DROP TABLE
Command: DROP TABLE
Description: remove a table
Syntax:
DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
psql - Get help on the CLI (basckslash commands)¶
postgres=# \?
General
\copyright show PostgreSQL usage and distribution terms
\g [FILE] or ; execute query (and send results to file or |pipe)
\gset [PREFIX] execute query and store results in psql variables
\h [NAME] help on syntax of SQL commands, * for all commands
\q quit psql
\watch [SEC] execute query every SEC seconds
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
[...]
Users and roles¶
Databases¶
List databases¶
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+----------+----------+-------------+-------------+-----------------------
icinga2_ido | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
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
(4 rows)
# the information is held by global PG tables, and can be obtained through SQL queries
postgres=# SELECT datname, datcollate FROM pg_database ORDER BY datname;
datname | datcollate
-----------------+-------------
icinga2_ido | en_US.UTF-8
postgres | en_US.UTF-8
template0 | en_US.UTF-8
template1 | en_US.UTF-8
(4 rows)
Connect to a database¶
postgres=# \c icinga2_ido
You are now connected to database "icinga2_ido" as user "postgres".
List tables¶
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------------------------------+-------+-------------
public | icinga_acknowledgements | table | icinga2_ido
public | icinga_commands | table | icinga2_ido
public | icinga_commenthistory | table | icinga2_ido
public | icinga_comments | table | icinga2_ido
Describe table¶
icinga2_ido=# \d icinga_commands
Table "public.icinga_commands"
Column | Type | Modifiers
--------------+---------+----------------------------------------------------------------------
command_id | bigint | not null default nextval('icinga_commands_command_id_seq'::regclass)
instance_id | bigint | default 0
config_type | integer | default 0
object_id | bigint | default 0
command_line | text | default ''::text
Indexes:
"pk_command_id" PRIMARY KEY, btree (command_id)
"uq_commands" UNIQUE CONSTRAINT, btree (instance_id, object_id, config_type)
"command_object_idx" btree (object_id)
"commands_i_id_idx" btree (instance_id)