Thread: Trick to create alias/mirror table
Hi,
Looking for a trick to make something like an alias or a mirror table. The alias can be updated. View is not suitable because it is read only data. The alias is always linked to the original table. When a modification occurred in the original table this will be visible in all link tables.
Thanks
Hi
You can use following options
1. Postgres FDW
2. Materialized Views
Regards
Kashif Zeeshan
On Mon, Jul 8, 2024 at 4:25 PM intmail01 <intmail01@gmail.com> wrote:
intmail01 schrieb am 08.07.2024 um 13:27: > Hi, Looking for a trick to make something like an alias or a mirror > table. The alias can be updated. View is not suitable because it is > read only data. The alias is always linked to the original table. > When a modification occurred in the original table this will be > visible in all link tables. This sounds very much as if a view would solve that requirement. Views can be updated in Postgres. Either automatically if it's a "simple view" or by providing a function that handles the DML.
On 7/8/24 14:27, intmail01 wrote:
What? Check out the docs. Views can be perfectly modified.View is not suitable because it is read only data.
The alias is always linked to the original table. When a modification occurred in the original table this will be visible in all link tables.Thanks
No. Simple view can not be updated with a GUI like PgAdmin or LibreOffice Base. Only query code can modify it and every people can not code sql.
On Mon, Jul 8, 2024 at 3:09 PM <shammat@gmx.net> wrote:
intmail01 schrieb am 08.07.2024 um 13:27:
> Hi, Looking for a trick to make something like an alias or a mirror
> table. The alias can be updated. View is not suitable because it is
> read only data. The alias is always linked to the original table.
> When a modification occurred in the original table this will be
> visible in all link tables.
This sounds very much as if a view would solve that requirement.
Views can be updated in Postgres. Either automatically if it's a "simple view"
or by providing a function that handles the DML.
On Monday, July 8, 2024, intmail01 <intmail01@gmail.com> wrote:
No. Simple view can not be updated with a GUI like PgAdmin or LibreOffice Base. Only query code can modify it and every people can not code sql.
Fair. But updatable views are the solution PostgreSQL provides to your use case. That client applications aren’t fully exposing it to the user is unfortunate. There isn’t another way of doing this aside from having a bunch of synchronized tables done probably with manual triggers.
David J.
intmail01 schrieb am 08.07.2024 um 15:04: > No. Simple view can not be updated with a GUI like PgAdmin or > LibreOffice Base. Only query code can modify it and every people can > not code sql. Well, that's a bug in those applications and you should file a bug report or enhancment request with those tools. As a side note: I would never provide an "admin" tool like pgAdmin to end users that have no idea what "SQL" is.
intmail01 schrieb am 08.07.2024 um 15:04: > No. Simple view can not be updated with a GUI like PgAdmin or > LibreOffice Base. Only query code can modify it and every people can > not code sql. You might want to look for "no code" or "low code" solutions to provide an end-user friendly GUI to edit the data. Tools in that direction that I have came across, but have never tried are SQLPage, NocoDB or Budibase. Using a tool like that might even remove the need to "shield" the actual tables through views.