CLI mariadb/mysql
-> psql
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:
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)
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 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
.