Table Of Contents

One of the features coming in PostgreSQL 9.5 is the triumvirate GROUPING SETS, CUBE, and ROLLUP nicely covered in Bruce's recent slide deck. The neatest thing about PostgreSQL development is that when improvements happen, they don't just affect the core, but can be taken advantage of by extensions, without even lifting a finger. Such is the case with these features.

One of the things I was curious about with these new set of predicates is Would they work with any aggregate function?. I assumed they would, so decided to put it to the test, by using it with PostGIS ST_Union function (using PostGIS 2.2.0 development). This feature was not something the PostGIS Development group planned on supporting, but by the magic of PostgreSQL, PostGIS accidentally supports it. The grouping sets feature is particularly useful if you want to aggregate data multiple times, perhaps for display using the same dataset. It allows you to do it with a single query that in other PostgreSQL versions would require a UNION query. This is a rather boring example but hopefully you get the idea.

Example of GROUPING SETS with ST_Union spatial aggregate

SELECT COALESCE('County ' || countyfp, 'State ' || statefp) As region,
    ST_Union(the_geom) As geom, count(*) AS cnt
FROM cousub
GROUP BY GROUPING SETS (countyfp, statefp)
ORDER BY countyfp, statefp;

Output looks like:

   region   |              geom               | count
------------+-----------------------------------+-------
 County 001 | 0103000020AD10000001000000EB00... |     3
 County 003 | 0103000020AD10000001000000B307... |     5
 County 005 | 0103000020AD100000010000005902... |     7
 County 007 | 0103000020AD100000010000006F07... |    16
 County 009 | 0106000020AD100000020000000103... |     9
 State 44   | 0106000020AD100000020000000103... |    40
(6 rows)

Note in the example, I use COALESCE for labeling because in the GROUPING SETS will force only one of the columns to be filled in that is for that group and the rest will be NULL.

PostGIS 2.2 is planned to reach feature freeze June 30th 2015 so we can make the September PostgreSQL 9.5 curtain call with confidence. Great KNN enhancements for PostgreSQL 9.5 only users. I've been busy getting all my ducks lined up. A lot on tiger geocoder and address standardizer extension to be shipped with windows builds, story for later. One other feature we plan to ship with the windows PostGIS 2.2 builds is the ogr_fdw ogr_fdw Foreign data wrapper extension. I've been nagging Paul Ramsey a lot about issues with it, this in particular https://github.com/pramsey/pgsql-ogr-fdw/issues/25, and after some prodding, he finally put his nose in and fixed them and pinged Even Rouault for some help on a GDAL specific item.

