Back to <a Wiki page</a><hr><br>
<h1>The GUI as a PostgreSQL client</h1>
Starting from version <b>2.1.0</b> the GUI has the capability to support connections to external <b>PostgreSQL / PostGIS</b> databases.<br>
This functionality is mainly based on <a that is now a required dependency for the GUI tool.
The present tutorial is intended to quickly explain how the PostgreSQL support is implemented by <b>spatialite_gui</b>.<br><br>
<table cellspacing="6" cellpadding="6" bgcolor="#f0ffe0" width="100%" border="1">
<tr><td colspan="2" align="center"><h3>1 - Establishing a Connecting to a PostgreSQL
<tr>
<td><b>1.a</b> first press the appropriate button from the Toolbar</td>
<td><img alt="connection 1"></td>
</tr>
<tr>
<td><b>1.b</b> then select the appropriate item from the main Menu</td>
<td><img alt="connection 2"></td>
</tr>
</tr>
<tr>
<td><b>1.c</b> and finally select the appropriate item from the context Menu that will appear by clicking the <b>left</b> button of the mouse over the <b>PostreSQL</b> main node from the Table Tree.</td>
<td><img alt="connection 3"></td>
</tr><tr>
<td><b>1.d</b> in all three cases the PostgreSQL connection dialog will then appear.<br>
Just insert the required connection arguments and confirm.<br><br>
When invalid arguments are given, an error message  will be displayed, otherwise the connection will be immediately established.<br><br><br><hr><br><br><br>
If you are attempting to connect a remote server, you may find it useful to read the tutorial about <a tunneling and Port Forwarding</a>
<br><br><br>
<hr>
<table bgcolor="#ffcc80" cellspacing="6" cellpadding="6" border="1" width="100%">
<tr><td align="center"><h3>Special note for Windows
<tr><td>
Please read the <a href="#addendum">Addendum</a> about LibPQ late binding.


<td><img alt="connection 4"></td>
</tr>
<tr><td colspan="2" align="center"><h3>2 - Expanding the PostgreSQL
<tr>
<td><b>2.a</b> once a new Postgres connection has been successfully established, the Postgres node on the main Table Tree will contain all Tables and Views defined in the Postgres Database, in the same arrangement adopted by PostgreSQL:
<ul>
<li>Connection <i>aka</i> <b>Database</b>
<ul>
<li><b>Schema</b>
<ul>
<li><b>Table</b> or


