SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public'
    
ORDER BY
    tablename,
    indexname;

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
          and nspname in ('user_blah', 'user_blar', 'schema1', 'schema2')
  ) a
) a

SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()

select blockingl.relation, blockingl.pid, blockingl.mode, blockingl.granted,
        pgclass.relname, stat.usename, stat.application_name, stat.wait_event_type, stat.wait_event, stat.state, stat.query
    from pg_catalog.pg_locks blockingl
    join pg_class pgclass on blockingl.relation = pgclass.oid 
    join pg_stat_activity stat on stat.pid = blockingl.pid 

select rr.*, pam.* from pg_catalog.pg_roles rr 
 join pg_catalog.pg_auth_members pam on rr."oid" = pam.roleid 
 

with datar(col1,col2) as (
    values (1,2), (2,3), (4,5)
    )
select col1, col2 from datar

select 999999> 'infinity'::float 

CREATE  TABLE foo (id int, a text, b text, c text);
INSERT INTO foo VALUES (1, 'ant', 'cat', 'chimp'), (2, 'grape', 'mint', 'basil'),
                        (3, 'blur', 'cart', 'jump');

select * from foo

CREATE TABLE blah_backup AS
SELECT *
FROM source_table

CREATE USER blah_user WITH PASSWORD 'swear_words';

GRANT CONNECT ON DATABASE mycooldb TO blah_user;
GRANT USAGE ON SCHEMA myschema TO blah_user;
GRANT SELECT ON myschema.quick_table TO blah_user;

ALTER USER user_name WITH PASSWORD 'new_password';

SELECT table_catalog, table_schema, table_name, privilege_type, grantee
FROM   information_schema.table_privileges 

select * from pg_user

# example from article , which bit me personally..
SET lock_timeout TO '2s';
ALTER TABLE items ADD COLUMN last_update timestamptz;

with arf as (select 1 as aa, 
            max(id) as max_arf
            from mytable 
            where key = 'arf'),
bar as (select 1 as aa, 
            max(id) as max_bar
            from mytable
            where key = 'bar'),
car as (select 1 as aa,
            max(id) as max_car
            from mytable 
            where key = 'car')
            
select arf.*, bar.*, car.*
from arf join bar on arf.aa = bar.aa
join car on arf.aa = car.aa

SELECT * FROM myTable
WHERE attribute = 'myValue'
ORDER BY random()
LIMIT 1000;

ids_sql = '(1, 2, 3)' 
blah_key = 'green_type' # some additional constraint other than the id

target_cols = ['norm_col_1', 'norm_col_2', 'norm_col_3']

# Map between 
foovec = [['norm_col_1', 'nested_col_1'], 
          ['norm_col_2', 'nested_col_2'],
          ['norm_col_3', 'nested_col_3']]

select_cols = ', '.join([f'''value->>'{x[1]}' as "{x[0]}"'''
                         for x in foovec])

col_str = ', '.join([f'"{x}"' for x in target_cols])
targetcol_str = ', '.join([f'blah."{x}"' for x in target_cols])

UPDATE {schema}.mytable as dp
SET ({col_str}) = ({targetcol_str})
FROM (select id, key, 
      {select_cols}  -- json->expressions->to->unpack->data!
      from {schema}.mytable
      where {ids_sql}
            and key = '{blah_key}'
        ) as blah(id, key, {col_str})

WHERE (blah.id = dp.id
       AND blah.key = dp.key)

with ids(foo) as (
    select * from generate_series(1, 5000) as foo    
    )    

select * 
from t1 join t2 
on t1.id = t2.id
where t2.id in (select foo from ids)

with ids(foo) as (
    select * from generate_series(1, 5000) as foo    
    )    

select * 
from t1 join t2 
on t1.id = t2.id
where t1.id in (select foo from ids)

with deltas(delta, countt) as (
    values (-1, 3), (0, 4), (19, 9), 
           (50, 2), (2, 8), (189, 3), 
           (2000, 98), (2001, 3),
           (null::int, 2),
           (null, 9)
           ),
binned as (
    select  width_bucket (deltas.delta::float, array[
                0, 50, 100, 150,200, 2000]::float[] ) as bin, 
    deltas.delta, deltas.countt
    from deltas 
)
select delta, countt, bin  -- sum(countt) 
from binned 

with deltas(delta, countt) as (
    values (10, 3), (11, 4), (19, 9), 
           (50, 2), (2, 8), (189, 3), 
           (77, 98), (178, 3)),
binned as (
    select  width_bucket (deltas.delta::float, array[
                0::float, 50::float, 100::float, 150::float,200::float, 2000::float] ) as bin, 
    deltas.delta, deltas.countt
    from deltas 
)
select bin, sum(countt) 
from binned
group by bin 
order by bin

def make_table_header_line(header):
    dashes = ['--' for x in header.split('|')]
    return '|'.join(dashes)
    

SELECT con.*
       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 = '<schema name>'
             AND rel.relname = '<table name>';

-- step one.
alter table    the_table_name
ADD constraint the_constraint_name unique (col1, col2) NOT VALID 

-- step two..
alter table the_table_name
    VALIDATE CONSTRAINT the_constraint_name

select id, key  , count(*) countt
from my_table

group by id, key 
having count(*) > 1

update mytable
    set blahcol = null
where  blahcol = ''
    and ( id between 1 and 100000)

SELECT trigger_schema,event_object_table,trigger_schema,trigger_name,event_manipulation,action_statement,action_timing 
FROM information_schema.triggers 

ORDER BY event_object_table,event_manipulation

with laptops(user_id, laptop, purchase_date) as  (
    values (1, 'sony', 1),
           (1, 'nokia', 2),
           (1, 'bell', 3),
           (2, '3m', 2),
           (2, 'nokia', 8)
)
select l.*, row_number() over w as rnum
from laptops as l
window w as (partition by l.user_id order by purchase_date asc )

with laptops(user_id, laptop, purchase_date) as  (
    values (1, 'sony', 1),
           (1, 'nokia', 2),
           (1, 'bell', 3),
           (2, '3m', 2),
           (2, 'nokia', 8)
),
select aa.* from 
(
    select l.*, row_number() over w as rnum
    from laptops as l
    window w as (partition by l.user_id order by purchase_date asc )
) as aa
where aa.rnum = 1

select cardinality(ARRAY[[1,2],[3,4]])

CREATE or replace FUNCTION myschema.logistic(val float) RETURNS float AS $$
BEGIN
RETURN (1/(1 + ( exp(1)^(-(val))))) ;
END; $$
LANGUAGE PLPGSQL;

with articles(author, article_id, article_title) as (values 
        ('joe', 1, 'birds'),
        ('joe', 2, 'more birds'),
        ('sally', 3, 'eagles'),
        ('sally', 4, 'seagulls'),
        ('jan', 5, 'the philosophical of flying'))
select a.author, a.article_id as left_id, a.article_title as left_title, b.article_id as right_id, b.article_title as right_title
from articles as a left join articles as b on (a.author = b.author and a.article_id < b.article_id)

WITH myconstants (var1, var2) as (
   values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
   OR something_else = var2;