Needless to say, I've been super happy with the progress and support I've gotten with ogr_fdw development and really enjoying my ogr_fdw use. The XLSX reading a file saved after the connection was open required a fix in GDAL 2.0 branch (which missed GDAL 2.0.0 release, so because of this, this new package contains a GDAL 2.0.1ish library. Hopeful GDAL 2.0.1 will be out before PostGIS 2.2.0 comes out so I can release without guilt with this fix.

What's packaged and where do I get it

• PostgreSQL 9.4 w32 FDWs (zip) 7z version
• PostgreSQL 9.4 w64 FDWs (zip) 7z version
• PostgreSQL 9.3 w64 FDWs (zip) 7z version
• 9.3 32-bit update coming soon

One of the things we decided to take out of the new build is CURL support (it's still in for packaged www_fdw). We added in sqlite3 support (which opens up a couple of key spatial drivers needed by some of our clients). The main reason for the change is since we are shipping this with PostGIS 2.2, we didn't want the extra CURL dependency and none of our clients or users funding our work had a need for any of the ogr www based drivers (Google Fusion tables, CartoDb, WFS etc.). If you have issue with that, let us know. We've also included the executables ogr2ogr.exe, ogrinfo.exe which gets built with GDAL. This we did mostly so you can see available formats and do basic troubleshooting: with command like:

ogrinfo --formats

Supported Formats:
  PCIDSK -raster,vector- (rw+v): PCIDSK Database File
  PDF -raster,vector- (w+): Geospatial PDF
  ESRI Shapefile -vector- (rw+v): ESRI Shapefile
  MapInfo File -vector- (rw+v): MapInfo File
  UK .NTF -vector- (ro): UK .NTF
  OGR_SDTS -vector- (ro): SDTS
  S57 -vector- (rw+v): IHO S-57 (ENC)
  DGN -vector- (rw+): Microstation DGN
  OGR_VRT -vector- (rov): VRT - Virtual Datasource
  REC -vector- (ro): EPIInfo .REC
  Memory -vector- (rw+): Memory
  BNA -vector- (rw+v): Atlas BNA
  CSV -vector- (rw+v): Comma Separated Value (.csv)
  GML -vector- (rw+v): Geography Markup Language (GML)
  GPX -vector- (rw+v): GPX
  KML -vector- (rw+v): Keyhole Markup Language (KML)
  GeoJSON -vector- (rw+v): GeoJSON
  OGR_GMT -vector- (rw+): GMT ASCII Vectors (.gmt)
  GPKG -raster,vector- (rw+vs): GeoPackage
  SQLite -vector- (rw+v): SQLite / Spatialite
  ODBC -vector- (rw+): ODBC
  WAsP -vector- (rw+v): WAsP .map format
  PGeo -vector- (ro): ESRI Personal GeoDatabase
  MSSQLSpatial -vector- (rw+): Microsoft SQL Server Spatial Database
  OpenFileGDB -vector- (rov): ESRI FileGDB
  XPlane -vector- (rov): X-Plane/Flightgear aeronautical data
  DXF -vector- (rw+v): AutoCAD DXF
  Geoconcept -vector- (rw+): Geoconcept
  GeoRSS -vector- (rw+v): GeoRSS
  GPSTrackMaker -vector- (rw+v): GPSTrackMaker
  VFK -vector- (ro): Czech Cadastral Exchange Data Format
  PGDUMP -vector- (w+v): PostgreSQL SQL dump
  OSM -vector- (rov): OpenStreetMap XML and PBF
  GPSBabel -vector- (rw+): GPSBabel
  SUA -vector- (rov): Tim Newport-Peace's Special Use Airspace Format
  OpenAir -vector- (rov): OpenAir
  OGR_PDS -vector- (rov): Planetary Data Systems TABLE
  HTF -vector- (rov): Hydrographic Transfer Vector
  AeronavFAA -vector- (rov): Aeronav FAA
  Geomedia -vector- (ro): Geomedia .mdb
  EDIGEO -vector- (rov): French EDIGEO exchange format
  SVG -vector- (rov): Scalable Vector Graphics
  Idrisi -vector- (rov): Idrisi Vector (.vct)
  ARCGEN -vector- (rov): Arc/Info Generate
  SEGUKOOA -vector- (rov): SEG-P1 / UKOOA P1/90
  SEGY -vector- (rov): SEG-Y
  XLS -vector- (ro): MS Excel format
  ODS -vector- (rw+v): Open Document/ LibreOffice / OpenOffice Spreadsheet
  XLSX -vector- (rw+v): MS Office Open XML spreadsheet
  Walk -vector- (ro): Walk
  SXF -vector- (ro): Storage and eXchange Format
  Selafin -vector- (rw+v): Selafin
  JML -vector- (rw+v): OpenJUMP JML
  TIGER -vector- (rw+v): U.S. Census TIGER/Line
  AVCBin -vector- (ro): Arc/Info Binary Coverage
  AVCE00 -vector- (ro): Arc/Info E00 (ASCII) Coverage

ogrinfo ODBC:user/password@SQLServerDSNName -sql "SELECT table_name, column_name from information_schema.columns WHERE column_name = 'FirstName'"

The ogr_fdw_info doesn't have a --formats option

ODBC Support

The most sort after driver support at least for windows users is the ODBC support (particularly for connecting to SQL Server databases). Someone wrote me about this trying to use the old odbc_fdw we talked about a while ago, and I told him to use ogr_fdw instead since its much more robust and support 9.3 and 9.4

I mentioned in Querying MS Access and other ODBC data sources with OGR_FDW it's use with MS Access, but still plan to follow up soon with a SQL Server example. With SQL Server there are two drivers, which both rely on ODBC support. The ODBC connection form requires a system dsn. The MSSQLSpatial you can do with a DSNLess connection, but there is an extra environment variable you need to set. More on that in our followup article.

pgRouting 2.1 Beta release is out. Full details at https://lists.osgeo.org/pipermail/pgrouting-dev/2015-August/001569.html. pgRouting 2.1 works with PostGIS 2.1 and 2.2.0dev.

Windows experimental pgRouting and PostGIS binaries for PostgreSQL 9.3 and 9.4 available at http://postgis.net/windows_downloads.

After installing PostgreSQL 9.4 and PostGIS following An Almost Idiot's guide to installing PostgreSQL, PostGIS, and pgRouting, on my CentOS 6.7 64-bit except replacing 9.3 references with equivalent 9.4 reference, I then proceeded to install ogr_fdw. To my disappointment, there are no binaries yet for that, which is not surprising, considering there aren't generally any binaries for any OS, except the windows ones I built which I will be packaging with PostGIS 2.2 windows bundle. Getting out of my windows comfort zone, I proceeded to build those on CentOS. Mainly because I have a client on CentOS where ogr_fdw I think is a perfect fit for his workflow and wanted to see how difficult of a feat this would be. I'll go over the steps I used for building and stumbling blocks I ran into in this article with hope it will be of benefit to those who find themselves in a similar situation.

I should add I also experimented with building stuff from PGXN registry on my CentOS box, and one big peeve I have about that, is that for less than trivial extensions, you've got to go searching for the dependencies yourself. Not like some other extension systems like Node (NPM) and Python Pip that just install these things for you or warn you if they can't. At the very least it would be nice if it gave such a message rather than often cryptic ones. I know I'm spoiled, but I can dream. I'll save using PGXN for another article.

I'll start off with a couple of stumbling blocks you may have if you are new to compiling stuff on Linux. Most Linux/Unix folks can skip these sections, since they probably are already setup. Everytime I spin up a Linux VM, it for some reason never has these things installed.

You need gcc-c++ and git

As with all platforms where you intend to compile stuff like PostGIS and PostgreSQL extensions, you need a functioning gcc-c++. You know you don't have one when you get messages like: No way to build binaries

Should fix that

ogr_fdw not currently in pgxn, got to get from git repo

If you don't have git client already installed, just run this:

You need GDAL Develop and PostgreSQL develop

When you install PostGIS / PostgreSQL via Yum, it installs all needed to run PostGIS, which includes GDAL, but not necessarily GDAL development or PostgreSQL develop package. So if you do this:

gdal-devel.x86_64                           1.9.2-7.rhel6                @pgdg94
gdal-java.x86_64                            1.9.2-7.rhel6                @pgdg94
gdal-libs.x86_64                            1.9.2-7.rhel6                @pgdg94

and you don't see listed: gdal-devel.x86_64 (that is for x64, for 32-bit it would be i686), then you need to do a:

Similarly you don't need PostgreSQL development to run PostgreSQL, but need it for compiling extensions:

The 94 you should replace with the version you are compiling for.

Should include postgresql94-devel in there. Mine for reference looked like:

postgresql94.x86_64    9.4.4-1PGDG.rhel6
postgresql94-contrib.x86_64
postgresql94-devel.x86_64
postgresql94-libs.x86_64
postgresql94-server.x86_64

If you don't see it listed, then run

Ready to download, compile, and install ogr_fdw

mkdir /sources
chmod -R 777 /sources #this is needed for installcheck to work to give postgres daemon access
cd /sources
git clone -b master https://github.com/pramsey/pgsql-ogr-fdw.git pgsql_ogr_fdw
cd pgsql_ogr_fdw
export PATH=/usr/pgsql-9.4/bin:$PATH   # this is needed because pg_config is not in path
make && make install
export PGUSER=postgres
make installcheck

The make installcheck part is very finicky because the postgres service account needs rights to read the data folder to reference the test dbf and on top of that you need to be a superuser to build the foreign. So don't be too upset if your installcheck yields failure instead of:

(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test ogr_fdw                  ... ok

=====================
 All 1 tests passed.
=====================

Happy compiling

Using ogr_fdw in a database

Well first you install the extension in your database with a

/connect mydb
CREATE EXTENSION postgis; --only needed if you want to connect to a spatial datasource
CREATE EXTENSION ogr_fdw;

The rest of this I stole from Paul Ramsey's ogr_fdw page, adding a query for demonstration:

CREATE SERVER opengeo
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource 'WFS:http://demo.opengeo.org/geoserver/wfs',
    format 'WFS' );

CREATE FOREIGN TABLE topp_states (
  fid integer,
  geom geometry,
  gml_id varchar,
  state_name varchar,
  state_fips varchar,
  sub_region varchar,
  state_abbr varchar,
  land_km real,
  water_km real,
  persons real,
  families real,
  houshold real,
  male real,
  female real,
  workers real,
  drvalone real,
  carpool real,
  pubtrans real,
  employed real,
  unemploy real,
  service real,
  manual real,
  p_male real,
  p_female real,
  samp_pop real )
  SERVER opengeo
  OPTIONS ( layer 'topp:states' );
  
SELECT ST_Area(geom), persons, families, p_male, p_female, state_abbr
FROM topp_states
LIMIT 2;

Outputs --

      st_area       |   persons   |  families   | p_male | p_female | state_abbr
--------------------+-------------+-------------+--------+----------+------------
    15.396467068064 | 1.14306e+07 | 2.92488e+06 |  0.486 |    0.514 | IL
 0.0177697208289998 |      606900 |      122087 |  0.466 |    0.534 | DC

My next test will be to experiment with UnixODBC to see if I can connect my windows SQL Server install to my CentOS PostgreSQL install.

For windows users, reading this. I've had a change of heart, and will be putting CURL support back in and with SSL support. I managed to get my curl binaries to use the packaged EDB ssleasy32 and libeay32.dll by making sure I compile against the same version (and no higher).

There are two PostgreSQL FDWs (currently maintained) I know of for connecting to SQL Server from a Linux/Unix PostgreSQL box. There is the TDS Foreign Data wrapper (tds_fdw driver) which relies on the Free TDS driver. This is a fairly light-weight FDW since it just relies on TDS which is commonly already available on Linux installs or an easy install away. Unfortunately when I tried to use it on windows (compiling my usual mingw64 way), while it compiled and installed, it crashed when I attempted to connect to my SQL Server 2008 R2 box table, so I gave up on it for the time being as a cross-platform solution. One thing I will say about it is that it accepts ad-hoc queries from what I can see, as a data source, which is pretty nice. So we may revisit it in the future to see if we can get it to work on windows. I'm not sure if tds_fdw would support SQL Server spatial geometry columns though would be interesting to try.

The second option, which as you may have noticed, we spent much time talking about is the ogr_fdw foreign data driver. ogr_fdw utilizes UnixODBC on Linux, iODBC on MacOSX and Windows ODBC on windows for connecting to SQL Server. The ogr_fdw big downside is that it has a dependency on GDAL, which is a hefty FOSS swiss-army knife ETL tool that is a staple of all sorts of spatial folks doing both open source and proprietary development. The good thing about ogr_fdw, is that since it is a spatial driver, it knows how to translate SQL Server geometry to it's equivalent PostGIS form in addition to being able to handle most of the other not-so spatial columns.

Although GDAL is big, the good news is that, PostGIS relies on it as well since PostGIS 2.0 to support raster functionality. That was one of my ulterior motives for pushing raster into the PostGIS extension in 2.0: There will come a day when PostgreSQL will need to reach out to vast different kinds of spatial and not-so spatial data and GDAL would be a convenient ring to do so, so lets start planting the roots.. This makes compiling and installing ogr_fdw on Linux pretty trivial if you already have PostGIS with raster support installed and even easier now that PGDG Yum packages it in the repo.

Until recently, I've only used ogr_fdw on Windows and have been very happy with it connecting to all sorts of datasources from open-street map extract, dbase files, excel spreadsheets, MS Access databases, and SQL Server, it's a real gem. That is not to say it couldn't stand for many improvements. For a good chunk of these like OSM and MySQL and SQLite, GDAL doesn't rely on ODBC and uses the native drivers directly. Recently people have been writing me about how they can use it on Linux to connect to SQL Server. Yes, my jaw dropped, Linux people want to connect their PostgreSQL to SQL Server, why the heck would they want to do that. I thought maybe it's a good idea to try this out myself to experience first hand issues people are running into rather than simply relaying the information between people on what they tried that worked and didn't work. So here is my naive attempt to do so and distill the body of information that people have been sending me.

There are two UnixODBC drivers you can use for connecting to SQL Server. There is the TDS based one, and in theory, if you are on CentOS/Red Hat EL (5,6) or SUSE Linux EL 11, you can also use the Microsoft provided via: https://msdn.microsoft.com/en-us/library/hh568454%28v=sql.110%29.aspx which I have not attempted, but may in a future article.

We'll use the more commonly available TDS driver which I think works on pretty much all Linux/Unix and MacOSX systems. This I am doing on a CentOS 7 box.

If you installed ogr_fdw or PostGIS using, yum, then you probably have UnixODBC installed, to verify do this:

We get this for output

unixODBC.x86_64                  2.3.1-10.el7                          @base

I should note, that if GDAL isn't compiled with UnixODBC support, you have a much harder hurdle to jump. Luckily as far as I can tell, I think GDAL is generally compiled with UnixODBC support on Linux and possibly on Mac as well.

Next see what drivers you have installed already

On our CentOS 7 box, got returned:

[PostgreSQL]
[MySQL]

Setting up UnixODBC connection using TDS ODBC Driver

Here are the following steps. Much of this information is gleaned from unixODBC - MS SQL Server. You need to do this if FreeTDS didn't show up as an option when querying odbc manager.

1. Install FreeTDS Driver
   sudo yum install freetds
   

   This is a RedhatEL/CentOS/Fedora way, on Ubuntu you'd probably need to replace yum install with apt-get sudo yum install freetds

   Dependencies Resolved
   
   ==============================================================================================================================================================================================================
    Package                                        Arch                                          Version                                                       Repository                                   Size
   ==============================================================================================================================================================================================================
   Installing:
    freetds                                        x86_64                                        0.91-12.git0a42888.el7                                        epel                                        1.1 M
   
   Transaction Summary
   ==============================================================================================================================================================================================================
   Install  1 Package
   
   Total download size: 1.1 M
   Installed size: 9.8 M
   Is this ok [y/d/N]: y

2. Verify you got FreeTDS compiled with UnixODBC support.
   tsql -C

   Output should look something like

   Compile-time settings (established with the "configure" script)
                               Version: freetds v0.91
                freetds.conf directory: /etc
        MS db-lib source compatibility: yes
           Sybase binary compatibility: yes
                         Thread safety: yes
                         iconv library: yes
                           TDS version: 4.2
                                 iODBC: no
                              unixodbc: yes
                 SSPI "trusted" logins: no
                              Kerberos: yes

3. Find where the library is installed
   ldconfig -p | grep libtdsodbc.so

   Should output something like

   libtdsodbc.so.0 (libc6,x86-64) => /lib64/libtdsodbc.so.0

4. Now connect as postgres account. Create a driver template file, doesn't really matter where you put it or call it, since it will be copied by the installer, contents should contain

   [FreeTDS]
   Description     = FreeTDS for connecting to Sybase and SQL Server
   Driver          = /lib64/libtdsodbc.so.0

   Make sure to replace the driver with whatever path ldconfig gave. We called ours tds.driver.template

5. Install the driver template
   

   su postgres    
   odbcinst -i -d -f tds.driver.template

   Should get an output:

   odbcinst: Driver installed. Usage count increased to 1.
       Target directory is /etc

6. Verify you now have TDS driver
   odbcinst -d -q

   Output should now include FreeTDS

   [root@centie source]# odbcinst -d -q
   [PostgreSQL]
   [MySQL]
   [FreeTDS]

7. Register a connection to your SQL Server box
   

   To do this we need to create a datasource template file and register that with odbcinst manager similar to what we did with the driver as follows:

   Create a file call it tds-testmssql.datasource.template and has contents as follows. Note the [..] is the name you want to refer to your data source and should be different for each database you want to connect to.

   [MSSQLTDSTest]
   Driver  = FreeTDS
   Description     = My test database
   Trace   = No
   Server      = 192.168.1.25
   Port      = 1433
   Database        = Northwind
   TDS Version      = 7.3

   using a server name or fully qualified domain name instead of the ip of the SQL Server works just as well.

   Note: From other people's experiences I been informed that TDS Version needs to be 7.3 or above to work with ogr_fdw.

   Then run:

   odbcinst -i -s -f tds-testmssql.datasource.template
8. Test by connecting:
   isql -v MSSQLTDSTest your_user_name your_password

   You should be able to execute queries on console. For a simple exercise try this:

   SELECT @@VERSION

   Should output something like:

   +-------------------------------------------------------------------------------------------------------+
   | Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)
           Mar 19 2015 12:32:14
           Copyright (c) Microsoft Corporation
           Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: )
                                                                           |
   +-------------------------------------------------------------------------------------------------------+

   Type: quit

   To get out.

