On Tue, 2021-06-01 at 20:09 +0000, Laura Smith wrote:
> I'm creating a Postgres backend for an internal tool which is essentially a
> very simple implementation of multi-lingual CMS.
>
> I am especially interested in feedback and suggestions in relation to the
> following questions:
>
> (a) Is this going to work as expected (i.e. have I missed some obvious
> foot-guns ?)
>
> (b) Is this manner of doing things reasonably efficient or are there better
> ways I should be thinking of ?
> (bear in mind the schema is not set in stone, so completely out of the box
> suggestions welcome !).
>
> The basic design concept (oversimplified) is: For each page, you have one or
> more objects and those objects may have content in one or more languages.
>
> create table langtest(
> pageid text not null,
> objectid text not null ,
> objectlang text not null,
> objectdata text not null);
>
> create unique index on (pageid,objectid,objectlang);
>
> select distinct on(objectid)objectid,objectlang,pageid,objectdata
> from langTest where pageid='zzz' and objectLang = any('{de,en}'::text[])
> order by objectid,array_position('{de,en}'::text[],objectLang);
That looks ok, except you should remove "objectid" from the index.
That column makes that the index cannot be used for "objectlang" effectively.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
