beginner   

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;

All this time we've been using COPY FROM PROGRAM without any additional adornments. Then we noticed for some things like directory listings in Microsoft windows, it would come across a sequence of inputs it mangled or you may even get an error such as ERROR: end-of-copy marker corrupt.

The trick to get around the issue is to use the FORMAT option. The default FORMAT for COPY FROM PROGRAM is 'text', which tries to escape out some things and in doing so it misinterprets windows slashes so you get weird stuff or no stuff at all.

We'll be hosting the September 10th, 2013 PostgreSQL User Group meetup at Devonshire Sky Club (in the center of Boston Financial district and block away from city hall). This is the same venue we hosted the Boston OSGeo Code Sprint except this time we'll have some EnterpriseDb folks joining us. Sign-up while supplies last. Leo's got a nice cocktail and food lineup.

We'll be giving an introductory talk about PostGIS and also give a brief overview of what's packed in the PostGIS 2.1 release that is currently in beta, and that should be out by meetup time. Remember this is the first version of PostGIS to support upcoming PostgreSQL 9.3.

Not only will we have cocktails, but we'll also be giving out PostgreSQL/PostGIS door prizes.

PostgreSQL allows you to customize statement logging in numerous ways. You can globally set the level of logging you want at both the postgresql.conf (that will affect all databases) or at the database level using the various log_statement* variables. Most of these are documented in runtime config logging.

For this brief article, we'll talk about the log_line_prefix variable. By default this variable is very minimalistic and just prefixes the log lines with the date time of the statement. If you have just one database (like the way Oracle runs) this is not a big deal, but if you are like us and run several databases on one PostgreSQL instance, it would be nice to as part of the prefix include the database name.

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.

   -- there is no such thing as T for default priviledges that pg_dump understands based on 
-- https://github.com/adunstan/postgresql-dev/blob/master/src/bin/pg_dump/pg_dump.c#L11838
-- backup bad records just in case we need them again --
SELECT * into zz_bad_pg_default_acl FROM pg_default_acl  WHERE defaclobjtype = 'T'; 
-- delete unknown records --
DELETE from pg_default_acl WHERE defaclobjtype = 'T'; 

If anyone else has further input on this, I'd be interested.

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.

If I could name a number one feature I love most about PostgreSQL, it's the table inheritance feature which we described in How to Inherit and Uninherit. A lot of people use it for table partitioning using CONSTRAINT EXCLUSION. Aside from that, in combination with PostgreSQL schema search_path (customizable by user and/or database) it makes for a very flexible abstraction tool. For example, for many of our web apps that service many departments where each department/client wants to keep a high level of autonomy, we have a schema set aside for each that inherits from a master template schema. Each department site uses a different set of accounts with the primary schema being that of the department/client so that they are hitting their own tables.

Inheritance allows us to keep data separate,do roll-up reports if we need to, use the same application front-end, and yet allows us the ability to add new columns in just one place (the master template schema). It is more flexible than other approaches because for example we may have a city organization that need to share tables, like for example a system loaded list of funding source shared across the agency. We can set aside these shared tables in a separate schema visible to all or have some have their own copy they can change if they don't want to use the shared one.

Every once in a while, we find ourselves needing to query the whole hierarchy and needing to know which table the results of the query are coming from. To help solve that issue, we employ the use of the system column tableoid which all user tables have. The tableoid is the the object id of a table. PostgreSQL has many system columns that you have to explicitly select and can't be accessed with a SELECT * with the tableoid being one of them. These are: tableoid, cmax,cmin, xmin,xmax,ctid which are all described in System Columns. The PostgreSQL docs on inheritance have examples of using it, but we thought it worthwile to repeat the exercise since it's not that common knowledge and is unique enough feature of PostgreSQL that others coming from other relational databases, may miss the treat. I've often demonstrated it to non-PostgreSQL users who use for example SQL Server or MySQL, and they literally fall out of their chair when I show the feature to them and its endless possibilities.

