Connecting to postgresql from a range of different front-ends
Posted June 9, 2008 
Comments(2)
Comments(2) Aware that there haven’t been Thursday Tip days for a couple of weeks, or indeed anything else in the way of blog posts (follow-up post coming along soon)- here’s a real quicky:
If you want to be able to connect to your postgresql data using an external programme (such as Open Office Base, QGIS, gvSIG or Mapserver) AND be able to properly view/select and edit the data, you will need to do the following:
- Ensure that the table you are trying to view has a primary key. Error messages if you don’t do this might vary, or be non-existent, but the end result will be that your data won’t display on the map, or won’t be editable.
- Ensure that there is an entry in the geometry_columns table for the table you are trying to view. The geometry_columns table is specific to postgis and contains metadata about your tables. If the geometry of your table was created using the addgeoemtry function then this will be filled in, but otherwise it won’t. The function probe_geometry_columns() may fix this if run at the psql command line, but sometimes it doesn’t. But, you can fill in records in the geometry_columns table in the normal way. The table contains the following columns:
- F_TABLE_CATALOG (this is left blank for postgresql- it’s an oracle thing apparently)
- F_TABLE_SCHEMA (the schema of your table- if you haven’t set this it’s likely to be the default PUBLIC)
- F_TABLE_NAME (tha name of your table)
- F_GEOMETRY_COLUMN (the name of the geometry column in your table)
- COORD_DIMENSION (the number of spatial dimensions of your geometry- 2 or 3, or 4 if you’re really ambitious)
- SRID (the EPSG code for your projection, eg 4326 for lat/long, 27700 for British National Grid)
- TYPE (the type of spatial object held in your table, eg point, line, polygon etc etc)
The moral of the story- always include a primary key in any tables you expect people to select or edit data in, and always add an entry in geometry_columns if you want to display your spatial data on a map.
“always include a primary key in any tables you expect people to select or edit data in”
As a database developer, I’d go a little further and emphasise that you really must define a primary key for *every* table in a relational database such as Postgres, Oracle etc. This is a fundamental requirement of the relational database model – if you can’t define a way to identify each row in your table uniquely (the PK) then you haven’t finished normalising the data in the first place.
From a practical point of view, many DB tools expect you to have performed this basic step and may not work properly without a PK for each table. For example, the DB itself can use your PK definition to make sure you don’t accidentally create duplicate rows in a table. Also, PKs are typically indexed by default, so defining a PK will also speed up access to the data. Other tools, such as Java DB interfaces using Hibernate etc, will not work without a PK for each table.
Similar arguments apply to foreign keys i.e. when you include the PK of a row from table A inside a record in table B, so you can relate the “child” data in B back to its “parent” row in A. Defining a foreign key allows the DB to check that the parent row exists when you try to add a child record, so you can avoid creating “orphaned” data. Also, the DB can enforce extra rules based on the FK e.g. warning you if you try to delete a parent record which still has children, or “cascading” the delete to remove the children as well as the parent. And FKs are usually good candidates for indexing, as you often search for data using them, and indexing FKs will often speed up your DB access.
Again, this is a fundamental part of relational DB design: if you haven’t done this, you haven’t got a proper database, just a bunch of unrelated data.
Sorry to rant, but I’ve encountered too many “databases” where people have simply dumped their spreadsheets into an RDBMS without bothering to create a basic relational data model first (e.g. work out what the keys are for each table), then complain because their “database” is too slow, filled with rubbish/duplicate rows/orphans etc.
But I feel much better now.
Hey ChrisW, Thanks for the rant! In general I agree with you- but… with my comment I was thinking of lookup tables. Since all they are doing is providing values for dropdown lists and such like, they are not really part of the relational database because you don’t relate data in your main tables to them- you wouldn’t want cascading updates or deletes, and as such you can get away without a primary key. In general I would always include one, but I don’t think in those cases it’s totally necessary. Everything else though- agreed!