pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
New database: SQL_ASCII vs UTF-8 trade-offs
6+ messages / 3 participants
[nested] [flat]

* New database: SQL_ASCII vs UTF-8 trade-offs
@ 2006-03-07 14:08  ow <oneway_111@yahoo.com>
  0 siblings, 2 replies; 6+ messages in thread

From: ow @ 2006-03-07 14:08 UTC (permalink / raw)
  To: pgsql-admin

"PostgreSQL 8.1.0 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 20051111
(prerelease) (Debian 4.0.2-4)"

Hi,

Am having some doubts whether a new db should be with SQL_ASCII or UTF-8
encoding. We expect ALL of our data to be ASCII. At the same time, I guess,
it's possible that some user may decide to get creative and enter, for example,
his own name with non-ASCII chars.

So, it seems that UTF-8 would be a better choise even if we plan to store only 
ASCII data (a lot of ASCII data though).

Are there any negative effects related to the selection of UTF-8 over SQL_ASCII
(e.g. size of the database, sort/like/group issues, etc)?

Thanks in advance

---------------------------------------------------------------------------

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: New database: SQL_ASCII vs UTF-8 trade-offs
@ 2006-03-07 14:38  Peter Eisentraut <peter_e@gmx.net>
  parent: ow <oneway_111@yahoo.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Peter Eisentraut @ 2006-03-07 14:38 UTC (permalink / raw)
  To: pgsql-admin; +Cc: ow <oneway_111@yahoo.com>

Am Dienstag, 7. März 2006 15:08 schrieb ow:
> Are there any negative effects related to the selection of UTF-8 over
> SQL_ASCII (e.g. size of the database, sort/like/group issues, etc)?

If you're only planning to store ASCII data, choosing UTF-8 will not cause any 
additional problems.  But obviously you're more future-proof that way.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: New database: SQL_ASCII vs UTF-8 trade-offs
@ 2006-03-07 14:49  Tom Lane <tgl@sss.pgh.pa.us>
  parent: ow <oneway_111@yahoo.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Tom Lane @ 2006-03-07 14:49 UTC (permalink / raw)
  To: ow <oneway_111@yahoo.com>; +Cc: pgsql-admin

ow <oneway_111@yahoo.com> writes:
> Are there any negative effects related to the selection of UTF-8 over SQL_ASCII

There will be a speed penalty; whether it's significant in your
application is something you can only determine by experiment.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: New database: SQL_ASCII vs UTF-8 trade-offs
@ 2006-03-07 15:48  ow <oneway_111@yahoo.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: ow @ 2006-03-07 15:48 UTC (permalink / raw)
  To: pgsql-admin



--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> ow <oneway_111@yahoo.com> writes:
> > Are there any negative effects related to the selection of UTF-8 over
> SQL_ASCII
> 
> There will be a speed penalty; whether it's significant in your
> application is something you can only determine by experiment.

I see... If *ALL* data is in ASCII, is it possible to just update
"pg_database.encoding" to UTF-8 or will I need to recreate the db?

Thanks







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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: New database: SQL_ASCII vs UTF-8 trade-offs
@ 2006-03-07 16:07  Tom Lane <tgl@sss.pgh.pa.us>
  parent: ow <oneway_111@yahoo.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2006-03-07 16:07 UTC (permalink / raw)
  To: ow <oneway_111@yahoo.com>; +Cc: pgsql-admin

ow <oneway_111@yahoo.com> writes:
> I see... If *ALL* data is in ASCII, is it possible to just update
> "pg_database.encoding" to UTF-8 or will I need to recreate the db?

It seems risky, but you could probably get away with that as long
as the database locale (LC_COLLATE/LC_CTYPE) is "C" ... which is really
the only one that's safe with SQL_ASCII anyway ... note that
already-started backends will probably fail to notice such a change.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: New database: SQL_ASCII vs UTF-8 trade-offs
@ 2006-03-07 16:22  ow <oneway_111@yahoo.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: ow @ 2006-03-07 16:22 UTC (permalink / raw)
  To: pgsql-admin


--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> It seems risky, but you could probably get away with that as long
> as the database locale (LC_COLLATE/LC_CTYPE) is "C" ... which is really
> the only one that's safe with SQL_ASCII anyway ...

I actually created the cluster with:
test1:~# /usr/lib/postgresql/8.1/bin/initdb --pwprompt -D
/var/lib/postgresql/8.1/main/ --lc-collate=POSIX


test1:~# locale
LANG=
LC_CTYPE="POSIX"
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=

Not sure if it's going to make a difference. Thanks








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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2006-03-07 16:22 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-07 14:08 New database: SQL_ASCII vs UTF-8 trade-offs ow <oneway_111@yahoo.com>
2006-03-07 14:38 ` Peter Eisentraut <peter_e@gmx.net>
2006-03-07 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-07 15:48   ` ow <oneway_111@yahoo.com>
2006-03-07 16:07     ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-07 16:22       ` ow <oneway_111@yahoo.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox