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 :-)
Thanks for any idea, Andrea
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
On Tue, Jan 25, 2022 at 10:00 AM andrea antonello < andrea.antonello@gmail.com> wrote:
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 :-)
Thanks for any idea, Andrea
===============================================
Linux User Group Bolzano-Bozen-Bulsan mailing list
Le preferenze possono essere modificate da / Einstellungen k?nnen ge?ndert werden durch / Preferences can be changed from https://postorius.lugbz.org oppure inviando una e-mail a / oder durch Senden einer E-Mail an / or by sending an e-mail to lugbz-list-request@lists.lugbz.org
-- Luca Romano luke.romano@gmail.com
Have fun!
===============================================
Linux User Group Bolzano-Bozen-Bulsan mailing list
Le preferenze possono essere modificate da / Einstellungen k?nnen ge?ndert werden durch / Preferences can be changed from https://postorius.lugbz.org oppure inviando una e-mail a / oder durch Senden einer E-Mail an / or by sending an e-mail to lugbz-list-request@lists.lugbz.org
Ciao, a solution is:
CREATE VIEW AS SELECT row_number() over() AS ID, * FROM ( SELECT ... UNION SELECT ... ) AS myview;
On Tue, Jan 25, 2022 at 10:00 AM andrea antonello < andrea.antonello@gmail.com> wrote:
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 :-)
Thanks for any idea, Andrea
===============================================
Linux User Group Bolzano-Bozen-Bulsan mailing list
Le preferenze possono essere modificate da / Einstellungen k?nnen ge?ndert werden durch / Preferences can be changed from https://postorius.lugbz.org oppure inviando una e-mail a / oder durch Senden einer E-Mail an / or by sending an e-mail to lugbz-list-request@lists.lugbz.org
-
andrea antonello -
Luca Romano