Connecting to SQL Server using ogr_fdw

Connecting to SQL Server using ogr_fdw is more or less the same at this point regardless of if you are using Windows or Unix/Linux.

• Install ogr_fdw if you haven't already. Thanks to Devrim, this should be a simple exercise on any platform supported by PGDG Yum repo.

  yum install ogr_fdw94;

  (if you are running PostgreSQL 9.3 or above. Of course replace the 94 with version you are running.)

• Install the extension in your database. I'm dong the full create, and install here using psql

  su postgres
  psql
  CREATE DATABASE test_ogr_fdw;
  \connect test_ogr_fdw;
  CREATE EXTENSION ogr_fdw;
  \q

• Use the include ogr_fdw_info commandline tool to figure out the server and fdw for a table. Connecting to ODBC sources with GDAL/OGR is described in more detail here: http://www.gdal.org/drv_odbc.html I'll use my favorite example of information_schema.columns. You should replace with what you want to connect to.

  ogr_fdw_info -s "ODBC:your_user_name/your_password@MSSQLTDSTest" -l "information_schema.columns"

  This outputs something of the form:

  CREATE SERVER myserver
    FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS (
      datasource 'ODBC:your_user_name/your_password@MSSQLTDSTest',
      format 'ODBC' );
  
  CREATE FOREIGN TABLE information_schema_columns (
    fid integer,
    geom geometry,
    table_catalog varchar,
    table_schema varchar,
    table_name varchar,
    column_name varchar,
    ordinal_position integer,
    column_default varchar,
    is_nullable varchar,
    data_type varchar,
    character_maximum_length integer,
    character_octet_length integer,
    numeric_precision varchar,
    numeric_precision_radix integer,
    numeric_scale integer,
    datetime_precision integer,
    character_set_catalog varchar,
    character_set_schema varchar,
    character_set_name varchar,
    collation_catalog varchar,
    collation_schema varchar,
    collation_name varchar,
    domain_catalog varchar,
    domain_schema varchar,
    domain_name varchar )
    SERVER myserver
    OPTIONS ( layer 'INFORMATION_SCHEMA.COLUMNS' );

  Note that ogr_fdw always tacks on an fid and sometimes a geometry column. In case of a SQL Server that has no spatial columns, this is just an artifact, and can be changed to bytea and ignored in use.

  So revise statements as follows and run in psql.

  
  \connect test_ogr_fdw  
    
  CREATE SERVER mssql_tds_test
    FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS (
      datasource 'ODBC:your_user_name/your_password@MSSQLTDSTest',
      format 'ODBC' );
  
  CREATE FOREIGN TABLE tds_information_schema_columns (
    fid integer,
    geom bytea,
    table_catalog varchar,
    table_schema varchar,
    table_name varchar,
    column_name varchar,
    ordinal_position integer,
    column_default varchar,
    is_nullable varchar,
    data_type varchar,
    character_maximum_length integer,
    character_octet_length integer,
    numeric_precision varchar,
    numeric_precision_radix integer,
    numeric_scale integer,
    datetime_precision integer,
    character_set_catalog varchar,
    character_set_schema varchar,
    character_set_name varchar,
    collation_catalog varchar,
    collation_schema varchar,
    collation_name varchar,
    domain_catalog varchar,
    domain_schema varchar,
    domain_name varchar )
    SERVER mssql_tds_test
    OPTIONS ( layer 'INFORMATION_SCHEMA.COLUMNS' );

  Now if you have a lot of tables, the CREATE SERVER process (and selecting from an FDW table) tends to take a while (like 30 seconds), you can winnow down the list a bit to only list tables you would ever want to connect to like so:

  ALTER SERVER mssql_tds_test 
      OPTIONS(SET datasource 'ODBC:your_user_name/your_password@MSSQLTDSTest,information_schema.columns,dbo.ZIPS,someschema.whatever');
  