</ul><br><br>
The different database types will be identified by an appropriate icon; Views will be distinguished from Tables, and <b>PostGIS</b> Tables (containing <b>Geometry</b> columns) will be clearly distinguished from ordinary Tables.<br><br>
<b>Note</b>: all Postgres Tables and Views (namely: <b>VirtualPostgres</b> Tables) will always have a conventional name starting with the <b>vpg_</b> prefix.<br>
Whenever possible the original Postgres Name will be preserved, otherwise a numeric suffix will be added, ensuring a unique name .<br><br><br>
<hr>
<br><br>
<b>Note</b>: all database objects in the Postgres Tree are dynamic, and will automatically disappear when the corresponding connection has been closed, or when the current session ends.</td>
<td><img alt="table tree"></td>
</tr>
<tr>
<td><b>2.b</b> if, for any possible reason (such as on: a system crash, power failure, etc.), the current session fails to correctly perform the expected final cleanup, your SQLite database will remain in a dirty and inconsistent state.<br>
As the side figure shows, when restarting a new session all <b>orphaned VirtualPostgres tables</b>, from any aborted session(s), can still be seen.<br><br>
If this happens, don't despair: all you need to do is to select the appropriate item from the Postgres context menu and the DB will be immediately be converted into a safe, consistent and clean state.</td>
<td><img alt="orphan tables"></td>
</tr>
<tr><td colspan="2" align="center"><h3>3 - Metadata of PostgreSQL
<tr>
<td><b>3.a</b> you can easily query the metadata for each Postgres object; just select the appropriate item from the context menu.</td>
<td><img alt="metadata info"></td>
</tr>
<tr>
<td><b>3.b</b> Metadata for a <b>Connection</b> <i>aka</i> <b>Database</b> object will report the initial connection arguments.<br><br><br>
<hr>
<br><br>
<b>Note</b>: you can always connect more than one Postgres Database in the same session; however connecting the same Database more than once is not permitted (a warning message will be shown in this case).</td>
<td><img alt="connection metadata"></td>
</tr>
<tr>
<td><b>3.c</b> Metadata for a <b>Schema</b> object aren't really very interesting, since only the name will be shown.</td>
<td><img alt="schema metadata"></td>
</tr>
<tr>
<td><b>3.d</b> Metadata for <b>Table</b> or <b>View</b> object will report all <b>GRANT permissions</b> for the current User.<br><br><br>
<hr>
<br><br>
<b>Note</b>: also in the case of a <b>READ-WRITE</b> enabled connection, all Postgres Tables lacking the permission to execute <b>INSERT</b>, <b>UPDATE</b> or <b>DELETE</b> statements will be treated as <b>READ-ONLY</b> Tables.<br>
The same is true for Tables where no <b>PRIMARY KEY</b> has been defined.</td>
<td><img alt="table metadata"></td>
</tr>
<tr>
<td><b>3.e</b> Metadata for a <b>PostGIS Table</b> objects show all settings of the <b>GEOMETRY</b> column(s), including the GeometryType, the Srid and the CoordinateDimension.</td>
<td><img alt="postgis metadata"></td>
</tr>
<tr>
<td><b>3.f</b> all <b>PostGIS Tables</b> will always have a <b>companion Table</b> sharing the same identical name, but with an added '<b>_postgis</b>' suffix.<br><br><br>
<b>Note</b>: these are not the original <b>VirtualPostgres Tables</b> which store the Geometries in the original Postgres binary format, but are instead Spatialite's own <b>Spatial Views</b> containing Geometries converted to the Spatialite binary format. The conversion between the two formats are done in the background and thus, for the user, in a compleatly transparent way.</td>
<td><img alt="postgis wrapper metadata"></td>
</tr>
<tr><td colspan="2" align="center"><h3>4 - Handling PostgreSQL Tables and
<tr>
<td><b>4.a</b> PostgreSQL Tables and Views are considered, as far as possible, as if they were native SQLite objects.<br>
So the usual operations supported on SQLite's own objects will be supported also in the case of PostgreSQL objects.</td>
<td><img alt="table operations"></td>
</tr>
<tr>
<td><b>4.b</b> The above criteria will apply also to PostGIS Geometries.<br><br><br>
<b>Hint</b>: exporting a full Shapefile from a PostGIS Table was never more direct and simple than this.</td>
<td><img alt="geometry operations"></td>
</tr>
<tr>
<td><b>4.c</b> be warned: directly querying a <b>PostGIS Table</b> (i.e. a VirtualPostgres Table containing PostGIS Geometries) never is a good idea.
For reasons explained in <b>4.d</b>, you'll be always asked <b>twice</b> before starting such an extensive (and potentially dangerous) action.</td>
<td><img alt="direct postgis query"></td>
</tr>
<tr>
<td><b>4.d</b> The reason for such a behaviour is clearly shown in the side figure.<br>
A VirtualPostgres Table will expose all Geometries as <b>EWKB text strings</b> using an hexadecimal notation; in this case we have simple POINTS, and the EWKB strings are reasonably compact.<br>
But unhappily complex LINESTRING or POLYGON Geometries may easily require several million characters, and this will easily over stress the GUI.<br>
The execution speed of such a query will certainly be <b>very</b> sluggish, and instability problems, caused by excessive memory use, could eventually arise.<br><br><br>
<b>Short conclusion</b>: never, ever, attempt to directly query a PostGIS Table !</td>
<td><img alt="direct postgis query"></td>
</tr>
<tr>
<td><b>4.e</b> To avoid this problem, a companion Spatial View exists for every PostGIS Table, and is specifically intended to support safe and trouble free queries.<br>
Also the Spatial View will take care to silently convert binary Geometries to and from the needed format, simplifying the writing of customized SQL queries using Spatial Functions since the user does not need to do any binary conversions of the Geometries.</td>
<td><img alt="indirect postgis query"></td>
</tr>
<tr>
<td><b>4.f</b> The companion Spatial View will always be a <b>Writable View</b> when the corresponding PostGIS Table is appropriately enabled and when the connection supports READ-WRITE operations.<br><br><br>
<b>Note</b>: All appropriate Triggers (supporting <b>INSERT</b>, <b>UPDATE</b> and <b>DELETE</b> operations), for a Writable Spatial View, will be automatically created.</td>
<td><img alt="postgis editing"></td>
</tr>
<tr><td colspan="2" align="center"><h3><a name="addendum">Addendum</a>: special note for Windows
<tr><td colspan = "2">
All previous versions of the GUI binaries released in the past were always based on a single monolithic <b>.exe</b> fully depending on static linkage, not requiring any other DLL at all.<br><br>
Unhappily this is no longer possible, since <b>libpq</b> (<i>the PostgreSQL own client library</i>) is only available as a DLL on Windows, and therefore cannot be used to create a corresponding static library.<br>
More precisely: a static library is actually built when compiling PostgreSQL, but on Windows it simply is a stub still requiring to load the DLL at run time.<br><br><br>
For this reason, <b>spatialite_gui.exe</b>, which otherwise depends on a static linkage, must make an exception for <b>libpq.dll</b>.<br>
So a reasonable compromise has been adopted in order to mitigate the negative impact of such an odd combination:
<ul>
<li>the binaries for Windows platforms will be built in such a way that <b>libpq.dll</b> will support what is generally known as <b>late binding</b> aka <b>deferred
<li>this way, the main core of the GUI  will continue to work without loading
<li><b>libpq.dll</b> will be only be loaded when needed (i.e. when the user requests a PostgreSQL connection).</li>
<li>if the DLL fails to be loaded for any reason, the PostgreSQL support will remain disabled and the GUI will continue unaffected.</li>
</ul>

<tr><td colspan="2" align="center"><h3>How to load LibPG, when it has not been automatically
<tr>
<td><b>A.1</b> <b>libpq.dll</b> will be loaded when accessing a PostgreSQL for the first time:
<ul>
<li>an automatic attempt will be performed accordingly to the standard platform rules (mostly: search paths).<br>
<b>Hint</b>: on Windows, placing the DLL in the same folder as the GUI executable, simplifys matters nicely.</li>
<li>if <b>libpq.dll</b> cannot be found, then the user is asked to manually load <b>libpq.dll</b>, as shown by the side figure.</li>

<td><img alt="libpq not found"></td>
</tr>
<tr>
<td><b>A.2</b> The standard File Selection dialog can be used to locate and open the <b>libpq.dll</b>.<br><br><br>
<b>Warning</b>: a common pitfall is attempting to load a 32/64 bit DLL to a 64/32 bit GUI.<br>
The code model (32/64 bit) of both the executable and the DLL must the same.</td>
<td><img alt="libpq open"></td>
</tr>
<tr>
<td><b>A.3</b> If <b>libpq.dll</b> has been successfully loaded, a confirmation message will be shown with the association between the GUI and the DLL permanently registered.<br>
Once successfully loaded, all the following sessions will use this association automatically, making this a one time effort.</td>
<td><img alt="libpq loaded"></td>
</tr>
</table>
Back to <a Wiki page</a>