Postgres OnLine Journal (Entries tagged as ansi sql)

Saying Happy Valentine in PostGIS

nospam@example.com (Leo Hsu and Regina Obe) — Thu, 14 Feb 2013 02:44:00 -0500

A while back I mentioned to Bborie (aka dustymugs) and Sandro (aka strk): We're missing ability to label our images with text. Picture this: What if someone has got a road or a parcel of land and they want to stamp the parcel id or the road name on it and they don't want to have to depend on some piece of mapping software. Many report writers and databases can talk to each other directly such as we demonstrated in Rendering PostGIS raster graphics with LibreOffice and we've got raster functionality in the database already. Can we cut out the middleman? Some ideas came to mind. One we could embed a font lib into PostGIS thus having the ultimate bloatware minus the built-in coffee maker. strk screeched. He's still recovering from my constant demands of having him upgrade his PostgreSQL version. Okay fair enough.

Bborie suggested why don't you import your letters as rasters and then vectorize them. So that's exactly what we are going to demonstrate in this article and in doing so we will combine some of the new features coming in PostGIS 2.1 we've been talking about in Waiting for PostGIS 2.1 series.

Warning this article will have a hint of Rube Goldbergishness in it. If you are easily offended by seeing stupid tricks done with SQL, stop reading now. We are going to take a natural vector product and rasterize it just so we can vectorize it again so we can then rasterize it again. Don't think about it too much. It may trigger activity in parts of your brain you didn't know were there thus resulting in stabbing pains similar to what you experience by quickly guplping down a handful of Wasabi peas.

So here are the steps for creating your own font set you can overlay on your geometries and rasters.

See if you can spot the use of window functions and CTEs in these examples.

Continue reading "Saying Happy Valentine in PostGIS"

greatest and least - oldies but goodies

nospam@example.com (Leo Hsu and Regina Obe) — Fri, 05 Nov 2010 01:50:00 -0400

Problem: You have a set of numbers, or characters or whatever and you are trying to find the max or min of this set?

If the values are separate records in a table or query, the answer is well known and respected across all relational databases -- use the aggregate MAX and MIN functions.

But what if you have a set of free wheeling numbers or text not in separate records, and you want the max or min of each. Here is where the least and greatest functions come in handy.

PostgreSQL has had these functions for as far back as I can remember and is not the only database to sport these marvelous functions. Our beloved MySQL and Oracle database have these functions as well. Sadly our more beloved SQL Server even in the SQL Server 2008 variant - lacks these functions.

Okay how to use these functions -- you use it like this:

    SELECT least(1,-2,5) As num_least, greatest('Bobby', 'Catty', 'Kitty') As greatest_cat;
    
    Yields:
 num_least| greatest_cat
----------+----------
       -2 | Kitty

We would classify these functions along the lines of COALESCE. They are like COALESCE because they take an arbitrary number of arguments and the datatype that is returned is highest datatype that all arguments in the function can be autocast to. If there is no autocast then well you get an error. To demonstrate, guess what happens when you do this:

SELECT least(-1, 'Kitty');

Well do this in PostgreSQL at least in 8.3+, you get a nice slap if you haven't installed any deprecated autocasts:

ERROR: invalid input syntax for integer: "Kitty" LINE 1: SELECT least('Kitty', -1)

Do this in MySQL - so friendly and forgiving, and great reader of minds and you get:

-1
OF COURSE

I apologize for the ambiguous sarcasm, its just sometimes I want my mind read and sometimes I don't and I just can't figure out whether today is one of those days or the other day.

Using LTree to Represent and Query Hierarchy and Tree Structures

nospam@example.com (Leo Hsu and Regina Obe) — Sun, 22 Aug 2010 01:15:00 -0400

PostgreSQL offers several options for displaying and querying tree like structures. In Using Recursive Common Table Expressions (CTE) to represent tree structures we demonstrated how to use common table expressions to display a tree like structure. Common Table Expressions required PostgreSQL 8.4 and above but was fairly ANSI standards compliant. In addition to that approach you have the option of using recursive functions. There is yet another common approach for this which is specific to PostgreSQL. This is using the ltree contrib datatype that has been supported for sometime in PostgreSQL. For one of our recent projects, we chose ltree over the other approaches because the performance is much better when you need to do ad-hoc queries over the tree since it can take advantage of btree and gist indexes and also has built-in tree query expressions that make ad-hoc queries simpler to do; similar in concept to the tsearch query syntax for querying text.