• Do a test query:

  SELECT ordinal_position As ord, table_name, column_name, data_type, character_maximum_length
  FROM tds_information_schema_columns
  WHERE table_name = 'ZIPS';

  Which for my particular SQL Server db outputs:

   ord | table_name | column_name | data_type | character_maximum_length
  -----+------------+-------------+-----------+--------------------------
     1 | ZIPS       | Country     | varchar   |                        4
     2 | ZIPS       | ZipCode     | varchar   |                       12
     3 | ZIPS       | ZipType     | varchar   |                        2
     4 | ZIPS       | CityName    | varchar   |                       80
     5 | ZIPS       | CityType    | varchar   |                        2
     6 | ZIPS       | CountyName  | varchar   |                       80
     7 | ZIPS       | CountyFIPS  | varchar   |                        5
     8 | ZIPS       | StateName   | varchar   |                       80
     9 | ZIPS       | StateAbbr   | varchar   |                       40
    10 | ZIPS       | StateFIPS   | varchar   |                        4
    11 | ZIPS       | MSACode     | varchar   |                        8
    12 | ZIPS       | AreaCode    | varchar   |                       12
    13 | ZIPS       | TimeZone    | varchar   |                       24
    14 | ZIPS       | UTC         | varchar   |                       12
    15 | ZIPS       | DST         | varchar   |                        2
    16 | ZIPS       | Latitude    | float     |
    17 | ZIPS       | Longitude   | float     |
    18 | ZIPS       | id          | int       |
  (18 rows)

  Now although SQL Server isn't case sensitive, since PostgreSQL is, the example has to match the case of the SQL Server table.

