Do you plan to implement this feature in the future? Or was I not careful enough and it is there somewhere? Thanks,
* 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?
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:
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:
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:
Here are the links you may find useful:
If you need more help, I would gladly participate in your great project.
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.
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.
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.