In this day and age of clouds and what not, it is still important to keep tabs on your database growth to ensure it doesn't outgrow your disks or so that you can be prepared when it does. A common question that is asked is how big your database is or all your databases combined. Luckily PostgreSQL has had for a long time functions to help you answer this easily. In this little article, I'll demonstrate how to answer these questions and more with some PostgreSQL built-in functions

Ever have the need to create a holding table say spreadsheet data with say 100 columns. You need to create a table to hold this stuff. Or perhaps you were feeling in a sadist mood and wanted to abuse your PostgreSQL database to see how many columns you can create in a table of a specific data type. Here is a quick script to do it:

-- 8.4+ syntax
SELECT 'CREATE TABLE data_import('
|| array_to_string(array_agg('field' || i::text || ' varchar(255)'), ',') || ');'
FROM generate_series(1,10) As i;

-- 9.0+ syntax (string_agg was introduced in 9.0)
SELECT 'CREATE TABLE data_import('
|| string_agg('field' || i::text || ' varchar(255)', ',') || ');'
FROM generate_series(1,10) As i;

Both variants will return output that looks like this:

CREATE TABLE data_import(field1 varchar(255),field2 varchar(255),field3 varchar(255),field4 varchar(255)
    ,field5 varchar(255),field6 varchar(255),field7 varchar(255)
    ,field8 varchar(255),field9 varchar(255),field10 varchar(255));

Now if you want it to also execute because you are running it as part of an sql script, you could wrap it in an anonymous function.

 ---wrap it in an anonymous function to run
 -- requires PostgreSQL 9.0+
DO language 'plpgsql'
$$
DECLARE var_sql text :=  'CREATE TABLE data_import('
    || string_agg('field' || i::text || ' varchar(255)', ',') || ');'
    FROM generate_series(1,10) As i;
BEGIN
    EXECUTE var_sql;
END;
$$ ;

We've been working on converting some of our SQL Server apps to PostgreSQL. In this article we'll describe some things to watch out for and provide a function we wrote to automate some of the conversion.

Although both databases are fairly ANSI-SQL compliant, there are still differences with their CREATE TABLE statements, data types, and how they handle other things that makes porting applications not so trivial.

One of the biggest complaints about software and perhaps even more specifically FLOSS software is the lack of documentation. I'm not talking about those small little dialogs we throw in our code that rarely see the light of day. I'm talking about stuff you throw in user docs or specifications and so forth that an analyst or end-user reads.

The main reasons for this lack of documentation is that while everyone seems to want documentation, few really want to pay for it, and it's time consuming to keep documentation in synch with what the software actually does. Having documentation that is very inconsistent with the software is worse than not having any documentation at all. A good way to keep documentation up to date is to fold it into the process of developing and testing the software such that changes in software expose errors in the documentation and vice-versa or that the act of changing the software corrects the documentation.

Part of the way we try to do that on the PostGIS project is to require each function patch to include documentation. We also try to make our documentation executable by auto-generating PostgreSQL help and some test suites from the documentation. For example the process of installing documentation-generated function and type descriptions in PostgreSQL catches inconsistencies between the implementation and what we actually publish about how the functions work. Even the image generator that builds most of the images in the manual is built with PostGIS as a core of it so that when our build-bot is rebuilding the documentation it is exercising some PostGIS output functions.

I can't tell you how many times I've flagged changes in the current PostGIS 2.0 release simply by trying to install the docbook generated PostgreSQL comment descriptions and PostgreSQL complains that the function signature no longer exists that the documentations says should be there. So then I go back and correct the documentation or yell at the programmer if the documentation makes more sense than what they coded. On the other side, its easy to catch what we neglected to document simply by scanning the functions in PostgreSQL and seeing which ones don't have descriptions.

