Pingu
Computer MySQL PostgreSQL Books Publications
Spielereien Kanu Business TopoDB POI Klettersteigen History TransPool Thermal Baden Brokenstuben Goldwaschen
Blog Contact
Shinguz
Google
/ch/open

PostgreSQL for MariaDB and MySQL Admins - Navigation

CLI mariadb/mysql -> psql

Connect

Connect to a database cluster (default database: postgres, equivalent: # mariadb):

` psql
psql (15.2 (Ubuntu 15.2-1.pgdg18.04+1))
Type "help" for help.

postgres=#

Connect to a specific database in a cluster (test, equivalent: # mariadb test):

# psql test
psql (15.2 (Ubuntu 15.2-1.pgdg18.04+1))
Type "help" for help.

test=#

Change the database (reconnect):

postgres=# \c test
You are now connected to database "test" as user "postgres".

Alternative: postgres=# \connect test

MariaDB/MySQL equivalent:

mariadb> exit
# mariadb test

List databases

List databases:

postgres-# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 test      | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |            | libc            |
(4 rows)

Alternative: postgres=# \list or # psql --list

Extended list of databases (+):

postgres-# \l+
                                                                                   List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |            | libc            |                       | 7453 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |            | libc            | =c/postgres          +| 7297 kB | pg_default | unmodifiable empty database
           |          |          |             |             |            |                 | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |            | libc            | =c/postgres          +| 7533 kB | pg_default | default template for new databases
           |          |          |             |             |            |                 | postgres=CTc/postgres |         |            |
 test      | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |            | libc            |                       | 616 MB  | pg_default |
(4 rows)

Alternative: postgres=# \list+

List databases with SQL commands:

SELECT datname AS "Name", encoding AS "Encoding", datlocprovider AS "Locale Provider", datcollate AS "Collate"
     , datctype AS "Ctype", daticulocale AS "ICU Locale", datcollversion AS "Collation Version"
  FROM pg_database
 WHERE datistemplate = false
;
   Name   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | Collation Version
----------+----------+-----------------+-------------+-------------+------------+-------------------
 postgres |        6 | c               | en_GB.UTF-8 | en_GB.UTF-8 |            | 2.35
 test     |        6 | c               | en_GB.UTF-8 | en_GB.UTF-8 |            | 2.35
(2 rows)

List schemas

test=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
 test_s | postgres
(2 rows)

test=# \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
 test_s | postgres          |                                        |
(2 rows)

With SQL Queries:

SELECT catalog_name, schema_name, schema_owner
  FROM information_schema.schemata;
 catalog_name |    schema_name     |   schema_owner
--------------+--------------------+-------------------
 test         | test_s             | postgres
 test         | information_schema | postgres
 test         | pg_catalog         | postgres
 test         | pg_toast           | postgres
 test         | public             | pg_database_owner
(5 rows)
psql -h localhost --username=pgadmin --list
psql -U pgadmin -d test
set search_path to schema1, public;
\d information_schema.dat*

Relations

Relations in PostgreSQL are table and sequences.

List relations:

test-# \d
                    List of relations
 Schema |           Name            |   Type   |  Owner
--------+---------------------------+----------+----------
 public | accounts                  | table    | postgres
 public | accounts_user_id_seq      | sequence | postgres
 public | queue_destinations        | table    | postgres
 public | queue_destinations_id_seq | sequence | postgres
 public | test                      | table    | postgres
 public | test_id_seq               | sequence | postgres
(6 rows)

Question: What is the difference between \d and \dt??? \dt *.

MariaDB/MySQL equivalent: SHOW TABLES.

List table:

test-# \d accounts
                                           Table "public.accounts"
   Column   |            Type             | Collation | Nullable |                  Default
------------+-----------------------------+-----------+----------+-------------------------------------------
 user_id    | integer                     |           | not null | nextval('accounts_user_id_seq'::regclass)
 username   | character varying(50)       |           | not null |
 password   | character varying(50)       |           | not null |
 email      | character varying(255)      |           | not null |
 created_on | timestamp without time zone |           | not null |
 last_login | timestamp without time zone |           |          |
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (user_id)
    "accounts_email_key" UNIQUE CONSTRAINT, btree (email)
    "accounts_username_key" UNIQUE CONSTRAINT, btree (username)

MariaDB/MySQL equivalent: SHOW CREATE TABLE or desc.

Sources