NoPublic, bureaucrat, administrator
1,068
edits
Changes
no edit summary
Note that the columns cast(progress.startdate as date), cast(progress.enddate as date) and progress.areasize in the GROUP BY are optional.
{{Note | Although the PostgreSQL version can seem more "tedious" here, it is actually more correct and standard-compliant. In fact, it is important to specify all the groupings, otherwise the system (e.g. MySQL) will choose the remaining ones and apply some sorting to do so.}}
{{Important | When upgrading views and making such changes/adaptations regarding the GROUP BY, it is important to compare the result set in both versions!}}
The MySQL way of using the GROUP BY can also be emulated by the '''DISTINCT ON''' in PostgreSQL. The '''MySQL''' query
<pre>
SELECT a,b,c,d,e FROM table GROUP BY a
</pre>
can be translated into the '''PostgreSQL''' query
<pre>
SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c
</pre>
== Postgres Tips & Tricks ==__NOEDITSECTION__
The following is a collection of useful PostgreSQL queries using special functions.
=== String manipulations===
'''Concatenation'''
<pre>
SELECT hazard_localid||'----'||hazardname AS full_haz_desc
FROM hazard;
</pre>
'''Substring'''
<pre>
SELECT DISTINCT substr(hazreduc_localid, 1, 2)
FROM hazreduc;
</pre>
=== Rounding numeric values ===__NOEDITSECTION__
<pre>
SELECT round(areasize)
FROM hazard;
SELECT round(areasize, 2)
FROM hazard;
</pre>
=== Type conversions ===__NOEDITSECTION__
* to_char
* to_date
* to_timestamp
* to_number
* cast
<pre>
SELECT to_char(areasize, '9999999999')
FROM hazard;
SELECT cast(areasize as varchar)
FROM hazard;
</pre>
=== Query for duplicates ===__NOEDITSECTION__
<pre>
SELECT hazard_localid, count(*)
FROM hazard
GROUP BY hazard_localid
HAVING count(*) > 1;
</pre>
=== WITH clause ===__NOEDITSECTION__
Provides a very convenient way to write auxiliary statements for use in a larger query
<pre>
WITH my_subquery1 AS (
SELECT *
FROM a
), my_subquery2 AS (
SELECT *
FROM b
)
SELECT *
FROM my_subquery1
WHERE my_subquery1.x IN (SELECT x FROM my_subquery2);
</pre>
== References ==__NOEDITSECTION__
* PostgreSQL 1.9.4 documentation: http://www.postgresql.org/docs/9.1/static/index.html
