PostgreSQL: Difference between revisions

Latest revision as of 20:46, 19 January 2026

export PGPASSWORD='p@55w0rd'

# deploy@cdn.chorke.org:~ #
psql -U postgres
#psql (10.15 (Ubuntu 10.15-0ubuntu0.18.04.1))
#Type "help" for help.

# root@cdn.chorke.org:~ #
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=#

Connection String

# parameters based psql
psql -U postgres
psql -U academia postgres
psql -U academia -h rds.vpc.chorke.org postgres
psql -U academia -h rds.vpc.chorke.org -p 5432 postgres
PGPASSWORD='sadaqah' psql -U academia -h rds.vpc.chorke.org -p 5432 postgres

# connection string based psql
psql 'postgres://postgres:@:/'
psql 'postgres://academia:@:/postgres'
psql 'postgres://academia:@rds.vpc.chorke.org:/postgres'
psql 'postgres://academia:@rds.vpc.chorke.org:5432/postgres'
psql 'postgres://academia:sadaqah@rds.vpc.chorke.org:5432/postgres'

Login Error

Connection String

# root@cdn.chorke.org:~ #
su - postgres psql
# Last login: Tue Jan 01 12:00:00 MYT 2013 on pts/0
# /bin/createuser: /bin/createuser: cannot execute binary file

# root@cdn.chorke.org:~ #
su postgres
psql -p 5433
# psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))

# deploy@cdn.chorke.org:~ #
sudo su postgres
psql -p 5433
# psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))

Cluster Login

Cluster Login
pg_lsclusters sudo su - postgres	psql -p 5433 psql -U postgres -p 5433
GRANT ALL ON tmp_coll TO academia; GRANT ALL ON test_json TO academia;	-- psql -V SELECT version();

Creating User

# root@cdn.chorke.org:~ #
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

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.
# root@cdn.chorke.org:~ # 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

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

Extensions

ENABLE_DB_LINK=$(cat <<-DDL
CREATE EXTENSION IF NOT EXISTS dblink;
CREATE EXTENSION IF NOT EXISTS dblink SCHEMA extensions;
SELECT * FROM pg_available_extensions WHERE name = 'dblink';
DDL
)
echo "${ENABLE_DB_LINK}" | psql -p 5432 -U ${USER}
echo "${ENABLE_DB_LINK}" | psql -p 5432 -U ${USER} academia_data_staging

ENABLE_PG_CRYPTO=$(cat <<-DDL
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA extensions;
SELECT * FROM pg_available_extensions WHERE name = 'pgcrypto';
DDL
)
echo "${ENABLE_PG_CRYPTO}" | psql -p 5432 -U ${USER}
echo "${ENABLE_PG_CRYPTO}" | psql -p 5432 -U ${USER} academia_data_staging

sudo apt install postgresql-16-pgvector

ENABLE_PG_VECTOR=$(cat <<-DDL
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS vector SCHEMA extensions;
SELECT * FROM pg_available_extensions WHERE name = 'vector';
DDL
)
echo "${ENABLE_PG_VECTOR}" | psql -p 5432 -U ${USER}
echo "${ENABLE_PG_VECTOR}" | psql -p 5432 -U ${USER} academia_data_staging

Logout PSQL

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

Meta Data

-- show databases
SELECT datname FROM pg_database;             -- \l+

-- show sequence
SELECT c.relname FROM pg_class c             -- \ds+
WHERE c.relkind = 'S' ORDER BY c.relname;

-- 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    -- \d+ my_table
FROM information_schema.columns WHERE table_name = 'my_table';

-- find tables with similar field name
SELECT table_name, column_name, data_type 
FROM information_schema.columns WHERE column_name LIKE '%slug%';

SELECT pg_database_size('my_database');                     -- \l+
SELECT pg_size_pretty(pg_database_size('my_database'));     -- \l+


SELECT 
    db.datname AS db_name,
    pg_size_pretty(pg_database_size(db.datname)) AS db_size
FROM pg_database db ORDER BY pg_database_size(db.datname) DESC;



SELECT pg_size_pretty(pg_total_relation_size('my_table'));  -- \dt+
SELECT                                                      -- \dt+
    table_schema, table_name,
    pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) AS table_size
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY 2 ASC;

XML Query

XML Query
Skipped » Find More 👉 PostgreSQL » XML

Array Query

XML Query

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

SELECT * FROM user
WHERE owner_ref LIKE ANY (ARRAY[
   ',academia,chorke.org,shahed.biz,'
]) ORDER BY id ASC;

SELECT * FROM user
WHERE owner_ref LIKE ALL (ARRAY[
   ',academia,chorke.org,shahed.biz,'
]) ORDER BY id ASC;

JSON Query

JSON Query
Skipped » Find More 👉 PostgreSQL » JSON

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;

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO chorke_init_pro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO chorke_init_pro;

Echo/Print

DO $BODY$
DECLARE
    p_jsonb jsonb;
BEGIN
--  SELECT INTO p_jsonb '["", "template","{templateType}","{productSlug}"]'::jsonb || '["{filePath}"]'::jsonb;
    p_jsonb := '["", "template","{templateType}","{productSlug}"]'::jsonb || '["{filePath}"]'::jsonb;
    RAISE NOTICE '%', p_jsonb;
END;
$BODY$;

Execute DDL

Insert Using Loop

DO $BODY$
DECLARE
    p_id         integer;
    p_domain     varchar:= 'chorke.org';
    p_group_name varchar:= 'occupation_class';
BEGIN
  SELECT INTO p_id (COALESCE(MAX(id), 0)) FROM grouped_collection;
    FOR p_code IN 1..5 LOOP
        INSERT INTO grouped_collection (id, created_on, domain, code, name, group_name, extended_properties)
        VALUES ((p_id + p_code), LOCALTIMESTAMP, p_domain, p_code, p_code, p_group_name, '{}');
    END LOOP;
END;
$BODY$;

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

Create Sequence with Comment

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 * FROM policy 
WHERE DATE_TRUNC('day', created_on) = TO_DATE('2021-12-31', 'yyyy-mm-dd');

UPDATE policy SET deleted_on = LOCALTIMESTAMP
WHERE DATE_TRUNC('day', created_on) = TO_DATE('2021-12-31', 'yyyy-mm-dd');

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;

String Format

String Format
SELECT STRING_TO_ARRAY('/my/request/path', '/') "string_array"; SELECT ARRAY_TO_JSON(STRING_TO_ARRAY('/my/request/path', '/')) "json_array";

Number Format

SELECT NULLIF(regexp_replace('CKI00109'     , '\D','','g'), '')::numeric;
SELECT NULLIF(regexp_replace('CKI0010#9####', '\D','','g'), '')::numeric;
SELECT NULLIF(regexp_replace('CKI2203000075', '\D','','g'), '')::numeric;

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

# export table as csv
psql -U postgres
\c my_database_staging
\COPY my_table_name TO '/home/academia/Downloads/my_table_name.csv' DELIMITER ',' CSV HEADER;

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 POC

DBLink POC
Skipped » Find More 👉 PostgreSQL » DBLink

DBLink Exec

DBLink Exec
Skipped » Find More 👉 PostgreSQL » DBLink

Recursive Query

WITH RECURSIVE hierarchy AS (
    SELECT
        o.id,
        o.code,   o.name,
        o.parent "parent_code",
        p.name   "parent_name"
    FROM       org_unit  o
    LEFT  JOIN org_unit  p ON p.code        = o.parent
    WHERE o.code = LOWER(REPLACE('Chorke Agency', ' ', '-'))
    UNION
    SELECT
        o.id,
        o.code,   o.name,
        o.parent "parent_code",
        p.name   "parent_name"
    FROM       org_unit  o 
    LEFT  JOIN org_unit  p ON p.code        = o.parent
    INNER JOIN hierarchy h ON h.parent_code = o.code
)
SELECT * FROM hierarchy;

