PostgreSQL
Login to PSQL
# [email protected]:~ #
psql -U postgres
# [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
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$
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';
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 NEXTVAL('policy_1000')::int AS policy_id;
Execute DDL
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);
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
Knowledge
-- 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;