• I chose use of information_schema.columns because from it, it's fairly trivial to create a new foreign table. So if you wanted a new foreign table, you'd do this:

  SELECT 'CREATE FOREIGN TABLE ' || table_name || ' (fid int, geom bytea, ' || 
      string_agg(column_name || ' ' || data_type, ',' ORDER BY ordinal_position ) || ') 
  SERVER mssql_tds_test 
  OPTIONS ( layer ''' || table_schema || '.' || table_name || ''' ) '
  FROM tds_information_schema_columns 
  WHERE table_name IN('ZIPS') AND table_schema = 'dbo'
  GROUP BY table_schema, table_name;
  Warning: This example leaves out logic to handle data types like datetime that need to be mapped to different timestamp in PostgreSQL and other oddities like quote ident.
  
  The output of the above query would be this which can then be used to define a new foreign table against the SQL Server
  CREATE FOREIGN TABLE ZIPS (fid int, geom bytea, Country varchar,ZipCode varchar,ZipType varchar,CityName varchar,CityType varchar,
  CountyName varchar,CountyFIPS varchar,StateName varchar,StateAbbr varchar,
  StateFIPS varchar,MSACode varchar,AreaCode varchar,TimeZone varchar,UTC varchar,DST varchar,
  Latitude float,Longitude float,id int) 
  SERVER mssql_tds_test 
  OPTIONS ( layer 'dbo.ZIPS' );
  
  

• Test out the new table:

  SELECT zipcode, cityname from zips WHERE statename ILIKE 'New York' limit 100;

Someone mentioned to me that their list of tables that have compound keys didn't show up as options on Linux, but did when using ogr_fdw under windows. I haven't experimented with that to see if I can replicate the issue.