Wednesday, November 23. 2022
Continue reading "VARIADIC Unnest"
PostgreSQL keeps on adding new great stuff. It's hard to remember all the good stuff that has been added over the years.
One of the neat ones from the past is the variadic Unnest function which I believe was introduced in PostgreSQL 9.4. It's rare that I ever had to use it, but today I was handed some data where this function was just literally what the doctor ordered. I can't do anything this sweet in other databases I have used.
Thursday, June 14. 2018
Continue reading "Unpivoting data using JSON functions"
Most of our use-cases for the built-in json support in PostgreSQL is not to implement schemaless design storage, but instead to remold data.
One of the common cases we use json support is what we call UNPIVOTING data.
We demonstrated this in Postgres Vision 2018 presentation in slide 23.
This trick won't work in other relational databases that support JSON because
it also uses a long existing feature of PostgreSQL to be able to treat a row as a data field.
Saturday, June 17. 2017
Continue reading "Dollar-quoting for escaping single quotes"
PostgreSQL has a feature
which allows you to include a body of text without escaping the single quotes. This feature has existed for quite some time.
You've probably seen this in action when defining functions for example:
CREATE OR REPLACE FUNCTION hello_world(param_your_name text)
RETURNS text AS
SELECT 'Hello world. My name is ' || param_your_name || '.';
language sql STRICT;
Which is easier to read, than the equivalent escape quoted function:
CREATE OR REPLACE FUNCTION hello_world(param_your_name text)
RETURNS text AS
SELECT ''Hello world. My name is '' || param_your_name || ''.'';
language sql STRICT;
Friday, April 15. 2016
Continue reading "First Look at pgAdmin 4 "
When David Page announced pgAdmin 4, I was really excited to try it out. I was impressed I could compile it so easily on windows. I had a few bumps, but not too bad.
One of the reasons I'm excited about it is that it's built on Python and a web framework, and there is a large Python and web developer following in the GIS community, so I suspect someone will step up to the plate to add a mapviewer plugin to this so I can have a seamless PostGIS experience.
The interface is also very slick and pretty and I love the sorting and paging capability now in the query window. Check this sampling from our workshop database.
Friday, January 29. 2016
Continue reading "An almost idiot's guide to install PostgreSQL 9.5, PostGIS 2.2 and pgRouting 2.1.0 with Yum"
If you already have a working PostgreSQL 9.5 install, and just want to skip to relevant sections, follow this list:
As a general note, these instructions are what I did for CentOS 7. For lower versions ther are some differences in packages you'll get.
For example currently if you are installing on CentOS 6 (and I presume by extension other 6 family), you won't get SFCGAL and might have pgRouting 2.0 (instead of 2.1)
Saturday, September 19. 2015
Continue reading "Compiling and installing ogr_fdw on CentOS after Yum Install PostgreSQL PostGIS"
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.
UPDATE pgdg yum now has ogr_fdw as an offering. If you are on PostgreSQL 9.4, you can now install with :
yum install ogr_fdw94
Wednesday, March 26. 2014
Continue reading "An almost idiot's guide to install PostgreSQL 9.3, PostGIS 2.1 and pgRouting with Yum"
In this exercise, we'll go thru installing PostgreSQL 9.3 on a CentOS 6 64-bit box. We'll cover upgrading in a later article. For the rest of this article, we'll go over configuring yum to use the PostgreSQL PGDG Yum repository found at http://yum.postgresql.org
, which has the latest and greatest of 9.3. It's been a while since we wrote step by step instructions for installing with Yum.
Note: PostGIS 2.1.2 release is eminent, so you might want to wait till we release and Yum is updated before you install/upgrade.
Saturday, December 21. 2013
Continue reading "Using wget directly from PostgreSQL using COPY FROM PROGRAM"
One of the features new to PostgreSQL 9.3 is the
COPY FROM PROGRAM sql construct and in psql the equivalent
\copy from program. Michael Paquier covered the COPY TO/FROM PROGRAM in hist Postgres 9.3 feature highlight: COPY TO/FROM PROGRAM. Depesz covered the companion psql construction in
Support for piping copy to from an external program.
Michael demonstrated an example using curl. I wanted to try something similar using wget since I have wget readily available on all my Linux and Unix boxes. For this example I'll demonstrate doing it on windows, but doing it on Linux is much the same and simpler since the wget and curl are generally already in Linux default path.
Saturday, November 09. 2013
Continue reading "Migrating from SQL Server to PostgreSQL"
Alexander Kuznetsov on SQLblog.com
has an interesting series going entitled with Learning PostgreSQL. In the series he focuses on what it takes to move
a SQL Server database and App to PostgreSQL and highlights some of the key differences between the two platforms that you should watch out for.
I recommend it to any SQL Server developer planning to make the switch to PostgreSQL or any PostgreSQL consultant tasked with the job
and not familiar with the intricacies of SQL Server.
His PostgreSQL feed can be found here here
So far on his list of articles in the series:
Saturday, April 13. 2013
Continue reading "Determine which version of PostGIS each database is running"
One of the features of PostGIS (pain to some however you look at it), is that PostGIS library file is versioned by minor version. The library will have for example a postgis-2.0 or postgis-1.5.dll / .so to denote the version. Each version of PostGIS can be compiled to run on usually about 3 or 4 versions of PostgreSQL.
Since PostGIS is not part of PostgreSQL proper and has to be installed separately, it is possible to run a different version of PostGIS in each database of a cluster. While this is a great feature for PostGIS developers and also great for users who want to keep their old legacy PostGIS apps, while testing or creating new apps with the PostGIS 2.0 or experiment with 2.1 development series, it does pose some obvious challenges.
For example you can't simply just upgrade your cluster to a new version of PostgreSQL. You need to make sure the new cluster has the various versions of PostGIS compiled and available. One step to that end is figuring out exactly what version of PostGIS each database in your cluster is running. Here is a quick psql script I wrote up to help with that.
Monday, December 31. 2012
We recently started upgrading some of our databases on windows 64-bit to 9.2, in doing so we ran into a nasty issue we discovered when some of our backups were failing. The first time it happened, I chucked it up to a dirty PostgreSQL 8.4 database being restored to PostgreSQL 9.2.1. The second time it happened restoring a 9.1 database to 9.2.2, I thought, better look into this to see if there is a known issue. Low and behold I found this:
http://archives.postgresql.org/pgsql-bugs/2012-12/msg00091.php (Bug #7741). Apparently something to do with granting rights on Types.
As a workaround for this problem so our backups would work again was to delete the offending permissions from system tables. It's probably not the best way but only way we could think of, we delete the bad record in pg_default_acl and after that backup works without complaint.
SELECT * into zz_bad_pg_default_acl FROM pg_default_acl WHERE defaclobjtype = 'T';
DELETE from pg_default_acl WHERE defaclobjtype = 'T';
If anyone else has further input on this, I'd be interested.
Wednesday, November 28. 2012
Continue reading "Schema and search_path surprises"
This was an article we were meaning to write and got reminded by Szymon Guz latest PostgreSQL search_path behavior.
The PostgreSQL search_path variable allows you to control what order schemas are searched and which schemas do not require schema qualification to use tables/views/functions in the schema. The pg_catalog schema is one that never requires schema qualification even if you don't add it to your schema search_path. Schemas are searched in the order they are listed
and when creating new objects (e.g. tables, views, function, types) that are not schema qualified, they are always created in the first schema of the search path. Any objects not in a schema listed in the search_path must be schema qualified.
Saturday, November 10. 2012
Continue reading "Biggest Obstacle to PostgreSQL Adoption: It is not Database X"
We've been fighting to get clients we have thinking of upgrading or creating new apps to also choose PostgreSQL in the process.
Here I'll just itemize some of the obstacles we've run into in making the sale. All of these fall under the It is not Database X line item.
By database X I mean SQL Server, MySQL, and Oracle and for us in exactly that order. Our obstacle focus is probably a bit different from others since
we are consultants to mostly Windows shops or consultants to ISVs who have to sell their applications to U.S. government agencies or units of agencies.
Monday, September 17. 2012
Chris Travers has an interesting series going on about the Object-Oriented and Polymorphic like features that PostgreSQL has built-in. It reminded me of this syntax
I have rarely seen used with PostGIS, that is perfectly valid and may be more familiar looking to Oracle and SQL Server spatial folks. It looks something like this, though sadly
will only work on unary functions.
SELECT ('LINESTRING(1 2, 3 4)'::geometry).ST_Length;
More common though, if you were to have a table of say geography objects:
CREATE TABLE pois(gid serial primary key, geog geography(LINESTRING,4326));
INSERT INTO pois(geog)
VALUES ('LINESTRING(-164.2559 54.0558,-162.0943 54.33243)'::geography)
, ('LINESTRING(-46.2559 54.0558,-46.0943 54.33243, -47.1005 55.33243)'::geography);
SELECT (geog).ST_Length As len, (geog::geometry).ST_NPoints As n_pt
If you notice though, no keystrokes were saved. We've simply changed the order of the parenthesis. Damn those ().
Tuesday, July 10. 2012
Continue reading "Working with Timezones"
UPDATE TO UPDATE: Bruce Momjian suggested replacing the dynamic set local sql with set_config. We've revised further to incorporate this suggestion. That got rid of our last pet peeve about this function. Thanks all.
Simon Bertrang proposed using set local which seems much nicer. We've updated our function using his revision.
One of PostgreSQL's nice features is its great support for temporal data. In fact it probably has the best support for temporal data than any other database. We'll see more of this power in PostgreSQL 9.2 with the introduction of date time range types.
One of the features we've appreciated and leveraged quite a bit in our applications is its numerous time zone aware functions. In PostgreSQL timestamp with time zone data type
always stores the time in UTC but default displays in the time zone of the server, session, user. Now one of the helper functions we've grown to depend on is
to_char() which supports timestamp and timestamp with timezone among many other types and allows you to format the pieces of a timestamp any way you like. This function is great except for one small little problem, it doesn't allow you to designate the display of the output timezone and always defaults to the TimeZone value setting of the currently running session.
This is normally just fine (since you can combine with AT TIMEZONE to get a timestamp only time that will return the right date parts, except for the case when you want your display to output the time zone -- e.g. EDT, EST, PST, PDT etc (timestamp without timezone is timezone unaware). In this article we'll demonstrate a quick hack to get around this issue. First let's take to_char for a spin.