WITH RECURSIVE product_hierarchy AS (
    SELECT product_list_uuid,
        product_uuid_parent,
        product_uuid,
        product_list_order_priority,
        0 AS level,
        product_uuid AS breadcrumb
    FROM product_list
    WHERE product_uuid_parent = p_product_uuid
    UNION
    SELECT pl.product_list_uuid,
        pl.product_uuid_parent,
        pl.product_uuid,
        pl.product_list_order_priority,
        ph.level + 1,
        ph.breadcrumb || ' => ' || pl.product_uuid
    FROM product_list pl
          INNER JOIN product_hierarchy ph ON ph.product_uuid = pl.product_uuid_parent
    )
SELECT * FROM product_hierarchy ORDER BY product_list_order_priority DESC;

CREATE OR REPLACE FUNCTION lerp_query_suppliers_for_product_all(p_product_uuid UUID)
    RETURNS TABLE
            (
                SUPPLIER_UUID          UUID,
                SUPPLIER_NO            INTEGER,
                SUPPLIER_NAME          VARCHAR(200),
                SUPPLIER_NAME2         VARCHAR(200),
                SUPPLIER_STREET        VARCHAR(200),
                SUPPLIER_ZIP           VARCHAR(10),
                SUPPLIER_CITY          VARCHAR(200),
                COUNTRY_ID             INTEGER,
                SUPPLIER_TEL           VARCHAR(100),
                SUPPLIER_FAX           VARCHAR(100),
                SUPPLIER_EMAIL         VARCHAR(200),
                SUPPLIER_WWW           VARCHAR(200),
                SUPPLIER_LANG_ISO      ENUM_SUPPORTED_LANG_ISO,
                INDUSTRY_CATEGORY_UUID UUID,
                SUPPLIER_TAX_ID        VARCHAR(20),
                SKR_03_CODE            CHAR(4),
                SUPPLIER_TIME_CREATE   TIMESTAMP,
                SUPPLIER_TIME_UPDATE   TIMESTAMP,
                SUPPLIER_NOTE          TEXT
            )
AS
$$
DECLARE
BEGIN
    RETURN QUERY SELECT s.*
                 FROM supplier s
                 WHERE s.supplier_uuid IN (
                     SELECT DISTINCT po.supplier_uuid
                     FROM purchase_order po
                     WHERE po.purchase_order_uuid IN (
                         SELECT DISTINCT poi.purchase_order_uuid
                         FROM purchase_order_item poi
                         WHERE poi.product_uuid = p_product_uuid
                     )
                 )
                    OR s.supplier_uuid IN (
                     SELECT DISTINCT pr.supplier_uuid
                     FROM purchase_request pr
                     WHERE pr.purchase_request_uuid IN (
                         SELECT DISTINCT pri.purchase_request_uuid
                         FROM purchase_request_item pri
                         WHERE pri.product_uuid = p_product_uuid
                     )
                 )
                    OR s.supplier_uuid IN
                       (SELECT ps.supplier_uuid FROM product_supplier ps WHERE ps.product_uuid = p_product_uuid);
END;

$$ LANGUAGE plpgsql;

ALTER FUNCTION lerp_query_suppliers_for_product_all(UUID) OWNER TO postgres;

…und so ruft man sie auf:

SELECT * FROM lerp_query_suppliers_for_product_all('80ef3210-c6a8-47e4-a50e-0cf5792077d7');

stackoverflow

stored procedure/function stub

CREATE OR REPLACE FUNCTION app_get_foo(P_TEST_UUID UUID)
    RETURNS TABLE
            (
                TEST_UUID UUID
            )
    LANGUAGE plpgsql
AS
$$
DECLARE
BEGIN

END;
$$

Ohne Zeitzone:

extract(epoch from mytimestampfield::timestamp(0))

Die 0, als Argument für timestamp() bedeutet null Nachkommastellen.

SELECT uuid_generate_v4(),
       doc_order_uuid,
       doc_order_filename,
       doc_order_price,
       doc_order_tax,
       user_uuid_create
INTO TABLE doc_order_send
FROM doc_order;

…es wirft aber den Fehler: [42P07] ERROR: relation "doc_order_send" already exists

Funktionieren tut folgendes:

INSERT INTO doc_order_send
SELECT uuid_generate_v4(),
       doc_order_uuid,
       doc_order_filename,
       doc_order_price,
       doc_order_tax,
       user_uuid_create
FROM doc_order;

$lastId = $this->getAdapter()->getDriver()->getConnection()->getLastGeneratedValue('public.seq_some_name');

stackoverflow

