I'm a dragonfly

Jé pa l'temps #5 - Postgresql

jplt database PostgreSQL

Reset database without delete it

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
commit;

PSQL command

  • Show users : \du
  • Show databases : \list

Load / Dump database

# Load SQL
psql postgres://user:password@localhost:port/database --file myDumpFile.sql
# Dump Database
pg_dump postgres://user:password@localhost:port/database --file myDumpFile.sql

Generate fake data

INSERT INTO myTable (name)
SELECT 'name #' || x.id
  FROM generate_series(1,100) AS x(id);

How can load a dump to the database if port is not exposed ?

cat your_dump.sql | docker exec \
  -i {docker-postgres-container} psql -U {user} -d {database_name}

Right management

REVOKE CONNECT ON DATABASE miniflux FROM funkwhale;
REVOKE ALL PRIVILEGES ON DATABASE miniflux FROM funkwhale;
REVOKE ALL ON DATABASE miniflux FROM PUBLIC; -- By default public schema are accessible by others users, but not the data

/!\ It appears to be normal that we can not hide database existence from others users, but we can prevent them from connecting.

Triggers

https://www.postgresql.org/docs/9.1/plpgsql-trigger.html

  • pg_notify(name, value) : Notify an event called name. The value should be type of text.
  • json_build_object('key1', value1, key2, value2)::text : Allow to create a JSON object.
  • TG_ARGV[], TG_OP

Notes

Calcul a difference between two timestamptz

SELECT extract(epoch from (a - b)) as difference

Group date by day

select date_trunc(‘day’, date), count(*)
from <...>
where <...>
group by 1
order by 1 desc

Create a function

CREATE OR REPLACE FUNCTION "function_name" ()
  -- Returns cutome table
  RETURNS TABLE (
      timed float
  )
  -- Returns set of
  RETURNS SETOF table_name
AS 
$$
  SELECT *
  FROM table;
$$ LANGUAGE sql STABLE;

Create a view

CREATE OR REPLACE view "view_name" as 
  SELECT *
  FROM table;

— — — — — — — — — — — — — — — — — — — — — —

La série « Jé pa l’temps » est une série de tutoriels rapides en mode “prise de note” pour avoir une trace de tout ce dont je ne peux me rappeler et pourquoi pas le partager à d’autre. On va à l’essentiel, laissons les jolis pavés à d’autres sites comme medium… LOL !