Basic commands in postgress:

To start the session :

1: $ psql
2: $ create database mydb;
3: $ \c mydb -- it will open the database
4: $ \l -- it will list all the databases
5: $ \d -- it will list all the tables of current database
6: $ select oid,datname from pg_database;
7: $\x -- it will display the result row wise
8: \dp -- it will list privileges on a table

--- The oid2name utility is available as a contrib module and helps us

examine the databases, tables, and related file nodes.

select oid, datname from pg_database;

\dt pg_catalog.* -- to list all system catalogs

explain (analyze,buffers) select * from users order by userid limit 10;

This command will list all postgress process.

8: # ps –f U postgres

9: postgres=# \dx -- it will list all installed extensions

10: This command will list all available extensions:

SELECT name, comment FROM pg_available_extensions limit 5;

11: To check for buffer caches:

CREATE EXTENSION pg_buffercache;
SELECT DISTINCT reldatabase FROM pg_buffercache;
SELECT
c.relname,
count(*) AS buffers
FROM pg_class c
JOIN pg_buffercache b
ON b.relfilenode=c.relfilenode
INNER JOIN pg_database d
ON (b.reldatabase=d.oid AND d.datname=current_database())
GROUP BY c.relname
ORDER BY 2 DESC;

- This command will tell us about the oid of a table.

SELECT pg_relation_filepath('emp');

 It will tell in which wal file is writing:

SELECT pg_xlogfile_name(pg_current_xlog_location());

SELECT pg_total_relation_size('myt');

12: This command will tell you that in which file the WAL is writing now.

SELECT pg_xlogfile_name(pg_current_xlog_location());
Note: pg_xlog is the folder were WAL are found.

13: This command will tell you name of schema of emp and dept tables;
SELECT schemaname, tablename FROM pg_tables WHERE tablename IN('emp','dept');

14 : This command will display name of the current schema of current database
SELECT current_schema;

15 : This command will display name of current schemas saved in search_path schema;
CREATE SCHEMA mynewschema;
SELECT current_schemas(true);
SHOW search_path;
SET search_path="$user",public,mynewschema;
SHOW search_path;
16 To display all user and roles :
\du+
ALTER USER my_user WITH superuser; -- it will make my_user as superuser

\dp+ emp;
We can get the privileges output in an easy to understand format with the
query:
SELECT pu.usename , pc.tbl, pc.privilege_type
FROM pg_user pu JOIN (
SELECT oid::regclass tbl, (aclexplode(relacl)).grantee,
(aclexplode(relacl)).privilege_type FROM pg_class WHERE relname='emp'
) pc ON pc.grantee=pu.usesysid;

\copyright -- will show you the copyright info of postgres

\h --it will show you the help
\e -- edit the query buffer with editor
\p --show the contents of query buffer
\r --reset the query buffer
\w file -- write the query buffer to a file
\i aa -- will execute commands from `aa` file.
\d -- will display all tables, views and sequences
\d emp -- will display all columns of emp table
\da -- list aggregates
\db -- list tablespace
\dc -- list conversions
\ddp --list default privileges
\di -- list indexes
\dg --list indexes
\dL --list procedural languages
\dx --list extensions
\ds --list sequences
\dn -- list schemas
\dt -- list tables
\dt+ pg_catalog.pg_t* -- list all tables from pg_catalog schema
\d+ -- If we wanted details about a particular object, such as the pg_ts_config table,
we would use the \d+ command for example \d+ pg_ts_dict
\dT --list data types
\dv --list views
\dy -- list event triggers
\y --list databases
\df --list all functions
# vi history
# chown postgres history
# export PSQL_HISTORY=history -- it will save all commands of psql in history file.
# psql -f some_script_file -- it will run some script file

psql reads settings from a configuration file called psqlrc.

\set PROMPT1 '%n@%M:%>%x %/# ' -- will change the prompt to user defined style
\timing on -- will set the timing of command execution on
\set AUTOCOMMIT off. -- by default all transaction are autocommit, if you don’t want it
then use this command.
\set eav 'EXPLAIN ANALYZE VERBOSE' -- You can use the \set command to create useful
typing shortcuts
:eav select coun(*) from pg_tables;

\! ls -- \! Will allow linux command to be executed inside psql

\watch --it will display the result of sql commad repatedly
For example : SELECT datname, waiting, query
FROM pg_stat_activity
WHERE state = 'active' AND pid != pg_backend_pid(); \watch 10 -- \watch command is Used
to repeatedly run an SQL statement at fixed intervals so you can
watch the output.
\copy -- \copy command that lets you import data from and export data
to a text file.
For example : to import the commads are -:
\connect postgresql_book
\cd /postgresql_book/ch03
\copy staging.factfinder_import FROM DEC_10_SF1_QTH1_with_ann.csv CSV
\copy sometable FROM somefile.txt DELIMITER '|';
\copy sometable FROM somefile.txt NULL As '';

To Export commands are -:

\connect postgresql_book
\copy (SELECT * FROM staging.factfinder_import WHERE s01 ~ E'^[0-9]+' ) TO '/
test.tab' WITH DELIMITER E'\t' CSV HEADER

You can also copy from the result of a command like :

\connect postgresql_book
CREATE TABLE dir_list (filename text);
\copy dir_list FROM PROGRAM `ls`;
Table Templates:
CREATE TYPE basic_user AS (user_name varchar(50), pwd varchar(10));
CREATE TABLE super_users OF basic_user (CONSTRAINT pk_su PRIMARY KEY (user_name));
ALTER TYPE basic_user ADD ATTRIBUTE phone varchar(10) CASCADE;

