PostgreSQL

Fra IoTwiki
Skift til: navigering, søgning
PostgreSQL
Systemtype
Version
Leverandør
Kort beskrivelse PostgreSQL Database med pep
Open Source Ja
Dokumentation https://www.postgresql.org/docs/
Hjemmeside https://www.postgresql.org/
Sider, der referer til dette system Digitalisering af vandstandsmålinger i Værebro Å-systemet


SQL Cheat Sheet

Join typer

600px

Tabeltilretning

Omdøb tabel

ALTER TABLE schema.table_foo RENAME TO table_bar;

Flyt tabel til andet Schema

ALTER TABLE schema_foo.table_foo SET SCHEMA schema_bar;

Indexering

Indexer geometri

CREATE INDEX _00_01_kommune_geom_gist
  ON _00_grundkort._00_02_kommune
  USING gist (the_geom);

Indexer tekst/tal

CREATE INDEX index_elev_char ON _00_grundkort.contour25cm_simplify (elev_char);

JSONB

Det er muligt at indeksere JSONB data, som f.eks. bruges til at indeksere device id (eui) fra payloaden

CREATE INDEX sensor_data_eui_index ON lora.sensor_data (((payload ->> 'EUI')::text))
WHERE (payload ->> 'EUI') IS NOT NULL;

Kilde:

Primærnøgle

Angiv hvilken kolonne som er primærnøgle

ALTER TABLE test.lygter
  ADD CONSTRAINT lygter_pkey PRIMARY KEY (gid);

Eller angiv flere kolonner som tilsammen udgør primær nøgle

ALTER TABLE test.lygter
  ADD CONSTRAINT lygter_pkey PRIMARY KEY (col1, col2);

Ændre værdi for indexstart

Kan sættes til f.eks. 1001

--Startværdi angives
ALTER SEQUENCE seq RESTART WITH 1;
--Derefter opdateres id kolonnen så række 1 får værdien angivet ovenfor og forøges jf. sequencen. 
UPDATE table_name SET id=nextval('seq');

Tilføj primærnøgle-kolonne "gid"

ALTER TABLE _01_fysisk_plan_og_naturbeskyt._01_13_bjoerneklo_2016 
    ADD COLUMN gid SERIAL PRIMARY KEY;

Fake primærnøgle for views

QGIS skal have unikke numre når der arbejdes med ved views/tabeller

SELECT row_number() over() as gid, the_geom
   FROM _05_veje_trafik._05_01_belysningskabler;

Update column

Opdater kolonne med værdier fra join

update PROJ_BREDBAAND.BREDBAANDS_ETAPER a
set etape = 'Etape 1 – Skovlunde'
from PROJ_ANDB.NAN_1SKOV b
where a.gid = b.gid::int;

String

Mellemrum mellem husnummer og litra

Ikke den smukkeste løsning, men fungerer godt nok til jazz

SELECT
  CASE
    WHEN right(husnr, 1) IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'X', 'Y', 'Z', 'Æ', 'Ø', 'Å') 
    THEN concat(adresseringsvejnavn, ' ', left(husnr, char_length(husnr) - 1), ' ', right(husnr, 1))
    ELSE concat(adresseringsvejnavn, ' ', husnr)
  END
FROM _00_grundkort._00_01_adresser_adgang


JSON

Der kan forespørges på JSON/JSONB kolonner. Bruges bl.a. ved opdatering af GeoCloud metadata om tabellerne f.eks. tags.

--Tilføj item til array
UPDATE jsontesting
SET jsondata = jsondata || '["newString"]'::jsonb
WHERE id = 7;

-- Sammen som ovenfor men tager højde for tags IS NULL.
update SETTINGS.GEOMETRY_COLUMNS_JOIN set TAGS =
case 
                 when tags is null then '["_conflict"]'::jsonb
                 else tags || '["_conflict"]'::jsonb
end             
where CONDITION

--Fjern item til array
UPDATE jsontesting
SET jsondata = jsondata - 'newString'
WHERE id = 7; 

-- Spørg på item i json array
select "_key_", TAGS
from SETTINGS.GEOMETRY_COLUMNS_JOIN
where tags @> '["_sagsbehandler"]';

--Fjern tag dubletter i json array
update settings.geometry_columns_join
set tags = 
(select array_to_json(array(select distinct jsonb_array_elements( tags ))) 
from settings.geometry_columns_join t1 
where settings.geometry_columns_join.tags is not null and t1._key_ = settings.geometry_columns_join._key_)

--Ændre navnet på tag (kan nok gøres smartere)
update SETTINGS.GEOMETRY_COLUMNS_JOIN set TAGS = tags || '["_sagsbehandler"]'::jsonb
where tags @> '["sagsbehandler"]'::jsonb;

update SETTINGS.GEOMETRY_COLUMNS_JOIN set TAGS = tags - 'sagsbehandler'
where tags @> '["sagsbehandler"]'::jsonb;

Geometrier

SRID skiftes til 25832

ALTER TABLE tablename 
  ALTER COLUMN the_geom TYPE geometry(LINESTRING, 25832) 
    USING ST_transform(the_geom, 25832);

