0

I am wondering if I can achieve this using a postgres view (or materialized view) rather than a table.

Requirement: I need a unique ID on my view that must always belong to the same row. My first thought was a composite key - there are two integer fields in my view that when combined would be unique. However of course (52, 6) will look the same as (5, 26) when combined.

As per Is there a simple way to create a unique integer key from a two-integer composite key? it looks like a lot of complicated maths can be done to create a unique composite bigint value for a view, however I feel like this might be a bit hard to maintain.

I'm wondering if there's anything a materialized view might offer to ensure an ID is always unique and not changing, even once the view is refreshed.

I've tried Row_number() over but that is ruled out as, although it is unique, it changes each time.

I'm currently considering just using a table to get around this issue if there is nothing simple that achieves this.

alichur
  • 925
  • 7
  • 19
  • 1
    The request is not clear to me. Why do you need a unique id in a view ? As far as I can remember, the only case where it is required is for a materialized view to be refreshed concurrently. Starting from two integers int1 and int2, it is simple to create one bigint which may be a unique id if the binome (int1, int2) is unique : (int1 * 2^32 + int2) :: bigint. This bigint is different for both binomes (52,6) and (5,26). Hope that will help. – Edouard Oct 22 '21 at 21:14
  • Hi thanks for your comment - we need a unique id for a 3rd party piece of code that is really designed to be used with a table rather than a view. The id is used as a reference so must not change for that row ever. – alichur Oct 25 '21 at 21:29

1 Answers1

1

If you need a table with the two columns int1 and int2, then the id = (int1 * 2^32 + int2) :: bigint will not change until int1 and/or int2 are updated in the table. Then, this id can be stored in the table as a new column of type bigint, or you can create an index on that table which will store this id while being used when querying that table :

CREATE OR REPLACE FUNCTION bigint_id(int1 integer, int2 integer)
RETURNS bigint LANGUAGE sql AS
$$
SELECT (int1 * 2^32 + int2) :: bigint ;
$$ ;

CREATE INDEX bigint_id ON your_table USING btree (bigint_id(int1, int2)) ;

The following query will use the bigint_id index :

SELECT * FROM your_table WHERE bigint_id(int1, int2) = nnnn
Edouard
  • 6,577
  • 1
  • 9
  • 20