PostgreSQL

Login to PSQL

export PGPASSWORD='p@55w0rd'

# [email protected]:~ #
psql -U postgres
#psql (10.15 (Ubuntu 10.15-0ubuntu0.18.04.1))
#Type "help" for help.

# [email protected]:~ #
su - postgres
# Last login: Tue Jan 01 12:00:00 MYT 2013 on pts/0
# -bash-4.2$

psql
# psql (9.6.8)
# Type "help" for help.
# 
# postgres=#

Login Error

# [email protected]:~ #
su - postgres psql
# Last login: Tue Jan 01 12:00:00 MYT 2013 on pts/0
# /bin/createuser: /bin/createuser: cannot execute binary file

Creating User

# [email protected]:~ #
su - postgres
# Last login: Tue Jan 01 12:00:00 MYT 2013 on pts/0
# -bash-4.2$
createuser root
# createuser: creation of new role failed: ERROR:  role "root" already exists
# ALTER USER root WITH SUPERUSER;

Creating DB

Until creating root database it's always show error could not change directory to "/root": Permission denied. To overcome this error we should need to create a root database. only for avoid error while login using root user.

# [email protected]:~ #
su - postgres
# Last login: Tue Jan 01 12:00:00 MYT 2013 on pts/0
# -bash-4.2$
createdb root

Similarly you can create a database as same as your frequently used OS User. In this case no need password to login. It will authenticated from OS. That's means you can login to your PostgreSQL user using OS Authentication.

Grant Privileges

# postgres=#
grant all privileges on database root to root;
# GRANT

# postgres=#
grant all privileges on database postgres to root;
# GRANT

Logout PSQL

# postgres=#
\q
# -bash-4.2$

Meta Data

-- show databases
SELECT datname FROM pg_database;

-- show tables
SELECT * FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog','information_schema');

-- describe tables
SELECT table_name, column_name, data_type 
FROM information_schema.columns WHERE table_name = 'my_table';

Array Query

SELECT dependencies||'"Running Fiscal Year"'
FROM scope WHERE name = 'Fiscal Year Config';

JSON Query

{
    "type": "Reseller",
    "commission": {
        "type": "Percentage",
        "value": "25"
    }
}

UPDATE seller
SET seller_properties = jsonb_set(seller_properties, '{type}', '"Retailer"')
WHERE id = 1000;
--
UPDATE seller
SET seller_properties = jsonb_set(seller_properties, '{commission, type}', '"Bonus"')
WHERE id = 1000 AND seller_properties ->> 'type' = 'Reseller';

JSON SELECT id, name FROM json_to_record ( '{"id":1,"name":"Academia"}'::json ) AS concern(id int, name text); SELECT * FROM json_array_elements_text('[ {"id":1,"name":"Academia"}, {"id":2,"name":"Agronomy"}, {"id":3,"name":"Software"} ]'::json); SELECT id, name FROM json_array_elements_text('[ {"id": 1, "name": "Academia"}, {"id": 2, "name": "Agronomy"}, {"id": 3, "name": "Software"} ]'::json) AS concerns_text, json_to_record(concerns_text::json) AS concerns_json(id int, name text); SELECT id, name FROM json_array_elements('[ {"id": 1, "name": "Academia"}, {"id": 2, "name": "Agronomy"}, {"id": 3, "name": "Software"} ]'::json) AS concerns_array, json_to_record(concerns_array) AS concerns_rows(id int, name text); SELECT id, name FROM json_to_recordset('{ "data": [{ "concerns": {"id": 1, "name": "Academia"} }, { "concerns": {"id": 2, "name": "Agronomy"} }, { "concerns": {"id": 3, "name": "Software"} }] }'::json -> 'data') as r(concerns json), json_to_record(concerns) AS concern(id int, name text);

