This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Based off IETF draft, https://datatracker.ietf.org/doc/draft-peabody-dispatch-new-uuid-format/ | |
create or replace function uuid_generate_v7() | |
returns uuid | |
as $$ | |
begin | |
-- use random v4 uuid as starting point (which has the same variant we need) | |
-- then overlay timestamp | |
-- then set version 7 by flipping the 2 and 1 bit in the version 4 string | |
return encode( |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Create a group | |
CREATE ROLE readaccess; | |
-- Grant access to existing tables | |
GRANT USAGE ON SCHEMA public TO readaccess; | |
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess; | |
-- Grant access to future tables | |
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with table_stats as ( | |
select psut.relname, | |
psut.n_live_tup, | |
1.0 * psut.idx_scan / greatest(1, psut.seq_scan + psut.idx_scan) as index_use_ratio | |
from pg_stat_user_tables psut | |
order by psut.n_live_tup desc | |
), | |
table_io as ( | |
select psiut.relname, | |
sum(psiut.heap_blks_read) as table_page_read, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION public.json_append(data json, insert_data json) | |
RETURNS json | |
IMMUTABLE | |
LANGUAGE sql | |
AS $$ | |
SELECT ('{'||string_agg(to_json(key)||':'||value, ',')||'}')::json | |
FROM ( | |
SELECT * FROM json_each(data) | |
UNION ALL | |
SELECT * FROM json_each(insert_data) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Use Home/End keyboard keys to navigate current line (same as CTRL+A or E), listing all variants for those keys here | |
"\e[1~": beginning-of-line # Home | |
"\e[H": beginning-of-line # Home | |
"\eOH": beginning-of-line # Home | |
"\0001": beginning-of-line # Home | |
"\e[4~": end-of-line # End | |
"\e[F": end-of-line # End | |
"\eOF": end-of-line # End | |
"\0005": end-of-line # End |