Postgres OnLine Journal: Jan 2019 - Dec 2019
An in-depth Exploration of the PostgreSQL Open Source Database
 

Table Of Contents

From the Editors
Using PostgreSQL Contribs

From the Editors

 

SQL Server on Linux



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 SQL Server now really does run on Linux and is on it's 2017th edition, but still a poor competition to PostgreSQL.

A goody from our old joke archives

CatchMe - Microsoft SQL Server for Unix and Linux

Using PostgreSQL Contribs

 

http extension for windows 64 and 32-bit updated to include PostgreSQL12 64-bit



Updated October 15th, 2019 64-bit package for PostgreSQL 11, 10 updated to 1.3.1 and PostgreSQL 12 added http extension v1.3.1 release

For those folks on windows who want to do http gets and posts directly from your PostgreSQL server, we've made binaries for the http extension for PostgreSQL Windows.

These should work fine on both PostgreSQL EDB windows distributions as well as the BigSQL windows 64 distributions. Instructions in the ReadMe.txt for where to copy the files to for each distribution. Caution should be taken especially with BigSQL to not overwrite existing BigSQL files. Many of the files in this already exist in the BigSQL distribution since we both compile using the Mingw64 chain.

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.

Updated November 22nd, 2017 all packages updated to http extension v1.2.2 release

http extension binaries for PostgreSQL 11, 10, 9.6, 9.5, and 9.4 windows 64-bit downloads

The curl library for http is built with SSL support and utilizes the ssleasy.dll packaged with the EDB and BigSQL installs.

http extension binaries for PostgreSQL 10, 9.6, 9.5, and 9.4 windows 32-bit downloads

http quick primer

To enable in a database after having installed the binaries.

CREATE EXTENSION http;

Do a basic get

SELECT h.content, h.content_type, hkv.value As dt
FROM http_get('http://postgis.net/tips/') AS h 
    LEFT JOIN LATERAL (SELECT *  
    FROM unnest(h.headers) 
        WHERE field =  'Date') AS hkv ON true;

Check out more examples at: https://github.com/pramsey/pgsql-http


Using PostgreSQL Contribs

 

Compiling http extension on ubuntu 18.04



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.

For this exercise, I'm going to assume you have a working PostgreSQL install you got from apt.postgresql.org. If you don't you can check out PostGIS 2.4 and PostgreSQL 10 install on Ubuntu which covers installing PostgreSQL, PostGIS, and pgRouting on Ubuntu.

Building extensions general requirements

For any extension written in C or C++, You'll need a couple extra things, you may not have

sudo -i  #get into root mode
apt install make gcc g++
apt install postgresql-server-dev-11

If you are running a different version of PostgreSQL, replace the number 11 with the version you are running.

Building http

The http extension relies on curl development library, so in addition to the aforementioned, you'll need to do:

apt install libcurl4-openssl-dev

Now we are ready to build. The latest as of this writing is 1.3.0, you should check if there is newer when you build.

wget https://github.com/pramsey/pgsql-http/archive/v1.3.0.tar.gz
tar xvfz v1.3.0.tar.gz
cd pgsql-http-1.3.0
make && make install

If all is successful you should see something like this:

/usr/bin/install -c -m 644 .//http--1.3.sql .//http--1.2--1.3.sql .//http--1.1--1.2.sql .//http--1.0--1.1.sql  
'/usr/share/postgresql/11/extension/'

Installing http extension in your database

As a general rule we don't like our public schema cluttered with extension functions, so we usually install extensions in contrib schema or a schema dedicated to the extension or related extensions. So our steps below are a few more than a standard vanila install.

su postgres
psql
CREATE DATABASE test_http;
\connect test_http;
CREATE SCHEMA contrib;
GRANT USAGE ON SCHEMA contrib TO some_user_group;
ALTER DATABASE test_http SET search_path=public,contrib;
CREATE EXTENSION http SCHEMA contrib;
\connect test_http -- I do this so search path takes effect
-- test the extension by grabbing first 100 characters of web page
SELECT left(content,100) FROM http_get('https://postgis.net');

You can find more examples of usage at https://github.com/pramsey/pgsql-http


Using PostgreSQL Contribs

 

PostgreSQL 11 64-bit Windows FDWs



We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 11 Windows 64-bit.

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

We are no longer including 32-bit binaries since EDB has stopped supporting 32-bit windows for PostgreSQL 11 and up.

This package contains the following FDWs:

  • odbc_fdw (version 0.4.0) for connecting to ODBC data sources such as SQL Server, Oracle, MS Access databases, and anything else that has a 64-bit ODBC driver. Note that since this is for PostgreSQL 64-bit, it can only use ODBC 64-bit connections.
  • file_textarray_fdw, great and fast for working with arbitrary and pesky delimited data. Especially where they weren't considerate enough to give you the same number of columns per row.

Note this package does not include ogr_fdw since ogr_fdw is packaged as part of PostGIS packages from EnterpriseDb Stackbuilder (for PostGIS >= 2.2) and also BigSQL distributions.

If you do not have PostGIS installed (and don't want to for some reason) and want to use ogr_fdw on windows, you can download from: Winnie's PG 11 Extras. ogr_fdw is a great FDW for querying not just spatial data, but also a ton of other file formats or relational (including odbc, dbase files, spreadsheets) since spatial is a superset.


Using PostgreSQL Contribs

 

PostgreSQL 12 64-bit for Windows FDWs



We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 12 Windows 64-bit.

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

We are no longer including 32-bit binaries since EDB has stopped supporting 32-bit windows for PostgreSQL 11 and up.

This package contains the following FDWs:

  • odbc_fdw (version 0.4.0) for connecting to ODBC data sources such as SQL Server, Oracle, MS Access databases, and anything else that has a 64-bit ODBC driver. Note that since this is for PostgreSQL 64-bit, it can only use ODBC 64-bit connections.
  • file_textarray_fdw, great and fast for working with arbitrary and pesky delimited data. Especially where they weren't considerate enough to give you the same number of columns per row.

Note this package does not include ogr_fdw since ogr_fdw is packaged as part of PostGIS packages from EnterpriseDb Stackbuilder (for PostGIS >= 3.0) .

If you do not have PostGIS installed (and don't want to for some reason) and want to use ogr_fdw on windows, you can download from: Winnie's PG 12 Extras. ogr_fdw is a great FDW for querying not just spatial data, but also a ton of other file formats or relational (including odbc, dbase files, spreadsheets) since spatial is a superset.