SELECT *
FROM lerp_get_product_list_all(?) AS listall
         LEFT JOIN product ON product.product_uuid = listall.product_uuid
         LEFT JOIN product_no ON product_no.product_no_uuid = product.product_no_uuid
         LEFT JOIN quantityunit ON quantityunit.quantityunit_uuid = product.quantityunit_uuid
         LEFT JOIN product_calc ON product_calc.product_uuid = listall.product_uuid -- can be null

Das passiert nicht wenn man den JOIN mit den NULL Werten an den Anfang stellt:

SELECT *
FROM lerp_get_product_list_all(?) AS listall
         LEFT JOIN product_calc ON product_calc.product_uuid = listall.product_uuid -- can be null
         LEFT JOIN product ON product.product_uuid = listall.product_uuid
         LEFT JOIN product_no ON product_no.product_no_uuid = product.product_no_uuid
         LEFT JOIN quantityunit ON quantityunit.quantityunit_uuid = product.quantityunit_uuid

Bei Zeile 90 findet man:

	// If extra login security is true, then logins via phpPgAdmin with no
	// password or certain usernames (pgsql, postgres, root, administrator)
	// will be denied. Only set this false once you have read the FAQ and
	// understand how to change PostgreSQL's pg_hba.conf to enable
	// passworded local connections.
	$conf['extra_login_security'] = true;

Auf false setzen und es funzt.

\r\nPostGIS 2.0 Ubuntu 12.04 Installation\r\nBei mir (Xubuntu 12.04) half:\r\n

sudo apt-get install python-software-properties\r\nsudo apt-add-repository ppa:ubuntugis/ppa\r\nsudo apt-get update\r\nsudo apt-get install postgresql-9.1-postgis

\r\nAnschließend sollte im pgAdmin III die neue Extension PostGIS folgendermaßen zu aktivieren sein:\r\nServerGruppen/Server/derIndiName/Datenbanken/postgres/Extensions einen Rechtsklick und „Neue Extension“ wählen. In der Auswahlliste sollte postgis vorhanden sein.\r\n\r\nalternativ soll funktionieren:\r\nNach der Installation mit psql in PostgreSQL einloggen und\r\n

CREATE EXTENSION postgis;

\r\num die Extension zu aktivieren (Semikolon nicht vergessen).\r\n\r\n——-\r\n\r\nUm die Openstreetmap Karten zu holen braucht man osm2pgsql:\r\nOsm2pgsql

\r\nsollte die Datei HagenZentrum-map.osm in die DB schreiben.\r\nLeider gibt es einen Fehler:\r\n

osm2pgsql SVN version 0.70.5\r\nPassword:\r\nConnection to database failed: FATAL:  Peer authentication failed for user "postgres"\r\n

\r\n\r\nUm diesen Fehler zu beheben: in /etc/postgresql//main/pg_hba.conf\r\naus dem „peer“ bei „postgres“ „trust“ machen.\r\n\r\n\r\nDann ist man einen Schritt weiter und es kommt der nächste Fehler:\r\n

Using projection SRS 900913 (Spherical Mercator)\r\nCouldn't open style file '/usr/share/default.style': No such file or directory\r\nError occurred, cleaning up\r\n

\r\nDieser Fehler ist bekannt: lists.openstreetmap.org\r\nAlso kopiere ich die Datei /usr/share/osm2pgsql/default.style\r\nnach: /usr/share/default.style\r\n\r\nWieder einen Schritt weiter.\r\nnächster Fehler:\r\n

Using projection SRS 900913 (Spherical Mercator)\r\nSetting up table: planet_osm_point\r\nNOTICE:  table "planet_osm_point" does not exist, skipping\r\nNOTICE:  table "planet_osm_point_tmp" does not exist, skipping\r\nPREPARE get_way (int4) AS SELECT AsText(way) FROM planet_osm_point WHERE osm_id = $1;\r\n failed: ERROR:  function astext(geometry) does not exist\r\nLINE 1: PREPARE get_way (int4) AS SELECT AsText(way) FROM planet_osm...\r\n                                         ^\r\nHINT:  No function matches the given name and argument types. You might need to add explicit type casts.\r\nError occurred, cleaning up\r\n

\r\n\r\n…der ERROR muß weg.\r\n\r\nlegacy_faq\r\npostgis_installation\r\n\r\nXubuntu 12.04 legacy.sql\r\n/usr/share/postgresql/9.1/contrib/postgis-2.0/legacy.sql\r\n

psql -U postgres -d osm_hagenzentrum -f /usr/share/postgresql/9.1/contrib/postgis-2.0/legacy.sql\r\n

\r\n\r\nOK, das hat funktioniert, also ein neuer Versuch die .osm datei in die DB zu bekommen.\r\n\r\n

