Skip site navigation (1) Skip section navigation (2)

Use arrays to store multilanguage texts

From: Michal Táborský <michal(at)taborsky(dot)cz>
To: pgsql-general(at)postgresql(dot)org
Subject: Use arrays to store multilanguage texts
Date: 2004-05-29 09:31:27
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
I am wondering, if it's effective to use text arrays to store
multilanguage information.

We used to do it like this:

CREATE TABLE product (
	id serial NOT NULL,
	price float4,

CREATE TABLE product_txt (
	product_id integer,
	language_id integer, -- 1=English, 2=Czech, ...
	name text,
	description text,
	PRIMARY KEY (product_id, language_id)

Then in queries for English version we used joins:

SELECT product.*, product_txt.*
FROM product JOIN product_txt ON
WHERE product_txt.language_id=1

It works as is supposed to, but of course there is some database
overhead and mainly it is much more complicated to handle data this way.
Since 7.4 introduced arrays, I was thinking about using them for storing
multilanguage strings. Like this:

CREATE TABLE product (
	id serial NOT NULL,
	price float4,
	name text[],
	description text[]

Then I'd just do:

SELECT id, price, name[1], description[1] FROM product

Much simpler and IMHO faster (but I'm not a pg-hacker). I never had time
to test it much, but now we are going to build a new database with
multilanguage strings and I am seriously considering using arrays.

The only question, which remains unanswered is how is it with indexes. I
mean--most of the queries on such table are ordered by name, for
example. Therefore I'd do this:

SELECT id, price, name[1], description[1] FROM product ORDER BY name[1]

Is it possible to build an index, which will be used in such query? I
had no luck with CREATE INDEX product_name1 ON product (r[1]), but maybe
the syntax is just somehow different.

Are there any more drawbacks or limitations, that maybe I am not aware
of, which would discourage you from doing the multilanguage support this
way? Should we consider this or stick to the old ways?

Thanks for your attention.

Michal Taborsky


pgsql-general by date

Next:From: Shridhar DaithankarDate: 2004-05-29 09:31:45
Subject: Re: filesystem option tuning
Previous:From: Paul ThomasDate: 2004-05-29 08:58:56
Subject: Re: PostgreSQL delete the blank in the end of the String automatically. how can I avoid it?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group