PostgreSQL - Tipps und Tricks: Unterschied zwischen den Versionen
Aus Geoinformation HSR
Stefan (Diskussion | Beiträge) (→PostgreSQL SQL) |
Stefan (Diskussion | Beiträge) (→PostgreSQL SQL) |
||
Zeile 20: | Zeile 20: | ||
WHERE GeometryType(the_geom) = 'GEOMETRYCOLLECTION' | WHERE GeometryType(the_geom) = 'GEOMETRYCOLLECTION' | ||
− | Problem mit Datenfelder ( | + | Problem mit Datenfelder (for < 8.3): |
− | SELECT | + | SELECT the_geom ... (end_date - NOW() >= 10) AS GREEN |
− | AND ( | + | AND (end_date - NOW() < 0) AS RED ... FROM ... |
− | Casting changes in 8.3: | + | Casting changes in >= 8.3: |
− | SELECT | + | SELECT the_geom ... (end_date - NOW() >= '10 DAYS'::INTERVAL) AS GREEN |
− | AND ( | + | AND (end_date - NOW() < '0'::INTERVAL) AS RED ... FROM ... |
− | SELECT | + | SELECT the_geom ... |
− | EXTRACT(DAY FROM ( | + | EXTRACT(DAY FROM (end_date - NOW()) >= 10) AS GREEN |
− | AND EXTRACT(DAY FROM ( | + | AND EXTRACT(DAY FROM (end_date - NOW()) < 0) AS RED ... FROM ... |
== Konfiguration == | == Konfiguration == |
Version vom 8. Dezember 2008, 15:31 Uhr
Siehe auch:
Quickreference
Im Aufbau...
- Start pgsl-Client:
$ psql.exe -h localhost -p 5432 template1 "postgres"
PostgreSQL SQL
Version:
select version();
Anfrage des Geometrie-Typs ('the_geom' ist der Name des Geometrie-Felds):
SELECT * FROM sometable WHERE GeometryType(the_geom) = 'GEOMETRYCOLLECTION'
Problem mit Datenfelder (for < 8.3):
SELECT the_geom ... (end_date - NOW() >= 10) AS GREEN AND (end_date - NOW() < 0) AS RED ... FROM ...
Casting changes in >= 8.3:
SELECT the_geom ... (end_date - NOW() >= '10 DAYS'::INTERVAL) AS GREEN AND (end_date - NOW() < '0'::INTERVAL) AS RED ... FROM ...
SELECT the_geom ... EXTRACT(DAY FROM (end_date - NOW()) >= 10) AS GREEN AND EXTRACT(DAY FROM (end_date - NOW()) < 0) AS RED ... FROM ...
Konfiguration
Die pg_hba.conf kann so eingestellt werden, dass bei der Ausführung von PostgreSQL-Kommandozeilen-Tools kein Passwort übertragen werden muss.
Bsp mit 127.0.0.1: Anstelle
# IPv4 local connections: host all all 127.0.0.1/32 md5
neu:
# IPv4 local connections: host all all 127.0.0.1/32 trust