We're steadily making progress on DoltgreSQL, which is a version of Dolt built to be a drop-in replacement for PostgreSQL. For those that may not know about Dolt, it's built as a drop-in replacement for MySQL that is built, from the ground up, with Git-influenced versioning features in mind. This means that you can use branch, merge, diff, and more with your data and schema. DoltgreSQL is the PostgreSQL-flavored version of Dolt, and it is in active development. For those that need a production-ready database today then we recommended Dolt. However, for those that prefer PostgreSQL, then we have some exciting news as we're announcing that we've added support for SEQUENCES in DoltgreSQL!

What are sequences?

In Dolt and MySQL, you have the AUTO_INCREMENT attribute, which allows a table to automatically insert an incrementing value into the table for you, without having to keep track of some unique index. DoltgreSQL and PostgreSQL have a similar feature called sequences, however they're far more powerful. They can be created through the use of a SERIAL type, which would create a sequence that behaves similarly to the AUTO_INCREMENT attribute. They can also be created through CREATE SEQUENCE, which allows you to customize many different properties. Our supported CREATE SEQUENCE statement has the following definition:

CREATE SEQUENCE [ IF NOT EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]

• name: Specifies the name of the sequence, which must be unique.
• data_type: Valid values are int2, int4, and int8, which defines the upper and lower bounds. If excluded, defaults to int8.
• increment: The value that the sequence changes by. If positive, then the sequence increments (or ascends), however a negative value causes the sequence to decrement (or descend). If excluded, defaults to 1.
• minvalue: Specifies the minimum value of the sequence. If ascending, this value is only relevant when cycling. If descending, this is the lowest valid value. If excluded, the default value is based on the data type and sequence direction.
• maxvalue: Specifies the maximum value of the sequence. If ascending, this is the highest valid value. If descending, this value is only relevant when cycling. If excluded, the default value is based on the data type and sequence direction.
• start: Specifies the starting value of the sequence. If excluded, defaults to minvalue for an ascending sequence, and maxvalue for a descending sequence.
• CYCLE: Cycling causes the sequence to restart from the minvalue or maxvalue, depending on if the sequence is ascending or descending, otherwise it returns an error. By default, cycling is disabled.
• OWNED BY: Specifies the specific column that owns this sequence. When the owning column is deleted (or the table it is on), then the sequence is also deleted. If excluded, the default value is NONE.

When declaring a column with either smallserial, serial, or bigserial, DoltgreSQL automatically performs the following steps for you:

• Converts the type to int2, int4, or int8 respectively.
• Creates a sequence with the relevant data type, with all other options having their default values.
• Changes the OWNED BY attribute to point to the column.
• Adds a default value to the column that calls nextval('SEQUENCE_NAME'), where SEQUENCE_NAME is an automatically-generated name for the created sequence.

There are two primary functions associated with sequences, which are nextval() and setval(). nextval() is used to fetch the next value from a sequence, while setval() sets the sequence to the value given. Most users will only interact with nextval().

Examples

For these examples, I'll assume you've installed DoltgreSQL and have connected to it using your favorite Postgres client. DoltgreSQL is designed to be drop-in compatible with Postgres, so if your favorite client doesn't work, then please submit an issue and we'll get to work on adding support!

First, let's create a basic sequence and fetch a few values from it.

doltgres=> CREATE SEQUENCE test_sequence;
CREATE SEQUENCE
doltgres=> SELECT nextval('test_sequence');
 nextval('test_sequence')
--------------------------
                        1
(1 row)


doltgres=> SELECT nextval('test_sequence');
 nextval('test_sequence')
--------------------------
                        2
(1 row)


doltgres=> SELECT nextval('test_sequence');
 nextval('test_sequence')
--------------------------
                        3
(1 row)

Next, let's create a descending sequence that decrements by 2 and cycles when it reaches the minimum.

doltgres=> CREATE SEQUENCE desc_sequence INCREMENT BY -2 MINVALUE -5 CYCLE;
CREATE SEQUENCE
doltgres=> SELECT nextval('desc_sequence');
 nextval('desc_sequence')
--------------------------
                       -1
(1 row)


doltgres=> SELECT nextval('desc_sequence');
 nextval('desc_sequence')
--------------------------
                       -3
(1 row)


doltgres=> SELECT nextval('desc_sequence');
 nextval('desc_sequence')
--------------------------
                       -5
(1 row)


doltgres=> SELECT nextval('desc_sequence');
 nextval('desc_sequence')
--------------------------
                       -1
(1 row)

Lastly, we'll use a serial type to see how it's used as the Postgres-equivalent of AUTO_INCREMENT.

doltgres=> CREATE TABLE test_table (v1 serial, v2 text);
CREATE TABLE
doltgres=> INSERT INTO test_table (v2) VALUES ('hello'), ('world'), ('people');
INSERT 0 3
doltgres=> SELECT * FROM test_table ORDER BY v1;
 v1 |   v2
----+--------
  1 | hello
  2 | world
  3 | people
(3 rows)

Conclusion

DoltgreSQL is improving at a rapid pace, and we are excited with both the reception and its future. If there are any features that are missing that are critical to your workflow, then let us know! You can find us on Twitter/X and chat with us on Discord. We also welcome all issue reports! Thank you for taking this journey with us!