Encode/Decode

Encode/Decode
SELECT decode('Chorke Academia, Inc.', 'escape'); -- text to binary SELECT encode('Chorke Academia, Inc.'::bytea, 'escape'); -- binary to text SELECT convert_to('Chorke Academia, Inc.', 'UTF8'); -- text to binary SELECT convert_from('Chorke Academia, Inc.'::bytea, 'UTF8'); -- binary to text SELECT encode('Chorke Academia, Inc.'::bytea, 'base64'); -- text to base64 SELECT convert_from(decode('Q2hvcmtlIEFjYWRlbWlhLCBJbmMu', 'base64'), 'UTF8'); -- base64 to text
Base36 Encode
CREATE OR REPLACE FUNCTION fn_base36_encode(IN base10 bigint) RETURNS varchar LANGUAGE plpgsql IMMUTABLE AS $BODY$ DECLARE base36 varchar := ''; intval bigint := abs(base10); char0z char[] := regexp_split_to_array('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', ''); BEGIN WHILE intval != 0 LOOP base36 := char0z[(intval % 36)+1] \|\| base36; intval := intval / 36; END LOOP; IF base10 = 0 THEN base36 := '0'; END IF; RETURN base36; END $BODY$;
Base36 Decode
CREATE OR REPLACE FUNCTION fn_base36_decode(IN base36 varchar) RETURNS bigint LANGUAGE plpgsql AS $BODY$ DECLARE rindex int; intval int; carray char[]; base10 bigint := 0; char0z varchar := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; BEGIN FOR rindex IN REVERSE char_length(base36)..1 LOOP carray := carray \|\| substring(upper(base36) FROM rindex FOR 1)::char; END LOOP; rindex := 0; WHILE rindex < (array_length(carray,1)) LOOP intval := position(carray[rindex+1] IN char0z)-1; base10 := base10 + (intval * (36 ^ rindex)); rindex := rindex + 1; END LOOP; RETURN base10; END $BODY$;

Large Object

Large Object
Skipped » Find More 👉 PostgreSQL » Large Object

Bastion Dump

BASH_REMOTE_SCRIPT_FRMT=$(cat <<'EOF'
# suppress stdout
echo '/* $STDOUT$'
echo "${HOSTNAME}${HOME}"

ssh -qt gtw.vpc.chorke.org <<'EOF_00'
echo "${HOSTNAME}${HOME}"
ssh -qt app.vpc.chorke.org <<'EOF_01'
echo "${HOSTNAME}${HOME}"
echo '$STDOUT$ */'

pg_dump postgres://academia:%s@rds.vpc.chorke.org:5432/%s
# suppress stdout
echo '/* $STDOUT$'
EOF_01
EOF_00
echo '$STDOUT$ */'
EOF
)

BASH_REMOTE_SCRIPT_PASS='sadaqah!'
BASH_REMOTE_SCRIPT_PGDB='academia_keycloak_staging'
printf -v  BASH_REMOTE_SCRIPT_EXEC          "${BASH_REMOTE_SCRIPT_FRMT}" "${BASH_REMOTE_SCRIPT_PASS}" "${BASH_REMOTE_SCRIPT_PGDB}"
printf -v  BASH_EXPORT_SCRIPT_DUMP '%s.sql' "${BASH_REMOTE_SCRIPT_PGDB}"
bash -c "${BASH_REMOTE_SCRIPT_EXEC}" >       ${BASH_EXPORT_SCRIPT_DUMP}

sed '/^\/\* \$STDOUT\$$/,/^\$STDOUT\$ \*\/$/{/^\/\* \$STDOUT\$$/!{/^\$STDOUT\$ \*\/$/!d}}' -i ${BASH_EXPORT_SCRIPT_DUMP}
sed -z 's|\n/\* $STDOUT$\n$STDOUT$ \*/||g' -i ${BASH_EXPORT_SCRIPT_DUMP}
sed -z 's|/\* $STDOUT$\n$STDOUT$ \*/\n||g' -i ${BASH_EXPORT_SCRIPT_DUMP}
gzip ${BASH_EXPORT_SCRIPT_DUMP}

Export CSV

psql -U academia academia_audit_staging
\COPY (SELECT * FROM audit_log WHERE action_type = 'Sync Collection' AND DATE_TRUNC('day', logged_on) = '20241010')\
 To '~/.config/audit_log_20241010_1010_MYT.csv' With CSV DELIMITER ',' HEADER;

Knowledge

Knowledge
# wsl (windows subsystem for linux) sudo service redis-server restart sudo service postgresql restart sudo service apache2 restart # 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_%';
SELECT n.nspname AS "schema", c.relname AS "name", CASE c.relkind WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'r' THEN 'table' WHEN 's' THEN 'special' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' WHEN 'm' THEN 'materialized view' END AS "type", pg_catalog.pg_get_userbyid(c.relowner) AS "owner" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('S','') AND n.nspname !~ '^pg_toast' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2;
SELECT d.datname AS "db_name", pg_catalog.pg_get_userbyid(d.datdba) AS "db_owner", pg_catalog.pg_encoding_to_char(d.encoding) AS "db_encoding", d.datcollate AS "db_collation", d.datctype AS "db_collation_type", pg_catalog.array_to_string(d.datacl, e'\n') AS "db_access_privileges", CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE 'No Access' END AS "db_size", t.spcname AS "db_tablespace", pg_catalog.shobj_description(d.oid, 'pg_database') AS "db_description" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t ON d.dattablespace = t.oid ORDER BY 1;
SELECT usesysid AS "user_id", usename AS "user_name", passwd AS "user_password", usesuper AS "is_super_user" FROM pg_catalog.pg_user ORDER BY 1;
DO $BODY$ DECLARE p_password varchar :='p@$$w0rd'; p_connection_string varchar :='hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres password='; BEGIN p_connection_string := p_connection_string \|\| p_password; RAISE NOTICE 'Connection String: %', p_connection_string; SELECT INTO p_connection_string 'hostaddr=' \|\| host(inet_server_addr()) \|\| ' port=' \|\| inet_server_port() \|\| ' dbname=' \|\| current_database() \|\| ' user=' \|\| CURRENT_USER \|\| ' password=' \|\| p_password; RAISE NOTICE 'Connection String: %', p_connection_string; END $BODY$;
cat << EOF \| psql -U postgres DROP DATABASE IF EXISTS academia_ebis_staging; CREATE DATABASE academia_ebis_staging; CREATE USER academia WITH ENCRYPTED PASSWORD 'sadaqah!'; GRANT ALL PRIVILEGES ON DATABASE academia_ebis_staging TO academia; ALTER DATABASE academia_ebis_staging OWNER TO academia; -- ALTER USER academia WITH NOSUPERUSER; -- ALTER USER academia WITH SUPERUSER; EOF

Allow Remote

Docker » Compose » Manage

apt install -y iputils-ping telnet dnsutils
sudo systemctl status ufw
sudo ufw status verbose

sudo ufw status numbered
sudo ufw allow 5432/tcp
sudo ufw enable

nano /etc/postgresql/14/main/postgresql.conf
nano /etc/postgresql/16/main/postgresql.conf

nano /etc/postgresql/14/main/pg_hba.conf
nano /etc/postgresql/16/main/pg_hba.conf

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               scram-sha-256
# host  all             all             10.19.83.1/24           scram-sha-256
# host  all             all             10.20.13.1/24           scram-sha-256

Spread Sheet

