Re: UTF8 problem

On Thu, 17 Nov 2011 15:52:05 +0100
"Kai Otto" <Kai(at)medis(dot)nl> wrote:

OOPS: putting post back into the ML.

> I have created the database like this:
> CREATE DATABASE postgres
> WITH OWNER = postgres
> ENCODING = 'UTF8'
> TABLESPACE = pg_default
> LC_COLLATE = 'English_United States.1252'
> LC_CTYPE = 'English_United States.1252'
> CONNECTION LIMIT = -1;

Ok, so your DB is natively working in UTF-8 but collates to cp1252.

I don't use collate (yet?), but it don't seem a good idea to use it directly
at source (svr) - On this part I'm helpless, there are more experienced
people that should tell you the best way to go.

> ALTER DEFAULT PRIVILEGES
> GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
> ON TABLES
> TO public;

You really don't wanna do that (means anyone w/ CONNECT right to DB will be
able to do whatever on public.* tables).

This is typical of w$ (lack of) security deformation: opening everything,
then closing when in the mood for.
Use the Linux way: close everything and only allow what's needed - meaning:
grant *only* needed privileges on a user basis.

Use also schemas: when you have a large normalized DB it allows you to have
tables with same names and (almost) same content, but in different schemas.

> ALTER DEFAULT PRIVILEGES
> GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
> ON TABLES
> TO postgres;

Totally useless as user postgres is already THE big chief; you should
carefully read (AND experiment) about Pg rights system (and side effects).

And don't use double quotes unless imperative need.
As Pg is by default case insensitive, it is easier to have (and write!):

CREATE TABLE japanesetest (
id bigint PRIMARY KEY,
name text
) WITHOUT OIDS;

INSERT INTO japanesetest (id, name) VALUES (2, '\x83}\x83C
\x83h\x83L\x83\x85\x83\x81\x83\x93\x83g (My Documents)')

And if primary-key-number-designation-when-inserting (that's its Indian
name:) isn't really a concern, use a bigserial instead, and autoincrement
it using .... VALUES( default, .....

--
Interchangeable parts won't.