Creating PostgreSQL Sequences with Navicat

Sequences in PostgreSQL are similar to the AUTO_INCREMENT in MSSQL Server. If you've been creating AUTO_INCREMENTs in MSSQL Server for years like I have, you'll find that creating Sequences in PostgreSQL is a bit different. Quite easy to do in Navicat, but different.

Navicat has an excellent table designer, but it's not going to help you creating a table with a Sequence. The SQL for creating a Sequence while defining a table is simply

post_id    SERIAL

If you try to do anything like that in the Navicat designer you'll get tagged for it.

So the trick is to start your table in SQL and complete it in the designer, because PostgreSQL automatically creates a Sequence for you and Navicat adds its tools to make the process even simpler.

You see in our SQL Query Message that the Sequence was automatically created for us.

We COULD have scripted out the sequence or created it in Navicat. If compelled to do so, Sequences are available in Navicat here.

Nice form dialog if we want to use it. A nice tool to use for modifying any existing sequences, certainly.

The SQL of the Sequence would look like this. Again, nothing stops us from banging this out by hand. But why, when Navicat and PostgreSQL create it for us?

And here's our table SQL definition with the auto-created Sequence for the import_id key.

Posted April 11, 2014 02:39 PM EDT

More Like This Post