=CONCAT("(LOCALTIMESTAMP, 'religion', 'chorke.org', (SELECT (COALESCE(MAX(id), 0) + 1) FROM grouped_collection), '",  SUBSTITUTE(A2 , """" ,  ""), "', '", SUBSTITUTE(A2 , """" ,  ""),  "', '{""state"": """ , SUBSTITUTE(B2 , "'" , "''")  , """, ""city"": """ , SUBSTITUTE(C2 , "'" , "''") , """}'),")

=CONCAT("[""",  SUBSTITUTE(A2 , """",  ""), """, """, SUBSTITUTE(A2, """",  ""), "",""", {""state"": """ , SUBSTITUTE(B2 , "'" , "''")  , """, ""city"": """ , SUBSTITUTE(C2 , "'" , "''") , """}", "],")

=CONCAT("[""",  SUBSTITUTE(C518 , """",  ""), """, """, SUBSTITUTE(B518, """",  ""), "",""", {}, " , A518 , ", ",  IF(ISBLANK(D518), "false", "true"), "],")

=CONCAT("[""",  SUBSTITUTE(C5 , """",  ""), """, """, SUBSTITUTE(B5, """",  ""), "",""", {""class"": """, D5 ,""", ""income"": true}, " , A5, "],")

=CONCAT("[""",  SUBSTITUTE(C27 , """",  ""), """, """, SUBSTITUTE(B27, """",  ""), "",""", {}, " , A27, "],")

=CONCAT("[""",  C27, """, """, B27, "",""", {}, " , A27, "],")

Docker PSQL

docker run -d --name postgres\
 -e POSTGRES_PASSWORD=sadaqah\
 -v ./init.sql:/docker-entrypoint-initdb.d/init.sql\
 -p 127.0.0.1:5433:5432\
 postgres:12.14

docker exec -it postgres bash
psql -U postgres
 
docker stop postgres
docker rm postgres

docker exec -it postgres\
 psql -U postgres
 
docker ps -a
docker rm postgres -f

References

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` Checking if a row exists `PL/pgSQL` Syntax Constants `PL/pgSQL` DBLink Connect `PL/pgSQL` Drop Function `PL/pgSQL` DBLink
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	Does JPA delete also remove large objects of Blob fields Explanation of JSONB introduced by PostgreSQL Insert a Large Object using SQL in PostgreSQL Insert or update Large Object in PostgreSQL Change a User to Superuser in PostgreSQL Gist: Base36 Conversion in PostgreSQL Combination of `LIKE` and `IN` in SQL List all sequences in a PostgreSQL Base36 Conversion in PostgreSQL Remove attribute from JSON
PostgreSQL » Effectively Store & Index JSON Data PostgreSQL » Update a table using a WITH query PostgreSQL » Return Result Set as JSON Array PostgreSQL » Update all values in JSON Array PostgreSQL » pgAdmin » Export to a CSV File PostgreSQL » Nested JSON Arrays Query PostgreSQL » Nested JSON Querying PostgreSQL » Loop over JSON Arrays PostgreSQL » Export to a CSV File PostgreSQL » WITH Queries	PostgreSQL » `WITH` clause with `INSERT` statement PostgreSQL » PSQL Short Commands and Flags Column type is JSON but expression is Varchar PostgreSQL » `RETURNING` with `ON CONFLICT` PostgreSQL 14 not Started on Ubuntu 22.04 PostgreSQL » Dockerized
Convention for Database Tables Base36 Encoding and Decoding EclipseLink MapStruct Hibernate Liquibase MyBatis Lombok Locale JPA	PostgreSQL » Aggregate multiple rows as array PostgreSQL » JSON column with H2 PostgreSQL » 12 » XML2 Functions PostgreSQL » 12 » JSON Functions PostgreSQL » 9.1 » XML Functions Java » ISO `DateTimeFormatter` PostgreSQL » XML `xmlelement` PostgreSQL » XML » Categories Base64 Jasypt	PostgreSQL » PgAdmin » OAuth 2.0 » Configure PostgreSQL » PgAdmin » OAuth 2.0 » Enabling PostgreSQL » 12 » `pgcrypto` PostgreSQL » PgBouncer PostgreSQL » PgLoader MySQL

@@ Line 1: / Line 1: @@
-==Login to PSQL==
+{|class='wikitable'
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
-!scope='col' style='text-align:left' colspan='2'|
-Login to PSQL
-|-
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 export PGPASSWORD='p@55w0rd'
@@ Line 27: / Line 23: @@
 |}
-===Connection String===
+==Login==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='2'|
 Connection String
 |-
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 # parameters based psql
 psql -U postgres
@@ Line 43: / Line 39: @@
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 # connection string based psql
 psql 'postgres://postgres:@:/'
@@ Line 54: / Line 50: @@
 == Login Error ==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='2'|
 Connection String
 |-
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 # root@cdn.chorke.org:~ #
 su - postgres psql
@@ Line 67: / Line 63: @@
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 # root@cdn.chorke.org:~ #
 su postgres
@@ Line 75: / Line 71: @@
 |-
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 # deploy@cdn.chorke.org:~ #
 sudo su postgres
@@ Line 85: / Line 81: @@
 == Cluster Login ==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='2'|
 Cluster Login
 |-
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 pg_lsclusters
 sudo su - postgres
@@ Line 96: / Line 92: @@
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 psql -p 5433
 psql -U postgres -p 5433
@@ Line 103: / Line 99: @@
 |-
 |valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+<syntaxhighlight lang='sql'>
 GRANT ALL ON tmp_coll TO academia;
 GRANT ALL ON test_json TO academia;
@@ Line 109: / Line 105: @@
 |valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+<syntaxhighlight lang='sql'>
 -- psql -V
 SELECT version();
@@ Line 116: / Line 112: @@
 == Creating User ==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='2'|
 Creating User
 |-
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 # root@cdn.chorke.org:~ #
 su - postgres
@@ Line 134: / Line 130: @@
 == Creating DB ==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='2'|
 Creating DB
@@ Line 144: / Line 140: @@
 |-
 |valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 # root@cdn.chorke.org:~ #
 su - postgres
@@ Line 159: / Line 155: @@
 == Grant Privileges ==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='2'|
 Grant Privileges
 |-
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 # postgres=#
 grant all privileges on database root to root;
@@ Line 177: / Line 173: @@
 ==Extensions==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='2'|
 Extensions
 |-
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 ENABLE_DB_LINK=$(cat <<-DDL
 CREATE EXTENSION IF NOT EXISTS dblink;
@@ Line 194: / Line 190: @@
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 ENABLE_PG_CRYPTO=$(cat <<-DDL
 CREATE EXTENSION IF NOT EXISTS pgcrypto;
@@ Line 206: / Line 202: @@
 |-
 |valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 sudo apt install postgresql-16-pgvector
@@ Line 224: / Line 220: @@
 == Logout PSQL ==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='2'|
 Logout PSQL
 |-
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 # postgres=#
 \q
@@ Line 238: / Line 234: @@
 ==Meta Data==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='2'|
 Meta Data
 |-
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+<syntaxhighlight lang='sql'>
 -- show databases
 SELECT datname FROM pg_database;             -- \l+
@@ Line 265: / Line 261: @@
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+<syntaxhighlight lang='sql'>
 SELECT pg_database_size('my_database');                     -- \l+
 SELECT pg_size_pretty(pg_database_size('my_database'));     -- \l+
@@ Line 288: / Line 284: @@
 ==XML Query==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='2'|
 XML Query
 |-
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+* [[PostgreSQL/XML|Skipped » Find More 👉 PostgreSQL » XML]]
-SELECT
-    xml_is_well_formed('<>')         AS "<>",                  -- false
-    xml_is_well_formed('<name >')    AS "<name >",             -- false
-    xml_is_well_formed('<name />')   AS "<name />",            -- true
-    xml_is_well_formed('academia')   AS "academia",            -- true
-    xml_is_well_formed('{academia')  AS "{academia",           -- true
-    xml_is_well_formed('{academia}') AS "{academia}";          -- true
-</syntaxhighlight>
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-SELECT
-    xml_is_well_formed_content('<>')         AS "<>",          -- false
-    xml_is_well_formed_content('<name >')    AS "<name >",     -- false
-    xml_is_well_formed_content('<name />')   AS "<name />",    -- true
-    xml_is_well_formed_content('academia')   AS "academia",    -- true
-    xml_is_well_formed_content('{academia')  AS "{academia",   -- true
-    xml_is_well_formed_content('{academia}') AS "{academia}";  -- true
-</syntaxhighlight>
-|-
-|valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-SELECT
-    xml_is_well_formed_document('<>')         AS "<>",         -- false
-    xml_is_well_formed_document('<name >')    AS "<name >",    -- false
-    xml_is_well_formed_document('<name />')   AS "<name />",   -- true
-    xml_is_well_formed_document('academia')   AS "academia",   -- false
-    xml_is_well_formed_document('{academia')  AS "{academia",  -- false
-    xml_is_well_formed_document('{academia}') AS "{academia}"; -- false
-</syntaxhighlight>
-|valign='top'|
-|-
-|valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-WITH xpath_node AS (
-    SELECT
-        cast(raw_xpath_node->>0 AS integer) "sn",
-        cast(raw_xpath_node->>1 AS varchar) "node"
-    FROM jsonb_array_elements('[
-        [0, ""],
-        [1, "bpmn:definitions"],
-        [2, "bpmn:process"],
-        [3, "bpmn:serviceTask"],
-        [4, "bpmn:extensionElements"],
-        [5, "camunda:connector"],
-        [6, "camunda:inputOutput"],
-        [7, "camunda:inputParameter"]
-    ]'::jsonb) AS raw_xpath_node
-)
-SELECT string_agg(node, '/') AS "path" FROM xpath_node;
-</syntaxhighlight>
-|valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-WITH workflow_namespace AS (
-    SELECT
-        cast(raw_workflow_namespace->>0 AS varchar) "prefix",
-        cast(raw_workflow_namespace->>1 AS varchar) "namespace"
-    FROM jsonb_array_elements('[
-        ["bpmn",    "http://www.omg.org/spec/BPMN/20100524/MODEL"],
-        ["bpmndi",  "http://www.omg.org/spec/BPMN/20100524/DI"],
-        ["dc",      "http://www.omg.org/spec/DD/20100524/DC"],
-        ["di",      "http://www.omg.org/spec/DD/20100524/DI"],
-        ["modeler", "http://camunda.org/schema/modeler/1.0"],
-        ["camunda", "http://camunda.org/schema/1.0/bpmn"]
-    ]'::jsonb) AS raw_workflow_namespace
-)
--- SELECT * FROM workflow_namespace;
--- SELECT array[prefix, namespace] FROM workflow_namespace;
-SELECT array_agg(array[prefix, namespace]) FROM workflow_namespace;
-</syntaxhighlight>
-|-
-| valign="top" colspan="2"|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-WITH xpath_ns AS (
-    SELECT
-        cast(raw_xpath_ns->>0 AS varchar) "alias",
-        cast(raw_xpath_ns->>1 AS varchar) "namespace"
-    FROM jsonb_array_elements('[
-        ["bpmn",    "http://www.omg.org/spec/BPMN/20100524/MODEL"],
-        ["bpmndi",  "http://www.omg.org/spec/BPMN/20100524/DI"],
-        ["dc",      "http://www.omg.org/spec/DD/20100524/DC"],
-        ["di",      "http://www.omg.org/spec/DD/20100524/DI"],
-        ["modeler", "http://camunda.org/schema/modeler/1.0"],
-        ["camunda", "http://camunda.org/schema/1.0/bpmn"]
-    ]'::jsonb) AS raw_xpath_ns
-),
-xpath_nss AS (
-    SELECT
-        array_agg(array[ns.alias, ns.namespace])
-    FROM xpath_ns ns
-),
-stored_dataflow AS (
-    SELECT
-        id_ AS "id", rev_ AS "revision",
-        generated_ AS "is_generated", tenant_id_ AS "tenant_id",
-        type_ AS "type", create_time_ AS "created_on", root_proc_inst_id_ AS "root_proc_inst_id",
-        removal_time_ AS "deleted_on", name_ AS "name", convert_from(bytes_, 'UTF8') AS "dataflow"
-    FROM act_ge_bytearray
-),
-stored_workflow AS (
-    SELECT
-        sd.id, sd.name, sd.type, sd.revision, sd.is_generated,
-        sd.created_on,  sd.deleted_on,  sd.root_proc_inst_id,
-        CAST(sd.dataflow AS xml)  AS "workflow", sd.dataflow,
-        (SELECT * FROM xpath_nss) AS "ns"
-    FROM stored_dataflow sd
-    WHERE xml_is_well_formed_document(sd.dataflow)
-    ORDER BY sd.name ASC, sd.created_on DESC
-)
-SELECT
-    sw.name,
-    (xpath('count(/bpmn:definitions/bpmn:process)', sw.workflow, sw.ns))[1]::text::int,
-    (xpath('/bpmn:definitions/bpmn:process', sw.workflow, sw.ns))[1], dataflow
-FROM stored_workflow sw;
-</syntaxhighlight>
 |}
 ==Array Query==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='2'|
 XML Query
 |-
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+<syntaxhighlight lang='sql'>
 SELECT dependencies||'"Running Fiscal Year"'
 FROM scope WHERE name = 'Fiscal Year Config';
@@ Line 426: / Line 307: @@
 |-
 |valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+<syntaxhighlight lang='sql'>
 SELECT * FROM user
 WHERE owner_ref LIKE ANY (ARRAY[
@@ Line 434: / Line 315: @@
 |valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+<syntaxhighlight lang='sql'>
 SELECT * FROM user
 WHERE owner_ref LIKE ALL (ARRAY[
@@ Line 443: / Line 324: @@
 ==JSON Query==
-<syntaxhighlight lang="json">
+{|class='wikitable mw-collapsible'
-{
+!scope='col' style='text-align:left' colspan='2'|
-    "type": "Reseller",
+JSON Query
-    "commission": {
+|-
-        "type": "Percentage",
+|valign='top' style='width:50%'|
-        "value": "25"
+* [[PostgreSQL/JSON|Skipped » Find More 👉 PostgreSQL » JSON]]
-    }
-}
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|valign='top' style='width:50%'|
-SELECT extended_properties ->'commission'->'value'
-FROM seller WHERE extended_properties ->>'type' = 'Reseller';
---
-UPDATE seller
-SET extended_properties = jsonb_set(extended_properties, '{type}', '"Retailer"')
-WHERE id = 1000;
---
-UPDATE seller
-SET extended_properties = extended_properties || jsonb_build_object('code', code)
-WHERE id = 1000;
---
-UPDATE seller
-SET extended_properties = jsonb_set(extended_properties, '{commission, type}', '"Bonus"')
-WHERE id = 1000 AND extended_properties ->> 'type' = 'Reseller';
-</syntaxhighlight>
-----
-{|
-|valign='top'|
-'''JSON'''
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-SELECT id, name FROM json_to_record (
-    '{"id":1,"name":"Academia"}'::json
-) AS concern(id int, name text);
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-SELECT * FROM json_array_elements_text('[
-    {"id":1,"name":"Academia"},
-    {"id":2,"name":"Agronomy"},
-    {"id":3,"name":"Software"}
-]'::json);
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-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);
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-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);
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-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);
-</syntaxhighlight>
-|valign='top'|
-'''JSONB'''
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-SELECT id, name FROM jsonb_to_record (
-    '{"id":1,"name":"Academia"}'::jsonb
-) AS concern(id int, name text);
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-SELECT * FROM jsonb_array_elements_text('[
-    {"id":1,"name":"Academia"},
-    {"id":2,"name":"Agronomy"},
-    {"id":3,"name":"Software"}
-]'::jsonb);
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-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);
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-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);
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-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);
-</syntaxhighlight>
-|valign='top'|
-'''Result'''
-  id |   name
- ----+----------
-| Academia
-              value
- -------------------------------
-  {"id": 1, "name": "Academia"}
-  {"id": 2, "name": "Agronomy"}
-  {"id": 3, "name": "Software"}
-  id |   name
- ----+----------
-| Academia
-| Agronomy
-| Software
- (3 rows)
-  id |   name
- ----+----------
-| Academia
-| Agronomy
-| Software
- (3 rows)
-  id |   name
- ----+----------
-| Academia
-| Agronomy
-| Software
- (3 rows)
 |}
-----
-===Insert===
-<syntaxhighlight style='margin:3px 0' lang='sql'>
--- query » group : religion
--- -----------------------------------------------------------------------------
--- SELECT MAX(id) FROM grouped_collection;
--- SELECT *       FROM grouped_collection WHERE group_name = 'religion';
--- DELETE         FROM grouped_collection WHERE group_name = 'religion';
--- -----------------------------------------------------------------------------
-WITH mixed_grouped_collection AS (
-    SELECT
-        cast('chorke.org' AS varchar) "domain",
-        cast('religion'   AS varchar) "group_name",
-        cast(raw_grouped_collection->>0 AS varchar) "code",
-        cast(raw_grouped_collection->>1 AS varchar) "name",
-        cast(raw_grouped_collection->>2 AS jsonb  ) "extended_properties",
-        cast(raw_grouped_collection->>3 AS numeric) "order_coll"
-    FROM jsonb_array_elements('[
-        ["I", "Islam",     {}, 1],
-        ["B", "Buddhist",  {}, 2],
-        ["H", "Hindu",     {}, 3],
-        ["C", "Christian", {}, 4],
-        ["O", "Others",    {}, 5]
-    ]'::jsonb) AS raw_grouped_collection
-)
-, new_grouped_collection AS (
-    SELECT
-        ROW_NUMBER() OVER() "id", LOCALTIMESTAMP "created_on",
-        mgc.domain,  mgc.code, mgc.name, mgc.group_name,
-        mgc.extended_properties, mgc.order_coll
-    FROM mixed_grouped_collection mgc
-    --  prevent old duplicate
-    WHERE NOT EXISTS (
-        SELECT id FROM grouped_collection
-        WHERE (code, group_name, domain) = (mgc.code, mgc.group_name, mgc.domain)
-    )
-)
-SELECT ngc.* FROM new_grouped_collection ngc;
--- INSERT INTO grouped_collection (id, created_on, domain, code, name, group_name, extended_properties, order_coll)
--- SELECT (SELECT (COALESCE(MAX(id), 0) + ngc.id) FROM grouped_collection),
---     ngc.created_on, ngc.domain, ngc.code, ngc.name, ngc.group_name,
---     ngc.extended_properties, ngc.order_coll
--- FROM new_grouped_collection ngc
--- --  prevent new duplicate
--- WHERE NOT EXISTS (
---     SELECT id FROM grouped_collection
---     WHERE (code, group_name, domain) = (ngc.code, ngc.group_name, ngc.domain)
--- )
--- ORDER BY (ngc.order_coll, ngc.id) ASC;
-</syntaxhighlight>
 ==Delete Join==
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left'|
+Delete Join
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 -- USING is not an ANSI standard not supported by others RDMBS
 -- it's better to use sub query rather than USING for join
@@ Line 655: / Line 347: @@
 c.name = 'Grocery';
 </syntaxhighlight>
+|}
 ==Sequence==
-<syntaxhighlight lang="sql>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left'|
+Sequence
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 CREATE SEQUENCE IF NOT EXISTS public.policy_1000
      INCREMENT 1
@@ Line 673: / Line 371: @@
 SELECT NEXTVAL('policy_1000')::int AS policy_id;
 </syntaxhighlight>
-----
+|-
-<syntaxhighlight lang="sql>
+|valign='top'|
+<syntaxhighlight lang='sql'>
 GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO chorke_init_pro;
 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO chorke_init_pro;
 </syntaxhighlight>
+|}
 ==Echo/Print==
-<syntaxhighlight lang="sql>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left'|
+Echo/Print
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 DO $BODY$
 DECLARE
@@ Line 691: / Line 396: @@
 $BODY$;
 </syntaxhighlight>
+|}
 ==Execute DDL==
 ===Insert Using Loop===
-<syntaxhighlight lang="sql>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left'|
+Insert Using Loop
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 DO $BODY$
 DECLARE
@@ Line 709: / Line 420: @@
 $BODY$;
 </syntaxhighlight>
+|}
 ===Anonymous Blocks===
-<syntaxhighlight lang="sql>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left'|
+Anonymous Blocks
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 DO
 $BODY$
@@ Line 737: / Line 454: @@
 $BODY$;
 </syntaxhighlight>
+|}
 ===Create Sequence===
-<syntaxhighlight lang="sql>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left'|
+Create Sequence
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 CREATE OR REPLACE FUNCTION fn_get_policy_no(IN agent_no character varying)
      RETURNS  integer
@@ Line 765: / Line 488: @@
 SELECT fn_get_policy_no('0001');
 </syntaxhighlight>
+|}
 ===Create Sequence with Comment===
-<syntaxhighlight lang="sql>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left'|
+Create Sequence with Comment
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 CREATE OR REPLACE FUNCTION fn_get_policy_no(IN agent_no character varying, IN agent_desc character varying)
      RETURNS  integer
@@ Line 797: / Line 526: @@
 -- Integer getPolicyNo(@Param("agent_no") String agentNo, @Param("agent_desc") String agentDesc);
 </syntaxhighlight>
+|}
 ===Create From Table Exclude Column===
-<syntaxhighlight lang="sql>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left'|
+Create Sequence with Comment
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 DO
 $BODY$
@@ Line 812: / Line 547: @@
 $BODY$;
 </syntaxhighlight>
+|}
 ==Date & Time==
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left' colspan='2'|
+Date & Time
+|-
+|valign='top' style='width:50%'|
+<syntaxhighlight lang='sql'>
 SELECT * FROM policy
 WHERE DATE_TRUNC('day', created_on) = TO_DATE('2021-12-31', 'yyyy-mm-dd');
@@ Line 821: / Line 562: @@
 WHERE DATE_TRUNC('day', created_on) = TO_DATE('2021-12-31', 'yyyy-mm-dd');
 </syntaxhighlight>
+|valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 SELECT EXTRACT(CENTURY FROM TIMESTAMP '2013-10-10 10:10:10');
 SELECT DATE_TRUNC('year', TIMESTAMP '2013-10-10 10:10:10');
@@ Line 842: / Line 585: @@
 SELECT CURRENT_DATE;
 </syntaxhighlight>
+|valign='top'|
+|}
 ==String Format==
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left' colspan='2'|
+String Format
+|-
+|valign='top' style='width:50%'|
+<syntaxhighlight lang='sql'>
 SELECT STRING_TO_ARRAY('/my/request/path', '/') "string_array";
 SELECT ARRAY_TO_JSON(STRING_TO_ARRAY('/my/request/path', '/')) "json_array";
 </syntaxhighlight>
+|valign='top' style='width:50%'|
+|}
 ==Number Format==
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left' colspan='2'|
+Number Format
+|-
+|valign='top' style='width:50%'|
+<syntaxhighlight lang='sql'>
 SELECT NULLIF(regexp_replace('CKI00109'     , '\D','','g'), '')::numeric;
 SELECT NULLIF(regexp_replace('CKI0010#9####', '\D','','g'), '')::numeric;
 SELECT NULLIF(regexp_replace('CKI2203000075', '\D','','g'), '')::numeric;
 </syntaxhighlight>
+|valign='top' style='width:50%'|
+|}
 ==Restoring the Dump==
- '''export dumps'''
+{|class='wikitable mw-collapsible'
- pg_dump academia > academia_20200210_1020.sql
+!scope='col' style='text-align:left' colspan='2'|
- pg_dump academia| gzip > academia_2020021.sql.gz
+Restoring the Dump
+|-
+|valign='top' style='width:50%'|
+<syntaxhighlight lang='bash'>
+# export dumps
+pg_dump academia > academia_20200210_1020.sql
+pg_dump academia| gzip > academia_2020021.sql.gz
+</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|valign='top' style='width:50%'|
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 SELECT pg_terminate_backend(pid)
 FROM pg_stat_activity WHERE datname IN
@@ Line 870: / Line 641: @@
 CREATE DATABASE academia;
 </syntaxhighlight>
+|valign='top'|
- '''restore dumps'''
+|-
- psql academia < academia_20200210_1020.sql
+|valign='top'|
- gunzip -c academia_20200210_1020.sql.gz | psql academia
+<syntaxhighlight lang='sql'>
+# restore dumps
+psql academia < academia_20200210_1020.sql
+gunzip -c academia_20200210_1020.sql.gz | psql academia
+# export table as csv
+psql -U postgres
+\c my_database_staging
+\COPY my_table_name TO '/home/academia/Downloads/my_table_name.csv' DELIMITER ',' CSV HEADER;
+</syntaxhighlight>
-'''export table as csv'''
+|valign='top'|
- psql -U postgres
+|}
- \c my_database_staging
- \COPY my_table_name TO '/home/academia/Downloads/my_table_name.csv' DELIMITER ',' CSV HEADER;
 ==OS User Auth==
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left' colspan='2'|
+OS User Auth
+|-
+|valign='top' style='width:50%'|
+<syntaxhighlight lang='sql'>
 CREATE DATABASE academia;
 CREATE USER academia WITH LOGIN;
@@ Line 887: / Line 670: @@
 -- CREATE USER academia WITH ENCRYPTED PASSWORD 'sadaqah!';
 </syntaxhighlight>
+|valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+|-
+|valign='top'|
+<syntaxhighlight lang='bash'>
 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;
 </syntaxhighlight>
+|valign='top'|
+|}
 ==DBLink POC==
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+{|class='wikitable mw-collapsible'
-CREATE EXTENSION dblink;
+!scope='col' style='text-align:left' colspan='2'|
-CREATE EXTENSION dblink SCHEMA extensions;
+DBLink POC
+|-
+|valign='top' style='width:50%'|
+* [[PostgreSQL/DBLink#POC|Skipped » Find More 👉 PostgreSQL » DBLink]]
-SELECT * FROM pg_available_extensions;
+|valign='top' style='width:50%'|
-SELECT * FROM pg_available_extensions WHERE name = 'dblink';
+|}
-</syntaxhighlight>
-----
-<syntaxhighlight style='margin:3px 0' lang='sql'>
--- using pgsql block
-DO
-$BODY$
-DECLARE
-    p_db_host        varchar:= host(inet_server_addr());
-    p_db_port        varchar:= inet_server_port();
-    p_db_user        varchar:= CURRENT_USER;
-    p_db_pass        varchar:= 'p@$$w0rd';
-    p_db_name        varchar:= 'postgres';
-    p_db_extn_name   varchar;
-    p_db_exec_query  varchar;
-    p_db_conn_format varchar:= 'hostaddr=%s port=%s dbname=%s user=%s password=%s';
-    p_db_conn_string varchar:= format(p_db_conn_format, p_db_host, p_db_port, p_db_name, p_db_user, p_db_pass);
-BEGIN
-    p_db_exec_query  := 'SELECT name FROM pg_available_extensions WHERE name = ''dblink''';
-    SELECT INTO p_db_extn_name name FROM dblink(p_db_conn_string, p_db_exec_query) AS extensions(name varchar);
-    RAISE NOTICE 'Extension Name: %', p_db_extn_name;
-END
-$BODY$;
-</syntaxhighlight>
-----
-<syntaxhighlight style='margin:3px 0' lang='sql'>
--- using pgsql query
-WITH user_info AS (
-    SELECT
-        'hostaddr=%s port=%s dbname=%s user=%s password=%s' "db_conn_format",
-        host(inet_server_addr()) "db_host",
-        inet_server_port()       "db_port",
-        CURRENT_USER             "db_user",
-        'p@$$w0rd'               "db_pass",
-        'postgres'               "db_name"
-)
-, conn_info AS (
-    SELECT
-        db_conn_format,
-        format(db_conn_format, db_host, db_port, db_name, db_user, db_pass) "db_conn_string",
-        'SELECT name FROM pg_available_extensions WHERE name = ''dblink'''  "db_exec_query"
-    FROM user_info
-)
-SELECT * FROM dblink(
-    (SELECT db_conn_string FROM conn_info),
-    (SELECT db_exec_query  FROM conn_info)
-) AS extensions(name varchar)
-</syntaxhighlight>
 ==DBLink Exec==
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+{|class='wikitable mw-collapsible'
-DO
+!scope='col' style='text-align:left' colspan='2'|
-$BODY$
+DBLink Exec
-DECLARE
+|-
-    p_connstr text := 'hostaddr=127.0.0.1 port=5432 dbname=academia';
+|valign='top' style='width:50%'|
-BEGIN
+* [[PostgreSQL/DBLink#Exec|Skipped » Find More 👉 PostgreSQL » DBLink]]
-    PERFORM dblink_exec(p_connstr, format('UPDATE commission SET type = ''%s'' WHERE type = ''%s''', 'new_type', 'old_type'));
-END
-$BODY$;
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|valign='top' style='width:50%'|
-DO
+|}
-$BODY$
-DECLARE
-    p_schema varchar := 'academia';
-    p_passwd varchar := 'p@$$w0rd';
-    p_dbname varchar := 'academia_audit_staging';
-    p_dblink varchar := 'hostaddr=127.0.0.1 port=5432 dbname=postgres';
-BEGIN
-    SELECT INTO p_dblink
-        'hostaddr='|| host(inet_server_addr()) ||
-        ' port='   || inet_server_port()       || ' dbname=' || current_database();
-    RAISE NOTICE '%', p_dblink;
-    IF NOT EXISTS (SELECT FROM pg_catalog.pg_database WHERE datname = p_dbname) THEN
-    --  PERFORM dblink_exec(p_dblink, format('CREATE DATABASE %s', p_dbname));
-        RAISE NOTICE 'CREATE DATABASE %', p_dbname;
-    END IF;
-    IF NOT EXISTS (SELECT FROM pg_catalog.pg_user WHERE usename = p_schema) THEN
-    --  PERFORM format('CREATE USER %s WITH ENCRYPTED PASSWORD ''%s''', p_schema, p_passwd);
-        RAISE NOTICE 'CREATE USER % WITH ENCRYPTED PASSWORD ''%''', p_schema, p_passwd;
-    END IF;
---  PERFORM format('GRANT ALL PRIVILEGES ON DATABASE %s TO %s', p_dbname, p_schema);
-    RAISE NOTICE 'GRANT ALL PRIVILEGES ON DATABASE % TO %', p_dbname, p_schema;
-END
-$BODY$;
-</syntaxhighlight>
 ==Recursive Query==
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left' colspan='2'|
+Recursive Query
+|-
+|valign='top' style='width:50%'|
+<syntaxhighlight lang='sql'>
 WITH RECURSIVE hierarchy AS (
      SELECT
@@ Line 1,015: / Line 732: @@
 SELECT * FROM hierarchy;
 </syntaxhighlight>
+|valign='top' style='width:50%'|
+|}
 ==Encode/Decode==
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left' colspan='2'|
+Encode/Decode
+|-
+|valign='top' colspan='2'|
+<syntaxhighlight lang='sql'>
 SELECT decode('Chorke Academia, Inc.', 'escape');            -- text to binary
 SELECT encode('Chorke Academia, Inc.'::bytea, 'escape');     -- binary to text
@@ Line 1,027: / Line 751: @@
 SELECT convert_from(decode('Q2hvcmtlIEFjYWRlbWlhLCBJbmMu', 'base64'), 'UTF8'); -- base64 to text
 </syntaxhighlight>
-----
+|-
-'''Base36 Encode'''
+!scope='col' style='text-align:left' colspan='2'| Base36 Encode
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|-
+|valign='top' colspan='2'|
+<syntaxhighlight lang='sql'>
 CREATE OR REPLACE FUNCTION fn_base36_encode(IN base10 bigint)
      RETURNS  varchar
@@ Line 1,050: / Line 776: @@
 $BODY$;
 </syntaxhighlight>
-----
+|-
-'''Base36 Decode'''
+!scope='col' style='text-align:left' colspan='2'| Base36 Decode
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|-
+|valign='top' style='width:50%'|
+<syntaxhighlight lang='sql'>
 CREATE OR REPLACE FUNCTION fn_base36_decode(IN base36 varchar)
      RETURNS  bigint
@@ Line 1,080: / Line 808: @@
 $BODY$;
 </syntaxhighlight>
+|valign='top' style='width:50%'|
+|}
 ==Large Object==
- \dfS lo_*
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left' colspan='2'|
+Large Object
+|-
+|valign='top' style='width:50%'|
+* [[PostgreSQL/Large Object|Skipped » Find More 👉 PostgreSQL » Large Object]]
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|valign='top' style='width:50%'|
--- 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  x'20000'::int;          -- SELECT cast(x'20000' AS integer);          -- 131072
-SELECT  x'40000'::int;          -- SELECT cast(x'40000' AS integer);          -- 262144
-SELECT  x'60000'::int;          -- SELECT cast(x'60000' AS integer);          -- 393216
-SELECT (x'20000'|x'40000')::int -- SELECT cast(x'20000'|x'40000' AS integer); -- 393216
-</syntaxhighlight>
-----
-<syntaxhighlight style='margin:3px 0' lang='sql'>
--- example: a
-SELECT lo_get(photo::oid), lo_get(resume::oid) FROM employee;                        -- select large object as binary data
-SELECT lo_from_bytea(0, 'Chorke Academia, Inc.'::bytea) FROM employee;               -- create large object from ascii text
-SELECT lo_from_bytea(0, convert_to('Chorke Academia, Inc.', 'UTF8')) FROM employee;  -- create large object from utf-8 text
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
--- example: b
-SELECT resume FROM profile;                                   -- oid
-SELECT lo_get(resume::oid) FROM profile;                      -- binary
-SELECT encode(lo_get(resume::oid), 'escape') FROM profile;    -- binary to text
-SELECT encode(lo_get(resume::oid), 'base64') FROM profile;    -- binary to base64
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
--- example: c
-SELECT convert_from(lo_get(resume::oid), 'UTF8') FROM profile;
-SELECT convert_from(loread(lo_open(resume::oid, x'40000'::int), x'40000'::int), 'UTF8') FROM profile;
-SELECT lowrite(lo_open(lo_creat(-1), x'60000'::int), convert_to('Chorke Academia, Inc.', 'UTF8')) FROM profile; -- create large object
-SELECT lowrite(lo_open(resume::oid,  x'60000'::int), convert_to('Chorke Academia, Inc.', 'UTF8')) FROM profile; -- update large object
-</syntaxhighlight>
-----
-===Insert===
-<syntaxhighlight style='margin:3px 0' lang='sql'>
--- right way to insert in insert
-INSERT INTO employee(id, resume, name) VALUES(
-    (SELECT COALESCE(max(id), 0) + 1 FROM employee),
-    lo_from_bytea(0, convert_to('Chorke Academia, Inc.', 'UTF8')),
-    'Chorke Academia, Inc.'
-);
-</syntaxhighlight>
-===Select===
-<syntaxhighlight style='margin:3px 0' lang='sql'>
--- right way to select in select
-SELECT
-    id,
-    name,
-    resume AS resume_oid,
-    lo_get(resume::oid) AS resume_binary,
-    convert_from(lo_get(resume::oid), 'UTF8') AS resume_text
-FROM employee;
-</syntaxhighlight>
-===Update===
-<syntaxhighlight style='margin:3px 0' lang='sql'>
--- right way to create in update
--- wrong way to update in update
--- SELECT oid FROM pg_largeobject_metadata;
-UPDATE employee SET
-    resume = lo_from_bytea(0, convert_to('Chorke Academia, Inc.', 'UTF8'))
-WHERE id = 1;
--- SELECT oid FROM pg_largeobject_metadata;
--- we should've to have 1 oid for a sigle record where it will
--- be created N+1 oid for N times of update. after N times of
--- update we need to delete N oid's from database else it will
--- be wastage of huge disk space if those object not deleted!
--- we need to delete manually and which one might be disaster
--- SELECT lo_unlink(orphan_lo::oid);
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
--- right way to update in update
--- SELECT oid FROM pg_largeobject_metadata;
-DO
-$BODY$
-DECLARE
-    p_id     integer:= 1;
-    p_oid    integer:= 0;
-    p_resume varchar:= 'Chorke Academia, Inc.';
-BEGIN
-    SELECT INTO p_oid COALESCE(m.oid, 0) FROM employee e
-    LEFT JOIN pg_largeobject_metadata m ON e.resume::oid = m.oid WHERE e.id = p_id;
-    IF p_oid > 0 THEN
-        PERFORM lowrite(lo_open(p_oid::oid, x'60000'::int), convert_to(p_resume, 'UTF8'));
-    ELSE
-        UPDATE employee SET
-            resume = lo_from_bytea(0, convert_to(p_resume, 'UTF8'))
-        WHERE id = p_id;
-    END IF;
-END
-$BODY$;
--- SELECT oid FROM pg_largeobject_metadata;
--- SELECT * FROM employee
-</syntaxhighlight>
-===Delete===
-<syntaxhighlight style='margin:3px 0' lang='sql'>
--- wrong way to unlink in delete
--- SELECT oid FROM pg_largeobject_metadata;
-DELETE FROM employee WHERE id = 1;
--- SELECT oid FROM pg_largeobject_metadata;
--- we should've to unlink respective oid's for those deleted
--- records. Either it will be root causes of wasting of huge
--- disk space if those object not deleted! we need to delete
--- manually and which one might be disaster
--- SELECT lo_unlink(orphan_lo::oid);
-</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
--- right way to unlink in delete
--- SELECT oid FROM pg_largeobject_metadata;
-DO
-$BODY$
-DECLARE
-    p_id     integer:= 1;
-    p_oid    integer:= 0;
-    p_resume varchar:= 'Chorke Academia, Inc.';
-BEGIN
-    SELECT INTO p_oid COALESCE(m.oid, 0) FROM employee e
-    LEFT JOIN pg_largeobject_metadata m ON e.resume::oid = m.oid WHERE e.id = p_id;
-    DELETE FROM employee WHERE id = p_id;
-    IF p_oid > 0 THEN
-        PERFORM lo_unlink(p_oid::oid);
-    END IF;
-END
-$BODY$;
--- SELECT oid FROM pg_largeobject_metadata;
--- SELECT * FROM employee;
-</syntaxhighlight>
-===Alter===
-<syntaxhighlight style='margin:3px 0' lang='sql'>
-ALTER TABLE employee
---  ALTER COLUMN photo TYPE bytea USING lo_get(photo::oid);
-    ALTER COLUMN photo TYPE text  USING encode(lo_get(photo::oid), 'base64');
-</syntaxhighlight>
 ==Bastion Dump==
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left' colspan='2'|
+Bastion Dump
+|-
+|valign='top' style='width:50%'|
 <syntaxhighlight lang='bash' highlight='6,8,12' line>
 BASH_REMOTE_SCRIPT_FRMT=$(cat <<'EOF'
@@ Line 1,255: / Line 850: @@
 )
 </syntaxhighlight>
-----
+|valign='top' style='width:50%'|
+|-
+|valign='top' colspan='2'|
 <syntaxhighlight lang='bash' start='21' highlight='5' line>
 BASH_REMOTE_SCRIPT_PASS='sadaqah!'
@@ Line 1,263: / Line 860: @@
 bash -c "${BASH_REMOTE_SCRIPT_EXEC}" >       ${BASH_EXPORT_SCRIPT_DUMP}
 </syntaxhighlight>
-----
+|-
+|valign='top' colspan='2'|
 <syntaxhighlight lang='bash' start='27' highlight='1-3' line>
 sed '/^\/\* \$STDOUT\$$/,/^\$STDOUT\$ \*\/$/{/^\/\* \$STDOUT\$$/!{/^\$STDOUT\$ \*\/$/!d}}' -i ${BASH_EXPORT_SCRIPT_DUMP}
@@ Line 1,270: / Line 868: @@
 gzip ${BASH_EXPORT_SCRIPT_DUMP}
 </syntaxhighlight>
+|}
 ==Export CSV==
+{|class='wikitable mw-collapsible'
+!scope='col' style='text-align:left'|
+Export CSV
+|-
+|valign='top'|
 <syntaxhighlight lang='bash'>
 psql -U academia academia_audit_staging
@@ Line 1,277: / Line 881: @@
   To '~/.config/audit_log_20241010_1010_MYT.csv' With CSV DELIMITER ',' HEADER;
 </syntaxhighlight>
+|}
 == Knowledge ==
- '''wsl (windows subsystem for linux)'''
+{|class='wikitable mw-collapsible'
- sudo service redis-server restart
+!scope='col' style='text-align:left' colspan='2'|
- sudo service postgresql restart
+Knowledge
- sudo service apache2 restart
+|-
+|valign='top' style='width:50%'|
+<syntaxhighlight lang='bash'>
+# wsl (windows subsystem for linux)
+sudo service redis-server restart
+sudo service postgresql restart
+sudo service apache2 restart
- '''comment and uncomment sql'''
+# 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
- sed -i -e 's|^-- ||g' src/main/resources/db/migration/*.sql
+sed -i -e 's|^-- ||g' src/main/resources/db/migration/*.sql
+</syntaxhighlight>
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|valign='top' style='width:50%'|
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 -- psql
 CREATE DATABASE chorke_init_pro;
@@ Line 1,295: / Line 909: @@
 -- \q
 </syntaxhighlight>
+|valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+<syntaxhighlight lang='sql'>
 -- revoke public connect from specific database after creation
 REVOKE CONNECT ON DATABASE academia_ebis_dev FROM PUBLIC;
@@ Line 1,302: / Line 916: @@
 REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;
 </syntaxhighlight>
+|-
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|valign='top'|
+<syntaxhighlight lang='sql'>
 SELECT
      con.conname,
@@ Line 1,316: / Line 931: @@
      con.conname LIKE 'uk_%';
 </syntaxhighlight>
+|valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 SELECT
      n.nspname AS "schema",
@@ Line 1,342: / Line 959: @@
 ORDER BY 1,2;
 </syntaxhighlight>
+|valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 SELECT d.datname                                       AS "db_name",
      pg_catalog.pg_get_userbyid(d.datdba)               AS "db_owner",
@@ Line 1,361: / Line 980: @@
 ORDER BY 1;
 </syntaxhighlight>
+|valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 SELECT
      usesysid  AS "user_id",
@@ Line 1,370: / Line 991: @@
 FROM pg_catalog.pg_user ORDER BY 1;
 </syntaxhighlight>
+|valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 DO
 $BODY$
@@ Line 1,387: / Line 1,010: @@
 $BODY$;
 </syntaxhighlight>
+|valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='sql'>
+|-
+|valign='top'|
+<syntaxhighlight lang='sql'>
 cat << EOF | psql -U postgres
 DROP DATABASE IF EXISTS academia_ebis_staging;
@@ Line 1,400: / Line 1,025: @@
 EOF
 </syntaxhighlight>
+|valign='top'|
+|}
 ==Allow Remote==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='2'|
 Docker » Compose » Manage
 |-
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 apt install -y iputils-ping telnet dnsutils
 sudo systemctl status ufw
@@ Line 1,413: / Line 1,040: @@
 </syntaxhighlight>
 |valign='top' style='width:50%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 sudo ufw status numbered
 sudo ufw allow 5432/tcp
@@ Line 1,420: / Line 1,047: @@
 |-
 |valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 nano /etc/postgresql/14/main/postgresql.conf
 nano /etc/postgresql/16/main/postgresql.conf
 </syntaxhighlight>
 |valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 nano /etc/postgresql/14/main/pg_hba.conf
 nano /etc/postgresql/16/main/pg_hba.conf
@@ Line 1,431: / Line 1,058: @@
 |-
 |valign='bottom'|
-<syntaxhighlight style='margin:3px 0' lang='ini' start='60' line>
+<syntaxhighlight lang='ini' start='60' line>
 listen_addresses = '*'                  # what IP address(es) to listen on;
                                          # comma-separated list of addresses;
@@ Line 1,440: / Line 1,067: @@
 |valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='ini' start='96' line>
+<syntaxhighlight lang='ini' start='96' line>
 # IPv4 local connections:
 host    all             all             127.0.0.1/32            scram-sha-256
@@ Line 1,450: / Line 1,077: @@
 ==Spread Sheet==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left'|
 Spread Sheet
 |-
 |valign='top'|
-<syntaxhighlight style='margin:3px 0' lang='python'>
+<syntaxhighlight lang='python'>
 =CONCAT("(LOCALTIMESTAMP, 'religion', 'chorke.org', (SELECT (COALESCE(MAX(id), 0) + 1) FROM grouped_collection), '",  SUBSTITUTE(A2 , """" ,  ""), "', '", SUBSTITUTE(A2 , """" ,  ""),  "', '{""state"": """ , SUBSTITUTE(B2 , "'" , "''")  , """, ""city"": """ , SUBSTITUTE(C2 , "'" , "''") , """}'),")
@@ Line 1,471: / Line 1,098: @@
 ==Docker PSQL==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='3'|
 Docker PSQL
 |-
 |valign='top' style='width:33%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 docker run -d --name postgres\
   -e POSTGRES_PASSWORD=sadaqah\
@@ Line 1,485: / Line 1,112: @@
 |valign='top' style='width:34%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 docker exec -it postgres bash
 psql -U postgres
@@ Line 1,494: / Line 1,121: @@
 |valign='top' style='width:33%'|
-<syntaxhighlight style='margin:3px 0' lang='bash'>
+<syntaxhighlight lang='bash'>
 docker exec -it postgres\
   psql -U postgres
@@ Line 1,504: / Line 1,131: @@
 ==References==
-{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
+{|class='wikitable mw-collapsible'
 !scope='col' style='text-align:left' colspan='3'|
 References

PostgreSQL: Difference between revisions

Latest revision as of 20:46, 19 January 2026

Login

Login Error

Cluster Login

Creating User

Creating DB

Grant Privileges

Extensions

Logout PSQL

Meta Data

XML Query

Array Query

JSON Query

Delete Join

Sequence

Echo/Print

Execute DDL

Insert Using Loop

Anonymous Blocks

Create Sequence

Create Sequence with Comment

Create From Table Exclude Column

Date & Time

String Format

Number Format

Restoring the Dump

OS User Auth

DBLink POC

DBLink Exec

Recursive Query

Encode/Decode

Large Object

Bastion Dump

Export CSV

Knowledge

Allow Remote

Spread Sheet

Docker PSQL

References

Navigation menu

Search