Postgresql incrementing index on view

Good morning everyone.

I am trying to create a view in postgresql with its own unique incrementing
id.

Problem is I am starting from a union query to have several values of a
record in a one-to-many relation.

Therefore I can't use row_number().

I.e. I am trying to create a view from:

select row_number() over() as id, value1
union
select row_number() over() as id, value2

and I would have the same id for the pair of values.

Also using a sequence won't work, since it will be changing at every
select.

Does anyone know of a way to handle this?
To be honest I do not even think it makes sense from a conceptual point of
view of a 'view'.
But you never know :slight_smile:

Thanks for any idea,
Andrea

attachment.html (1.02 KB)

Ciao, a solution is:

CREATE VIEW AS
SELECT row_number() over() AS ID,

attachment.html (2.38 KB)

Hi Luca,

Ciao, a solution is:

CREATE VIEW AS
SELECT row_number() over() AS ID,
   *
FROM (
   SELECT ...
  UNION
   SELECT ...
) AS myview;

thanks, that indeed is working.

Cheers,
Andrea

attachment.html (3.43 KB)