Which data type to use for UTF8 JSON and perl/PHP: varchar, text or bytea?

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Which data type to use for UTF8 JSON and perl/PHP: varchar, text or bytea?
Date: 2012-04-26 19:45:15
Message-ID: CAADeyWjGZdKMqK+pzWrN5n6vdvnvzbCBphuvbnfuETaF3seoew@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I run a small card game with PostgreSQL 8.4.11 on
CentOS 6 at https://apps.facebook.com/video-preferans/

List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
pref | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

The client is in Flash, game daemon in Perl 5.10.1
and the web pages (using same database) in PHP 5.3.

My game uses UTF8 and uses the Unicode chars ♠♦♣♥
for the card suits + game language/users are russian.

The users ask for 1 feature for longer time already:
a game journal (i.e. logging of cards dealt and played).

I'd like to store that data (user names, their hands, etc.)
as JSON data in UTF8 encoding, length aprox. 1000 bytes

Does anybody have an advice on what data type
to use best for such a JSON "string"?

Should I take varchar, text or bytea.

And for the latter - how to handle it in Perl
if I currently use DBI and DBD::Pg?

For PHP I probably should use pg_(un)escape_bytea?

(And does this all work with "?" placholders?)

Regards
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2012-04-26 20:11:54 Re: Which data type to use for UTF8 JSON and perl/PHP: varchar, text or bytea?
Previous Message dennis jenkins 2012-04-26 19:07:42 Re: database error xx000?