ASA17 vs PostgreSQL Syntax differences
As an internal guideline, we have established that the BEST PRACTICE for args in SQL statements should be:
"SELECT column1, column2 FROM table WHERE column1 = :argsName1 AND column2 = : argsName2", new {argsName1 = value1, argsName2 = value2}SELECT FIRST or SELECT TOP 1
SELECT FIRST arbpl_afo.arbpl_nr
FROM arbpl_afo
WHERE arbpl_afo.arbgkat_nr = :arbgkatNr
ORDER BY arbpl_afo.id;
SELECT … LIMIT 1
SELECT arbpl_nr
FROM arbpl_afo
WHERE arbgkat_nr = :arbgkatNr
ORDER BY id
LIMIT 1;
multiple INSERT Statements within the same block
INSERT INTO para (grp, id, wert, kommentar, system) VALUES ( :grp, :id, :wert, :kommentar, :SystemType);
INSERT INTO para (grp, id, wert, kommentar, system) VALUES ( :grp, :id, :wert, :kommentar, :SystemType);
INSERT INTO para (grp, id, wert, kommentar, system) VALUES ( :grp, :id, :wert, :kommentar, :SystemType);
easier way of doing it:
INSERT INTO para (grp, id, wert, kommentar, system) VALUES
( :grp1, :id1, :wert1, :kommentar1, :SystemType1),
( :grp2, :id2, :wert2, :kommentar2, :SystemType2),
( :grp3, :id3, :wert3, :kommentar3, :SystemType3)
SELECT LIST(..)
SELECT List(art.artnr, ORDER BY artnr) FROM art;
SELECT STRING_AGG
SELECT STRING_AGG(artnr, ', ' ORDER BY artnr)
FROM art;
Also useful:
SELECT ARRAY_TO_STRING( ARRAY_AGG(nr), ',' ) FROM planitem
COUNT()
COUNT(*)
ON EXISTING
UPDATE:
INSERT INTO para (grp, id, wert)
ON EXISTING UPDATE
VALUES ('FIFOLAGER', 'rollback_point', '1')
SKIP:
INSERT INTO el_mpn(nr, mpn) ON EXISTING
SKIP VALUES(:nr, :mpn)
UPDATE:
INSERT INTO para(grp, id, wert)
VALUES 'FIFOLAGER', 'rollback_point', '1'
ON CONFLICT ON CONSTRAINT para_pkey
DO UPDATE SET
grp = EXCLUDED.grp,
id = EXCLUDED.id,
wert = EXCLUDED.wert
SKIP:
INSERT INTO el_mpn(nr, mpn)
VALUES (:nr, :mpn)
ON CONFLICT DO NOTHING
NUMBER()
ROW_NUMBER() OVER()
STRING CONCAT
SELECT '123' + '456'…
STRING CONCAT
SELECT '123' || '456'…
CONVERSION
SELECT String(nr) FROM planitem
CONVERSION
SELECT nr::text FROM planitem
SELECT CAST(nr as TEXT) FROM planitem
IF … THEN … ELSE … ENDIF
SELECT val = IF EXISTS (SELECT nr FROM persgrp WHERE fordverb_samkto = '123') THEN 1 ELSE 0 ENDIF
CASE WHEN … THEN … ELSE … END
SELECT CASE WHEN EXISTS ( SELECT nr FROM persgrp WHERE fordverb_samkto = '123' ) THEN 1 ELSE 0 END AS "val"
Datatypes
DOUBLE
LONG VARCHAR
LONG BINARY
Datatypes
FLOAT8
TEXT
BYTEA
UPDATE with reference to multiple tables
UPDATE planitem, planitem_ref
SET planitem.status = 0
WHERE planitem.nr = planitem_ref.nr
AND planitem_ref.bab_typ = 15
UPDATE with reference to multiple tables
UPDATE planitem
SET status = 0
FROM planitem_ref
WHERE planitem.nr = planitem_ref.nr
AND planitem_ref.bab_typ = 15
After the SET keyword you shall not (and cannot) use aliases.
UNION
UNION ALL
DateDiff()
datetime1::date - datetime2::date
(YEAR (lebenslauf.datum) ) AS "jahr"
date_part('year', lebenslauf.datum) AS "jahr"
EXTRACT(YEAR FROM lebenslauf.datum) AS "jahr"
Case Insensitive Comparison
SELECT * FROM users WHERE name = 'gast'
Case Insensitive Comparison
SELECT * FROM users WHERE name ILIKE 'gast'
Delete with Join
DELETE FROM dispo
FROM belpos
INNER JOIN art ON COALESCE(art.art,10) = 40
WHERE
dispo.bel_typ in (2,8)
AND dispo.bel_typ = belpos.bel_typ
AND dispo.bel_nr = belpos.bel_nr
AND dispo.belpos_nr = belpos.posnr
AND dispo.art_nr = art.artnr
Delete with Join: PostgreSQL does not allow DELETE with JOIN or CROSS JOIN.
DELETE FROM dispo
USING belpos CROSS JOIN art
WHERE dispo.bel_typ IN (2, 8)
AND dispo.bel_typ = belpos.bel_typ
AND dispo.bel_nr = belpos.bel_nr
AND dispo.belpos_nr = belpos.posnr
AND dispo.art_nr = art.artnr
AND COALESCE(art.art, 10) = 40;
LEFT OUTER JOIN
RIGHT OUTER JOIN
LEFT JOIN
RIGHT JOIN
“FROM table1 CROSS JOIN table2” is identical to “FROM table1, table2”
Adrian’s advice is to use the CROSS-keyword
Last updated