Tag: SQL

Easy PostgreSQL docs for Sublime Text

I’m using this perfect tool called Sublime Text 3 for a bunch of tasks. One of them is viewing SQL scripts from time to time. ST3 has perfect SQL highlighting, but what I miss the most is the context help functionality, e.g. I select “LEFT JOIN” and hit F1 hot key. But that’s not problem since ST3 has a lot of packages. To solve my problem I need GotoDocumentation package and some tuning. Here is my settings for GotoDocumentation:

"docs": {
// obj containing the docs for each scope
// these are merged with the default ones
// the key value pair represent scope -> doc url
// supported placeholders:
// - %(query)s the selected text/word
// - %(scope)s the current scope
"sql": "http://www.postgresql.org/search/?q=%(query)s"

"pascal": "http://docwiki.embarcadero.com/Libraries/Seattle/en/Special:Search/%(query)s",
// if we have no docs for the current scope
// we will try using the fallback one,
// to disable set to false
"fallback_scope": "google"

ST3 context help  for pg sql
ST3 context help for pg sql

Creating titles from text (SQL)

Task: get title (or excerpt) from given text (body)

left(body, 29) ||
COALESCE( substring( substr( body, 30 ) from '(.+?)(\,| )' ), '' ) as title,

Details: Take the first 29 characters, concatenate them with substring started from the 30-th character till the first occurrence of comma or space. Or concatenate with empty string, if pattern doesn’t match (COALESCE).

MERGE in PostgreSQL

Found cool trick how today implement Orable MERGE in PostgreSQL:

Oracle statement:

  2. MERGE INTO acme_obj_value d
  3. USING ( SELECT object_id
  4.         FROM   acme_state_tmp
  5.       ) s
  6. ON (d.object_id = s.object_id)
  7.   WHEN matched THEN
  8.     UPDATE SET d.date_value = LEAST(l_dt, d.date_value)
  9.   WHEN NOT matched THEN
  10.     INSERT (d.id, d.object_id, d.date_value)
  11.     VALUES (acme_param_sequence.NEXTVAL, s.object_id, l_dt)

PostgreSQL statement:

  2. WITH s AS (
  3.      SELECT object_id
  4.      FROM   acme_state_tmp
  5. ),
  6. upd AS (
  7.      UPDATE acme_obj_value
  8.      SET    date_value = LEAST(l_dt, d.date_value)
  9.      FROM   s
  10.      WHERE  acme_obj_value.object_id = s.object_id
  11.      RETURNING acme_obj_value.object_id
  12. )
  13. INSERT INTO acme_obj_value(id, object_id,  date_value)
  14. SELECT NEXTVAL('acme_param_sequence'), s.object_id, l_dt
  15. FROM   s
  16. WHERE  s.object_id NOT IN (SELECT object_id FROM upd)

Factorial using CTE in PostgreSQL

Not so long ago I used Common Table Expressions for Fibonacci Numbers calculation.

Today I had a conversation with one client about SQL in general and about PosgreSQL dialect in particular. We talked about SQL’s Turing completeness also. Well my opponent is sure that SQL (Postgres dialect either) is not Turing Complete. But I know for sure that if SQL supports CTE it is Turing Complete. Well, I’m sure about it because some time ago at Oscon 2009 David Fetter said so. And my confidence in this man is boundless. :)

Anyway, my client proposed to implement Factorial calculation on a pure SQL. I choose Postgres dialect. He agreed.

That was his first mistake! He didn’t know that Postgres has built in “!” and “!!” operators for this purpose. :)

But to be more convincing, I have wrote this code:

WITH RECURSIVE fact(i, f) AS (
    VALUES (2, 1)
    SELECT i + 1, i * f FROM fact

Why you cannot create table and PK constraint with the same name

Very interesting message appeared on the pgsql-bugs@postgresql.org list today:

When I do this

"T1_ID" bigint NOT NULL,

I get the following message:

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "T1" for table "T1"
ERROR: relation "T1" already exists
********** Error **********
ERROR: relation "T1" already exists
SQL state: 42P07

It does NOT create either the table or the constraint, and the message is confusing because there is no relation by that name.

The SQLSTATE 42P07 is described in the manual as only as “table undefined”, and it is not clear if the intent is to allow or
disallow the creation of a constraint called the same as the table in Postgresql. Oracle 11g allows this, but my feeling is that
doing this should not be allowed, just as Postgresql handles it.

I am complaining about the confusing error message which IMO is off-topic, not about how the DB handles this.

The quick answer is PRIMARY KEY constraint always has underlying system index with the same name. Thus to implement CREATE statement above PostgreSQL should create table with the name “T1” and the index with the same name. This is impossible, because tables and indexes are stored in the same system catalog pg_class (they share the same namespace). That is where ambiguity appears. The same is true for UNIQUE constraint.

On the other hand you may freely create CHECK constraint under such conditions:

"T1_ID" bigint NOT NULL,

Determination of a leap year in PostgreSQL

From Wikipedia:

A leap year (or intercalary or bissextile year) is a year containing one extra day (or, in the case of lunisolar calendars, a month) in order to keep the calendar year synchronized with the astronomical or seasonal year. Because seasons and astronomical events do not repeat in a whole number of days, a calendar that had the same number of days in each year would, over time, drift with respect to the event it was supposed to track. By occasionally inserting (or intercalating) an additional day or month into the year, the drift can be corrected. A year that is not a leap year is called a common year.

So to determine whether a year is a leap year or not in either the Gregorian calendar we need to check such condition:

(Year mod 4 = 0) AND ((Year mod 100 != 0) or (Year mod 400 = 0)),
where mod is modulo operation

This dirty query does the trick then:

SELECT c.y, (c.y % 4 = 0) AND ((c.y % 100 <> 0) OR (c.y % 400 = 0))
 FROM generate_series(1582, 2020) AS c(y)


I prefer to have function for this task. First I thought it must be written in plpgsql to be fast enough. Yeah, I’m to lazy for C functions. However after tests I saw that SQL function has the same productivity. Don’t know why. Here the sources for both of them:

CREATE OR REPLACE FUNCTION isleapyear(year integer)
 RETURNS boolean AS
'SELECT ($1 % 4 = 0) AND (($1 % 100 <> 0) or ($1 % 400 = 0))'

CREATE OR REPLACE FUNCTION isleapyear(year integer)
 RETURNS boolean AS
  RETURN (Year % 4 = 0) AND ((Year % 100 0) or (Year % 400 = 0));


P.S. Some funny traditions:

  • On the British isles, it is a tradition that women may propose marriage only on leap years.
  • In Denmark, the tradition is that women may propose on the bissextile leap day, February 24, and that refusal must be compensated with 12 pairs of gloves.
  • In Finland, the tradition is that if a man refuses a woman’s proposal on leap day, he should buy her the fabrics for a skirt.

Poll: ALTER TYPE enumtype ADD <what?> ‘newlabel’

Yesterday, Hubert ‘depesz’ Lubaczewski wrote a post about new functionality in PostgreSQL 9.1.

In a few words we will have the ability to add new values to the existing ENUM type defined earlier. Thanks God!

However, Andrew Dunstan proposed to use another syntax for it:

ALTER TYPE enumtype ADD LABEL ‘newlabel’

The discussion was hot. Brendan Jurd wanted previous syntax, Tom Lane proposed substitution VALUE for LABEL, Pavel Stehule made a suggestion for ELEMENT etc.

What term sounds good for you, friend?