SQL DB

PG: install

https://www.postgresql.org/download/linux/debian/ https://computingforgeeks.com/install-postgresql-12-on-ubuntu/

PG: Allow remote connections

https://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html

PG: List roles

select * from pg_authid;

PG: List schemas

SELECT schema_name FROM information_schema.schemata;

PG: List all connections

  sudo -u postgres psql  << EOF
  SELECT 
      pid
      ,datname
      ,usename
      ,application_name
      ,client_hostname
      ,client_port
      ,backend_start
      ,query_start
      ,query
      ,state
  FROM pg_stat_activity;
  EOF

PG: Convert enum to integer

  CREATE FUNCTION events_processor.enum_to_position(anyenum) RETURNS integer
      LANGUAGE plpgsql STABLE STRICT
      AS $_$
  declare
      govno integer;
  begin
      SELECT enumpos::integer
      into govno
      FROM (
               SELECT row_number() OVER (order by enumsortorder) AS enumpos,
                      enumsortorder,
                      enumlabel
               FROM pg_catalog.pg_enum
               WHERE enumtypid = pg_typeof($1)
           ) enum_ordering
      WHERE enumlabel = ($1::text);
      return govno;
  end
  $_$;

create cast with this function

CREATE CAST (events_processor.event_state AS integer) WITH FUNCTION events_processor.enum_to_position(anyenum);

Get all enum values

SELECT unnest(enum_range(NULL::monitoring_card_notification_type))