Change the data type of a column:

alter table int1 alter column id type int;
How to add Foreign key to a table:
set search_path=census, public;
ALTER TABLE facts ADD CONSTRAINT fk_facts_1 FOREIGN KEY (fact_type_id)
REFERENCES lu_fact_types (fact_type_id)
ON UPDATE CASCADE ON DELETE RESTRICT;
CREATE INDEX fki_facts_1 ON facts (fact_type_id);

How to add Unique constraint to a table :

ALTER TABLE logs_2011 ADD CONSTRAINT uq UNIQUE (user_name,log_ts);

How to add check constraint :

ALTER TABLE logs ADD CONSTRAINT chk CHECK (user_name = lower(user_name));
Exclude constraint with GiST index:
Example 6-6. Prevent overlapping bookings for same room
CREATE TABLE schedules(id serial primary key, room smallint, time_slot tstzrange);
ALTER TABLE schedules ADD CONSTRAINT ex_schedules
EXCLUDE USING gist (room WITH =, time_slot WITH &&);

Partial Index:
CREATE TABLE subscribers (
id serial PRIMARY KEY,
name varchar(50) NOT NULL, type varchar(50),
is_active boolean);
CREATE UNIQUE INDEX uq ON subscribers USING btree(lower(name)) WHERE is_active;

Multicolumn Indexes:
CREATE INDEX idx ON subscribers USING btree (type, upper(name) varchar_pat
tern_ops);

Single Table Views

CREATE OR REPLACE VIEW census.vw_facts_2011 AS
SELECT fact_type_id, val, yr, tract_id FROM census.facts WHERE yr = 2011;
DELETE FROM census.vw_facts_2011 WHERE val = 0;
UPDATE census.vw_facts_2011 SET val = 1 WHERE val = 0 AND yr = 2012;

list of functions available to access large objects

SELECT n.nspname as "Schema", p.proname as "Name",

pg_catalog.pg_get_function_result(p.oid) as "Result
data type", pg_catalog.pg_get_function_arguments(p.oid)
as "Argument data types"
FROM pg_catalog.pg_proc p LEFT JOIN
pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname ~ '^(lo_.*)$'AND
pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;

To list all stored function of current public schema commad is :

SELECT p.proname
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p
ON p.pronamespace = n.oid
WHERE n.nspname = 'public';

To get the size of a table and its indexes the command is :

SELECT pg_relation_size('orders')
AS table_size,pg_relation_size('orders_pkey') index_size
FROM pg_tables WHERE tablename like 'orders';
To check the database size “
SELECT pg_size_pretty(pg_database_size('dellstore2'));

To show the search Paths\

Show search_path;
Select * from pg_available_extensions;

Tablespaces :
1: create tablespace tt location '/stage/pg_tablespace1';
2: \db --will display all tablespaces
3: create table new1(id int)tablespace tt;
4: alter table foo_new set tablespace tt;

Decide Character set of a database in Postgresql

CREATE DATABASE korean WITH ENCODING 'EUC_KR' LC_COLLATE='ko_KR.euckr'

LC_CTYPE='ko_KR.euckr' TEMPLATE=template0;

Show client_encoding;
It will give you name of current character set;
\l will also give
Prepare statments in postgresql
PREPARE some_insert(integer, integer) AS
INSERT INTO fib_cache (num, fib)
VALUES ($1, $2);

EXECUTE some_insert(fib_for, ret);

select pg_reload_conf();

There is an extension called pgfincore that implements a set of functions to manage PostgreSQL
data pages
in the operating system's file cache.
ALTER DATABASE foo_db CONNECTION LIMIT 0

ALTER USER foo CONNECTION LIMIT 0

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity

ALTER INDEX badly_named_index RENAME TO tablename_status_idx;

Steps for row level security

ALTER TABLE someschema.sometable3 ENABLE ROW LEVEL SECURITY;

GRANT SELECT ON someschema.sometable3 TO somerole;

In order to grant the ability to access some rows only, we create a policy specifying what is
allowed
and on which rows. For instance, this way we can enforce the condition that somerole is only
allowed
to select rows with positive values of col1:
CREATE POLICY example1 ON someschema.sometable3
FOR SELECT
TO somerole
USING (col1 > 0);

createuser --interactive alice

Watching the longest queries

Another thing of interest that you may want to look for is long-running queries. To get a list of
running
queries ordered by how long they have been executing, use the following: SELECT
current_timestamp
- query_start AS runtime, datname, usename, query FROM pg_stat_activity WHERE state =
'active' ORDER BY 1 DESC;

Which query is blocked =:

On PostgreSQL 9.6 or newer you can run the following query: SELECT
datname
, usename
, wait_event_type
, wait_event
, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

Who has blocked the query :

SELECT datname , usename , wait_event_type , wait_event , pg_blocking_pids(pid) AS

blocked_by , query FROM pg_stat_activity;
This query looks quite complex, and in fact this complexity is part of the reason for the
appearance
in version 9.6 of the
pg_blocking_pids() function.

To kill queries take long wait time than 3 seconds;

SET
statement_timeout TO '3 s';
select * from pg_stat_user_tables;

to convert bytea to uuid

insert into new SELECT CAST( substring(CAST (gps_guid AS text) from 3) as uuid),cc from gps;