Postgres OnLine Journal
https://www.postgresonline.com/journal/
Tips and tricks for PostgreSQLenhttps://www.postgresonline.com/journal/templates/default/img/s9y_banner_small.pngRSS: Postgres OnLine Journal - Tips and tricks for PostgreSQL
https://www.postgresonline.com/journal/
10021PostgreSQL Mistakes and How To Avoid them
https://www.postgresonline.com/journal/index.php?/archives/410-PostgreSQL-Mistakes-and-How-To-Avoid-them.html
<p>The adoption of PostgreSQL is growing each year. Many people coming to PostgreSQL are often coming from other
relational databases, with assumptions of how relational databases work. Although PostgreSQL may feel very familiar to these
people, it is different enough to cause some misunderstandings which lead to bad and slow queries.
There are also people coming often from only programming backgrounds, who assume data processing in SQL is much like
data processing in any language. For these two groups of folks, I think the
new book written by EDB and 2nd Quadrant Author, Jimmy Angelakos, "PostgreSQL Mistakes and How To Avoid Them" will save them a lot of miss-steps.</p>
<a class="block_level" href="https://www.postgresonline.com/journal/index.php?/archives/410-PostgreSQL-Mistakes-and-How-To-Avoid-them.html#extended">Continue reading "PostgreSQL Mistakes and How To Avoid them"</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
basics, 2024-03-05T17:43:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=4100https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=410PostgreSQL 16 64-bit for Windows FDWs
https://www.postgresonline.com/journal/index.php?/archives/409-PostgreSQL-16-64-bit-for-Windows-FDWs.html
<p>We are pleased to provide binaries for <i>file_textarray_fdw</i> and <i>odbc_fdw</i> for PostgreSQL 16 Windows 64-bit.</p>
<p>To use these, copy the files into your PostgreSQL 16 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt</p>
<p>These were compiled against PostgreSQL 16rc1, but should work fine against EDB PostgreSQL 16beta3.</p> <a class="block_level" href="https://www.postgresonline.com/journal/index.php?/archives/409-PostgreSQL-16-64-bit-for-Windows-FDWs.html#extended">Continue reading "PostgreSQL 16 64-bit for Windows FDWs"</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
16, contrib spotlight, fdws, file_textarray_fdw, odbc_fdw, ogr_fdw, postgresql versions, winextensions, 2023-09-03T04:13:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=4090https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=409VARIADIC Unnest
https://www.postgresonline.com/journal/index.php?/archives/406-VARIADIC-Unnest.html
<p>PostgreSQL keeps on adding new great stuff. It's hard to remember all the good stuff that has been added over the years.
One of the neat ones from the past is the <a href="https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS" target="_blank">variadic Unnest function</a> which I believe was introduced in PostgreSQL 9.4. It's rare that I ever had to use it, but today I was handed some data where this function was just literally what the doctor ordered. I can't do anything this sweet in other databases I have used.</p>
<a class="block_level" href="https://www.postgresonline.com/journal/index.php?/archives/406-VARIADIC-Unnest.html#extended">Continue reading "VARIADIC Unnest"</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
10, 11, 12, 13, 14, 15, 9.4, 9.5, 9.6, basics, postgresql versions, 2022-11-23T05:26:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=4060https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=406PostgreSQL 15 64-bit for Windows FDWs
https://www.postgresonline.com/journal/index.php?/archives/405-PostgreSQL-15-64-bit-for-Windows-FDWs.html
<p>We are pleased to provide binaries for <i>file_textarray_fdw</i> and <i>odbc_fdw</i> for PostgreSQL 15 Windows 64-bit.</p>
<p>To use these, copy the files into your PostgreSQL 15 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt</p> <a class="block_level" href="https://www.postgresonline.com/journal/index.php?/archives/405-PostgreSQL-15-64-bit-for-Windows-FDWs.html#extended">Continue reading "PostgreSQL 15 64-bit for Windows FDWs"</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
15, contrib spotlight, fdws, file_textarray_fdw, odbc_fdw, postgresql versions, winextensions, 2022-10-29T02:44:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=4050https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=405Creating cumulative sums by combining aggregation with windowing
https://www.postgresonline.com/journal/index.php?/archives/403-Creating-cumulative-sums-by-combining-aggregation-with-windowing.html
<p>When you want to include a running sum for each line of data in your query, you generally use a
window clause with SUM. What if you don't want to list all line items of your data. You want a report that gives you a weekly sum and another that gives you the running sum for the whole year.
What do you do then? We'll demonstrate how to do that.</p> <a class="block_level" href="https://www.postgresonline.com/journal/index.php?/archives/403-Creating-cumulative-sums-by-combining-aggregation-with-windowing.html#extended">Continue reading "Creating cumulative sums by combining aggregation with windowing"</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
q&a, 2022-03-30T19:14:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=4030https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=403Multirange types in PostgreSQL 14
https://www.postgresonline.com/journal/index.php?/archives/401-Multirange-types-in-PostgreSQL-14.html
<p>One of the features we are most excited about in recently released PostgreSQL 14 is the introduction of Multirange types.
In a nutshell Multirange types are sets of non-overlapping ranges. Unlike array of ranges, they prevent overlaps and thus
allow you to effectively model ranges with gaps.</p>
<p>One of the use cases we have for them is modeling time. For example if you wanted to keep track of the cumulative periods and how many days someone is in a hospital, you could store this as a datemultirange type.</p>
<p>There are quite a few operators and functions available in PostgreSQL 14, but some glaring ones we'd need include aggregates such as a union aggregate. That currently doesn't exist. There are however your standard operators like + (union two ranges) and * for intersection, and - for difference as well as your common containment boolean operators.</p> <a class="block_level" href="https://www.postgresonline.com/journal/index.php?/archives/401-Multirange-types-in-PostgreSQL-14.html#extended">Continue reading "Multirange types in PostgreSQL 14"</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
14, postgresql versions, q&a, 2021-10-12T02:18:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=4010https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=401PostgreSQL 14 64-bit for Windows FDWs
https://www.postgresonline.com/journal/index.php?/archives/402-PostgreSQL-14-64-bit-for-Windows-FDWs.html
<p>We are pleased to provide binaries for <i>file_textarray_fdw</i> and <i>odbc_fdw</i> for PostgreSQL 14 Windows 64-bit.</p>
<p>To use these, copy the files into your PostgreSQL 14 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt</p>
<a class="block_level" href="https://www.postgresonline.com/journal/index.php?/archives/402-PostgreSQL-14-64-bit-for-Windows-FDWs.html#extended">Continue reading "PostgreSQL 14 64-bit for Windows FDWs"</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
14, contrib spotlight, fdws, file_textarray_fdw, odbc_fdw, postgresql versions, winextensions, 2021-10-12T02:15:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=4020https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=402PostgreSQL 13 64-bit for Windows FDWs
https://www.postgresonline.com/journal/index.php?/archives/400-PostgreSQL-13-64-bit-for-Windows-FDWs.html
<p>We are pleased to provide binaries for <i>file_textarray_fdw</i> and <i>odbc_fdw</i> for PostgreSQL 13 Windows 64-bit.</p>
<p>To use these, copy the files into your PostgreSQL 13 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt</p>
<a class="block_level" href="https://www.postgresonline.com/journal/index.php?/archives/400-PostgreSQL-13-64-bit-for-Windows-FDWs.html#extended">Continue reading "PostgreSQL 13 64-bit for Windows FDWs"</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
13, contrib spotlight, fdws, file_textarray_fdw, odbc_fdw, postgresql versions, winextensions, 2021-03-07T19:53:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=4000https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=400PostgreSQL 12 64-bit for Windows FDWs
https://www.postgresonline.com/journal/index.php?/archives/398-PostgreSQL-12-64-bit-for-Windows-FDWs.html
<p>We are pleased to provide binaries for <a href="https://github.com/adunstan/file_text_array_fdw" target="_blank">file_textarray_fdw</a> and <a href="https://github.com/CartoDB/odbc_fdw" target="_blank">odbc_fdw</a> for PostgreSQL 12 Windows 64-bit.</p>
<p>To use these, copy the files into your PostgreSQL 12 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt</p>
<a class="block_level" href="https://www.postgresonline.com/journal/index.php?/archives/398-PostgreSQL-12-64-bit-for-Windows-FDWs.html#extended">Continue reading "PostgreSQL 12 64-bit for Windows FDWs"</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
12, contrib spotlight, fdws, file_textarray_fdw, odbc_fdw, ogr_fdw, postgresql versions, winextensions, 2019-10-23T23:55:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=39816https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=398http extension for windows updated to include PostgreSQL16 64-bit
https://www.postgresonline.com/journal/index.php?/archives/371-http-extension-for-windows-updated-to-include-PostgreSQL16-64-bit.html
<div style="background-color:green;color:white">Updated September 3, 2023 64-bit package for PostgreSQL 15-16 <a href="https://github.com/pramsey/pgsql-http/releases/tag/v1.6.0" target="_blank" style='color:white'>http extension v1.6.0 release</a>. PostgreSQL 11-14 will be updated shortly. </div>
<br /><br />
<div style="background-color:green;color:white">Updated April 30th, 2022 64-bit package for PostgreSQL 10-15 <a href="https://github.com/pramsey/pgsql-http/releases/tag/v1.5.0" target="_blank" style='color:white'>http extension v1.5.0 release</a> </div>
<p>For those folks on windows who want to do http gets and posts directly from your PostgreSQL server, we've made binaries for the <a href="https://github.com/pramsey/pgsql-http" target="_blank">http extension</a> for PostgreSQL Windows.</p>
<p>These are designed to work with PostgreSQL EDB windows distributions.</p>
<p>If you have PostGIS already installed, many of these files you will also already have since things like the libcurl and PCRE are also packaged with PostGIS.</p>
<a class="block_level" href="https://www.postgresonline.com/journal/index.php?/archives/371-http-extension-for-windows-updated-to-include-PostgreSQL16-64-bit.html#extended">Continue reading "http extension for windows updated to include PostgreSQL16 64-bit"</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
10, 11, 12, 13, 14, 15, 16, 9.4, 9.5, 9.6, contrib spotlight, http, postgresql versions, winextensions, 2019-10-16T00:15:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=37111https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=371PostGIS 3.0.0 coming soon - Try 3.0.0rc2 at a package repo near you
https://www.postgresonline.com/journal/index.php?/archives/397-PostGIS-3.0.0-coming-soon-Try-3.0.0rc2-at-a-package-repo-near-you.html
<p>PostGIS 3.0.0 is planned for release early next week. In the meantime you will find PostGIS 3.0.0rc1 or rc2 available via yum.postgresql.org, apt.postgresql.org, and EDB Windows 64-bit stackbuilder for PostgreSQL 12. </p>
<a class="block_level" href="https://www.postgresonline.com/journal/index.php?/archives/397-PostGIS-3.0.0-coming-soon-Try-3.0.0rc2-at-a-package-repo-near-you.html#extended">Continue reading "PostGIS 3.0.0 coming soon - Try 3.0.0rc2 at a package repo near you"</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
postgis, 2019-10-15T23:15:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=3972https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=397PostgreSQL 11 64-bit Windows FDWs
https://www.postgresonline.com/journal/index.php?/archives/396-PostgreSQL-11-64-bit-Windows-FDWs.html
<p>We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 11 Windows 64-bit.</p>
<p>To use these, copy the files into your PostgreSQL 11 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt</p>
<a class="block_level" href="https://www.postgresonline.com/journal/index.php?/archives/396-PostgreSQL-11-64-bit-Windows-FDWs.html#extended">Continue reading "PostgreSQL 11 64-bit Windows FDWs"</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
11, contrib spotlight, fdws, file_textarray_fdw, odbc_fdw, ogr_fdw, postgresql versions, winextensions, 2019-09-08T03:28:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=3964https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=396SQL Server on Linux
https://www.postgresonline.com/journal/index.php?/archives/395-SQL-Server-on-Linux.html
<p>Today is April 1st. Having no thoughts on Fools jokes for today, I dug up one of our old April fools, and it was pretty scary how the joke is just about true now. Yes <a href="https://www.microsoft.com/en-us/sql-server/sql-server-2017-linux" target="_blank">SQL Server now really does run on Linux</a> and is on it's 2017th edition, but still a poor competition to PostgreSQL.</p>
<p>A goody from our old joke archives</p>
<a href="https://www.postgresonline.com/journal/archives/155-CatchMe---Microsoft-SQL-Server-for-Unix-and-Linux.html" target="_blank">CatchMe - Microsoft SQL Server for Unix and Linux</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
editor note, joke, 2019-04-01T13:31:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=3952https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=395PGConf US 2019 Data Loading Slides up
https://www.postgresonline.com/journal/index.php?/archives/394-PGConf-US-2019-Data-Loading-Slides-up.html
<p>I gave a talk at PGConf US 2019 on some of the many ways you can load data into PostgreSQL using open source tools.
This is similar to the talk I gave last year but with the addition of the <a href="https://github.com/dimitri/pgloader" target="_blank">pgloader commandline tool</a> and the <a href="https://github.com/pramsey/pgsql-http" target="_blank">http</a> PostgreSQL extension.</p>
<p><a href="http://www.postgis.us/presentations/PGConf2019_data_loading.html" target="_blank">HTML slides</a> <a href="http://www.postgis.us/presentations/PGConf2019_data_loading.pdf" target="_blank">PDF slides</a></p>
<p>Even though it was a talk Not much about PostGIS, but just tricks for loading data, I managed to get a mouthful of PostGIS in there.</p>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
fdws, gis, odbc_fdw, ogr_fdw, postgis, 2019-03-25T22:00:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=3940https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=394Compiling http extension on ubuntu 18.04
https://www.postgresonline.com/journal/index.php?/archives/393-Compiling-http-extension-on-ubuntu-18.04.html
<p>We recently installed PostgreSQL 11 on an Ubuntu 18.04 using apt.postgresql.org. Many of our favorite extensions were already available via apt (postgis, ogr_fdw to name a few), but it didn't have the http extension we use a lot. The http extension is pretty handy for querying things like Salesforce and other web api based systems. We'll outline the basic compile and install steps. While it's specific to the http extension, the process is similar for any other extension you may need to compile.</p> <a class="block_level" href="https://www.postgresonline.com/journal/index.php?/archives/393-Compiling-http-extension-on-ubuntu-18.04.html#extended">Continue reading "Compiling http extension on ubuntu 18.04"</a>
Postgres OnLine Journalnospam@example.com (Leo Hsu and Regina Obe)
11, contrib spotlight, http, postgis, postgresql versions, 2019-02-11T08:31:00Zhttps://www.postgresonline.com/journal/wfwcomment.php?cid=3930https://www.postgresonline.com/journal/rss.php?version=1.0&type=comments&cid=393