--Hvis der blot skal angives SRID (her sker ingen reprojektion) bruges
SELECT UpdateGeometrySRID('Schema Name', 'mytable', 'the_geom', newSRID);

Simplifisering brugt til højdekurver

SELECT gid, ST_SimplifyPreserveTopology(the_geom, 0.25) the_geom
  FROM schema.contour
    WHERE st_length(the_geom) > 3 --Kun linierstykker over 3 meter medtages

Angiv geometritype for geometrikolonnen

ALTER TABLE _00_grundkort.contour25cm_simplify
    ALTER COLUMN the_geom TYPE geometry(LINESTRING,25832)

Cast geometri (angiv geometritype)

SELECT the_geom::geometry(LINESTRING,25832)

Single-part <> multi-part geometry

--To convert from a single-part to multi-part geometry, use ST_Multi:
ALTER TABLE my_table
    ALTER COLUMN geom TYPE geometry(MultiPoint,4326) USING ST_Multi(geom);

--To convert from a multi-part to a single-part geometry, it is a bit more tricky since you can only use one part,
-- and ignore all other parts (if they exist). Check your data first to see if you have some geometries with more 
--than one part:
SELECT COUNT(CASE WHEN ST_NumGeometries(geom) > 1 THEN 1 END) AS multi_geom,
       COUNT(geom) AS total_geom
FROM my_table;
--If you see multi_geom greater than 0, then you will risk loosing data, and you should probably keep it 
--as a multi-part geometry. If you see 0, then it is safe to make into a single-part geometry with:

ALTER TABLE my_table
    ALTER COLUMN geom TYPE geometry(Point,4326) USING ST_GeometryN(geom, 1);

Kilde

Default værdi i kolonne

-- Sæt default værdi til næste år. 
ALTER TABLE _01_fysisk_plan_og_naturbeskyt.bjoerneklo ALTER COLUMN udbuds_aar SET DEFAULT date_part('year', now())+1


Constraints

Valid geometri

--Opdater geometrikolonne med valid geometri
UPDATE test.test
  SET the_geom = st_makevalid(the_geom)
    WHERE ST_IsValid(the_geom) IS FALSE

--Tjek om geometrien er valid, inden den gemmes
--f.eks. om polygoner skærer sig selv.
ALTER TABLE schema.table
  ADD CONSTRAINT enforce_valid_geom CHECK (st_isvalid(the_geom));

Kilde

Unik værdi

--Lav regel som kræver unikke værdier i kolonnen
ALTER TABLE the_table ADD CONSTRAINT constraint_name UNIQUE (thecolumn);

List kolonner fra tabel

SELECT *
FROM information_schema.columns
WHERE table_schema = 'schema'
  AND table_name   = 'table'

List alle tabeller i database

Denne søgning finder alle tabelnavne i en database og returner hvilket skema den tabel tilhører.

SELECT table_schema,table_name FROM information_schema.tables
ORDER BY table_schema,table_name;

Harddiskforbrug

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes

Afhængigheder

Eksempel på view og funktioner til at vise afhængigheder

I python kan skal der ses nærmere på dette script

Herunder er funktion til at finde hvilke views en tabel har som afhængigheder:

CREATE OR REPLACE FUNCTION public.view_dependencies(
    IN schemata text,
    IN tablelu text)
  RETURNS TABLE(dependent_schema name, dependent_view name, source_schema name, source_view name) AS
$BODY$
SELECT distinct dependent_ns.nspname as dependent_schema
, dependent_view.relname as dependent_view 
, source_ns.nspname as source_schema
, source_table.relname as source_table
--, pg_attribute.attname as column_name
FROM pg_depend 
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid 
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid 
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid 
JOIN pg_attribute ON pg_depend.refobjid = pg_attribute.attrelid 
    AND pg_depend.refobjsubid = pg_attribute.attnum 
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
WHERE 
source_ns.nspname = schemata
AND source_table.relname = tablelu
AND pg_attribute.attnum > 0 
--AND pg_attribute.attname = 'my_column'
ORDER BY 1,2;

$BODY$
  LANGUAGE sql VOLATILE;

Kilde

View definition

SELECT schemaname,viewname,definition 
FROM pg_views 
WHERE schemaname = 'schemaname' AND viewname = 'viewname';

Trigger og funktioner

Sidst ændret datostempel

Tidstempler seneste ændringer i tabel.

-- FUNCTION --
CREATE FUNCTION gravearbejde.sidst_aendret () RETURNS trigger AS '
    BEGIN
        new.opdaterings_dato :=''now'';
        RETURN new;
    END;
' LANGUAGE 'plpgsql';

--TRIGGER --
CREATE TRIGGER sidst_aendret 
  BEFORE UPDATE or INSERT ON gravearbejde.anlaegsprojekter FOR EACH ROW EXECUTE PROCEDURE
    gravearbejde.sidst_aendret(opdaterings_dato);

Beregn areal

Funktion som opdaterer kolonne med afrundet areal (kilde)

