Question 89559 in PostgreSQL Question posted in
The official documentation can be found here.

How to store index number of row, based on other column? – Postgresql

I need to store order in which rows were written, but new for every unique value in other column

For example:

Column A Base index
1 1 1
2 2 1
3 2 2
4 2 3
5 2 4
6 3 1
7 3 2
8 1 2

So "index" must store identity based on "Base"

I tried:

INSERT INTO mytable ("Column A", "Base", "index") VALUES ($1, $2, next_index($2))

Where next_index:

Declare
new_index bigint;
BEGIN
SELECT MAX(index) + 1
INTO new_index
FROM mytable
WHERE Base = *arg*;
RETURN new_index;
END;

But this doesn’t work, when i make concurrent inserts: next_index() were counted multiple times before insert was made, so value of indexes were the same for this rows, but they must be unique for specific Base

Tags:

3

Answers


  1. 0

    I’d recommend to store a timestamp and create the index in a view.

    An alternative would be to prevent concurrent inserts by requiring a lock.
    The problem here is that all applications doing inserts have to honor that which becomes a organisational problem.

    Or you could achieve something similar by creating a unique index based on Base and index. When two concurrent inserts happen, the one will get a unique key violation and would have to retry.

    Login or Signup to reply.
  2. 0

    I agree with Jens. Storing when the row was written should be done with a timestamp.

    That said, to achieve the results in your example, you could use row_number.

      SELECT Column_A
       , Base
       , ROW_NUMBER() OVER (PARTITION BY Base ORDER BY Column_A) as _index
    FROM temp
ORDER BY Column_A, _index

    Fiddle

    Login or Signup to reply.
  3. 0

    As you have discovered the structure max(x)+1 when used in a multi-user environment is virtually guaranteed to produce duplicates. One way to avoid this basically ignores the index on insert, just use a sequence. Then for selection create a view, that constructs the order index from the relative values stored. (Demo here)

    create table test_tbl(column_A integer 
                , base     integer
                , gindex   serial 
                );

create or replace view test as
  select Column_a "Column A"
       , Base     "Base" 
       , gindex   "Stored Index"
       , row_number(*) over (partition by base order by gindex) "Index"
    from test_tbl;

    Potential downside. You have to manage the column gindex for Updates and Deletes. But I guess you need to do essentially the same maintenance either anyway.

    But, I also agree with Jens. Just use a timestamp.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search