JSONB SELECT id, name FROM jsonb_to_record ( '{"id":1,"name":"Academia"}'::jsonb ) AS concern(id int, name text); SELECT * FROM jsonb_array_elements_text('[ {"id":1,"name":"Academia"}, {"id":2,"name":"Agronomy"}, {"id":3,"name":"Software"} ]'::jsonb); SELECT id, name FROM jsonb_array_elements_text('[ {"id": 1, "name": "Academia"}, {"id": 2, "name": "Agronomy"}, {"id": 3, "name": "Software"} ]'::jsonb) AS concerns_text, jsonb_to_record(concerns_text::jsonb) AS concerns_json(id int, name text); SELECT id, name FROM jsonb_array_elements('[ {"id": 1, "name": "Academia"}, {"id": 2, "name": "Agronomy"}, {"id": 3, "name": "Software"} ]'::jsonb) AS concerns_array, jsonb_to_record(concerns_array) AS concerns_rows(id int, name text); SELECT id, name FROM jsonb_to_recordset('{ "data": [{ "concerns": {"id": 1, "name": "Academia"} }, { "concerns": {"id": 2, "name": "Agronomy"} }, { "concerns": {"id": 3, "name": "Software"} }] }'::jsonb -> 'data') as r(concerns jsonb), jsonb_to_record(concerns) AS concern(id int, name text);

Result id | name ----+---------- 1 | Academia value ------------------------------- {"id": 1, "name": "Academia"} {"id": 2, "name": "Agronomy"} {"id": 3, "name": "Software"} id | name ----+---------- 1 | Academia 2 | Agronomy 3 | Software (3 rows) id | name ----+---------- 1 | Academia 2 | Agronomy 3 | Software (3 rows) id | name ----+---------- 1 | Academia 2 | Agronomy 3 | Software (3 rows)

Delete Join

-- USING is not an ANSI standard not supported by others RDMBS
-- it's better to use sub query rather than USING for join
DELETE FROM product p USING category c
WHERE p.category_id = c.id AND
c.name = 'Grocery';

Sequence

CREATE SEQUENCE IF NOT EXISTS public.policy_1000
    INCREMENT 1
    START     46656   -- 1,000
    MINVALUE  46656   -- 1,000
    MAXVALUE  1679615 -- Z,ZZZ
    CACHE     10;

ALTER SEQUENCE public.policy_1000 OWNER TO chorke_init_pro;
COMMENT ON SEQUENCE public.policy_1000 IS 'Academia Policy Sequence';

SELECT CURRVAL('policy_1000');
SELECT SETVAL('policy_1000', 0);
ALTER  SEQUENCE IF EXISTS policy_1000 RESTART 1;
SELECT NEXTVAL('policy_1000')::int AS policy_id;

Execute DDL

Anonymous Blocks

DO
$BODY$
DECLARE
    fiscal_year_ukc character varying;
    fiscal_drop_ukc character varying := 'ALTER TABLE fiscal_year';
BEGIN
    SELECT
        INTO fiscal_year_ukc con.conname 
    FROM  pg_catalog.pg_constraint     con
    INNER JOIN pg_catalog.pg_class     rel ON rel.oid = con.conrelid
    INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
    WHERE
        nsp.nspname = 'public'      AND
        rel.relname = 'fiscal_year' AND
        con.conname LIKE 'uk_%';
 
	IF fiscal_year_ukc IS NOT NULL THEN
		fiscal_drop_ukc := fiscal_drop_ukc || ' '
		'DROP CONSTRAINT ' || fiscal_year_ukc;
		
    	EXECUTE fiscal_drop_ukc;
	END IF;
END; $BODY$;

Create Sequence

CREATE OR REPLACE FUNCTION fn_get_policy_no(IN agent_no character varying)
    RETURNS  integer
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    policy_no       integer;
    sequence_name   character varying := 'policy_' || agent_no;
    create_sequence character varying := 'CREATE SEQUENCE IF NOT EXISTS ' || sequence_name;
BEGIN
    create_sequence := create_sequence || ' '
    'INCREMENT 1       '
    'START     46656   ' -- 1,000
    'MINVALUE  46656   ' -- 1,000
    'MAXVALUE  1679615 ' -- Z,ZZZ
    'CACHE     10      ';
    
    EXECUTE create_sequence;
    SELECT INTO policy_no NEXTVAL(sequence_name)::int;
    RETURN policy_no;
END;
$BODY$;

SELECT fn_get_policy_no('0001');