-- FUNCTION --
CREATE OR REPLACE FUNCTION schema.calc_area()
  RETURNS trigger AS
$BODY$
BEGIN 
  --column "areal_m2" bliver opdateret med afrundet areal af geometri-column "the_geom"
  NEW.areal_m2 := round(st_area(NEW.the_geom));
  RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

--TRIGGER --
CREATE TRIGGER bjoerneklo_area_trig BEFORE INSERT OR UPDATE ON schema.table
  FOR EACH ROW EXECUTE PROCEDURE schema.calc_area();

Editerbare Views

https://vibhorkumar.wordpress.com/2011/10/28/instead-of-trigger/

Historiktabel

Her kan ses eksempel på triggerfunktion, som danner historik i ny tabel

DB Link

Det er muligt at oprette forbindelse til tabeller i andre databaser (på andre servere) med dblink extension.

--Der kan laves view som vil blive opdateret, hvis der sker ændringer i "moder"-tabellen

--CREATE VIEW schema.view_name AS
SELECT dblink_table.*
FROM dblink(
      'dbname=mydb port=myport host=myjost user=myuser password=mypw',
      'SELECT col1, col2, the_geom FROM schema.table'
    )
AS dblink_table(
      col1 integer,
      col2 character varying(80),
      the_geom geometry(MultiPoint,25832)
    );

Postgresonline dokumentation

Brugere

Der kan oprettes bruger i pgAdmin eller med SQL. Herunder oprettes read only bruger som får læseadgang til to schemaer. På fælles serveren oprettes roller med præfiks, så vi kan se hvilken kommen de tilhører (eg. bk_ , ek_ , fk_)

--Brugeren "readonly" med password ***** oprettes.
CREATE ROLE readonly LOGIN ENCRYPTED PASSWORD '*****'
  NOINHERIT
   VALID UNTIL 'infinity';

--Der gives adgang til at forbinde til databasen "ballerup"
GRANT CONNECT ON DATABASE ballerup 
  TO readonly;

--Brugeren får lov til at bruge schemaerne
GRANT USAGE ON SCHEMA schemaname_foo, schemaname_bar
  TO readonly;

--Brugeren får lov til at læse (select) fra alle tabellerne i schemaerne
GRANT SELECT ON ALL TABLES IN SCHEMA schemaname_foo, schemaname_bar 
  TO readonly;

--Hvis der i fremtiden oprettes nye tabeller i schemaerne har brugeren også adgang til dem
ALTER DEFAULT PRIVILEGES IN SCHEMA schemaname_foo, schemaname_bar
  GRANT SELECT ON TABLES TO readonly;

--Fratag privilegier til tabeller i schema:
REVOKE ALL
ON ALL TABLES IN SCHEMA schemaname_foo, schemaname_bar 
  FROM readonly;

---- Fratag privilegier til sequences i schema:
REVOKE ALL
ON ALL SEQUENCES IN SCHEMA schemaname_foo, schemaname_bar
  FROM readonly;

Kilder: Stackoverflow, PostgreSQL tutorial og Digitalocean


GRANT ALL ON SCHEMA lora_flaadestyring TO lev_lifa_bo;

GRANT ALL ON ALL TABLES    IN SCHEMA lora_flaadestyring TO lev_lifa_bo;
GRANT ALL ON ALL SEQUENCES IN SCHEMA lora_flaadestyring TO lev_lifa_bo; 
GRANT ALL ON ALL FUNCTIONS IN SCHEMA lora_flaadestyring TO lev_lifa_bo;

ALTER DEFAULT PRIVILEGES IN SCHEMA lora_flaadestyring GRANT ALL ON TABLES    TO lev_lifa_bo;
ALTER DEFAULT PRIVILEGES IN SCHEMA lora_flaadestyring GRANT ALL ON SEQUENCES TO lev_lifa_bo; 
ALTER DEFAULT PRIVILEGES IN SCHEMA lora_flaadestyring GRANT ALL ON FUNCTIONS TO lev_lifa_bo;

Skift password:

ALTER USER user_name WITH PASSWORD 'new_password';

Materialized View

"Tunge" view som kræver regnetid for at danne tabellen kan med fordel laves som MATERIALIZED VIEW for at øge performance. Her skabes en view-tabel som kan opdateres med:

--Opret viewet
CREATE MATERIALIZED VIEW schema.table AS
SELECT *
FROM schema.table
--Opdater viewet
REFRESH MATERIALIZED VIEW schema.table;

Ønskes et materialized view opdateret hver nat, kan det tilføjes view.txt filen på den Produktions Server. Se mere om Bash Script og redigering af view.txt her

Hent data fra andre kilder

DROP TABLE IF EXISTS folketal;
CREATE TABLE folketal
(
  komkode character varying,
  tid character varying,
  antal integer
);

COPY folketal
FROM PROGRAM
  'curl "http://api.statbank.dk/v1/data/FOLK1A/CSV?delimiter=Semicolon&OMR%C3%85DE=*&TID=*"' CSV HEADER DELIMITER ';';