Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Artifact ID:	a58449a6571c1b1a6a16e2c4c47ef59275559cdc
Page Name:	PostgreSQL
Date:	2018-08-03 16:12:08
Original User:	sandro
Parent:	77aaae0fe821aff22b0c99c06b08d10ac06e7ad4 (diff)
Next	63d84f2faee22912104dd85f917bfd9bdb72e3ce

Content

Back to main Wiki page

The GUI as a PostgreSQL client

Starting from version 2.1.0 the GUI has the capability to support connections to external PostgreSQL / PostGIS databases.
This functionality is mainly based on libvirtualpg, 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 spatialite_gui.

1 - Establishing a Connecting to a PostgreSQL DBMS
1.a you can press the appropriate button from the Toolbar
1.b you can select the appropriate item from the main Menu
1.c and finally you can select the appropriate item from the context Menu that will appear by clicking the left button of the mouse over the PostreSQL main node from the Table Tree.
1.d in all three cases the PostgreSQL connection dialog will appear. Just insert the required connection arguments and confirm. If the given arguments are invalid an error message will be then displayed, othrwise the connection will be immediately established. If you are attempting to connect a remote server, you could probably find useful reading first the tutorial about SSH tunneling and Port Forwarding
2 - Expanding the PostgreSQL tree
2.a once that a new Postgres connection has been succesfully established the Postgres node on the main Table Tree will contain all Tables and Views defined on the Postgres Database, accordingly to the usual arrangement adopted by Postgres: Connection aka Database Schema Table or View Each kind of database object will be clearly identified by an appropriate icon; Views will be distinguished from Tables, and PostGIS Tables (containing Geometry columns) will be clearly distinguished from ordinary Tables. Notice: all Postgres Tables and Views (namely: VirtualPostgres Tables) will always have a conventional name starting with a vpg_ prefix. Whenever possible the original Postgres Name will be preserved, otherwise it will be disambiguated by adding an appropriate numeric suffix. Note: all database objects in the Postgres Tree are inerently transient, and will automatically disappear when the corresponding connection will be closed, or when the current session ends.
2.b if, for any possible reason (system crash, power failure and alike), the current session will fail to correctly perform the expected final cleanup, your SQLite database will remain in a dirty and inconsistent state. As the side figure shows, when restarting a new session many *orphan VirtualPostgres tables* will badly remain from the last aborted session. If this is your case, don't despair: you just have to select the appropriate item from the Postgres contaxt menu and the DB will be immediately recovered in a safe consistent and clean state.
3 - Metadata of PostgreSQL objects
3.a you can easily query the metadata for each Postgres object; just select the appropriate item from the context menu.
3.b Metadata for a Connection aka Database object will report the initial connection arguments. Note: you can eventually connect more than a single Postgres Database in the same sesson: connecting twice the same Database is allways forbidden.
3.c Metadata for a Schema object aren't really interesting, because just the name is reported.
3.d Metadata for Table or View objects will report all GRANT permissions for the current User. Note: also in the case of a READ-WRITE enabled connection, all Postgres Tables lacking the permission to execute INSERT, UPDATE or DELETE statements will be allways considered to be READ-ONLY. Exactly as any other Table not supported by a PRIMARY KEY definition.
3.e Metadata for a PostGIS Table objects will report a full qualification of the GEOMETRY column(s), this including the GeometryType, the Srid and the CoordinateDimensions.
3.f all PostGIS Tables will always have a companion Table sharing the same identical name except that for a final _postgis suffix. Note: these are not VirtualPostgres Tables; they are instead Spatialite's own Spatial Views, and their intended scope is to perform a silent and fully transparent conversion of binary Geometries nack and forth from one format to the other in the most user friendly way.
4 - Handling PostgreSQL Tables and Views
4.a PostgreSQL Tables and Views are considered, as far as possible, as if they were native SQLite objects. So the usual operations supported on SQLite's own objects will be supported also in the case of PostgreSQL objects.
4.b the above criteria will apply also to PostGIS Geometries. Hint: exporting a full Shapefile from a PostGIS Geometry was never more direct and simple than this.