Create Sequence with Comment

CREATE OR REPLACE FUNCTION fn_get_policy_no(IN agent_no character varying, IN agent_desc character varying)
    RETURNS  integer
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    policy_no        integer;
    sequence_name    character varying := 'policy_' || agent_no;
    create_sequence  character varying := 'CREATE SEQUENCE IF NOT EXISTS ' || sequence_name;
    comment_sequence character varying := 'COMMENT ON SEQUENCE ' || sequence_name ||' IS ''' || agent_desc || '''';
BEGIN
    create_sequence := create_sequence || ' '
    'INCREMENT 1       '
    'START     46656   ' -- 1,000
    'MINVALUE  46656   ' -- 1,000
    'MAXVALUE  1679615 ' -- Z,ZZZ
    'CACHE     10      ';
    
    EXECUTE create_sequence;
	EXECUTE comment_sequence;
    SELECT INTO policy_no NEXTVAL(sequence_name)::int;
    RETURN policy_no;
END;
$BODY$;

SELECT fn_get_policy_no('0001', 'Fareast Islami Life Insurance');
-- @Query(value="SELECT fn_get_policy_no(:agent_no, :agent_desc)", nativeQuery = true)
-- Integer getPolicyNo(@Param("agent_no") String agentNo, @Param("agent_desc") String agentDesc);

Create From Table Exclude Column

DO
$BODY$
DECLARE
    create_table character varying;
BEGIN
    SELECT INTO create_table 'CREATE TABLE IF NOT EXISTS invoice__temp AS SELECT ' || STRING_AGG(isc.column_name, ', ') || ' FROM invoice'
    FROM (SELECT * FROM information_schema.columns WHERE table_name = 'invoice' AND
    column_name NOT IN ('id', 'created_on') ORDER BY ordinal_position ASC) isc;
    EXECUTE create_table;
END; $BODY$;

Date & Time

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2013-10-10 10:10:10');
SELECT DATE_TRUNC('year', TIMESTAMP '2013-10-10 10:10:10');
SELECT DATE_TRUNC('hour', TIMESTAMP '2013-10-10 10:10:10');
SELECT AGE(TIMESTAMP '2023-10-10', TIMESTAMP '1983-10-10');
SELECT EXTRACT(DAY FROM TIMESTAMP '2013-10-10 10:10:10');
SELECT DATE_PART('day', TIMESTAMP '2013-10-10 10:10:10');
SELECT DATE_PART('hour', INTERVAL '4 hours 3 minutes');

SELECT JUSTIFY_INTERVAL(INTERVAL '1 mon -1 hour');
SELECT JUSTIFY_HOURS(INTERVAL '27 hours');
SELECT JUSTIFY_DAYS(INTERVAL '35 days');
SELECT AGE(TIMESTAMP '1983-10-10');

SELECT CURRENT_TIMESTAMP(2)
SELECT CURRENT_TIMESTAMP;
SELECT LOCALTIMESTAMP;
SELECT CURRENT_TIME;
SELECT CURRENT_DATE;

Restoring the Dump

export dumps
pg_dump academia > academia_20200210_1020.sql
pg_dump academia| gzip > academia_2020021.sql.gz

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity WHERE datname IN
(
    'academia'
);
DROP DATABASE IF EXISTS academia;
CREATE DATABASE academia;

restore dumps
psql academia < academia_20200210_1020.sql
gunzip -c academia_20200210_1020.sql.gz | psql academia

OS User Auth

CREATE DATABASE academia;
CREATE USER academia WITH LOGIN;
GRANT ALL PRIVILEGES ON DATABASE academia TO academia;
-- CREATE USER academia WITH ENCRYPTED PASSWORD 'sadaqah!';

echo "os-map          academia                academia" >> $PG_DATA/pg_ident.conf
echo "host    all     all     127.0.0.1/32    ident map=os-map" >> $PG_DATA/pg_hba.conf
pg_ctl --pgdata="$PG_DATA" reload;

DBLink Exec

DO $BODY$
DECLARE
    p_connstr text := 'hostaddr=127.0.0.1 port=5432 dbname=academia';
BEGIN
    PERFORM dblink_exec(p_connstr, format('UPDATE commission SET type = ''%s'' WHERE type = ''%s''', 'new_type', 'old_type'));
