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:
// 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"
// if we have no docs for the current scope
// we will try using the fallback one,
// to disable set to false
Task: get title (or excerpt) from given text (body) Solution:
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).
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
SELECT f FROM fact LIMIT 10;
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:
CREATE TABLE "T1"
"T1_ID" bigint NOT NULL,
CONSTRAINT "T1" CHECK ("T1_ID" > 0 )
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))' LANGUAGE sql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION isleapyear(year integer) RETURNS boolean AS 'BEGIN
RETURN (Year % 4 = 0) AND ((Year % 100 0) or (Year % 400 = 0));
END' LANGUAGE plpgsql IMMUTABLE STRICT;
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.