This is an old revision of the document!
PostgreSQL
Common Commands
List OF DATABASES :
psql -l
\? Help
\d ~= DESC
\c <database> -- select database
\dt SHOW TABLES
\dT list datatypes
\df list functions
\di list indexes
\dv list views
\da [PATTERN] list aggregate functions
\db [PATTERN] list tablespaces (ADD "+" FOR more detail)
\dc [PATTERN] list conversions
\dC list casts
\dd [PATTERN] SHOW comment FOR object
\dD [PATTERN] list domains
\df [PATTERN] list functions (ADD "+" FOR more detail)
\dg [PATTERN] list groups
\dn [PATTERN] list schemas (ADD "+" FOR more detail)
\do [NAME] list operators
\dl list LARGE objects, same AS \lo_list
\dp [PATTERN] list TABLE, VIEW, AND SEQUENCE access privileges
\dT [PATTERN] list DATA types (ADD "+" FOR more detail)
\du [PATTERN] list users
\l list ALL DATABASES (ADD "+" FOR more detail)
\z [PATTERN] list TABLE, VIEW, AND SEQUENCE access privileges (same AS \dp)
Admin SQL
Create User
CREATE ROLE k2patel LOGIN PASSWORD 'test' VALID UNTIL 'infinity';
Create Table Space
CREATE TABLESPACE k2patel OWNER k2patel LOCATION '/var/lib/pgsql/k2patel';
Create Database
CREATE DATABASE k2patel WITH OWNER = k2patel ENCODING = 'UTF8' TABLESPACE = k2patel LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;
List Current Activity
SELECT * FROM pg_stat_activity;
Change Pasword
ALTER USER postgres WITH ENCRYPTED PASSWORD 'password';
Read Only User
This only allow user to access database but can not write to it.
CREATE ROLE carol;
ALTER ROLE carol WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN ENCRYPTED PASSWORD 'password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO carol;
SELECT inet_server_addr();
Following is the available procedures / variable.
{| style=“border-spacing:0;”
! <center>Name</center>
! <center>Return Type</center>
! <center>Description</center>
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#efefef;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#efefef;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#efefef;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |
|
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;” |
style=“background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;” |