Du har ikke rettigheder til at redigere denne side af følgende grund:
Denne handling kan kun udføres af brugere som tilhører gruppen Brugere.
==SQL Cheat Sheet== ===Join typer=== [[Fil:Sql Joins.jpg | 600px ]] ===Tabeltilretning=== ====Omdøb tabel==== <syntaxhighlight lang="sql"> ALTER TABLE schema.table_foo RENAME TO table_bar; </syntaxhighlight> ====Flyt tabel til andet Schema==== <syntaxhighlight lang="sql"> ALTER TABLE schema_foo.table_foo SET SCHEMA schema_bar; </syntaxhighlight> ===Indexering=== ====Indexer geometri==== <syntaxhighlight lang="sql"> CREATE INDEX _00_01_kommune_geom_gist ON _00_grundkort._00_02_kommune USING gist (the_geom); </syntaxhighlight> ====Indexer tekst/tal==== <syntaxhighlight lang="sql"> CREATE INDEX index_elev_char ON _00_grundkort.contour25cm_simplify (elev_char); </syntaxhighlight> ====JSONB==== Det er muligt at indeksere JSONB data, som f.eks. bruges til at indeksere device id (eui) fra payloaden <syntaxhighlight lang="sql"> CREATE INDEX sensor_data_eui_index ON lora.sensor_data (((payload ->> 'EUI')::text)) WHERE (payload ->> 'EUI') IS NOT NULL; </syntaxhighlight> 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==== <syntaxhighlight lang="sql"> ALTER TABLE test.lygter ADD CONSTRAINT lygter_pkey PRIMARY KEY (gid); </syntaxhighlight> Eller angiv flere kolonner som tilsammen udgør primær nøgle <syntaxhighlight lang="sql"> ALTER TABLE test.lygter ADD CONSTRAINT lygter_pkey PRIMARY KEY (col1, col2); </syntaxhighlight> ====Ændre værdi for indexstart==== Kan sættes til f.eks. 1001 <syntaxhighlight lang="sql"> --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'); </syntaxhighlight> ====Tilføj primærnøgle-kolonne "gid"==== <syntaxhighlight lang="sql"> ALTER TABLE _01_fysisk_plan_og_naturbeskyt._01_13_bjoerneklo_2016 ADD COLUMN gid SERIAL PRIMARY KEY; </syntaxhighlight> ====Fake primærnøgle for views==== QGIS skal have unikke numre når der arbejdes med ved views/tabeller <syntaxhighlight lang="sql"> SELECT row_number() over() as gid, the_geom FROM _05_veje_trafik._05_01_belysningskabler; </syntaxhighlight> ===Update column=== Opdater kolonne med værdier fra join <syntaxhighlight lang="sql"> update PROJ_BREDBAAND.BREDBAANDS_ETAPER a set etape = 'Etape 1 – Skovlunde' from PROJ_ANDB.NAN_1SKOV b where a.gid = b.gid::int; </syntaxhighlight> ===String=== ====Mellemrum mellem husnummer og litra==== Ikke den smukkeste løsning, men fungerer godt nok til jazz <syntaxhighlight lang="sql"> 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 </syntaxhighlight> ===JSON=== Der kan forespørges på JSON/JSONB kolonner. Bruges bl.a. ved opdatering af [[GeoCloud]] metadata om tabellerne f.eks. tags. <syntaxhighlight lang="sql"> --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; </syntaxhighlight> ===Geometrier=== ====SRID skiftes til 25832==== <syntaxhighlight lang="sql"> 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); </syntaxhighlight> ====Simplifisering brugt til højdekurver==== <syntaxhighlight lang="sql"> 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 </syntaxhighlight> ====Angiv geometritype for geometrikolonnen==== <syntaxhighlight lang="sql"> ALTER TABLE _00_grundkort.contour25cm_simplify ALTER COLUMN the_geom TYPE geometry(LINESTRING,25832) </syntaxhighlight> ====Cast geometri (angiv geometritype)==== <syntaxhighlight lang="sql"> SELECT the_geom::geometry(LINESTRING,25832) </syntaxhighlight> ====Single-part <> multi-part geometry==== <syntaxhighlight lang="sql"> --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); </syntaxhighlight> [http://gis.stackexchange.com/a/28878/28049 Kilde] ===Default værdi i kolonne=== <syntaxhighlight lang="sql"> -- 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 </syntaxhighlight> ===Constraints=== ====Valid geometri==== <syntaxhighlight lang="sql"> --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)); </syntaxhighlight> [http://gis.stackexchange.com/questions/1060/what-are-the-implications-of-invalid-geometries/11234#11234 Kilde] ====Unik værdi==== <syntaxhighlight lang="sql"> --Lav regel som kræver unikke værdier i kolonnen ALTER TABLE the_table ADD CONSTRAINT constraint_name UNIQUE (thecolumn); </syntaxhighlight> ===List kolonner fra tabel=== <syntaxhighlight lang="sql"> SELECT * FROM information_schema.columns WHERE table_schema = 'schema' AND table_name = 'table' </syntaxhighlight> === List alle tabeller i database === Denne søgning finder alle tabelnavne i en database og returner hvilket skema den tabel tilhører.<br> <syntaxhighlight lang="sql"> SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name; </syntaxhighlight> ===Harddiskforbrug=== <syntaxhighlight lang="sql"> 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 </syntaxhighlight> ===Afhængigheder=== Eksempel på [https://wiki.postgresql.org/wiki/Pg_depend_display view og funktioner] til at vise afhængigheder <br /> <br /> I python kan skal der ses nærmere på [https://sigterm.sh/2010/07/09/generating-a-dependency-graph-for-a-postgresql-database/ dette script] <br /> <br /> Herunder er funktion til at finde hvilke views en tabel har som afhængigheder: <syntaxhighlight lang=sql> 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; </syntaxhighlight> [https://stackoverflow.com/a/11773226 Kilde] ====View definition==== <syntaxhighlight lang=sql> SELECT schemaname,viewname,definition FROM pg_views WHERE schemaname = 'schemaname' AND viewname = 'viewname'; </syntaxhighlight> ===Trigger og funktioner=== ====Sidst ændret datostempel==== Tidstempler seneste ændringer i tabel. <syntaxhighlight lang="sql"> -- 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); </syntaxhighlight> ====Beregn areal==== Funktion som opdaterer kolonne med afrundet areal ([http://lists.osgeo.org/pipermail/postgis-users/2008-February/018432.html kilde]) <syntaxhighlight lang="sql"> -- 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(); </syntaxhighlight> ====Editerbare Views==== https://vibhorkumar.wordpress.com/2011/10/28/instead-of-trigger/ ====Historiktabel==== [[Driftweb#Historik_p.C3.A5_driftweb_opgaver | 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 [https://www.postgresql.org/docs/9.3/static/dblink.html dblink] extension. <syntaxhighlight lang="sql"> --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) ); </syntaxhighlight> [http://www.postgresonline.com/journal/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html 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_) <syntaxhighlight lang="sql"> --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; </syntaxhighlight> Kilder: [http://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql Stackoverflow], [http://www.postgresqltutorial.com/postgresql-roles/ PostgreSQL tutorial] og [https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2 Digitalocean] <syntaxhighlight lang="sql"> 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; </syntaxhighlight> Skift password: <syntaxhighlight lang="sql"> ALTER USER user_name WITH PASSWORD 'new_password'; </syntaxhighlight> ===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: <syntaxhighlight lang="sql"> --Opret viewet CREATE MATERIALIZED VIEW schema.table AS SELECT * FROM schema.table --Opdater viewet REFRESH MATERIALIZED VIEW schema.table; </syntaxhighlight> Ø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 [[Ubuntu_Server#refresh_materialized_view | her]] ===Hent data fra andre kilder=== <syntaxhighlight lang="sql"> 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 ';'; </syntaxhighlight>
Free text:
Sammenfatning:
Dette er en mindre ændring Overvåg denne side
Afbryd