Serial-Identity mix

As you probably know PostgreSQL 10 introduced IDENTITY columns. They are pretty much like serial columns from the first sight. To learn about differences one may refer to the perfect blog post by Peter Eisentraut.

So I was wondering what if I mix some serial with some identity. So I executed such simple query:


Of course, I got an error:

ERROR: both default and identity specified for column "id" of table "foo"
SQL state: 42601

“SQL state: 42601” stands for a syntax error. But the interesting thing is that SERIAL, as you know, converted to CREATE SEQUENCE and SET DEFAULT.

So now I know what exactly the reason of error. You cannot have both DEFAULT and IDENTITY for a column.


CHM-manual for PostgreSQL 9.6

CHM IconAs I already wrote years ago, I use PostgreSQL manuals very often since the 8.x versions. And if for earlier versions it was possible to download CHM help file, now it’s not.

PostgreSQL manual consists of the 1351 topics (and still grows!) and I don’t like the search engine on the site much. It is also worth noting that the tree of contents (TOC) in CHM version speeds up navigation a lot!

A good friend of mine Edward Smirnov obsessed with creating a utility capable to compile the manual in CHM format using a set of HTML / SGML files shipped with PostgreSQL. He managed this! The only thing missing is the Index support (HHK file in CHM archive).

However, even without this feature such manual a hundred times more comfortable for me than the online counterpart. Especially I’m excited about lightning full-text searches.

We’ve put PostgreSQL 9.6 CHM manual at MicroOLAP Database Designer for PostgreSQL
and PostgresDAC download pages, welcome to download it.

UPD By default Windows doesn’t allow using .chm files downloaded from Internet. So you should right-click on the .chm file, then Properties and then check Unblock checkbox (or Unblock button in Windows 7 and older).

Unblock CHM
Unblock CHM

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": ""

"pascal": "",
// 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).