# ASA17 vs PostgreSQL Syntax differences

<details>

<summary>PostgreSQL Documentation:</summary>

[https://www.postgresql.org/docs/17/index.html](https://www.postgresql.org/docs/17/index.html)

[https://www.postgresql.org/docs/current/sql-keywords-appendix.html](https://www.postgresql.org/docs/17/index.htmlhttps://www.postgresql.org/docs/current/sql-keywords-appendix.html)[](https://www.postgresql.org/docs/17/index.htmlhttps://www.postgresql.org/docs/current/sql-keywords-appendix.html)

</details>

**As an internal guideline, we have established that the BEST PRACTICE for args in SQL statements should be:**

{% code overflow="wrap" %}
```sql
"SELECT column1, column2 FROM table WHERE column1 = :argsName1 AND column2 = : argsName2", new {argsName1 = value1, argsName2 = value2}
```
{% endcode %}

<table><thead><tr><th valign="top">ASA 17</th><th valign="top">PostgreSQL</th></tr></thead><tbody><tr><td valign="top"><p><strong>SELECT FIRST or SELECT TOP 1</strong><br></p><p>SELECT FIRST arbpl_afo.arbpl_nr</p><p>FROM arbpl_afo</p><p>WHERE arbpl_afo.arbgkat_nr = :arbgkatNr</p><p>ORDER BY arbpl_afo.id;</p></td><td valign="top"><p><strong>SELECT … LIMIT 1</strong></p><p></p><p>SELECT arbpl_nr</p><p>FROM arbpl_afo</p><p>WHERE arbgkat_nr = :arbgkatNr</p><p>ORDER BY id</p><p>LIMIT 1;</p></td></tr><tr><td valign="top"><p><strong>multiple INSERT Statements within the same block</strong></p><p></p><p>INSERT INTO para (grp, id, wert, kommentar, system) VALUES ( :grp, :id, :wert, :kommentar, :SystemType);</p><p></p><p>INSERT INTO para (grp, id, wert, kommentar, system) VALUES ( :grp, :id, :wert, :kommentar, :SystemType);</p><p></p><p>INSERT INTO para (grp, id, wert, kommentar, system) VALUES ( :grp, :id, :wert, :kommentar, :SystemType);</p></td><td valign="top"><p><strong>easier way of doing it:</strong> </p><p></p><p></p><p>INSERT INTO para (grp, id, wert, kommentar, system) VALUES </p><p>( :grp1, :id1, :wert1, :kommentar1, :SystemType1),</p><p>( :grp2, :id2, :wert2, :kommentar2, :SystemType2),</p><p>( :grp3, :id3, :wert3, :kommentar3, :SystemType3)</p></td></tr><tr><td valign="top"><p><strong>SELECT LIST(..)</strong></p><p></p><p>SELECT List(art.artnr, ORDER BY artnr) FROM art;</p></td><td valign="top"><p><strong>SELECT STRING_AGG</strong></p><p></p><p>SELECT STRING_AGG(artnr, ', ' ORDER BY artnr)</p><p>FROM art;</p><p></p><p><strong>Also useful:</strong></p><p>SELECT ARRAY_TO_STRING( ARRAY_AGG(nr), ',' ) FROM planitem</p></td></tr><tr><td valign="top"><strong>COUNT()</strong></td><td valign="top"><strong>COUNT(*)</strong></td></tr><tr><td valign="top"><p><strong>ON EXISTING</strong></p><p></p><p><strong>UPDATE:</strong></p><p>INSERT INTO para (grp, id, wert)</p><p>ON EXISTING UPDATE</p><p>VALUES ('FIFOLAGER', 'rollback_point', '1')</p><p> </p><p> </p><p> </p><p> </p><p><strong>SKIP:</strong></p><p>INSERT INTO el_mpn(nr, mpn) ON EXISTING</p><p>SKIP VALUES(:nr, :mpn)</p></td><td valign="top"><p> </p><p></p><p><strong>UPDATE:</strong></p><p>INSERT INTO para(grp, id, wert)</p><p>VALUES 'FIFOLAGER', 'rollback_point', '1'</p><p>ON CONFLICT ON CONSTRAINT para_pkey</p><p>DO UPDATE SET</p><p>grp = EXCLUDED.grp, </p><p>id = EXCLUDED.id,</p><p>wert = EXCLUDED.wert</p><p></p><p></p><p><strong>SKIP:</strong></p><p>INSERT INTO el_mpn(nr, mpn)</p><p>VALUES (:nr, :mpn) </p><p>ON CONFLICT DO NOTHING</p></td></tr><tr><td valign="top"><strong>NUMBER()</strong></td><td valign="top"><strong>ROW_NUMBER() OVER()</strong></td></tr><tr><td valign="top"><p><strong>STRING CONCAT</strong></p><p></p><p>SELECT '<strong>123' + '456'</strong>…</p></td><td valign="top"><p><strong>STRING CONCAT</strong></p><p></p><p>SELECT <strong>'123' || '456</strong>'…</p></td></tr><tr><td valign="top"><p><strong>CONVERSION</strong></p><p></p><p>SELECT <strong>String(nr)</strong> FROM planitem</p></td><td valign="top"><p><strong>CONVERSION</strong></p><p></p><p>SELECT <strong>nr::text</strong> FROM planitem</p><p>SELECT <strong>CAST(nr as TEXT)</strong> FROM planitem</p></td></tr><tr><td valign="top"><p><strong>IF … THEN … ELSE … ENDIF</strong></p><p></p><p>SELECT val = IF EXISTS (SELECT nr FROM persgrp WHERE fordverb_samkto = '123') THEN 1 ELSE 0 ENDIF</p></td><td valign="top"><p><strong>CASE WHEN … THEN … ELSE … END</strong></p><p></p><p>SELECT CASE WHEN EXISTS (<br>SELECT nr<br>FROM persgrp<br>WHERE fordverb_samkto = '123'<br>) THEN 1 ELSE 0 END AS "val"</p></td></tr><tr><td valign="top"><p><strong>Datatypes</strong></p><p>DOUBLE</p><p>LONG VARCHAR</p><p>LONG BINARY</p></td><td valign="top"><p><strong>Datatypes</strong></p><p>FLOAT8</p><p>TEXT</p><p>BYTEA</p></td></tr><tr><td valign="top"><p><strong>UPDATE with reference to multiple tables</strong></p><p></p><p>UPDATE planitem, planitem_ref</p><p>SET planitem.status = 0</p><p>WHERE planitem.nr = planitem_ref.nr </p><p>AND planitem_ref.bab_typ = 15</p></td><td valign="top"><p><strong>UPDATE with reference to multiple tables</strong></p><p></p><p>UPDATE planitem</p><p>SET status = 0</p><p>FROM planitem_ref</p><p>WHERE planitem.nr = planitem_ref.nr </p><p>AND planitem_ref.bab_typ = 15</p><p> </p><p><strong>After the SET keyword you shall not (and cannot) use aliases.</strong></p></td></tr><tr><td valign="top"><strong>UNION</strong></td><td valign="top"><strong>UNION ALL</strong></td></tr><tr><td valign="top"><strong>DateDiff()</strong></td><td valign="top"><strong>datetime1::date - datetime2::date</strong></td></tr><tr><td valign="top">(<strong>YEAR</strong> (lebenslauf.datum) ) AS "jahr"</td><td valign="top"><p><strong>date_part</strong>(<strong>'year'</strong>, lebenslauf.datum) AS "jahr"</p><p></p><p><strong>EXTRACT(YEAR FROM</strong> lebenslauf.datum) AS "jahr"</p></td></tr><tr><td valign="top"><p><strong>Case Insensitive Comparison</strong></p><p></p><p>SELECT * FROM users WHERE <strong>name = 'gast'</strong></p></td><td valign="top"><p><strong>Case Insensitive Comparison</strong></p><p></p><p>SELECT * FROM users WHERE <strong>name ILIKE 'gast'</strong></p></td></tr><tr><td valign="top"><p><strong>Delete with Join</strong></p><p> </p><p><strong>DELETE FROM dispo</strong> </p><p><strong>FROM belpos</strong> </p><p><strong>INNER JOIN art</strong> ON COALESCE(art.art,10) = 40 </p><p>WHERE </p><p>dispo.bel_typ in (2,8) </p><p>AND  dispo.bel_typ = belpos.bel_typ </p><p>AND dispo.bel_nr = belpos.bel_nr </p><p>AND dispo.belpos_nr = belpos.posnr </p><p>AND dispo.art_nr = art.artnr</p></td><td valign="top"><p><strong>Delete with Join:</strong> <em>PostgreSQL does not allow DELETE with JOIN or CROSS JOIN.</em></p><p></p><p><strong>DELETE FROM dispo</strong></p><p><strong>USING belpos  CROSS JOIN art</strong></p><p>WHERE dispo.bel_typ IN (2, 8)</p><p>AND dispo.bel_typ = belpos.bel_typ</p><p>AND dispo.bel_nr = belpos.bel_nr</p><p>AND dispo.belpos_nr = belpos.posnr</p><p>AND dispo.art_nr = art.artnr</p><p>AND COALESCE(art.art, 10) = 40; </p></td></tr><tr><td valign="top"><p><strong>LEFT OUTER JOIN</strong> </p><p><strong>RIGHT OUTER JOIN</strong></p></td><td valign="top"><p><strong>LEFT JOIN</strong> </p><p><strong>RIGHT JOIN</strong></p></td></tr></tbody></table>

<table><thead><tr><th valign="top">Tips</th></tr></thead><tbody><tr><td valign="top"><p>“FROM table1 CROSS JOIN table2” is identical to “FROM table1, table2”</p><p><em>Adrian’s advice is to use the CROSS-keyword</em></p></td></tr></tbody></table>

