PostgreSQL
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 |
Indholdsfortegnelse
- 1 SQL Cheat Sheet
- 1.1 Join typer
- 1.2 Tabeltilretning
- 1.3 Indexering
- 1.4 Primærnøgle
- 1.5 Update column
- 1.6 String
- 1.7 JSON
- 1.8 Geometrier
- 1.9 Default værdi i kolonne
- 1.10 Constraints
- 1.11 List kolonner fra tabel
- 1.12 List alle tabeller i database
- 1.13 Harddiskforbrug
- 1.14 Afhængigheder
- 1.15 Trigger og funktioner
- 1.16 DB Link
- 1.17 Brugere
- 1.18 Materialized View
- 1.19 Hent data fra andre kilder
SQL Cheat Sheet
Join typer
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:
- https://stackoverflow.com/a/36076895
- https://bitnine.net/blog-postgresql/postgresql-internals-jsonb-type-and-its-indexes/
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);
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));
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;
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)
);
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 ';';