Re: Database Encoding

i would like to make sure my template1 is encoded as
latin1.

1. how can i check the db encoding? either a psql or
pgadmin method is fine.

2. how can i change it?

3. how can i change it in an existing database with
lots of tables (i can remove all data, if required).

i just went with the default when i set up the cygwin
version of pgsql - i'm not even sure what it is.

tia...

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Hi,

psql -l gives you your database listing with encoding

bash-2.05b$ psql -l
List of databases
Name | Owner | Encoding
-------------------------+----------+----------
1sthoney | postgres | LATIN1
ami_bulletin | postgres | LATIN1
amiedu_www | postgres | LATIN1
amiedu_www_eng | postgres | LATIN1
...

The default, template1, is set with initdb command. e.g.

initdb -E LATIN1 ..., so one way to change it is to re-init.

You can create databases with different encoding (from template1) with same
switch e.g.

reatedb mydb -E UTF8 ...

Cheers,

Aarni

On Saturday 16 April 2005 14:22, you wrote:
> i would like to make sure my template1 is encoded as
> latin1.
>
> 1. how can i check the db encoding? either a psql or
> pgadmin method is fine.
>
> 2. how can i change it?
>
> 3. how can i change it in an existing database with
> lots of tables (i can remove all data, if required).
>
> i just went with the default when i set up the cygwin
> version of pgsql - i'm not even sure what it is.
>
> tia...
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

--------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system
--------------

Aarni =?iso-8859-1?q?Ruuhim=E4ki?= <aarni(at)kymi(dot)com> writes:
> initdb -E LATIN1 ..., so one way to change it is to re-init.

> You can create databases with different encoding (from template1) with same
> switch e.g.

> createdb mydb -E UTF8 ...

Note that in most cases you can't just whack the encoding around without
paying attention to locale. I believe the only locale that really works
with multiple encodings is "C" --- all the other ones assume a
particular encoding. You'll get very odd and unpleasant results from
text sorting and functions like upper/lower if you have the database
locale and encoding set incompatibly.

Unfortunately we don't currently support changing locale on the fly ---
so you can only set it at initdb time. So the -E switch to createdb
is a bit dangerous.

This stuff is covered in the docs at
http://www.postgresql.org/docs/8.0/static/charset.html

regards, tom lane

Hi,

Yes, and thanks for correction and guidance, another thing to consider when
planning new system surroundings, well, the general idea ..., bearing in mind
the installed locale(s) etc., amongst other things.

Long way to Tipitina's,

Aarni

On Saturday 16 April 2005 19:18, you wrote:
> Aarni =?iso-8859-1?q?Ruuhim=E4ki?= <aarni(at)kymi(dot)com> writes:
> > initdb -E LATIN1 ..., so one way to change it is to re-init.
> >
> > You can create databases with different encoding (from template1) with
> > same switch e.g.
> >
> > createdb mydb -E UTF8 ...
>
> Note that in most cases you can't just whack the encoding around without
> paying attention to locale. I believe the only locale that really works
> with multiple encodings is "C" --- all the other ones assume a
> particular encoding. You'll get very odd and unpleasant results from
> text sorting and functions like upper/lower if you have the database
> locale and encoding set incompatibly.
>
> Unfortunately we don't currently support changing locale on the fly ---
> so you can only set it at initdb time. So the -E switch to createdb
> is a bit dangerous.
>
> This stuff is covered in the docs at
> http://www.postgresql.org/docs/8.0/static/charset.html
>
> regards, tom lane

--------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system
--------------