Sunday, December 01. 2024
Printer Friendly
I was reviewing some old code when I stumbled across something I must have completely forgotten
or someone else some time ago knew. That is that it is possible to use the function substring for regular expression work.
Continue reading "Substring function Regex style"
Monday, November 25. 2024
Printer Friendly
When I work with other relational databases I am reminded about how I can't use my favorite PostgreSQL hacks in them.
I call these hacks PostgreSQLisms. A PostgreSQLism is a pattern of SQL unique to PostgreSQL or descendants of PostgreSQL.
In this post I'm going to cover some of my favorite ones.
Continue reading "Some of my favorite PostgreSQLisms"
Tuesday, March 05. 2024
Printer Friendly
The adoption of PostgreSQL is growing each year. Many people coming to PostgreSQL are often coming from other
relational databases, with assumptions of how relational databases work. Although PostgreSQL may feel very familiar to these
people, it is different enough to cause some misunderstandings which lead to bad and slow queries.
There are also people coming often from only programming backgrounds, who assume data processing in SQL is much like
data processing in any language. For these two groups of folks, I think the
new book written by EDB and 2nd Quadrant Author, Jimmy Angelakos, "PostgreSQL Mistakes and How To Avoid Them" will save them a lot of miss-steps.
Continue reading "PostgreSQL Mistakes and How To Avoid them"
Wednesday, November 23. 2022
Printer Friendly
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.
Continue reading "VARIADIC Unnest"
Thursday, June 14. 2018
Printer Friendly
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.
Remolding can take the form of restructuring data into json documents suitable for web maps, javascript charting web apps, or datagrids. It also has uses beyond just outputting data in json form. In addition the functions are useful for unraveling json data into a more meaningful relational form.
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.
Continue reading "Unpivoting data using JSON functions"
Saturday, June 17. 2017
Printer Friendly
PostgreSQL has a feature
called dollar-quoting,
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;
Continue reading "Dollar-quoting for escaping single quotes"
Friday, April 15. 2016
Printer Friendly
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.
Continue reading "First Look at pgAdmin 4 "
Friday, January 29. 2016
Printer Friendly
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)
Continue reading "An almost idiot's guide to install PostgreSQL 9.5, PostGIS 2.2 and pgRouting 2.1.0 with Yum"
Saturday, September 19. 2015
Printer Friendly
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
Continue reading "Compiling and installing ogr_fdw on CentOS after Yum Install PostgreSQL PostGIS"
Wednesday, March 26. 2014
Printer Friendly
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.
Continue reading "An almost idiot's guide to install PostgreSQL 9.3, PostGIS 2.1 and pgRouting with Yum"
Saturday, December 21. 2013
Printer Friendly
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.
Continue reading "Using wget directly from PostgreSQL using COPY FROM PROGRAM"
Saturday, November 09. 2013
Printer Friendly
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:
Continue reading "Migrating from SQL Server to PostgreSQL"
Saturday, April 13. 2013
Printer Friendly
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.
Continue reading "Determine which version of PostGIS each database is running"
Monday, December 31. 2012
Printer Friendly
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
Printer Friendly
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.
Continue reading "Schema and search_path surprises"
|