Tag: sequence

Waiting for PostgreSQL 10 – Identity columns

On 6th of April 2017, Peter Eisentraut committed patch:

Identity columns
 
This is the SQL standard-conforming variant of PostgreSQL's serial
columns.  It fixes a few usability issues that serial columns have:
 
- CREATE TABLE / LIKE copies default but refers to same sequence
- cannot add/drop serialness with ALTER TABLE
- dropping default does not drop sequence
- need to grant separate privileges to sequence
- other slight weirdnesses because serial is some kind of special macro

Continue reading Waiting for PostgreSQL 10 – Identity columns

Waiting for PostgreSQL 10 – Add pg_sequence system catalog

On 20th of December, Peter Eisentraut committed patch:

Add pg_sequence system catalog
 
Move sequence metadata (start, increment, etc.) into a proper system
catalog instead of storing it in the sequence heap object.  This
separates the metadata from the sequence data.  Sequence metadata is now
operated on transactionally by DDL commands, whereas previously
rollbacks of sequence-related DDL commands would be ignored.
 
Reviewed-by: Andreas Karlsson

Continue reading Waiting for PostgreSQL 10 – Add pg_sequence system catalog

Tips N’ Tricks – setting field based on order

Let's imagine following situation:

CREATE TABLE test (id int4 PRIMARY KEY, priority int4);
INSERT INTO test (id)
    SELECT DISTINCT (random() * 100000000)::int4 FROM generate_series(1,1000);

Table test will now contain some (up to 1000) records, with random ids.

Now, we want to update first 3 records (ordered by id) to have following values in priority:

  1. 10000
  2. 5000
  3. 1000

Continue reading Tips N’ Tricks – setting field based on order

Waiting for 8.4 – partial-match support in GIN, and sequence restart

Today we have two interesting patches:

  • patch by Teodor Sigaev and Oleg Bartunov, and committed by Tom Lane, which adds interesting capability to GIN indexes
  • patch by Zoltan Boszormenyi, also committed by Tom, which adds “RESTART" option to ALTER SEQUENCE. With some interesting consequences

Continue reading Waiting for 8.4 – partial-match support in GIN, and sequence restart