db.dobo.sk

Postgres 9 ETL

2 komentárov

lets go back to roots because roots mean culture

pridat stlpec s autoinkrementalnou hodnotou a PK

ALTER TABLE tablename ADD COLUMN id_aut SERIAL PRIMARY KEY;

updajtni tabulku na zaklade 2 parametroveho subselectu

UPDATE tab_finds SET datation = 4000 WHERE id_aut IN
(SELECT id_aut FROM tab_finds where material = ‘stavebni keramika’ AND datation < 3100);

 texty, stringy char a varchar

lowercase/uppercase

UPDATE tabulka SET column=lower(column);

updatovanie tabuľky – concatenácia dvoch polí, s tým, že číselné polia sú prevedené na characters, všimni si parameter formátovania (FM)

UPDATE tab_polygony_sezony SET concat_season_polygon = (TO_CHAR(season,’FM9′) || TO_CHAR(r,’FM999′)) AS ddd  FROM probe;

prehľad stingových funkcií

matematické operácie

pomerne intuitívne

UPDATE tabulka SET calcul = datace/(hustota*koeficient);

UPDATE tab_polygony_sezony
SET koeficient_rozestupy = ROUND
(rozestup_linii/(SELECT AVG(rozestup_linii)
FROM tab_polygony_sezony),2)

window funkcie

SELECT distinct(id_polygon), sum(“vaha”) OVER (PARTITION BY id_polygon) AS Vaha_overall
FROM tab_finds
where season=1
ORDER BY id_polygon;

joiny

poznáme nasledovné typy joinov v postgrese:

postgresql-all-joins

left join abstraktny priklad

SELECT A.pka, A.c1, B.pkb, B.c2
FROM A
LEFT JOIN B ON A .pka = B.fka;

 

konverzia datových typov medzi Oracle a Postgres

 

PostgreSQL Oracle
BIGINT 64-bit integer NUMBER(19)
BIGSERIAL 64-bit autoincrementing integer Sequence and trigger
BIT(n) Fixed-length bit string RAW(n/8)
BIT VARYING(n) Variable-length bit string RAW(n/8)
BOOLEAN, BOOL True, false or NULL CHAR(1)
BYTEA Variable-length binary data, ⇐ 2G BLOB
CHARACTER(n), CHAR(n) Fixed-length string, 1 ⇐ n ⇐ 1G CHARACTER(n), CHAR(n)
CHARACTER VARYING(n) Variable-length string, 1 ⇐ n ⇐ 1G VARCHAR2(n)
DATE Date (year, month and day) DATE Includes time part
DECIMAL(p,s), DEC(p,s) Fixed-point number NUMBER(p,s)
DOUBLE PRECISION Double-precision floating-point BINARY_DOUBLE
FLOAT4 Single-precision floating-point BINARY_FLOAT
FLOAT8 Double-precision floating-point BINARY_DOUBLE
INTEGER, INT 32-bit integer NUMBER(10)
INT2 16-bit integer NUMBER(5)
INT4 32-bit integer NUMBER(10)
INT8 64-bit integer NUMBER(20)
INTERVAL Date and time interval INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
INTERVAL unit Date and time interval NUMBER(5)
INTERVAL YEAR TO MONTH Date interval INTERVAL YEAR TO MONTH
INTERVAL DAY TO HOUR Day and time interval INTERVAL DAY(5) TO SECOND
INTERVAL DAY TO MINUTE Day and time interval INTERVAL DAY(5) TO SECOND
INTERVAL DAY TO SECOND[(p)] Day and time interval INTERVAL DAY(5) TO SECOND[(p)]
INTERVAL HOUR TO MINUTE Time interval INTERVAL DAY(5) TO SECOND
INTERVAL HOUR TO SECOND[(p)] Time interval INTERVAL DAY(5) TO SECOND[(p)]
INTERVAL MINUTE TO SECOND[(p)] Time interval INTERVAL DAY(5) TO SECOND[(p)]
MONEY Currency amount NUMBER(17,2)
NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
REAL Single-precision floating-point BINARY_FLOAT
SERIAL 32-bit autoincrementing integer Sequence and trigger
SERIAL2 16-bit autoincrementing integer Sequence and trigger
SERIAL4 32-bit autoincrementing integer Sequence and trigger
SERIAL8 64-bit autoincrementing integer Sequence and trigger
SMALLINT 16-bit integer NUMBER(5)
SMALLSERIAL 16-bit autoincrementing integer Sequence and trigger
TEXT Variable-length character data, ⇐ 1G CLOB
TIME(p) Time with fraction TIMESTAMP(p)
TIME(p) WITH TIME ZONE Time with fraction and time zone TIMESTAMP(p) WITH TIME ZONE
TIMETZ(p)
TIMESTAMP Date and time with fraction TIMESTAMP(p)
TIMESTAMP(p) WITH TIME ZONE Date and time with time zone TIMESTAMP(p) WITH TIME ZONE
TIMESTAMPTZ(p)
UUID Universally unique identifier CHAR(36)
VARBIT(n) Variable-length bit string RAW(n/8)
VARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 1G VARCHAR2(n)
XML XML data XMLTYPE

pekný prehľad konverzií a funkcií i s linkami na konvertory tu

píše: ďobo

Január 18th, 2018 o 11:46 am

chlievik: postgres

2 odpovedí to 'Postgres 9 ETL'

Subscribe to comments with RSS or TrackBack to 'Postgres 9 ETL'.

  1. postgres

    25 Okt 18 at 23:06

  2. jak se pocita median, neni na to built-in agregacni funkce (aka AVG(), MIN()…)
    https://wiki.postgresql.org/wiki/Aggregate_Median

    postgres

    26 Okt 18 at 0:17

okomentuj