====== PostgreSQL ======

==== Common Commands ====
<code sql>
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)
</code>

==== Admin SQL ====
== Create User ==
<code sql>
CREATE ROLE k2patel LOGIN PASSWORD 'test' VALID UNTIL 'infinity';
</code>

== Create Table Space ==
<code sql>
CREATE TABLESPACE k2patel OWNER k2patel LOCATION '/var/lib/pgsql/k2patel';
</code>

== Create Database ==
<code sql>
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;
</code>

== List Current Activity ==
<code sql>
select * from pg_stat_activity;
</code>

== Change Pasword ==
<code sql>
ALTER USER postgres WITH ENCRYPTED PASSWORD 'password';
</code>

== Read Only User ==
This only allow user to access database but can not write to it.\\
<note important>Before you grant make sure you selected database else it could not work</note>

<code sql>
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;
</code>

<note tip>If there is ERROR : pg_dump: The command was: LOCK SEQUENCE public.client_group IN ACCESS SHARE MODE See next Section </note>

<code sql>
\dp public.client_group; # check permission
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup;
</code>

<note tip>Even you can use above commands to create backup user for specific database.</note>

==== Getting Connection Information ====

<code sql>
select inet_server_addr();
</code>

Following is the available procedures / variable.

<code text>
Name				Description
current_catalog			name of current database (called "catalog" in the SQL standard)
current_database()		name of current database
current_schema[()]		name of current schema
current_schemas(boolean)	names of schemas in search path, optionally including implicit schemas
current_user			user name of current execution context
current_query()			text of the currently executing query, as submitted by the client (might contain more than one statement)
pg_backend_pid()		Process ID of the server process attached to the current session
pg_listening_channels()		channel names that the session is currently listening on
inet_client_addr()		address of the remote connection
inet_client_port()		port of the remote connection
inet_server_addr()		address of the local connection
inet_server_port()		port of the local connection
pg_my_temp_schema()		OID of session's temporary schema, or 0 if none
pg_is_other_temp_schema(oid)	boolean	is schema another session's temporary schema?
pg_postmaster_start_time()	server start time
pg_conf_load_time()		configuration load time
session_user			session user name
user				equivalent to current_user
version()			PostgreSQL version information
</code>