postgresql sequence

[expired user #8418]'s profile image [expired user #8418] posted 9 years ago in Feature discussion Permalink
Hi Ansgar, thank you for this amazing HeidiSQL. I love it, I think it is the best postgres manager SQL tool. However, I recognised that something important is missing from it. Unfortunately HeidiSql cannot handle the sequences of postgres.

Do you plan to implement this feature in the future? Or was I not careful enough and it is there somewhere? Thanks,

Csongor
ansgar's profile image ansgar posted 9 years ago Permalink
I guess sequences are similar to MySQL's auto-increment columns, right? If so, yes, the table editor should handle that but cannot yet. But I need help here from a PostgreSQL professional. So:
* how to read out a sequence from existing tables?
* how to alter an existing sequence?
* how to create a new sequence?
* how to drop a sequence?
[expired user #8418]'s profile image [expired user #8418] posted 9 years ago Permalink
Hi Ansgar, thanks for the quick response.

In postgres, if you create a field with SERIAL/BIGSERIAL type then internally it is transferred to INT/BIGINT and a new sequence is automatically created. In other RDBMS managers the tree looks like this:
T:.
└───databases
└───my_cms_database
└───my_schema
├───my_functions
├───my_sequences
│ ├───my_seq_for_articles
│ └───my_seq_for_users
├───my_tables
│ ├───articles
│ └───users
└───my_views

As you can see, a database (my_cms_database) can contain one or more schemes. Each schema can have their own tables, function, views, sequences and some other stuffs. In this case you can refer between schemes like this:
SELECT *
FROM schema1.table_same_name t1
LEFT JOIN schema2.table_same_name t2 ON t1.field_1 = t2.field_2
The different schemes can contain tables with the same name. You have to prefix the tables with schema names unless you are using the actual schema.

In other words, schemas act like databases in mysql: you can issue queries using more of them at the same time. The level of databases in one higher level of isolation: if you connect to a database then you cannot select from another one. It needs a separate connection. This is one reason why I love PG. ;)


You can figure out for a table which of the columns are (BIG)SERIALs from the pg_catalog. Once you learned the sequence name working under the particular SERIAL column, you can use the following functions:
currval()
lastval()
nextval()
setval()


Here are the links you may find useful:
http://www.neilconway.org/docs/sequences/
http://www.postgresql.org/docs/9.3/static/datatype-numeric.html#DATATYPE-SERIAL
http://www.postgresql.org/message-id/4B194EBD.4050803@fmed.uba.ar
http://www.postgresql.org/docs/9.3/static/functions-sequence.html
http://www.postgresql.org/docs/9.3/static/sql-createsequence.html
http://www.postgresql.org/docs/9.3/static/sql-dropsequence.html
http://www.postgresql.org/docs/9.3/static/sql-altersequence.html

If you need more help, I would gladly participate in your great project.smile

Thanks, Csongor
[expired user #8418]'s profile image [expired user #8418] posted 9 years ago Permalink
Damned, the tree went wrong. :)

T:.
└───databases
└───my_cms_database
└───my_schema
├───my_functions
├───my_sequences
│   ├───my_seq_for_articles
│   └───my_seq_for_users
├───my_tables
│   ├───articles
│   └───users
└───my_views

kalvaro's profile image kalvaro posted 9 years ago Permalink
I know nothing about PostgreSQL but I believe it copies several concepts from Oracle. In Oracle, sequences are first class objects (like tables, triggers or procedures). A sequence is a named routine with a given definition ("start at 1000, increment by 2") and an internal counter ("current value is 3140") and accepts a couple of basic operations: "fetch current value" and "generate next value", all this via SQL code. Although PostgreSQL syntax is slightly difference, the concept seems to be identical. So, to begin with, you need to implement all the stuff regarding sequences (tree, editor, etc.).

Of course, the most typical use case is auto-increment emulation:

1. Create a sequence per table
2. Create a trigger that inserts a value in the desired column

Oracle SQL Developer has a helper tool in its table editor that creates both sequence and trigger for a given column.

Sequences are quite a burden if you only need them for this so PostgreSQL invented the SERIAL column pseudo-type family.
ansgar's profile image ansgar posted 9 years ago Permalink
The following query gives names of all sequences.

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';
ansgar's profile image ansgar posted 9 years ago Permalink
I read about three different types of "serials":
smallserial, serial and bigserial

... of which "smallserial" is yet missing in HeidiSQL's data type list. And probably there is something missing when parsing such a column, as it's probably detected as ordinary integer. Don't know, need to test that.
[expired user #8418]'s profile image [expired user #8418] posted 9 years ago Permalink
I have never heard of smallserial but it is good to know we have this. I like to learn.smileMoreover, I don't know too much about Oracle bur I know that it is similar to PG in many aspects.

I think postgres doesn't create an "official" trigger for creating the autoincrement feature. It solves it in some hidden way.

And one more thing which can be important. In MySql, if you rollback an INSERT statement and run it again then the second one can re-use the AUTOINCREMENT value which was rolled back previously.

In PG it works in a bit different way. That is, if you get a new value from the sequence then this value will never be served again. Even if you rollback your INSERT and re-insert again then there will be a gap in the IDs.

Can I help anything, do you need any research in postgres what I could do instead of you?

Please login to leave a reply, or register at first.