Using projection SRS 900913 (Spherical Mercator)\r\nSetting up table: planet_osm_point\r\nNOTICE:  table "planet_osm_point" does not exist, skipping\r\nNOTICE:  table "planet_osm_point_tmp" does not exist, skipping\r\nSetting up table: planet_osm_line\r\nNOTICE:  table "planet_osm_line" does not exist, skipping\r\nNOTICE:  table "planet_osm_line_tmp" does not exist, skipping\r\nSetting up table: planet_osm_polygon\r\nNOTICE:  table "planet_osm_polygon" does not exist, skipping\r\nNOTICE:  table "planet_osm_polygon_tmp" does not exist, skipping\r\nSetting up table: planet_osm_roads\r\nNOTICE:  table "planet_osm_roads" does not exist, skipping\r\nNOTICE:  table "planet_osm_roads_tmp" does not exist, skipping\r\nMid: pgsql, scale=100, cache=800MB, maxblocks=102401*8192\r\nSetting up table: planet_osm_nodes\r\n*** WARNING: intarray contrib module not installed\r\n*** The resulting database will not be usable for applying diffs.\r\nNOTICE:  table "planet_osm_nodes" does not exist, skipping\r\nNOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_nodes_pkey" for table "planet_osm_nodes"\r\nSetting up table: planet_osm_ways\r\nNOTICE:  table "planet_osm_ways" does not exist, skipping\r\nNOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_ways_pkey" for table "planet_osm_ways"\r\nSetting up table: planet_osm_rels\r\nNOTICE:  table "planet_osm_rels" does not exist, skipping\r\nNOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_rels_pkey" for table "planet_osm_rels"\r\n\r\nReading in file: /home/allapow/zeug/OSM_GIS/HagenZentrum-map.osm\r\nWARNING: Found Out of order node 994740242 (3068578,18) - this will impact the cache efficiency\r\nProcessing: Node(34k) Way(6k) Relation(253)  parse time: 7s\r\n\r\nNode stats: total(34945), max(2136197385)\r\nWay stats: total(6840), max(222812607)\r\nRelation stats: total(253), max(2825698)\r\n\r\nGoing over pending ways\r\nprocessing way (4k)\r\n\r\nGoing over pending relations\r\n\r\nnode cache: stored: 25203(72.12%), storage efficiency: 1.29%, hit rate: 66.73%\r\nStopping table: planet_osm_nodes\r\nStopping table: planet_osm_ways\r\nStopping table: planet_osm_rels\r\nStopped table: planet_osm_nodes\r\nStopped table: planet_osm_rels\r\nCommitting transaction for planet_osm_point\r\nCommitting transaction for planet_osm_roads\r\nStopped table: planet_osm_ways\r\nSorting data and creating indexes for planet_osm_roads\r\nCommitting transaction for planet_osm_line\r\nCommitting transaction for planet_osm_polygon\r\nSorting data and creating indexes for planet_osm_point\r\nSorting data and creating indexes for planet_osm_polygon\r\nSorting data and creating indexes for planet_osm_line\r\nCREATE INDEX planet_osm_roads_index ON planet_osm_roads USING GIST (way GIST_GEOMETRY_OPS) TABLESPACE pg_default;\r\n failed: ERROR:  operator class "gist_geometry_ops" does not exist for access method "gist"\r\n\r\nError occurred, cleaning up\r\n

\r\n\r\nERROR aber Daten in der Tabelle :)\r\n\r\nlegacy_faq_gist\r\n\r\nWegen dem ERROR dann die neueste Version von PostGIS runterladen und aus dem Verzeichnis /postgis-2.0.3/postgis/ die Datei legacy_gist.sql.in umbenennen und ausführen:\r\n

psql -U postgres -d osm_hagenzentrum -f Downloads/legacy_gist.sql\r\nCREATE OPERATOR CLASS

\r\n\r\nok, ein neuer Versuch:\r\n

sudo osm2pgsql -s -U postgres -W -d osm_hagenzentrum /home/allapow/zeug/OSM_GIS/HagenZentrum-map.osm

\r\ngibt:\r\n

