jianhe-fun

# https://pgbackrest.org/user-guide.html#pitr


/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 stop
#info check
sudo  -u jian pgbackrest --stanza=pgsql15_3 --log-level-console=info check
sudo  -u jian pgbackrest --stanza=pgsql15_3 --type=diff --log-level-console=info backup

/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 stop

jianhe-fun

-- Optimize GROUP BY query to retrieve latest row per user
--https://dbfiddle.uk/men99GVd
begin;
create table users(
    user_id bigint primary key generated always as identity
    ,username text not null
);

insert into users(username) select 'usr_'::text || g 
from generate_series(1,1000) g; 

jianhe-fun

/*
https://dbfiddle.uk/DRmCu_7u
*/

begin;
create table observations(
    id bigint primary key generated always as identity
    ,station_id int
    ,create_at timestamptz
    ,foo text

jianhe-fun

/*
https://stackoverflow.com/questions/38297935/split-function-returned-record-into-multiple-columns
*/

create or replace function hi_lo(a numeric, 
                                 b numeric, 
                                 c numeric, 
                                 OUT hi numeric, 
                                 OUT lo numeric)
as $$

jianhe-fun

/*
Find latest entries for a person_id by submission_date for specified 
filter criteria type, plan, status. 
There could be more such filters, 
but the logic to return latest by submission date is the same regardless. 
Two major uses one for paginated viewing in UI 
and second for generating reports.
https://stackoverflow.com/questions/61160156/get-paginated-rows-and-total-count-in-single-query/61160817#61160817
*/

jianhe-fun

--using constraint make 2 timestamp range not overlap.
--using constraint make 2 timestamp range not adjacent.
--using constraint make rtimestamp range include range lower bound, exclude upper bound.
/*
https://dbfiddle.uk/KyOD2a58
Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
https://stackoverflow.com/questions/19504727/preventing-adjacent-overlapping-entries-with-exclude-in-postgresql/19505869#19505869
*/
DROP TABLE test_gist_lap;
BEGIN;

jianhe-fun

--https://stackoverflow.com/questions/9067335/how-does-the-search-path-influence-identifier-resolution-and-the-current-schema/9067777#9067777
--properly set search path.
--Set it in cluster level.
ALTER SYSTEM SET search_path TO 'public';
SELECT pg_reload_conf();

--Set it as default for database test16.
ALTER DATABASE test16 SET search_path = '$user', 'public';

-- Set it as default for the role (bob) you connect with (effective cluster-wide):

jianhe-fun

/*
https://dba.stackexchange.com/questions/303502/pass-a-variable-with-insert-update-delete-statements/303546#303546
https://dbfiddle.uk/ZsFssnOW 
 */

CREATE TABLE ins_tbl (
    id int GENERATED ALWAYS AS IDENTITY,
    name text
);

jianhe-fun

/*
https://stackoverflow.com/questions/5144036/escape-function-for-regular-expression-or-like-patterns/45741630#45741630

escape regular expression.
*/

CREATE OR REPLACE FUNCTION public.f_regexp_escape(text)
 RETURNS text
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE STRICT

jianhe-fun

/*
https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg/69658#69658

*/

BEGIN;
SET local search_path = '';
CREATE temp TABLE tbl_a (
    id bigint,
    name text