Hello Everyone,
This article will help you learning basic commands in postgreSQL.
Psql is an interactive terminal to work with the PostgreSQL database. It is used to retrieve data from the PostgreSQL database server faster and more effectively.
The below commands are very helpful for beginner’s :
How to connect PostgreSQL database using psql :
Note : Installation of PostgreSQL done by root user. During the installation of PostgreSQL software a user called postgres will be created.
|
1 2 3 4 5 6 7 8 9 10 |
[root@ip-172-31-4-35 ~]# su - postgres Last login: Wed Feb 16 01:20:08 UTC 2022 on pts/2 [postgres@ip-172-31-4-35 ~]$ psql psql.bin (10.12) Type "help" for help. Cannot read termcap database; using dumb terminal settings. postgres=# |
How to check databases available in cluster
Command :select * from pg_database;
|
1 2 3 4 5 6 7 |
postgres=# select * from pg_database; datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl -----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+------------------------------------- postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12989 | 549 | 1 | 1663 | template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 12989 | 549 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres} template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 12989 | 549 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres} (3 rows) |
by default PostgreSQL will provide three database
1.postgres
2.template1
3.template0
template 1 is the one used by default. You can alter / add / remove objects there to affect every newly created DB. CREATE DATABASE basically makes a copy of it on the file level (very fast) to create a new instance.
template0 starts out being the same and should never be changed – to provide a virgin template with original settings.
Check current database:
Command : select current_database();
|
1 2 3 4 5 |
postgres=# select current_database(); current_database ------------------ postgres (1 row) |
Check current user :
Command : select current_user;
|
1 2 3 4 5 |
postgres=# select current_user; current_user -------------- postgres (1 row) |
Check of list of roles:
Command : \du
|
1 2 3 4 5 |
postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
List of databases with Size,Tablespace and Description :
Command : \l
|
1 2 3 4 5 6 7 8 9 10 |
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 (3 rows) |
Command : \l+
|
1 2 3 4 5 6 7 8 9 10 |
postgres=# \l+ List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+-------------------------------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7623 kB | pg_default | default administrative connection database template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7473 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | | | template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7473 kB | pg_default | default template for new databases | | | | | postgres=CTc/postgres | | | (3 rows) |
How to check version of PostgreSQL :
|
1 2 3 4 5 |
postgres=# SELECT version(); version ---------------------------------------------------------------------------------------------------------- PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit (1 row) |
Tablespaces in PostgreSQL:
Command : select * from pg_tablespace;
|
1 2 3 4 5 6 |
postgres=# select * from pg_tablespace; spcname | spcowner | spcacl | spcoptions ------------+----------+--------+------------ pg_default | 10 | | pg_global | 10 | | (2 rows) |
List of tables:
Commands: \d or \d
If tables are not available in PostgreSQL we will receive below output
|
1 2 3 4 |
postgres=# \dt Did not find any relations. postgres=# \d Did not find any relations. |
Create a tables and use commands :
|
1 2 |
postgres=# create table test(a int); CREATE TABLE |
Note : If given command executes successfully postgres will give same command as output.
If tables are available :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
postgres=# \dt List of relations Schema | Name | Type | Owner --------+-------+-------+---------- public | t1 | table | postgres public | test | table | postgres public | test3 | table | postgres (3 rows) postgres=# \d List of relations Schema | Name | Type | Owner --------+-------+-------+---------- public | t1 | table | postgres public | test | table | postgres public | test3 | table | postgres (3 rows) |
List of Schemas :
|
1 2 3 4 5 6 |
postgres=# \dn List of schemas Name | Owner --------+---------- public | postgres (3 rows) |
List of functions :
|
1 2 3 4 5 |
postgres=# \df List of functions Schema | Name | Result data type | Argument data types | Type --------+------+------------------+---------------------+------ (0 rows) |
Display command history :
Command : \s
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
postgres=# \s select * from pg_database; select * from pg_database; select * from pg_database; select * from pg_database; select current_database(); select current_user; \l+ \du \l SELECT version(); select * from pg_tablespace; \ft \dt \d \dn \df \s |
Help command:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
postgres=# \h Available help: ABORT ALTER SERVER CREATE CAST CREATE TABLE DROP EXTENSION DROP TEXT SEARCH TEMPLATE REVOKE ALTER AGGREGATE ALTER STATISTICS CREATE COLLATION CREATE TABLE AS DROP FOREIGN DATA WRAPPER DROP TRANSFORM ROLLBACK ALTER COLLATION ALTER SUBSCRIPTION CREATE CONVERSION CREATE TABLESPACE DROP FOREIGN TABLE DROP TRIGGER ROLLBACK PREPARED ALTER CONVERSION ALTER SYSTEM CREATE DATABASE CREATE TEXT SEARCH CONFIGURATION DROP FUNCTION DROP TYPE ROLLBACK TO SAVEPOINT ALTER DATABASE ALTER TABLE CREATE DOMAIN CREATE TEXT SEARCH DICTIONARY DROP GROUP DROP USER SAVEPOINT ALTER DEFAULT PRIVILEGES ALTER TABLESPACE CREATE EVENT TRIGGER CREATE TEXT SEARCH PARSER DROP INDEX DROP USER MAPPING SECURITY LABEL ALTER DOMAIN ALTER TEXT SEARCH CONFIGURATION CREATE EXTENSION CREATE TEXT SEARCH TEMPLATE DROP LANGUAGE DROP VIEW SELECT ALTER EVENT TRIGGER ALTER TEXT SEARCH DICTIONARY CREATE FOREIGN DATA WRAPPER CREATE TRANSFORM DROP MATERIALIZED VIEW END SELECT INTO ALTER EXTENSION ALTER TEXT SEARCH PARSER CREATE FOREIGN TABLE CREATE TRIGGER DROP OPERATOR EXECUTE SET ALTER FOREIGN DATA WRAPPER ALTER TEXT SEARCH TEMPLATE CREATE FUNCTION CREATE TYPE DROP OPERATOR CLASS EXPLAIN SET CONSTRAINTS ALTER FOREIGN TABLE ALTER TRIGGER CREATE GROUP CREATE USER DROP OPERATOR FAMILY FETCH SET ROLE ALTER FUNCTION ALTER TYPE CREATE INDEX CREATE USER MAPPING DROP OWNED GRANT SET SESSION AUTHORIZATION ALTER GROUP ALTER USER CREATE LANGUAGE CREATE VIEW DROP POLICY IMPORT FOREIGN SCHEMA SET TRANSACTION ALTER INDEX ALTER USER MAPPING CREATE MATERIALIZED VIEW DEALLOCATE DROP PUBLICATION INSERT SHOW ALTER LANGUAGE ALTER VIEW CREATE OPERATOR DECLARE DROP ROLE LISTEN START TRANSACTION ALTER LARGE OBJECT ANALYZE CREATE OPERATOR CLASS DELETE DROP RULE LOAD TABLE ALTER MATERIALIZED VIEW BEGIN CREATE OPERATOR FAMILY DISCARD DROP SCHEMA LOCK TRUNCATE ALTER OPERATOR CHECKPOINT CREATE POLICY DO DROP SEQUENCE MOVE UNLISTEN ALTER OPERATOR CLASS CLOSE CREATE PUBLICATION DROP ACCESS METHOD DROP SERVER NOTIFY UPDATE ALTER OPERATOR FAMILY CLUSTER CREATE ROLE DROP AGGREGATE DROP STATISTICS PREPARE VACUUM ALTER POLICY COMMENT CREATE RULE DROP CAST DROP SUBSCRIPTION PREPARE TRANSACTION VALUES ALTER PUBLICATION COMMIT CREATE SCHEMA DROP COLLATION DROP TABLE REASSIGN OWNED WITH ALTER ROLE COMMIT PREPARED CREATE SEQUENCE DROP CONVERSION DROP TABLESPACE REFRESH MATERIALIZED VIEW ALTER RULE COPY CREATE SERVER DROP DATABASE DROP TEXT SEARCH CONFIGURATION REINDEX ALTER SCHEMA CREATE ACCESS METHOD CREATE STATISTICS DROP DOMAIN DROP TEXT SEARCH DICTIONARY RELEASE SAVEPOINT ALTER SEQUENCE CREATE AGGREGATE CREATE SUBSCRIPTION DROP EVENT TRIGGER DROP TEXT SEARCH PARSER RESET |
Exit form PostgreSQL :
|
1 2 |
postgres=# \q [postgres@ip-172-31-4-35 ~]$ |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