Using projection SRS 900913 (Spherical Mercator)\r\nSetting up table: planet_osm_point\r\nNOTICE:  table "planet_osm_point_tmp" does not exist, skipping\r\nSetting up table: planet_osm_line\r\nNOTICE:  table "planet_osm_line" does not exist, skipping\r\nSetting up table: planet_osm_polygon\r\nNOTICE:  table "planet_osm_polygon" does not exist, skipping\r\nSetting up table: planet_osm_roads\r\nNOTICE:  table "planet_osm_roads_tmp" does not exist, skipping\r\nMid: pgsql, scale=100, cache=800MB, maxblocks=102401*8192\r\nSetting up table: planet_osm_nodes\r\n*** WARNING: intarray contrib module not installed\r\n*** The resulting database will not be usable for applying diffs.\r\nNOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_nodes_pkey" for table "planet_osm_nodes"\r\nSetting up table: planet_osm_ways\r\nNOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_ways_pkey" for table "planet_osm_ways"\r\nSetting up table: planet_osm_rels\r\nNOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_rels_pkey" for table "planet_osm_rels"\r\n\r\nReading in file: /home/allapow/zeug/OSM_GIS/HagenZentrum-map.osm\r\nWARNING: Found Out of order node 994740242 (3068578,18) - this will impact the cache efficiency\r\nProcessing: Node(34k) Way(6k) Relation(253)  parse time: 6s\r\n\r\nNode stats: total(34945), max(2136197385)\r\nWay stats: total(6840), max(222812607)\r\nRelation stats: total(253), max(2825698)\r\n\r\nGoing over pending ways\r\nprocessing way (4k)\r\n\r\nGoing over pending relations\r\n\r\nnode cache: stored: 25203(72.12%), storage efficiency: 1.29%, hit rate: 66.73%\r\nStopping table: planet_osm_nodes\r\nStopping table: planet_osm_ways\r\nStopping table: planet_osm_rels\r\nStopped table: planet_osm_rels\r\nCommitting transaction for planet_osm_roads\r\nCommitting transaction for planet_osm_point\r\nStopped table: planet_osm_nodes\r\nStopped table: planet_osm_ways\r\nCommitting transaction for planet_osm_polygon\r\nCommitting transaction for planet_osm_line\r\nSorting data and creating indexes for planet_osm_point\r\nSorting data and creating indexes for planet_osm_roads\r\nSorting data and creating indexes for planet_osm_polygon\r\nSorting data and creating indexes for planet_osm_line\r\nCompleted planet_osm_roads\r\nCompleted planet_osm_point\r\nCompleted planet_osm_line\r\nCompleted planet_osm_polygon

\r\n\r\nDas sieht gut aus

PostgreSQL docs

In die PostgreSQL Console einloggen

sudo -u postgres psql

Zu User postgres werden

sudo su postgres
# und wenn ich schon root bin
su - postgres

Ausführungspfad und PGDATA setzen

In die /etc/profile folgendes rein schreiben:

if [ -d "/usr/lib/postgresql/12/bin" ] ; then
    export PATH="/usr/lib/postgresql/12/bin:$PATH"
fi
if [ -d "/var/lib/postgresql/12/main" ] ; then
    export PGDATA="/var/lib/postgresql/12/main"
fi

/var/lib/postgresql/11/main ist laut /etc/postgresql/11/main/postgresql.conf das richtige Verzeichnis.

Passwort für den Datenbankuser postgres ändern

In die PostgreSQL Console einloggen:

sudo -u postgres psql

Kommando um das Passwort zu ändern:

\password

Dann muss man zwei mal ein Passwort eingeben.
psql mit \q verlassen.

PostgreSQL neu starten:

sudo service postgresql restart

Ab Ubuntu 16.04:

sudo systemctl restart postgresql

Backup & Create & Restore

sudo -u postgres pg_dump my_db_name > dbname197001010000.sql # backup db
sudo -u postgres createdb another_db_name # create db
sudo -u postgres psql another_db_name < dbname197001010000.sql # restore db

wahres Zeug

Autoincrement Wert setzen:

ALTER SEQUENCE my_awesome_seq RESTART WITH 42

…der nächste autoincrement Wert ist 42.

add ENUM value (ALTER TYPE):

ALTER TYPE my_enum ADD VALUE 'orange' AFTER 'red';

Und weil ein ALTER TYPE my_enum DROP VALUE 'red'; (ein Enum Wert löschen) nicht supported ist, muss man löschen und neu erstellen:

DROP TYPE my_enum;
CREATE TYPE my_enum AS ENUM ('orange', 'grey');
ALTER TYPE my_enum OWNER TO postgres;

…kakke wenn es Referenzen gibt

Unixtime to lesbar

SELECT to_timestamp(1195374767);
-- 2007-11-18 08:32:47.000000

SELECT to_timestamp(1195374767)::date;
-- 2007-11-18

SELECT to_char(to_timestamp(1195374767),'yyyy-mm-dd hh:ii');
-- 2007-11-18 08:77

SELECT EXTRACT(ISOYEAR FROM TIMESTAMP '2007-11-18 08:32:47.000000');
-- 2007

postgresql.org/docs/current/functions-datetime

postgresql.org/docs/current/functions-formatting

CLI db admin

-- list all databases
\list
\l
-- switch databases
\connect database_name
\c database_name

Also:

\l
\c database_name;
select * from public.user;
-- ...weitere Abfragen für database_name
-- wieder raus mit der Taste 'Q'

https://www.postgresql.org/docs/current/app-psql.html

Datenbank URL

postgres://{user}:{password}@{hostname}:{port}/{database-name}