Update part (order number) that is primary key in Postgres

user_10188661 0
Viewed 103

I have table:

book_id | part | name
1       | 1    | chap 1
1       | 2    | chap 2
1       | 3    | chap 3
1       | 4    | chap 4

Primary key is book_id and part.

How can delete part 2 and update order of parts to get:

book_id | part | name
1       | 1    | chap 1
1       | 2    | chap 3
1       | 3    | chap 4

I can do a transaction and firstly delete part 2, but how can i then update part column without getting duplicate primary key error?

postgresql
2 Answers

I would choose a different approach. Instead of persisting the part number, persist the order of the parts:

CREATE TABLE book_part (
   book_id bigint NOT NULL,
   part_order real NOT NULL,
   name text NOT NULL,
   PRIMARY KEY (book_id, part_order)
);

The first part that gets entered gets a part_order of 0.0. If you add a part at the beginning or the end, you just assign to part_order 1.0 less or more than the previous minimum or maximum. If you insert a part between two existing parts, you assign a part_order that is the arithmetic mean of the adjacent parts.

An example:

-- insert the first part

INSERT INTO book_part VALUES (1, 0.0, 'Introduction');

-- insert a part at the end

INSERT INTO book_part VALUES (1, 1.0, 'Getting started with PostgreSQL');

-- insert a part between the two existing parts

INSERT INTO book_part VALUES (1, 0.5, 'The history of PostgreSQL');

-- adding yet another part between two existing parts

INSERT INTO book_part VALUES (1, 0.25, 'An introductory example');

The actual part number is calculated when you query the table:

SELECT book_id,
       row_number() OVER (PARTITION BY book_id ORDER BY part_order) AS part,
       name
FROM book_part;

The beauty of that is that you don't need to update a lot of rows when you add or delete a part.

user_64643080

Unlike most RDBMS, PostGreSQL does not support updating a primary key that might violate a preexisting value without having to use a deferred constraint. In fact PostGreSQL execute the update row by row which conducts to find a "phantom" duplicate key, while other RDBMS that respects the standard uses a set based approach (MS SQL Server, Oracle, DB2...)

So you must use a deferred constraint.

ALTER TABLE book_part 
    ALTER CONSTRAINT ??? *PK constraint name* ??? DEFERRABLE INITIALLY IMMEDIATE;

This is a severe limitations of PG... See "5 – The hard way to udpates unique values" in http://mssqlserver.fr/postgresql-vs-sql-server-mssql-part-3-very-extremely-detailed-comparison/

user_126598720
Related