Okay as far as databases goes, many a DB Programmer/Analyst has waltzed into a project only to be befuddled about the meanings of all these tables used by the application. If you are like us, you don't even want to look at any database documentation that is not part of the definition of the database because you know 99% of the time it's so obsolete or was just a pipe dream of someone working in a vacuum that its more useless than not having any documentation at all. It is nice to have nicely formatted documentation you can read separate from the code, but even sweeter if you can query the documentation just as easily as you can query the data.

A good way of having up to date documentation is to weave it in as part of the process of defining the structure. For databases this means using foreign keys, primary keys, and using the commenting features that most relational databases offer these days. For example the databases we commonly work with, PostgreSQL, SQL Server, MS Access, and even MySQL all allow you to provide descriptions for tables, table columns, and sometimes other objects such as functions and stored procs right in the database. PostgreSQL even allows you to provide descriptions of columns in views though that's a bit messier to do. Sadly there isn't a consistent way of pulling these descriptions out of the database that will work for all of these. Each has differently defined meta tables it stores these descriptions in. For thise article, we'll demonstrate how to pull this information from PostgreSQL.

PostgreSQL does offer many useful switches in psql for querying this data, but we'll focus our attention on pulling this data via SQL. It's much easier to incorporate this information in auto-generated documentation with SQL because you can have more control what you include and how to format it.

We've started to play with PostgreSQL 9.1beta and the PgAdmin III 1.14.0 Beta 1. We'll briefly go over the cool gems found in PgAdmin III beta 1. Most of the new features are for navigating the upcoming PostgreSQL 9.1. Well first obstacle we ran into was we can't get our favorite extension, PostGIS, to compile against PostgreSQL 9.1beta though it did with the alphas, so you won't be seeing any windows experimental builds until we resolve this issue. Details of ticket here? PostGIS 2.0 won't compile for PostgreSQL 9.1 beta1

Despite that minor set back, we decided to push on and navigate the new features by using PgAdmin III 1.14.0 as our Tour Guide. Below is a list of new features you can experience via PgAdmin III 1.14.0 Beta 1. I'm sure there are more we missed, but these are the ones that were most flashing.

In the past I have always chosen to compile my own PostGIS because the GEOS library that came with the regular PostgreSQL yum install, was a bit antiquated. This has changed, so this time around I figured I'd give it a go at using the Yum repository 1.5.2 release of PostGIS available via Yum Package List.

Before you can follow these directions, make sure you have your PostgreSQL 9.0 setup via our An almost idiot's guide to Install PostgreSQL 9.0 with Yum.

In this exercise, we'll go thru installing PostgreSQL 9.0 on a Cent OS 5.5 32-bit box. This I'm testing on a GoGrid Cloud server so I can do parallel benchmarks between my windows GoGrid and Linux GoGrid server.

Upgrading from PostgreSQL 8.* to PostgreSQL 9.0

If you are upgrading from a PostgreSQL 8.4 to PostgreSQL 9.0, please refer to Devrim's article: Upgrading from 8.4 to 9.0 on Fedora / Red Hat / CentOS using RPMs.

For the rest of this article, we'll go over configuring your yum to use the PostgreSQL PGDG Yum repository managed by Devrim Gunduz, which has the latest and greatest of 9.0 as well as the 9.1 latest development release. We'll also demonstrate how to have two instances of PostgreSQL running so you can experiment with the new features of PostgreSQL 9.1 while reminiscing about the soon to be old-hat features of PostgreSQL 9.0.

We were setting up another SQL Server 2005 64-bit where we needed a linked server connection to our PostgreSQL 9.0 server. This is something we've done before so not new and something we documented in Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit. What was different this time is that we decided to use the latest version of the new PostgreSQL 64-bit drivers now available main PostgreSQL site http://www.postgresql.org/ftp/odbc/versions/msi/. Sadly these did not work for us. They seemed to work fine in our MS Access 2010 64-bit install, but when used via SQL Server, SQL Server would choke with a message:

If you tried to do a query with them. You can however see all the tables via the linked server tab.