pages tagged postgresFeeding the Cloudhttps://feeding.cloud.geek.nz/tags/postgres/Feeding the Cloudikiwiki2021-06-11T20:43:57ZTaking the max/min of two columns in PostgreSQLhttps://feeding.cloud.geek.nz/posts/taking-maxmin-of-two-columns-in/
<a href="https://creativecommons.org/licenses/by-sa/4.0/">Creative Commons Attribution-ShareAlike 4.0 International License</a>
2021-06-11T20:43:57Z2010-10-11T01:00:00Z
<p>As part of a database <a href="http://www.postgresql.org/docs/9.0/static/sql-createview.html">view</a>, I found myself wanting to get <a href="http://www.postgresql.org/">Postgres</a> to display values from one of two columns, whichever was the largest.</p>
<p>My first attempt was:</p>
<pre>
SELECT id, <b>MAX</b>(column1, column2) FROM table1;
</pre>
<p>which of course didn't work because <code>MAX()</code> is an <a href="http://www.postgresql.org/docs/9.0/static/functions-aggregate.html">aggregate function</a> which only takes a single parameter.</p>
<p>What I was looking for instead, is something that was introduced in <a href="http://www.postgresql.org/docs/9.0/static/release-8-1.html#AEN109656">8.1</a>:</p>
<pre>
SELECT id, <b>GREATEST</b>(column1, column2) FROM table1;
</pre>
<p>where <code>GREATEST()</code> (and its opposite: <code>LEAST()</code>) is a <a href="http://www.postgresql.org/docs/9.0/static/functions-conditional.html">conditional expression</a>.</p>