In this article we'll demonstrate how to use ltree and along the way also show the PostgreSQL 9.0 new features conditional triggers and ordered aggregates.

Continue reading "Using LTree to Represent and Query Hierarchy and Tree Structures"

NOT IN NULL Uniqueness trickery

nospam@example.com (Leo Hsu and Regina Obe) — Tue, 22 Jun 2010 04:19:00 -0400

I know a lot has been said about this beautiful value we affectionately call NULL, which is neither here nor there and that manages to catch many of us off guard with its casual neither here nor thereness. Database analysts who are really just back seat mathematicians in disguise like to philosophize about the unknown and pat themselves on the back when they feel they have mastered the unknown better than any one else. Of course database spatial analysts, the worst kind of back seat mathematicians, like to talk not only about NULL but about EMPTY and compare notes with their brethren and write dissertations about what to do about something that is neither here nor there but is more known than the unknown, but not quite as known as the empty string.

Okay getting to the point, one of our clients asked us about a peculiar problem they had with a query, and the strange results they were getting. We admit this still manages to catch us off guard every once in a while.

Continue reading "NOT IN NULL Uniqueness trickery"

Forcing the planner's hand with set enable_seqscan off WTF

nospam@example.com (Leo Hsu and Regina Obe) — Sat, 09 Jan 2010 01:12:00 -0500

UPDATE: Thanks all for the suggestions. For now we ended up increasing the seq_page_cost from 1 to 2 in the database. That has gotten us back to our old much much faster speeds without change in code and seems to have improved the speeds of other queries as well, without reducing speed of any. ALTER DATABASE mydb SET seq_page_cost=2;

As Jeff suggested, we'll try to come up with a standalone example that exhibits the behavior. The below example was more to demonstrate the construct. Table names and fields were changed to protect the innocent so that is why we didn't bother showing explain plans. The behavior also seems to do with the distribution of data and gets worse when stats are updated (via vacuum analyze). Didn't see this in PostgreSQL 8.3 and this was a system recently upgraded from 8.3 to 8.4

---ORIGINAL ARTICLE HERE --

This is a very odd thing and I think has happened to us perhaps once before. Its a bit puzzling, and we aren't particularly happy with our work around because its something that looks to a casual observer as a bit bizarre. The hack is setting the enable_seqscan setting off for a particular query to force the planner to use indexes available to it.

What is particularly troubling about this problem, is that it wasn't always this way. This is a piece of query code we've had in an application for a while, and its worked shall I say really fast. Response times in 300 ms - 1 sec, for what is not a trivial query against a not so trivially sized hierarchy of tables. Anyrate, one day -- this query that we were very happy with, suddenly started hanging taking 5 minutes to run. Sure data had been added and so forth, but that didn't completely explain this sudden change of behavior. The plan it had taken had changed drastically. It just suddenly decided to stop using a critical index it had always used. Well it was still using it but just on the root table, not the children. Though querying a child directly proved that it still refused to use it, so it didn't seem to be the hierarchy at fault here.

Continue reading "Forcing the planner's hand with set enable_seqscan off WTF"

DML to generate DDL and DCL- Making structural and Permission changes to multiple tables

nospam@example.com (Leo Hsu and Regina Obe) — Wed, 13 Feb 2008 05:35:00 -0500

Every once in a while you are tasked with an issue such as having to create logging fields in each of your tables or having to put like constraints on each of your tables or you need to Grant an X group or X user rights to a certain set of tables.

The nice thing about having an information_schema is that it provides an easy way to generate scripts to do just that with plain SELECT statements. In PostgreSQL its even easier with the array_to_string functions and ARRAY functions, you can get the script in a single field result. In the following sections we'll demonstrate some examples of this.

Continue reading "DML to generate DDL and DCL- Making structural and Permission changes to multiple tables"