END;
$BODY$;

Encode/Decode

SELECT decode('Chorke Academia, Inc.', 'escape');         -- text to binary
SELECT encode('Chorke Academia, Inc.'::bytea, 'escape');  -- binary to text

SELECT encode('Chorke Academia, Inc.'::bytea, 'base64');                       -- text to base64
SELECT convert_from(decode('Q2hvcmtlIEFjYWRlbWlhLCBJbmMu', 'base64'), 'UTF8'); -- base64 to text

Large Object

-- lo_open(lobjId oid, mode integer) returns integer
-- The mode parameter to lo_open uses two constants:
--   INV_READ       = 0x20000
--   INV_WRITE      = 0x40000
--   INV_READ_WRITE = 0x60000

SELECT cast( x'20000' AS integer);         -- 131072
SELECT cast( x'40000' AS integer);         -- 262144
SELECT cast( x'60000' AS integer);         -- 393216
SELECT cast(x'20000'|x'40000' AS integer); -- 393216

SELECT lo_from_bytea(0, lo_get(photo::oid)) FROM employee;
SELECT lo_get(photo::oid), lo_get(resume::oid) FROM employee;

SELECT encode(lo_get(photo::oid), 'escape'), encode(lo_get(resume::oid), 'escape') FROM employee; -- binary to text
SELECT encode(lo_get(photo::oid), 'base64'), encode(lo_get(resume::oid), 'base64') FROM employee; -- binary to base64

Knowledge

comment and uncomment sql
sed -i -e 's|^|-- |g' src/main/resources/db/migration/*.sql
sed -i -e 's|^-- ||g' src/main/resources/db/migration/*.sql

-- psql
CREATE DATABASE chorke_init_pro;
CREATE USER chorke_init_pro WITH ENCRYPTED PASSWORD 'pa55w0rd';
GRANT ALL PRIVILEGES ON DATABASE chorke_init_pro TO chorke_init_pro;
-- \q

-- revoke public connect from specific database after creation
REVOKE CONNECT ON DATABASE academia_ebis_dev FROM PUBLIC;
-- revoke public connect for all future databases
REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;

SELECT
    con.conname,
    nsp.nspname,
    rel.relname
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class rel     ON rel.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
WHERE
    nsp.nspname = 'public'      AND
    rel.relname = 'fiscal_year' AND
    con.conname LIKE 'uk_%';

ALTER USER academia WITH SUPERUSER;

References

PostgreSQL users can access all databases without any grants Creating user, database and adding access on PostgreSQL ASP.NET Core MVC Identity using PostgreSQL System Catalogs pg_class::relkind Query a JSON Column in PostgreSQL Fluent NHibernate with PostgreSQL Create sequence if not exists SQL Create Procedure PL/PgSQL Trigger 9.3 PL/PgSQL Trigger 10

Custom Thread-safe Sequence by Hibernate Date/Time Functions and Operators Convention for Database Tables Object Identifier(OIDs) Types Date/Time Support SQL Key Words pg_lsn Type Pseudo-Types Domain Types Data Types

PL/pgSQL Create dynamic table from string PL/pgSQL SQL Procedural Language PL/pgSQL Syntax Constants PL/pgSQL Drop Function

---

Calling Stored Procedures from Spring Data JPA Explanation of cascade ON DELETE/UPDATE Concatenate a string variable in PostgreSQL Insert text with single quotes in PostgreSQL Append a item into the array-type Column Example of Stored Procedure Create Sequence if not exists Find all constraints of a table Multi-Line PL/pgSQL String PostgreSQL Dump

PostgreSQL jsonb_set multiple keys update Remove a item from the array-type Column How to show Large Objects (LOB) contents? Get TEXT value of a CLOB OID in Postgresql Using OS Users to connect to PostgreSQL PostgreSQL Export Import Script Online PostgreSQL turn a json array into rows Adding Data with INSERT and COPY PostgreSQL Delete with Inner Join JSON Functions and Operators