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.


Looking through the scan.l

Recently I was browsing the scan.l source code for changes since PostgreSQL 9.0 version. I always do so for new minor and major releases because I use PostgreSQL grammar in my project.

Well if you’ll look closer you may find that flex is switched to 8bit mode:

%option 8bit

But then you may find such declarations:

dolq_start [A-Za-z\200-\377_]
dolq_cont [A-Za-z\200-\377_0-9]
ident_start [A-Za-z\200-\377_]
ident_cont [A-Za-z\200-\377_0-9\$]

When I first saw these lines they blew my mind. Because from the school times I was sure that 8bit has maximum value of 255. But thanks God, there is a lot of information about flex all over internet. And suddenly, it turned out that flex uses octal notation by default. This means
the character with octal value 123

There is also a way to use hexadecimal notation:
the character with hexadecimal value 2a

So the next time you’ll see such declarations don’t be confused. So conceived! ๐Ÿ™‚

Windows path in libpq connection control functions

I already wrote a post about using Windows paths in SQL statements. But today I was playing with SSL connections to PostgreSQL from MicroOLAP Database Designer for PostgreSQL and found one interesting thing:

warning Windows user! Replace your usual slashes (backslashes, actually) with the slashes like this one: ‘/’, in the file system path parameters passed to libpq database connection control functions, e.g. PQconnectdbParams, PQconnectdb, PQconnectStartParams etc.!


PQconnectdb(“dbname=’carsales’ host=’localhost’ port=5434 user=’sslguy’ password=” sslmode=’require’ sslcert=’C:\MySSL\postgresql.crt’ sslkey=’C:\MySSL\postgresql.key'”);


PQconnectdb(“dbname=’carsales’ host=’localhost’ port=5434 user=’sslguy’ password=” sslmode=’require’ sslcert=’C:/MySSL/postgresql.crt’ sslkey=’C:/MySSL/postgresql.key'”);

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?

Poll: Do we need SET type in PostgreSQL?

This topic is discussed again and again with huge friend of mine and MySQL sectarian. ๐Ÿ™‚ And must confess I agree with him in this particular point of view.

For me it’s strange that such powerful database as PostgreSQL, which has a lot of drums and whistles inside, doesn’t support SET types. Especially when we have ENUM support now!

Of course one may say that this functionality can be implemented using Bit String Types. But where is the joy of power? Where is the crystal clarity of the SQL script? ๐Ÿ™‚

What’s you opinion, postgresman?

PS Don’t hesitate to share your thoughts in the comments.

Script slicing by PgMDD



November, 4th. Release Candidate 1 of Database Designer for PostgreSQL 1.2.9 become available. Among three changes comparing to the last beta there is the one which attracts attention โ€” “Execute Script In Single Transaction (Alt + F9)” functionality added. World community shocked.

“What means added? We thought it always was executed in single transaction…” โ€“ resounded from all sides.

November, 14th. MicroOLAP Headquarters. Explanatory mission entrusted to the best agent… Me. ๐Ÿ˜‰

pgAdmin shows the last result set

Right now there is no any opportunity to remember who got the idea about script slicing in SQL Executor. The gist was โ€” each returned result set must be displayed.

Have a look how pgAdmin handles multiple result sets. As you can see only the last is available while others are discarded (we may read about this on the Messages tab).

One more notice. Multiple statements in pgAdmin always executed in the single transaction context. This is not a miracle since PQsendQuery function from client library used.

By the way, the fact that PQsendQuery used give us a hope that someday pgAdmin will handle all result sets.

PgMDD shows all result sets in separate tabs
note Just for note, I’m not saying pgAdmin is a dinosaur or something. I like it a lot. This is “must have” tool for sure. I’m using it because of other GUI administration utility absence. ๐Ÿ™‚

As you probably guessed PgMDD creates separate tab for each result set from the very first release.

There is one more important issue why script slicing was implemented. Database Designer is some kind of ideal world. You may use any names, any functions, any data types for model creating.

But real life is cruel. Generated script must work in any conditions even if some statements may fail, e.g. old server version, non-existent role, lack of privileges for some operations, object with the same name already exists etc. That’s why PgMDD’s SQL Executor should give the developer right of choice โ€” abort execution or proceed anyway.

How it’s made

Let me one phrase before I begin: there is no any SQL parser library (or suite) on the market which suits even the basic needs (I mean PostgreSQL dialect of course). I guarantee this!

God is my witness, our team tried every 3rd party library we meet. Without success.

In Russian speaking IT folklore there is an adage “ะŸะตั€ะตะฟะธัะฐั‚ัŒ ะฒัั‘ ะฝะฐั…ั€ะตะฝ!”. Loose translation is “Rewrite all from scratch!”

OK, the moment of glory. We made it ourselves using the native PostgreSQL grammar. Yeah, bite me unbelievers!

Let’s omit technical details. I know nobody cares anyway. ๐Ÿ˜‰ Our parser is absolutely… no, I mean absolutely compatible with PostgreSQL. But with 8.3.x version. ๐Ÿ™‚ It’s just a matter of time to update it, but we missed the moment.

So we have two reasons to add “Execute in Single Transaction” functionality:

  • Ability to ROLLBACK all changes made by script in case of need
  • PgMDD parser cannot proceed with some PostgreSQL 8.4.x features

That’s all folks!