Postgres OnLine Journal: July 2016 - December 2016
An in-depth Exploration of the PostgreSQL Open Source Database
 

Table Of Contents

Using PostgreSQL Contribs

Using PostgreSQL Contribs

 

http extension for windows 64-bit



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 v1.1.2 release for PostgreSQL 9.6, 9.5, and 9.4 Windows 64-bit.

We haven't gotten around to building the 32-bit variants but plan to and will add them here once done.

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.

http extension binaries for PostgreSQL 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 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

 

ODBC FDW now supports 9.5 and 9.6



A while ago when Foreign Data Wrappers in PostgreSQL was a fairly new thing, we talked about the ODBC_FDW foreign data wrapper. Since then, people have been asking us how to get the ODBC FDW to work on newer PostgreSQL. Sadly the ODBC_FDW was stuck in time not having updated to newer FDW API standards. Our recommendation was just to use OGR_FDW, which many distributions both Linux and Windows have compiled OGR_FDW with ODBC support. True that OGR_FDW is coined as a spatial data wrapper, but the reality is spatial data rarely lives apart from regular attribute data so a good spatial vector driver supports both vector data and bread and butter data types. OGR_FDW is still our go to for working with spreadsheets and folders of CSV files.

Recently the fine folks at Carto patched the ODBC FDW to work with PostgreSQL 9.5. I do hope they accept my modest patch to make it work with PostgreSQL 9.6 as well. So now 2 FDWs to choose from for connecting to ODBC datasources. Which one is better? The answer as most always is IT DEPENDS.

For users on windows, we've compiled binaries for PostgreSQL 9.5, 9.6 both (32/64 bit). The 64-bit I've tested on both EDB and BigSQL distributions and I compiled them using Mingw64-w64 gcc 4.9.2.

We've also updated the FDW bag of extensions to include this extension

  • ODBC_FDW PostgreSQL 9.6 64-bit: zip 7z
  • ODBC_FDW PostgreSQL 9.6 32-bit: zip 7z
  • ODBC_FDW PostgreSQL 9.5 64-bit: zip 7z
  • ODBC_FDW PostgreSQL 9.5 32-bit: zip 7z

If you are on windows compiling with Mingw64 and want to use Native windows ODBC, you can more or less follow the compile instructions on https://github.com/CartoDB/odbc_fdw, except for the Makefile, make the change:

SHLIB_LINK = -lodbc

Change to:

SHLIB_LINK = -lodbc32

This applies both for 64-bit and 32-bit compiles. It always seemed counter intuitive to me that the windows 64-bit odbc is also called odbc32.

For this exercise, I'll connect to a SQL Server 2008 R2 with the FDW ODBC driver.

CREATE EXTENSION odbc_fdw;
-- this is an example connecting to a SQL Server.  
-- The DSN name should be one registered in 64-bit (or 32-bit if you are using PostgreSQL 32-bit) ODBC as a system dsn
CREATE SERVER sql_server
  FOREIGN DATA WRAPPER odbc_fdw
  OPTIONS (dsn 'TESTSQL');

-- The user mapping should be an account on SQL Server
CREATE USER MAPPING FOR postgres
  SERVER sql_server
  OPTIONS (odbc_UID 'sa', odbc_PWD 'whatever');
  
-- change this to a database that exists on your SQL Server
IMPORT FOREIGN SCHEMA whatever
  FROM SERVER sql_server
  INTO public
  OPTIONS (
    odbc_DATABASE 'AdventureWorks',
    table 'fdt_columns', -- this will be the name of the created foreign table
    sql_query 'SELECT * FROM information_schema.columns'
  );
  
-- should list all columns in that database
SELECT * FROM fdt_columns;

The odbc_fdw preserves the casing of column names which is sometimes nice, but often not nice. Because now to select columns from the foreign table, you've got to do this quote nonsense.

SELECT "COLUMN_NAME" FROM fdt_columns;

If you don't want to be bothered with the hassle of quoting when you query the foreign table, you could define a query for your table like so:

IMPORT FOREIGN SCHEMA whatever
  FROM SERVER sql_server
  INTO public
  OPTIONS (
    odbc_DATABASE 'AdventureWorks',
    table 'fdt_columns_subset', -- this will be the name of the created foreign table
    sql_query 'SELECT table_name AS "table_name", column_name AS "column_name" 
        FROM information_schema.columns'
  );

With my new subsetted foreign table, I can now do:

SELECT column_name FROM fdt_columns_subset;

Which is better for querying ODBC datasources, OGR_FDW or ODBC_FDW?

Getting back to the "It depends" statement, I'll qualify that with a, but OGR_FDW is generally better.

Here is why:

ODBC_FDWOGR FDW
Lighter weight, no additional dependencies beyond ODBC and PostgreSQLRequires GDAL which might have a whole chain of dependencies. If you're using PostGIS, you've probably already got GDAL, so no additional requirement.
Push down, if it exists is pretty weak, try to do something like a simple LIMIT or a WHERE something='whatever' against a big table, and you could be waiting for days.Push down support for LIMIT and simple WHERE clauses like WHERE x > 80 or x = 'whatever', so pretty fast even for big tables. I've tried on a SQL server table with 14 million records and was not disappointed.
You can define a remote query as a foreign table - YES, number one win. This makes it ideal where you've got a complex query you want to run only on the remote server (ala SQL Server's OPENQUERY)No query as a foreign table support. Only remote tables and views can be foreign tables.
IMPORT FOREIGN SCHEMA, while existent, seems to just save in defining columns for a single table, can't pull in a whole load of tables. Also always preserves table names. On upside you can choose name for new table. Downside, no quick way to launder columns, so if you have an ODBC with upper case or mixed case, be prepared to write a lot of "sNot only does it pull in a bunch of tables, but you can use the schema part as a table filter e.g IMPORT FOREIGN SCHEMA "dbo.Finance" will pull in all tables in dbo schema that start with Finance. Can't choose table names, but you can choose to launder table names and columns, so you don't have to quote the columns every time you use them.
maintains both basic data type and modifiers in foreign table so nvarchar(50) becomes varchar(50). No support for spatial columns.It's a spatial driver first and foremost, so it can understand things like MSSQL geometry column. Doesn't maintain data type modifiers, so nvarchar(50) will come in as just varchar.

Our PostgreSQL Up and Running 3rd edition is now available in early preview.