pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedNew 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