Re: UTF8 problem - Mailing list pgsql-novice

> > 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).
>

Thanks for the suggestions. Will dive into that ASAP.

> 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.

Without double quotes the table is not recognized:
      SELECT * FROM JapaneseTest
Results in:
      ERROR:  relation "japanesetest" does not exist

For the ID column, I am actually using uuid. But for testing this was
easier.

Anyway, thanks for your help.