1

I created the partitioned table in Postgres 15 with composite primary key:

CREATE TABLE T (
    id bigserial NOT NULL,
    date date,
    ..., 
    PRIMARY KEY(id, date)
) PARTITION BY RANGE (test_date);

I added several partitions like this:

CREATE TABLE T_2020 PARTITION OF T for values from ('2020-01-01') to ('2021-01-01');
CREATE TABLE T_2021 PARTITION OF T for values from ('2021-01-01') to ('2022-01-01');

Question: is it guaranteed that the id bigserial column will be unique across all partitions?

I see from metadata that there is just 1 sequence for table T created automatically for this bigserial column. There is no individual sequence per partition.

Does it mean that id column will be unique across all partitions?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user3440012
  • 171
  • 2
  • 12
  • If there's only one sequence, I can't see how this would not be the case – Bergi Jul 21 '23 at 00:57
  • @Bergi: The sequence *assists* with unique default values, but it does not *guarantee* anything. – Erwin Brandstetter Jul 21 '23 at 03:11
  • @ErwinBrandstetter Right. I misunderstood the question to ask whether it is guaranteed that the sequence-generated values will be unique despite the sequence being used from multiple partitions (which they are, unless someone resets the sequence), not to ask whether the column values are contrained. – Bergi Jul 21 '23 at 03:56
  • @Bergi: Yes, subtle but important differences. – Erwin Brandstetter Jul 21 '23 at 14:47

1 Answers1

1

Question: is it guaranteed that the id bigserial column will be unique across all partitions?

Answer: No.

Quoting from the chapter "Limitations" in the manual:

To create a unique or primary key constraint on a partitioned table, the partition keys must not include any expressions or function calls and the constraint's columns must include all of the partition key columns. This limitation exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure itself must guarantee that there are not duplicates in different partitions.

The serial column (or bigserial for that matter) draws values from a single SEQUENCE. While inserting default values, that results in unique id values across the whole partitioned table.

However, there is no guarantee. We can manually insert duplicates at will. Just like a serial column in a plain (non-partitioned) is no guarantee for uniqueness. The underlying SEQUENCE just helps to avoid unique violations. Only a UNIQUE index (directly, or indirectly as implementation detail of a PRIMARY KEY or UNIQUE constraint) actually enforces distinct values. (With a loophole for null values - but PK columns are also NOT NULL.)

See demo in this fiddle.

A more up-to-date IDENTITY column can do a better job. (Still no guarantee, though.) See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228