Skip to content
Permalink
Branch: master
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
2323 lines (2312 sloc) 90.3 KB
<h1>
<a id="user-content-postgresql-views-and-functions" class="anchor" href="#postgresql-views-and-functions" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a><a href="https://www.postgresql.org" rel="nofollow">PostgreSQL</a> Views and Functions</h1>
<p>The functions and views published in this repository are some of those, which I
regularly need in my daily job.</p>
<p>As these procedures are sort of essential, at least to me, I install them in the
public schema. That way they are available for everyone without having to know
a schema name.</p>
<p>All functions and views are covered by tests. The tests are done in simple SQL
statements. Each test does raise division by zero if it fails.</p>
<p>The tests have been done with PostgreSQL 9.6 and 10. There have been some
changes in the behaviour in PostgreSQL 10, Timestamp and Date functions and
conversions have become more strict.</p>
<p>All examples have been done with PostgreSQL 10, differences in the behaviour of
previous versions are noted.</p>
<h1>
<a id="user-content-repositories" class="anchor" href="#repositories" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Repositories</h1>
<p>The main repository is now on
<a href="https://gitlab.com/sjstoelting/pgsql-tweaks.git" rel="nofollow">GitLab</a>. A mirror will stay on
<a href="https://github.com/sjstoelting/pgsql-tweaks">GitHub</a>.</p>
<p>If you discover any issue, please file them on
<a href="https://gitlab.com/sjstoelting/pgsql-tweaks/issues" rel="nofollow">https://gitlab.com/sjstoelting/pgsql-tweaks/issues</a>.</p>
<h1>
<a id="user-content-building-the-extension" class="anchor" href="#building-the-extension" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Building the extension</h1>
<p>All functions and tests are located in single files.</p>
<p>The files for the extension are build by the shell script "create-sql.sh".
To be able to run the script, you need to have a configuration with the
connection information. Please copy "build.cfg.example" to "build.cfg" and
change the configuration to fit your environment.<br>
The script assumes, that you have a
<a href="https://www.postgresql.org/docs/current/static/libpq-pgpass.html" rel="nofollow">.pgpass</a>
file with login information matching the configuration.</p>
<h1>
<a id="user-content-installation" class="anchor" href="#installation" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Installation</h1>
<p>You may either, install all functions as a package, or install single functions
of your choice.</p>
<h2>
<a id="user-content-install-the-package-from-source" class="anchor" href="#install-the-package-from-source" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Install the package from source</h2>
<p>Get the source by either, download the code as ZIP file, or by git clone.</p>
<div class="highlight highlight-source-shell"><pre><span class="pl-c1">cd</span> pgsql_tweaks
make install</pre></div>
<p>Afterwards you are able to create the extension in a database:</p>
<div class="highlight highlight-source-sql"><pre>CREATE EXTENSION pgsql_tweaks;</pre></div>
<h2>
<a id="user-content-install-the-package-with-pgxn" class="anchor" href="#install-the-package-with-pgxn" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Install the package with PGXN</h2>
<p>pgsql_tweaks is now available over the PostgreSQL extension management, <a href="https://pgxn.org/dist/pgsql_tweaks/" rel="nofollow">PGXN</a>.</p>
<p>The installation is done with the PGXN installer.</p>
<div class="highlight highlight-source-shell"><pre>pgxn install pgsql_tweaks</pre></div>
<p>Afterwards you are able to create the extension in a database:</p>
<h1>
<a id="user-content-table-of-content" class="anchor" href="#table-of-content" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Table of content</h1>
<p>1 <a href="#user-content-list-of-functions">List of functions</a></p>
<p>1.1 <a href="#user-content-functions-to-check-data-types">Functions to check data types</a><br>
1.1.1 <a href="#user-content-function-is_date">FUNCTION is_date</a><br>
1.1.2 <a href="#user-content-function-is_time">FUNCTION is_time</a><br>
1.1.3 <a href="#user-content-function-is_timestamp">FUNCTION is_timestamp</a><br>
1.1.4 <a href="#user-content-function-is_real">FUNCTION is_real</a><br>
1.1.5 <a href="#user-content-function-is_double_precision">FUNCTION is_double_precision</a><br>
1.1.6 <a href="#user-content-function-is_numeric">FUNCTION is_numeric</a><br>
1.1.7 <a href="#user-content-function-is_bigint">FUNCTION is_bigint</a><br>
1.1.8 <a href="#user-content-function-is_integer">FUNCTION is_integer</a><br>
1.1.9 <a href="#user-content-function-is_smallint">FUNCTION is_smallint</a><br>
1.1.10 <a href="#user-content-function-is_boolean">FUNCTION is_boolean</a><br>
1.1.11 <a href="#user-content-function-is_json">FUNCTION is_json</a><br>
1.1.12 <a href="#user-content-function-is_jsonb">FUNCTION is_jsonb</a><br>
1.1.13 <a href="#user-content-function-is_empty">FUNCTION is_empty</a></p>
<p>1.2 <a href="#user-content-functions-about-encryption">Functions about encryption</a><br>
1.2.1 <a href="#user-content-function-sha256">FUNCTION sha256</a></p>
<p>1.3 <a href="#user-content-functions-and-views-to-get-extended-system-information">Functions and views to get extended system information</a><br>
1.3.1 <a href="#user-content-function-pg_schema_size">FUNCTION pg_schema_size</a><br>
1.3.2 <a href="#user-content-view-pg_db_views">VIEW pg_db_views</a><br>
1.3.3 <a href="#user-content-view-pg_foreign_keys">VIEW pg_foreign_keys</a><br>
1.3.4 <a href="#user-content-view-pg_functions">VIEW pg_functions</a><br>
1.3.4 <a href="#user-content-view-pg_active_locks">VIEW pg_active_locks</a><br>
1.3.5 <a href="#user-content-view-pg_table_matview_infos">VIEW pg_table_matview_infos</a></p>
<p>1.4 <a href="#user-content-functions-about-encodings">Functions about encodings</a><br>
1.4.1 <a href="#user-content-function-is_encoding">FUNCTION is_encoding</a><br>
1.4.2 <a href="#user-content-function-is_latin1">FUNCTION is_latin1</a><br>
1.4.3 <a href="#user-content-function-return_not_part_of_latin1">FUNCTION return_not_part_of_latin1</a><br>
1.4.4 <a href="#user-content-function-replace_latin1">FUNCTION replace_latin1</a><br>
1.4.4.1 <a href="#user-content-replace_latin1-s-varchar-">replace_latin1(s VARCHAR)</a><br>
1.4.4.2 <a href="#user-content-replace_latin1-s-varchar-replacement-varchar-">replace_latin1(s VARCHAR, replacement VARCHAR)</a><br>
1.4.4.3 <a href="#user-content-replace_latin1-s-varchar-s_search-varchar-s_replace-varchar-">replace_latin1(s VARCHAR, s_search VARCHAR[], s_replace VARCHAR[])</a><br>
1.4.5 <a href="#user-content-function-return_not_part_of_encoding">FUNCTION return_not_part_of_encoding</a><br>
1.4.6 <a href="#user-content-function-replace_encoding">FUNCTION replace_encoding</a><br>
1.4.6.1 <a href="#user-content-replace_encoding-s-varchar-e-varchar-">replace_encoding(s VARCHAR, e VARCHAR)</a><br>
1.4.6.2 <a href="#user-content-replace_encoding-s-varchar-e-varchar-replacement-varchar-">replace_encoding(s VARCHAR, e VARCHAR, replacement VARCHAR)</a><br>
1.4.6.3 <a href="#user-content-replace_encoding-s-varchar-s_search-varchar-s_replace-varchar-">replace_encoding(s VARCHAR, s_search VARCHAR[], s_replace VARCHAR[])</a></p>
<p>1.5 <a href="#user-content-user-defined-aggregates">User defined aggregates</a><br>
1.5.1 <a href="#user-content-aggregate-gap_fill">AGGREGATE gap_fill</a><br>
1.5.2 <a href="#user-content-aggregate-array_min">AGGREGATE array_min</a><br>
1.5.3 <a href="#user-content-aggregate-array_max">AGGREGATE array_max</a><br>
1.5.4 <a href="#user-content-aggregate-array_avg">AGGREGATE array_avg</a><br>
1.5.5 <a href="#user-content-aggregate-array_sum">AGGREGATE array_sum</a></p>
<p>1.6 <a href="#user-content-format-functions">Format functions</a><br>
1.6.1 <a href="#user-content-function-date_de">FUNCTION date_de</a><br>
1.6.2 <a href="#user-content-function-datetime_de">FUNCTION datetime_de</a></p>
<p>1.7 <a href="#user-content-conversion-functions">Conversion functions</a><br>
1.7.1 <a href="#user-content-function-to_unix_timestamp">FUNCTION to_unix_timestamp</a></p>
<p>1.8 <a href="#user-content-other-functions">Other functions</a><br>
1.8.1 <a href="#user-content-function-array_trim">FUNCTION array_trim</a></p>
<h1>
<a id="user-content-list-of-functions" class="anchor" href="#list-of-functions" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>List of functions</h1>
<h2>
<a id="user-content-functions-to-check-data-types" class="anchor" href="#functions-to-check-data-types" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Functions to check data types</h2>
<h3>
<a id="user-content-function-is_date" class="anchor" href="#function-is_date" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_date</h3>
<p>The function checks strings for being a date.<br>
You might pass a second parameter to use a format string. Without the format,
the default format of PostgreSQL is used.</p>
<p>There has been a behaviour change in PostgreSQL 10. A conversion is now handled
strict, as in previous versions the conversion tried to calculate dates.</p>
<h4>
<a id="user-content-example-postgresql--10" class="anchor" href="#example-postgresql--10" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example PostgreSQL &lt; 10</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_date(<span class="pl-s"><span class="pl-pds">'</span>31.02.2018<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>DD.MM.YYYY<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Result is true because the conversion would return a valid result for</span>
<span class="pl-k">SELECT</span> to_date(<span class="pl-s"><span class="pl-pds">'</span>31.02.2018<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>DD.MM.YYYY<span class="pl-pds">'</span></span>);</pre></div>
<p>Result PostgreSQL 9.6 and previous versions:</p>
<table>
<thead>
<tr>
<th>to_date</th>
</tr>
</thead>
<tbody>
<tr>
<td>2018-03-03</td>
</tr>
</tbody>
</table>
<h4>
<a id="user-content-examples" class="anchor" href="#examples" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">/*</span>*</span>
<span class="pl-c"> * Parameter is in PostgreSQL default format</span>
<span class="pl-c"> <span class="pl-c">*/</span></span>
<span class="pl-k">SELECT</span> is_date(<span class="pl-s"><span class="pl-pds">'</span>2018-01-01<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_date(<span class="pl-s"><span class="pl-pds">'</span>2018-02-31<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">/*</span>*</span>
<span class="pl-c"> * Parameter is in PostgreSQL German format</span>
<span class="pl-c"> <span class="pl-c">*/</span></span>
<span class="pl-k">SELECT</span> is_date(<span class="pl-s"><span class="pl-pds">'</span>01.01.2018<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>DD.MM.YYYY<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_date(<span class="pl-s"><span class="pl-pds">'</span>31.02.2018<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>DD.MM.YYYY<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-is_time" class="anchor" href="#function-is_time" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_time</h3>
<p>The function checks strings for being a time.<br>
You might pass a second parameter to use a format string. Without the format,
the default format of PostgreSQL is used.</p>
<p>There has been a behaviour change in PostgreSQL 10. A conversion is now handled
strict, as in previous versions the conversion tried to calculate time.</p>
<h4>
<a id="user-content-example-postgresql--10-1" class="anchor" href="#example-postgresql--10-1" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example PostgreSQL &lt; 10</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_time(<span class="pl-s"><span class="pl-pds">'</span>25.33.55,456574<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>HH24.MI.SS,US<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Result is true because the conversion would return a valid result for</span>
<span class="pl-k">SELECT</span> to_timestamp(<span class="pl-s"><span class="pl-pds">'</span>25.33.55,456574<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>HH24.MI.SS,US<span class="pl-pds">'</span></span>)::<span class="pl-k">TIME</span>;</pre></div>
<p>Result PostgreSQL 9.6 and previous versions:</p>
<table>
<thead>
<tr>
<th>to_timestamp</th>
</tr>
</thead>
<tbody>
<tr>
<td>01:33:55.456574</td>
</tr>
</tbody>
</table>
<h4>
<a id="user-content-examples-1" class="anchor" href="#examples-1" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">/*</span>*</span>
<span class="pl-c"> * Parameter is in PostgreSQL default format</span>
<span class="pl-c"> <span class="pl-c">*/</span></span>
<span class="pl-k">SELECT</span> is_time(<span class="pl-s"><span class="pl-pds">'</span>14:33:55.456574<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_time(<span class="pl-s"><span class="pl-pds">'</span>25:33:55.456574<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">/*</span>*</span>
<span class="pl-c"> * Parameter is some time format</span>
<span class="pl-c"> <span class="pl-c">*/</span></span>
<span class="pl-k">SELECT</span> is_time(<span class="pl-s"><span class="pl-pds">'</span>14.33.55,456574<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>HH24.MI.SS,US<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_time(<span class="pl-s"><span class="pl-pds">'</span>25.33.55,456574<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>HH24.MI.SS,US<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-is_timestamp" class="anchor" href="#function-is_timestamp" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_timestamp</h3>
<p>The function checks strings for being a timestamp.<br>
You might pass a second parameter to use a format string. Without the format,
the default format of PostgreSQL is used.</p>
<p>There has been a behaviour change in PostgreSQL 10. A conversion is now handled
strict, as in previous versions the conversion tried to calculate a date.</p>
<h4>
<a id="user-content-example-postgresql--10-2" class="anchor" href="#example-postgresql--10-2" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example PostgreSQL &lt; 10</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_timestamp(<span class="pl-s"><span class="pl-pds">'</span>2018-01-01 25:00:00<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Result is true because the conversion would return a valid result for</span>
<span class="pl-k">SELECT</span> to_timestamp(<span class="pl-s"><span class="pl-pds">'</span>01.01.2018 25:00:00<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>DD.MM.YYYY HH24.MI.SS<span class="pl-pds">'</span></span>)::<span class="pl-k">TIMESTAMP</span>;</pre></div>
<p>Result PostgreSQL 9.6 and previous versions:</p>
<table>
<thead>
<tr>
<th>to_timestamp</th>
</tr>
</thead>
<tbody>
<tr>
<td>2018-01-02 01:00:00</td>
</tr>
</tbody>
</table>
<h4>
<a id="user-content-examples-2" class="anchor" href="#examples-2" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">/*</span>*</span>
<span class="pl-c"> * Parameter is in PostgreSQL default format</span>
<span class="pl-c"> <span class="pl-c">*/</span></span>
<span class="pl-k">SELECT</span> is_timestamp(<span class="pl-s"><span class="pl-pds">'</span>2018-01-01 00:00:00<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_timestamp(<span class="pl-s"><span class="pl-pds">'</span>2018-01-01 25:00:00<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Result is false in PostgreSQL &gt;= 10</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">/*</span>*</span>
<span class="pl-c"> * Parameter is in PostgreSQL German format</span>
<span class="pl-c"> <span class="pl-c">*/</span></span>
<span class="pl-k">SELECT</span> is_timestamp(<span class="pl-s"><span class="pl-pds">'</span>01.01.2018 00:00:00<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>DD.MM.YYYY HH24.MI.SS<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_timestamp(<span class="pl-s"><span class="pl-pds">'</span>01.01.2018 25:00:00<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>DD.MM.YYYY HH24.MI.SS<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-is_real" class="anchor" href="#function-is_real" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_real</h3>
<p>The function checks strings for being of data type REAL.</p>
<h4>
<a id="user-content-examples-3" class="anchor" href="#examples-3" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_real(<span class="pl-s"><span class="pl-pds">'</span>123.456<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_real(<span class="pl-s"><span class="pl-pds">'</span>123,456<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Result is false</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-is_double_precision" class="anchor" href="#function-is_double_precision" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_double_precision</h3>
<p>The function checks strings for being of data type DOUBLE PRECISION.</p>
<h4>
<a id="user-content-examples-4" class="anchor" href="#examples-4" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_double_precision(<span class="pl-s"><span class="pl-pds">'</span>123.456<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_double_precision(<span class="pl-s"><span class="pl-pds">'</span>123,456<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Result is false</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-is_numeric" class="anchor" href="#function-is_numeric" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_numeric</h3>
<p>The function checks strings for being of data type NUMERIC.</p>
<h4>
<a id="user-content-examples-5" class="anchor" href="#examples-5" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_numeric(<span class="pl-s"><span class="pl-pds">'</span>123<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_numeric(<span class="pl-s"><span class="pl-pds">'</span>1 2<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Result is false</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-is_bigint" class="anchor" href="#function-is_bigint" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_bigint</h3>
<p>The function checks strings for being of data type BIGINT.</p>
<h4>
<a id="user-content-examples-6" class="anchor" href="#examples-6" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_bigint(<span class="pl-s"><span class="pl-pds">'</span>3243546343<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Result is true</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_bigint(<span class="pl-s"><span class="pl-pds">'</span>123.456<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Result is false</span></pre></div>
<h3>
<a id="user-content-function-is_integer" class="anchor" href="#function-is_integer" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_integer</h3>
<p>The function checks strings for being of data type INTEGER.</p>
<h4>
<a id="user-content-examples-7" class="anchor" href="#examples-7" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_integer(<span class="pl-s"><span class="pl-pds">'</span>123<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Result is true</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_integer(<span class="pl-s"><span class="pl-pds">'</span>123.456<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Result is false</span></pre></div>
<h3>
<a id="user-content-function-is_smallint" class="anchor" href="#function-is_smallint" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_smallint</h3>
<p>The function checks strings for being of data type SMALLINT.</p>
<h4>
<a id="user-content-examples-8" class="anchor" href="#examples-8" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_smallint(<span class="pl-s"><span class="pl-pds">'</span>123<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Result is true</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_smallint(<span class="pl-s"><span class="pl-pds">'</span>123.456<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Result is false</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-is_boolean" class="anchor" href="#function-is_boolean" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_boolean</h3>
<p>The function checks a string variable for containing valid BOOLEAN values.</p>
<table>
<thead>
<tr>
<th>boolean strings</th>
</tr>
</thead>
<tbody>
<tr>
<td>t</td>
</tr>
<tr>
<td>f</td>
</tr>
<tr>
<td>T</td>
</tr>
<tr>
<td>F</td>
</tr>
<tr>
<td>y</td>
</tr>
<tr>
<td>n</td>
</tr>
<tr>
<td>Y</td>
</tr>
<tr>
<td>N</td>
</tr>
<tr>
<td>true</td>
</tr>
<tr>
<td>false</td>
</tr>
<tr>
<td>TRUE</td>
</tr>
<tr>
<td>FALSE</td>
</tr>
<tr>
<td>yes</td>
</tr>
<tr>
<td>no</td>
</tr>
<tr>
<td>YES</td>
</tr>
<tr>
<td>NO</td>
</tr>
<tr>
<td>0</td>
</tr>
<tr>
<td>1</td>
</tr>
</tbody>
</table>
<h4>
<a id="user-content-examples-9" class="anchor" href="#examples-9" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_boolean(<span class="pl-s"><span class="pl-pds">'</span>t<span class="pl-pds">'</span></span>);
<span class="pl-c"><span class="pl-c">--</span> Result is true</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_boolean(<span class="pl-s"><span class="pl-pds">'</span>F<span class="pl-pds">'</span></span>);
<span class="pl-c"><span class="pl-c">--</span> Result is true</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_boolean(<span class="pl-s"><span class="pl-pds">'</span>True<span class="pl-pds">'</span></span>);
<span class="pl-c"><span class="pl-c">--</span> Result is true</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_boolean(<span class="pl-s"><span class="pl-pds">'</span>False<span class="pl-pds">'</span></span>);
<span class="pl-c"><span class="pl-c">--</span> Result is true</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_boolean(<span class="pl-s"><span class="pl-pds">'</span>0<span class="pl-pds">'</span></span>);
<span class="pl-c"><span class="pl-c">--</span> Result is true</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_boolean(<span class="pl-s"><span class="pl-pds">'</span>1<span class="pl-pds">'</span></span>);
<span class="pl-c"><span class="pl-c">--</span> Result is true</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_boolean(<span class="pl-s"><span class="pl-pds">'</span>-1<span class="pl-pds">'</span></span>);
<span class="pl-c"><span class="pl-c">--</span> Result is false</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-is_json" class="anchor" href="#function-is_json" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_json</h3>
<p>The function checks a string variable for containing a valid JSON.</p>
<h4>
<a id="user-content-examples-10" class="anchor" href="#examples-10" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_json(<span class="pl-s"><span class="pl-pds">'</span>{"review": {"date": "1970-12-30", "votes": 10, "rating": 5, "helpful_votes": 0}, "product": {"id": "1551803542", "group": "Book", "title": "Start and Run a Coffee Bar (Start &amp; Run a)", "category": "Business &amp; Investing", "sales_rank": 11611, "similar_ids": ["0471136174", "0910627312", "047112138X", "0786883561", "0201570483"], "subcategory": "General"}, "customer_id": "AE22YDHSBFYIP"}<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_json(<span class="pl-s"><span class="pl-pds">'</span>Not a JSON<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-is_jsonb" class="anchor" href="#function-is_jsonb" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_jsonb</h3>
<p>The function checks a string variable for containing a valid JSONB.</p>
<h4>
<a id="user-content-example" class="anchor" href="#example" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_jsonb(<span class="pl-s"><span class="pl-pds">'</span>{"review": {"date": "1970-12-30", "votes": 10, "rating": 5, "helpful_votes": 0}, "product": {"id": "1551803542", "group": "Book", "title": "Start and Run a Coffee Bar (Start &amp; Run a)", "category": "Business &amp; Investing", "sales_rank": 11611, "similar_ids": ["0471136174", "0910627312", "047112138X", "0786883561", "0201570483"], "subcategory": "General"}, "customer_id": "AE22YDHSBFYIP"}<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_jsonb(<span class="pl-s"><span class="pl-pds">'</span>Not a JSONB<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-is_empty" class="anchor" href="#function-is_empty" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_empty</h3>
<p>The function checks a string variable for being either, NULL or ''.</p>
<h4>
<a id="user-content-examples-11" class="anchor" href="#examples-11" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_empty(<span class="pl-s"><span class="pl-pds">'</span>abc<span class="pl-pds">'</span></span>);
<span class="pl-c"><span class="pl-c">--</span> Result is false</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_empty(<span class="pl-s"><span class="pl-pds">'</span><span class="pl-pds">'</span></span>);
<span class="pl-c"><span class="pl-c">--</span> Result is true</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_empty(<span class="pl-k">NULL</span>);
<span class="pl-c"><span class="pl-c">--</span> Result is true</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<h2>
<a id="user-content-functions-about-encryption" class="anchor" href="#functions-about-encryption" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Functions about encryption</h2>
<h3>
<a id="user-content-function-sha256" class="anchor" href="#function-sha256" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION sha256</h3>
<p>Creates a function which returns a SHA256 hash for the given string.<br>
The parameter has to be converted into a binary string of <a href="https://www.postgresql.org/docs/current/static/datatype-binary.html" rel="nofollow">bytea</a>.
<g-emoji class="g-emoji" alias="exclamation" fallback-src="https://github.githubassets.com/images/icons/emoji/unicode/2757.png">❗️</g-emoji><span>The function needs the <a href="https://www.postgresql.org/docs/current/static/pgcrypto.html" rel="nofollow">pgcrypto</a> package</span><g-emoji class="g-emoji" alias="exclamation" fallback-src="https://github.githubassets.com/images/icons/emoji/unicode/2757.png">❗️</g-emoji></p>
<p><g-emoji class="g-emoji" alias="exclamation" fallback-src="https://github.githubassets.com/images/icons/emoji/unicode/2757.png">❗️</g-emoji>This function has an external dependency and is only installed, if the package pgcrypto is installed<g-emoji class="g-emoji" alias="exclamation" fallback-src="https://github.githubassets.com/images/icons/emoji/unicode/2757.png">❗️</g-emoji></p>
<h4>
<a id="user-content-example-1" class="anchor" href="#example-1" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> sha256(<span class="pl-s"><span class="pl-pds">'</span>test-string<span class="pl-pds">'</span></span>::<span class="pl-k">bytea</span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">ffe65f1d98fafedea3514adc956c8ada5980c6c5d2552fd61f48401aefd5c00e</td>
</tr>
</tbody>
</table>
<h2>
<a id="user-content-functions-and-views-to-get-extended-system-information" class="anchor" href="#functions-and-views-to-get-extended-system-information" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Functions and views to get extended system information</h2>
<h3>
<a id="user-content-function-pg_schema_size" class="anchor" href="#function-pg_schema_size" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION pg_schema_size</h3>
<p>The function returns the size for schema given as parameter in bytes.</p>
<h4>
<a id="user-content-examples-12" class="anchor" href="#examples-12" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">--</span> Returns the size of the schema public in bytes</span>
<span class="pl-k">SELECT</span> pg_schema_size(<span class="pl-s"><span class="pl-pds">'</span>public<span class="pl-pds">'</span></span>);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">pg_schema_size</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">348536832</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">--</span> Returns the size of the schema public formatted</span>
<span class="pl-k">SELECT</span> pg_size_pretty(pg_schema_size(<span class="pl-s"><span class="pl-pds">'</span>public<span class="pl-pds">'</span></span>));</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">pg_schema_size</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">332 MB</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-view-pg_db_views" class="anchor" href="#view-pg_db_views" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>VIEW pg_db_views</h3>
<p>Creates a view to get all views of the current database but excluding system views and all views which do start with "pg" or "_pg".</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> <span class="pl-k">*</span> <span class="pl-k">FROM</span> pg_db_views;</pre></div>
<table>
<thead>
<tr>
<th>view_catalog</th>
<th>view_schema</th>
<th>view_name</th>
<th>view_definition</th>
</tr>
</thead>
<tbody>
<tr>
<td>chinook</td>
<td>public</td>
<td>v_json_artist_data</td>
<td>WITH tracks AS (</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>SELECT "Track"."AlbumId" AS album_id,</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>"Track"."TrackId" AS track_id,</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>"Track"."Name" AS track_name,</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>"Track"."MediaTypeId" AS media_type_id,</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>"Track"."Milliseconds" AS milliseconds,</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>"Track"."UnitPrice" AS unit_price</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>FROM "Track"</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>), json_tracks AS (</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>SELECT row_to_json(tracks.*) AS tracks</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>FROM tracks</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>), albums AS (</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>SELECT a."ArtistId" AS artist_id,</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>a."AlbumId" AS album_id,</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>a."Title" AS album_title,</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>array_agg(t.tracks) AS album_tracks</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>FROM ("Album" a</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>JOIN json_tracks t ON ((a."AlbumId" = ((t.tracks -&gt;&gt; 'album_id'::text))::integer)))</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>GROUP BY a."ArtistId", a."AlbumId", a."Title"</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>), json_albums AS (</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>SELECT albums.artist_id,</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>array_agg(row_to_json(albums.*)) AS album</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>FROM albums</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>GROUP BY albums.artist_id</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>), artists AS (</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>SELECT a."ArtistId" AS artist_id,</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>a."Name" AS artist,</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>jsa.album AS albums</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>FROM ("Artist" a</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>JOIN json_albums jsa ON ((a."ArtistId" = jsa.artist_id)))</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>)</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>SELECT (row_to_json(artists.*))::jsonb AS artist_data</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td>FROM artists;</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-view-pg_foreign_keys" class="anchor" href="#view-pg_foreign_keys" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>VIEW pg_foreign_keys</h3>
<p>Creates a view to get all views of the current database but excluding system views and all views which do start with "pg" or "_pg".</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> <span class="pl-k">*</span> <span class="pl-k">FROM</span> pg_foreign_keys;</pre></div>
<table>
<thead>
<tr>
<th>table_catalog</th>
<th>table_schema</th>
<th>table_name</th>
<th>column_name</th>
<th>foreign_table_name</th>
<th>foreign_column_name</th>
</tr>
</thead>
<tbody>
<tr>
<td>chinook</td>
<td>public</td>
<td>Album</td>
<td>ArtistId</td>
<td>Artist</td>
<td>ArtistId</td>
</tr>
<tr>
<td>chinook</td>
<td>public</td>
<td>Customer</td>
<td>SupportRepId</td>
<td>Employee</td>
<td>EmployeeId</td>
</tr>
<tr>
<td>chinook</td>
<td>public</td>
<td>Employee</td>
<td>ReportsTo</td>
<td>Employee</td>
<td>EmployeeId</td>
</tr>
<tr>
<td>chinook</td>
<td>public</td>
<td>Invoice</td>
<td>CustomerId</td>
<td>Customer</td>
<td>CustomerId</td>
</tr>
<tr>
<td>chinook</td>
<td>public</td>
<td>InvoiceLine</td>
<td>InvoiceId</td>
<td>Invoice</td>
<td>InvoiceId</td>
</tr>
<tr>
<td>chinook</td>
<td>public</td>
<td>InvoiceLine</td>
<td>TrackId</td>
<td>Track</td>
<td>TrackId</td>
</tr>
<tr>
<td>chinook</td>
<td>public</td>
<td>PlaylistTrack</td>
<td>PlaylistId</td>
<td>Playlist</td>
<td>PlaylistId</td>
</tr>
<tr>
<td>chinook</td>
<td>public</td>
<td>PlaylistTrack</td>
<td>TrackId</td>
<td>Track</td>
<td>TrackId</td>
</tr>
<tr>
<td>chinook</td>
<td>public</td>
<td>Track</td>
<td>AlbumId</td>
<td>Album</td>
<td>AlbumId</td>
</tr>
<tr>
<td>chinook</td>
<td>public</td>
<td>Track</td>
<td>GenreId</td>
<td>Genre</td>
<td>GenreId</td>
</tr>
<tr>
<td>chinook</td>
<td>public</td>
<td>Track</td>
<td>MediaTypeId</td>
<td>MediaType</td>
<td>MediaTypeId</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-view-pg_functions" class="anchor" href="#view-pg_functions" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>VIEW pg_functions</h3>
<p>Creates a view to get all functions of the current database, excluding those in the schema pg_catalog and information_schema.</p>
<p>As there have been changes to the system tables used in this view, there are now two scripts dependend on the PostgreSQL version on which it has to be used,
one for PostgreSQL 11 or newer and one for PostgreSQL 10 or older. This is handled in the script that creates the view.</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> <span class="pl-k">*</span> <span class="pl-k">FROM</span> pg_functions;</pre></div>
<table>
<thead>
<tr>
<th>schema_name</th>
<th>function_name</th>
<th>returning_data_type</th>
<th>parameters</th>
<th>function_type</th>
<th>function_comment</th>
</tr>
</thead>
<tbody>
<tr>
<td>public</td>
<td>date_de</td>
<td>character varying</td>
<td>d date</td>
<td>function</td>
<td>Creates a function which returns the given date in German format</td>
</tr>
<tr>
<td>public</td>
<td>datetime_de</td>
<td>character varying</td>
<td>t timestamp without time zone</td>
<td>function</td>
<td>Creates a function which returns the given timestamp in German format</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-view-pg_active_locks" class="anchor" href="#view-pg_active_locks" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>VIEW pg_active_locks</h3>
<p>Creates a view to view all live locks with all necessary information about the connections and the query.<br>
<span>The view needs PostgreSQL 9.2 as minimum version. The column application_name was added in 9.2.</span></p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> <span class="pl-k">*</span> <span class="pl-k">FROM</span> pg_active_locks;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">pid</th>
<th>state</th>
<th>datname</th>
<th>usename</th>
<th>application_name</th>
<th>client_addr</th>
<th>query_start</th>
<th>wait_event_type</th>
<th>wait_event</th>
<th>locktype</th>
<th>mode</th>
<th>query</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">8872</td>
<td>active</td>
<td>chinook</td>
<td>stefanie</td>
<td>psql</td>
<td>127.0.0.1</td>
<td>2018-02-18 14:45:53.943047+01</td>
<td></td>
<td></td>
<td>relation</td>
<td>AccessShareLock</td>
<td>SELECT * FROM pg_active_locks;</td>
</tr>
<tr>
<td align="right">8872</td>
<td>active</td>
<td>chinook</td>
<td>stefanie</td>
<td>psql</td>
<td>127.0.0.1</td>
<td>2018-02-18 14:45:53.943047+01</td>
<td></td>
<td></td>
<td>virtualxid</td>
<td>ExclusiveLock</td>
<td>SELECT * FROM pg_active_locks;</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-view-pg_table_matview_infos" class="anchor" href="#view-pg_table_matview_infos" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>VIEW pg_table_matview_infos</h3>
<p>Creates a view with information about the size of the table/materialized view and sizes of indexes on that table/materialized view.
It does also list all indexes on that table in an array.</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> <span class="pl-k">*</span> <span class="pl-k">FROM</span> pg_table_matview_infos;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>type</th>
<th>schemaname</th>
<th>tablename</th>
<th>tableowner</th>
<th>tablespace</th>
<th>indexes</th>
<th align="right">table_size</th>
<th align="right">indexes_size</th>
<th align="right">total_relation_size</th>
<th align="right">table_size_pretty</th>
<th align="right">indexes_size_pretty</th>
<th align="right">total_relation_size_pretty</th>
</tr>
</thead>
<tbody>
<tr>
<td>table</td>
<td>public</td>
<td>MediaType</td>
<td>stefanie</td>
<td>[NULL]</td>
<td>{PK_MediaType}</td>
<td align="right">8192</td>
<td align="right">16384</td>
<td align="right">24576</td>
<td align="right">8192 bytes</td>
<td align="right">16 kB</td>
<td align="right">24 kB</td>
</tr>
<tr>
<td>table</td>
<td>public</td>
<td>Playlist</td>
<td>stefanie</td>
<td>[NULL]</td>
<td>{PK_Playlist}</td>
<td align="right">8192</td>
<td align="right">16384</td>
<td align="right">24576</td>
<td align="right">8192 bytes</td>
<td align="right">16 kB</td>
<td align="right">24 kB</td>
</tr>
</tbody>
</table>
<h2>
<a id="user-content-functions-about-encodings" class="anchor" href="#functions-about-encodings" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Functions about encodings</h2>
<h3>
<a id="user-content-function-is_encoding" class="anchor" href="#function-is_encoding" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_encoding</h3>
<p>The function checks if all characters are in included in a given encoding.
That is especially useful, if you have to deal with exports into other
encodings than the database encoding.</p>
<p>The function with two parameters uses UTF-8 as source encoding,<br>
The one with three parameters uses the third parameter as source encoding.</p>
<h4>
<a id="user-content-examples-13" class="anchor" href="#examples-13" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_encoding(<span class="pl-s"><span class="pl-pds">'</span>Some characters<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>LATIN1<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_encoding(<span class="pl-s"><span class="pl-pds">'</span>Some characters, ğ is Turkish and not latin1<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>LATIN1<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Returns false: The Turkish character ğ is not part of latin1</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_encoding(<span class="pl-s"><span class="pl-pds">'</span>Some characters<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>LATIN1<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>UTF8<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_encoding(<span class="pl-s"><span class="pl-pds">'</span>Some characters, ğ is Turkish and not latin1<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>UTF8<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>LATIN1<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Returns false: The Turkish character ğ is not part of latin1</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-is_latin1" class="anchor" href="#function-is_latin1" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION is_latin1</h3>
<p>The function is a shortcut for is_encoding('Some characters', 'LATIN1'), you
don't have to give the target encoding.</p>
<h4>
<a id="user-content-examples-14" class="anchor" href="#examples-14" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_latin1(<span class="pl-s"><span class="pl-pds">'</span>Some characters<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">t</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> is_latin1(<span class="pl-s"><span class="pl-pds">'</span>Some characters, ğ is Turkish and not latin1<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> Returns false: The Turkish character ğ is not part of latin1</span></pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">res</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">f</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-return_not_part_of_latin1" class="anchor" href="#function-return_not_part_of_latin1" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION return_not_part_of_latin1</h3>
<p>The function returns a distinct array containing all characters, which are not defined in latin1.<br>
The function <span>depends on is_latin1</span> which is part of this repository.</p>
<h4>
<a id="user-content-example-2" class="anchor" href="#example-2" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">--</span> Returns an array containing the characters ğ and Ƶ each one time</span>
<span class="pl-k">SELECT</span> return_not_part_of_latin1(<span class="pl-s"><span class="pl-pds">'</span>ağbƵcğeƵ<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>res</th>
</tr>
</thead>
<tbody>
<tr>
<td>{ğ,Ƶ}</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-replace_latin1" class="anchor" href="#function-replace_latin1" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION replace_latin1</h3>
<p>The function has three implementations. All implementations <span>depend on the function is_latin1</span>, the function is included in this repository.</p>
<h4>
<a id="user-content-replace_latin1s-varchar" class="anchor" href="#replace_latin1s-varchar" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>replace_latin1(s VARCHAR)</h4>
<p>The function takes one parameter with characters to be checked and replaced with
an empty string, if they are not part of latin1.</p>
<h5>
<a id="user-content-example-3" class="anchor" href="#example-3" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example</h5>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> replace_latin1(<span class="pl-s"><span class="pl-pds">'</span>Some characters, ğ is Turkish and not latin1<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>res</th>
</tr>
</thead>
<tbody>
<tr>
<td>Some characters, is Turkish and not latin1</td>
</tr>
</tbody>
</table>
<h4>
<a id="user-content-replace_latin1s-varchar-replacement-varchar" class="anchor" href="#replace_latin1s-varchar-replacement-varchar" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>replace_latin1(s VARCHAR, replacement VARCHAR)</h4>
<p>The function takes a second parameter which is used to replace <em>all</em> characters,
which are not part of latin1.</p>
<h5>
<a id="user-content-example-4" class="anchor" href="#example-4" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example</h5>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> replace_latin1(
<span class="pl-s"><span class="pl-pds">'</span>Some characters, ğ is Turkish and not latin1 and replaced with a g<span class="pl-pds">'</span></span>,
<span class="pl-s"><span class="pl-pds">'</span>g<span class="pl-pds">'</span></span>
) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>res</th>
</tr>
</thead>
<tbody>
<tr>
<td>Some characters, g is Turkish and not latin1 and replaced with a g</td>
</tr>
</tbody>
</table>
<h4>
<a id="user-content-replace_latin1s-varchar-s_search-varchar-s_replace-varchar" class="anchor" href="#replace_latin1s-varchar-s_search-varchar-s_replace-varchar" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>replace_latin1(s VARCHAR, s_search VARCHAR[], s_replace VARCHAR[])</h4>
<p>The function takes as first parameter a string which may or may not have none
latin1 characters. The second parameter is an arrays containing all characters,
that should be replaced. The third parameter is an array, too. The characters
defined in s_search are replaced with the characters in s_replace, it takes the
position in the array to identify which character should be replaced by which
one.</p>
<h5>
<a id="user-content-example-5" class="anchor" href="#example-5" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example</h5>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">--</span> First identify the characters which should be replaced, which are {ğ,Ƶ}</span>
<span class="pl-k">SELECT</span> return_not_part_of_latin1(<span class="pl-s"><span class="pl-pds">'</span>ağbƵcğeƵ<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> The ğ will be replaced whit a g and the Ƶ with a Z}</span>
<span class="pl-k">SELECT</span> <span class="pl-s"><span class="pl-pds">'</span>ağbƵcğeƵ<span class="pl-pds">'</span></span> <span class="pl-k">AS</span> original
, replace_latin1(
<span class="pl-s"><span class="pl-pds">'</span>ağbƵcğeƵ<span class="pl-pds">'</span></span>,
string_to_array(<span class="pl-s"><span class="pl-pds">'</span>ğ,Ƶ<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>,<span class="pl-pds">'</span></span>),
string_to_array(<span class="pl-s"><span class="pl-pds">'</span>g,Z<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>,<span class="pl-pds">'</span></span>)
) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>original</th>
<th>res</th>
</tr>
</thead>
<tbody>
<tr>
<td>ağbƵcğeƵ</td>
<td>agbZcgeZ</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-return_not_part_of_encoding" class="anchor" href="#function-return_not_part_of_encoding" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION return_not_part_of_encoding</h3>
<p>The function returns a distinct array containing all characters, which are not defined in the second parameter as encoding.<br>
The function <span>depends on is_encoding</span> which is part of this repository.</p>
<h4>
<a id="user-content-example-6" class="anchor" href="#example-6" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">--</span> Returns an array containing the characters ğ and Ƶ each one time</span>
<span class="pl-k">SELECT</span> return_not_part_of_encoding(<span class="pl-s"><span class="pl-pds">'</span>ağbƵcğeƵ<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>latin1<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>res</th>
</tr>
</thead>
<tbody>
<tr>
<td>{ğ,Ƶ}</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-function-replace_encoding" class="anchor" href="#function-replace_encoding" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION replace_encoding</h3>
<p>The function has three implementations. All implementations <span>depend on the function is_encoding</span>, the function is included in this repository.</p>
<h4>
<a id="user-content-replace_encodings-varchar-e-varchar" class="anchor" href="#replace_encodings-varchar-e-varchar" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>replace_encoding(s VARCHAR, e VARCHAR)</h4>
<p>The function takes one parameter with characters to be checked and replaced with
an empty string, if they are not part of the encoding given in the second
parameter.</p>
<h5>
<a id="user-content-example-7" class="anchor" href="#example-7" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example</h5>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> replace_encoding(
<span class="pl-s"><span class="pl-pds">'</span>Some characters, ğ is Turkish and not latin1<span class="pl-pds">'</span></span>,
<span class="pl-s"><span class="pl-pds">'</span>latin1<span class="pl-pds">'</span></span>
) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>res</th>
</tr>
</thead>
<tbody>
<tr>
<td>Some characters, is Turkish and not latin1</td>
</tr>
</tbody>
</table>
<h4>
<a id="user-content-replace_encodings-varchar-e-varchar-replacement-varchar" class="anchor" href="#replace_encodings-varchar-e-varchar-replacement-varchar" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>replace_encoding(s VARCHAR, e VARCHAR, replacement VARCHAR)</h4>
<p>The function takes a third parameter which is used to replace <em>all</em> characters
which are not part of the encoding given in parameter 2.</p>
<h5>
<a id="user-content-example-8" class="anchor" href="#example-8" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example</h5>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> replace_encoding(
<span class="pl-s"><span class="pl-pds">'</span>Some characters, ğ is Turkish and not latin1 and replaced with a g<span class="pl-pds">'</span></span>,
<span class="pl-s"><span class="pl-pds">'</span>latin1<span class="pl-pds">'</span></span>,
<span class="pl-s"><span class="pl-pds">'</span>g<span class="pl-pds">'</span></span>
) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>res</th>
</tr>
</thead>
<tbody>
<tr>
<td>Some characters, g is Turkish and not latin1 and replaced with a g</td>
</tr>
</tbody>
</table>
<h4>
<a id="user-content-replace_encodings-varchar-s_search-varchar-s_replace-varchar" class="anchor" href="#replace_encodings-varchar-s_search-varchar-s_replace-varchar" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>replace_encoding(s VARCHAR, s_search VARCHAR[], s_replace VARCHAR[])</h4>
<p>The function takes as first parameter a string which may or may not have none
latin1 characters. The second parameter is an arrays containing all characters,
that should be replaced. The third parameter is an array, too. The characters
defined in s_search are replaced with the characters in s_replace, it takes the
position in the array to identify which character should be replaced by which
one.</p>
<h5>
<a id="user-content-example-9" class="anchor" href="#example-9" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example</h5>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">--</span> First identify the characters which should be replaced, which are {ğ,Ƶ}</span>
<span class="pl-k">SELECT</span> return_not_part_of_latin1(<span class="pl-s"><span class="pl-pds">'</span>ağbƵcğeƵ<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> res;
<span class="pl-c"><span class="pl-c">--</span> The ğ will be replaced whit a g and the Ƶ with a Z}</span>
<span class="pl-k">SELECT</span> <span class="pl-s"><span class="pl-pds">'</span>ağbƵcğeƵ<span class="pl-pds">'</span></span> <span class="pl-k">AS</span> original
, replace_encoding(
<span class="pl-s"><span class="pl-pds">'</span>ağbƵcğeƵ<span class="pl-pds">'</span></span>,
string_to_array(<span class="pl-s"><span class="pl-pds">'</span>ğ,Ƶ<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>,<span class="pl-pds">'</span></span>),
string_to_array(<span class="pl-s"><span class="pl-pds">'</span>g,Z<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>,<span class="pl-pds">'</span></span>)
) <span class="pl-k">AS</span> res;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>original</th>
<th>res</th>
</tr>
</thead>
<tbody>
<tr>
<td>ağbƵcğeƵ</td>
<td>agbZcgeZ</td>
</tr>
</tbody>
</table>
<h2>
<a id="user-content-user-defined-aggregates" class="anchor" href="#user-defined-aggregates" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>User defined aggregates</h2>
<h3>
<a id="user-content-aggregate-gap_fill" class="anchor" href="#aggregate-gap_fill" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>AGGREGATE gap_fill</h3>
<p>The aggregate is used in <a href="https://www.postgresql.org/docs/current/static/tutorial-window.html" rel="nofollow">Window Functions</a>
to show the last value in case the current value is null.</p>
<h4>
<a id="user-content-example-10" class="anchor" href="#example-10" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">BEGIN</span>;
<span class="pl-k">CREATE</span> <span class="pl-k">TABLE</span> <span class="pl-en">test_gap_fill</span>(id <span class="pl-k">INTEGER</span>, some_value <span class="pl-k">VARCHAR</span>);
<span class="pl-k">INSERT INTO</span> test_gap_fill(id, some_value) <span class="pl-k">VALUES</span>
(<span class="pl-c1">1</span>, <span class="pl-s"><span class="pl-pds">'</span>value 1<span class="pl-pds">'</span></span>),
(<span class="pl-c1">1</span>, <span class="pl-k">NULL</span>),
(<span class="pl-c1">2</span>, <span class="pl-s"><span class="pl-pds">'</span>value 2<span class="pl-pds">'</span></span>),
(<span class="pl-c1">2</span>, <span class="pl-k">NULL</span>),
(<span class="pl-c1">2</span>, <span class="pl-k">NULL</span>),
(<span class="pl-c1">3</span>, <span class="pl-s"><span class="pl-pds">'</span>value 3<span class="pl-pds">'</span></span>)
;
<span class="pl-k">SELECT</span> id
, some_value
<span class="pl-k">FROM</span> test_gap_fill
;
<span class="pl-k">ROLLBACK</span>;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">id</th>
<th>some_value</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">1</td>
<td>value 1</td>
</tr>
<tr>
<td align="right">1</td>
<td></td>
</tr>
<tr>
<td align="right">2</td>
<td>value 2</td>
</tr>
<tr>
<td align="right">2</td>
<td></td>
</tr>
<tr>
<td align="right">2</td>
<td></td>
</tr>
<tr>
<td align="right">3</td>
<td>value 3</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">BEGIN</span>;
<span class="pl-k">CREATE</span> <span class="pl-k">TABLE</span> <span class="pl-en">test_gap_fill</span>(id <span class="pl-k">INTEGER</span>, some_value <span class="pl-k">VARCHAR</span>);
<span class="pl-k">INSERT INTO</span> test_gap_fill(id, some_value) <span class="pl-k">VALUES</span>
(<span class="pl-c1">1</span>, <span class="pl-s"><span class="pl-pds">'</span>value 1<span class="pl-pds">'</span></span>),
(<span class="pl-c1">1</span>, <span class="pl-k">NULL</span>),
(<span class="pl-c1">2</span>, <span class="pl-s"><span class="pl-pds">'</span>value 2<span class="pl-pds">'</span></span>),
(<span class="pl-c1">2</span>, <span class="pl-k">NULL</span>),
(<span class="pl-c1">2</span>, <span class="pl-k">NULL</span>),
(<span class="pl-c1">3</span>, <span class="pl-s"><span class="pl-pds">'</span>value 3<span class="pl-pds">'</span></span>)
;
<span class="pl-c"><span class="pl-c">--</span> Fill the empty rows with values</span>
<span class="pl-k">SELECT</span> id
, gap_fill(some_value) OVER (<span class="pl-k">ORDER BY</span> id) <span class="pl-k">AS</span> some_value
<span class="pl-k">FROM</span> test_gap_fill
;
<span class="pl-k">ROLLBACK</span>;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">id</th>
<th>some_value</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">1</td>
<td>value 1</td>
</tr>
<tr>
<td align="right">1</td>
<td>value 1</td>
</tr>
<tr>
<td align="right">2</td>
<td>value 2</td>
</tr>
<tr>
<td align="right">2</td>
<td>value 2</td>
</tr>
<tr>
<td align="right">2</td>
<td>value 2</td>
</tr>
<tr>
<td align="right">3</td>
<td>value 3</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-aggregate-array_min" class="anchor" href="#aggregate-array_min" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>AGGREGATE array_min</h3>
<p>Calculate minimum values from arrays.</p>
<p>Supported data types are SMALLINT, INTEGER, BIGINT, REAL, DOUBLE PRECISION, NUMERIC, and TEXT;</p>
<h4>
<a id="user-content-examples-15" class="anchor" href="#examples-15" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_min(ARRAY[<span class="pl-c1">45</span>, <span class="pl-c1">60</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>]::<span class="pl-k">SMALLINT</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_min</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">43</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_min(ARRAY[<span class="pl-c1">45</span>, <span class="pl-c1">60</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>]::<span class="pl-k">INTEGER</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_min</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">43</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_min(ARRAY[<span class="pl-c1">45</span>, <span class="pl-c1">60</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>]::<span class="pl-k">BIGINT</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_min</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">43</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_min(ARRAY[<span class="pl-c1">45</span>.<span class="pl-c1">6</span>, <span class="pl-c1">60</span>.<span class="pl-c1">8</span>, <span class="pl-c1">43</span>.<span class="pl-c1">7</span>, <span class="pl-c1">99</span>.<span class="pl-c1">3</span>]::<span class="pl-k">REAL</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_min</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">43.7</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_min(ARRAY[<span class="pl-c1">45</span>.<span class="pl-c1">6</span>, <span class="pl-c1">60</span>.<span class="pl-c1">8</span>, <span class="pl-c1">43</span>.<span class="pl-c1">7</span>, <span class="pl-c1">99</span>.<span class="pl-c1">3</span>]::<span class="pl-k">DOUBLE PRECISION</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_min</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">43.7</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_min(ARRAY[<span class="pl-c1">45</span>.<span class="pl-c1">6</span>, <span class="pl-c1">60</span>.<span class="pl-c1">8</span>, <span class="pl-c1">43</span>.<span class="pl-c1">7</span>, <span class="pl-c1">99</span>.<span class="pl-c1">3</span>]::<span class="pl-k">NUMERIC</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_min</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">43.7</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_min(ARRAY[<span class="pl-s"><span class="pl-pds">'</span>def<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>abc<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>ghi<span class="pl-pds">'</span></span>]::<span class="pl-k">TEXT</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>array_min</th>
</tr>
</thead>
<tbody>
<tr>
<td>abc</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-aggregate-array_max" class="anchor" href="#aggregate-array_max" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>AGGREGATE array_max</h3>
<p>Calculate minimum values from arrays.</p>
<p>Supported data types are SMALLINT, INTEGER, BIGINT, REAL, DOUBLE PRECISION, NUMERIC, and TEXT;</p>
<h4>
<a id="user-content-examples-16" class="anchor" href="#examples-16" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_max(ARRAY[<span class="pl-c1">45</span>, <span class="pl-c1">60</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>]::<span class="pl-k">SMALLINT</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_max</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">99</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_max(ARRAY[<span class="pl-c1">45</span>, <span class="pl-c1">60</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>]::<span class="pl-k">INTEGER</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_max</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">99</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_max(ARRAY[<span class="pl-c1">45</span>, <span class="pl-c1">60</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>]::<span class="pl-k">BIGINT</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_max</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">99</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_max(ARRAY[<span class="pl-c1">45</span>.<span class="pl-c1">6</span>, <span class="pl-c1">60</span>.<span class="pl-c1">8</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>.<span class="pl-c1">3</span>]::<span class="pl-k">REAL</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_max</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">99.3</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_max(ARRAY[<span class="pl-c1">45</span>.<span class="pl-c1">6</span>, <span class="pl-c1">60</span>.<span class="pl-c1">8</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>.<span class="pl-c1">3</span>]::<span class="pl-k">DOUBLE PRECISION</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_max</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">99.3</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_max(ARRAY[<span class="pl-c1">45</span>.<span class="pl-c1">6</span>, <span class="pl-c1">60</span>.<span class="pl-c1">8</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>.<span class="pl-c1">3</span>]::<span class="pl-k">NUMERIC</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_max</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">99.3</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_max(ARRAY[<span class="pl-s"><span class="pl-pds">'</span>def<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>abc<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>ghi<span class="pl-pds">'</span></span>]::<span class="pl-k">TEXT</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>array_max</th>
</tr>
</thead>
<tbody>
<tr>
<td>ghi</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-aggregate-array_avg" class="anchor" href="#aggregate-array_avg" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>AGGREGATE array_avg</h3>
<p>Calculate average values from arrays.</p>
<p>Supported data types are SMALLINT, INTEGER, BIGINT, REAL, DOUBLE PRECISION, and NUMERIC;</p>
<h4>
<a id="user-content-examples-17" class="anchor" href="#examples-17" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_avg(ARRAY[<span class="pl-c1">45</span>, <span class="pl-c1">60</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>]::<span class="pl-k">SMALLINT</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_avg</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">62</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_avg(ARRAY[<span class="pl-c1">45</span>, <span class="pl-c1">60</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>]::<span class="pl-k">INTEGER</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_avg</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">62</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_avg(ARRAY[<span class="pl-c1">45</span>, <span class="pl-c1">60</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>]::<span class="pl-k">BIGINT</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_avg</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">62</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_avg(ARRAY[<span class="pl-c1">45</span>.<span class="pl-c1">6</span>, <span class="pl-c1">60</span>.<span class="pl-c1">8</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>.<span class="pl-c1">3</span>]::<span class="pl-k">REAL</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_avg</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">62.1750001907349</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_avg(ARRAY[<span class="pl-c1">45</span>.<span class="pl-c1">6</span>, <span class="pl-c1">60</span>.<span class="pl-c1">8</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>.<span class="pl-c1">3</span>]::<span class="pl-k">DOUBLE PRECISION</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_avg</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">62.175</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_avg(ARRAY[<span class="pl-c1">45</span>.<span class="pl-c1">6</span>, <span class="pl-c1">60</span>.<span class="pl-c1">8</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>.<span class="pl-c1">3</span>]::<span class="pl-k">NUMERIC</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_avg</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">62.1750000000000000</td>
</tr>
</tbody>
</table>
<h3>
<a id="user-content-aggregate-array_sum" class="anchor" href="#aggregate-array_sum" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>AGGREGATE array_sum</h3>
<p>Calculate sum of values from arrays.</p>
<p>Supported data types are SMALLINT, INTEGER, BIGINT, REAL, DOUBLE PRECISION, and, NUMERIC;</p>
<h4>
<a id="user-content-examples-18" class="anchor" href="#examples-18" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_sum(ARRAY[<span class="pl-c1">45</span>, <span class="pl-c1">60</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>]::<span class="pl-k">SMALLINT</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_sum</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">247</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_sum(ARRAY[<span class="pl-c1">45</span>, <span class="pl-c1">60</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>]::<span class="pl-k">INTEGER</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_sum</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">247</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_sum(ARRAY[<span class="pl-c1">45</span>, <span class="pl-c1">60</span>, <span class="pl-c1">43</span>, <span class="pl-c1">99</span>]::<span class="pl-k">BIGINT</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_sum</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">247</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_sum(ARRAY[<span class="pl-c1">45</span>.<span class="pl-c1">6</span>, <span class="pl-c1">60</span>.<span class="pl-c1">8</span>, <span class="pl-c1">43</span>.<span class="pl-c1">7</span>, <span class="pl-c1">99</span>.<span class="pl-c1">3</span>]::<span class="pl-k">REAL</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_sum</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">249.4</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_sum(ARRAY[<span class="pl-c1">45</span>.<span class="pl-c1">6</span>, <span class="pl-c1">60</span>.<span class="pl-c1">8</span>, <span class="pl-c1">43</span>.<span class="pl-c1">7</span>, <span class="pl-c1">99</span>.<span class="pl-c1">3</span>]::<span class="pl-k">DOUBLE PRECISION</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_sum</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">249.4</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> array_sum(ARRAY[<span class="pl-c1">45</span>.<span class="pl-c1">6</span>, <span class="pl-c1">60</span>.<span class="pl-c1">8</span>, <span class="pl-c1">43</span>.<span class="pl-c1">7</span>, <span class="pl-c1">99</span>.<span class="pl-c1">3</span>]::<span class="pl-k">NUMERIC</span>[]);</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">array_sum</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">249.4</td>
</tr>
</tbody>
</table>
<h2>
<a id="user-content-format-functions" class="anchor" href="#format-functions" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Format functions</h2>
<p>Several countries use different formats for numbers, dates, and timestamps.
Therefore I needed some functions, which is easy to remember than the format
codes which differ in different programming languages.</p>
<h3>
<a id="user-content-german-formats" class="anchor" href="#german-formats" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>German formats</h3>
<h4>
<a id="user-content-function-date_de" class="anchor" href="#function-date_de" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION date_de</h4>
<p>Creates a function which returns the given date in German format.</p>
<h5>
<a id="user-content-example-11" class="anchor" href="#example-11" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example</h5>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> date_de(<span class="pl-s"><span class="pl-pds">'</span>2018-01-01<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> d_de;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">d_de</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">01.01.2018</td>
</tr>
</tbody>
</table>
<h4>
<a id="user-content-function-datetime_de" class="anchor" href="#function-datetime_de" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION datetime_de</h4>
<p>Creates a function which returns the given timestamp in German format.</p>
<h5>
<a id="user-content-example-12" class="anchor" href="#example-12" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Example</h5>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">SELECT</span> datetime_de(<span class="pl-s"><span class="pl-pds">'</span>2018-01-01 13:30:30 GMT<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> ts_de;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="center">ts_de</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center">01.01.2018 14:30:30</td>
</tr>
</tbody>
</table>
<h2>
<a id="user-content-conversion-functions" class="anchor" href="#conversion-functions" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Conversion functions</h2>
<h3>
<a id="user-content-function-to_unix_timestamp" class="anchor" href="#function-to_unix_timestamp" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION to_unix_timestamp</h3>
<p>Creates two functions which returns unix timestamp for the a given timestamp or
a given timestamp with time zone.</p>
<h4>
<a id="user-content-examples-19" class="anchor" href="#examples-19" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">--</span> Timestamp without time zone, server uses German / Berlin time zone</span>
<span class="pl-k">SELECT</span> to_unix_timestamp(<span class="pl-s"><span class="pl-pds">'</span>2018-01-01 00:00:00<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> unix_timestamp;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">unix_timestamp</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">1514761200</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">--</span> Timestamp with time zone</span>
<span class="pl-k">SELECT</span> to_unix_timestamp(<span class="pl-s"><span class="pl-pds">'</span>2018-01-01 00:00:00+01<span class="pl-pds">'</span></span>) <span class="pl-k">AS</span> unix_timestamp;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th align="right">unix_timestamp</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">1514761200</td>
</tr>
</tbody>
</table>
<h2>
<a id="user-content-other-functions" class="anchor" href="#other-functions" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Other functions</h2>
<h3>
<a id="user-content-function-array_trim" class="anchor" href="#function-array_trim" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>FUNCTION array_trim</h3>
<p>Removes empty strings and null entries from a given array. In addition the
function can remove duplicate entries. The function supports strings, numbers,
dates, and timestamps with or without time zone.</p>
<h4>
<a id="user-content-examples-20" class="anchor" href="#examples-20" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Examples</h4>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">--</span> Untrimmed timestamp array with time zone with duplicates</span>
<span class="pl-k">SELECT</span> array_trim(ARRAY[<span class="pl-s"><span class="pl-pds">'</span>2018-11-11 11:00:00 MEZ<span class="pl-pds">'</span></span>,<span class="pl-k">NULL</span>,<span class="pl-s"><span class="pl-pds">'</span>2018-11-11 11:00:00 MEZ<span class="pl-pds">'</span></span>]::<span class="pl-k">TIMESTAMP WITH TIME ZONE</span>[]) <span class="pl-k">AS</span> trimmed_array;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>untrimmed_array</th>
</tr>
</thead>
<tbody>
<tr>
<td>{'2018-11-11 11:00:00.000',,'2018-11-11 11:00:00.000'}</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">--</span> Timestamp array with time zone with duplicates</span>
<span class="pl-k">SELECT</span> ARRAY[<span class="pl-s"><span class="pl-pds">'</span>2018-11-11 11:00:00 MEZ<span class="pl-pds">'</span></span>,<span class="pl-k">NULL</span>,<span class="pl-s"><span class="pl-pds">'</span>2018-11-11 11:00:00 MEZ<span class="pl-pds">'</span></span>]::<span class="pl-k">TIMESTAMP WITH TIME ZONE</span>[] <span class="pl-k">AS</span> untrimmed_array;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>trimmed_array</th>
</tr>
</thead>
<tbody>
<tr>
<td>{'2018-11-11 11:00:00.000','2018-11-11 11:00:00.000'}</td>
</tr>
</tbody>
</table>
<div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">--</span> Timestamp array with time zone without duplicates</span>
<span class="pl-k">SELECT</span> array_trim(ARRAY[<span class="pl-s"><span class="pl-pds">'</span>2018-11-11 11:00:00 MEZ<span class="pl-pds">'</span></span>,<span class="pl-k">NULL</span>,<span class="pl-s"><span class="pl-pds">'</span>2018-11-11 11:00:00 MEZ<span class="pl-pds">'</span></span>]::<span class="pl-k">TIMESTAMP WITH TIME ZONE</span>[], TRUE) <span class="pl-k">AS</span> trimmed_array_distinct;</pre></div>
<p>Result:</p>
<table>
<thead>
<tr>
<th>trimmed_array_distinct</th>
</tr>
</thead>
<tbody>
<tr>
<td>{'2018-11-11 11:00:00.000'}</td>
</tr>
</tbody>
</table>
You can’t perform that action at this time.