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

On Thu, Apr 26, 2012 at 2:45 PM, Alexander Farber
<alexander(dot)farber(at)gmail(dot)com> wrote:
> 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?)

upgrade your database and use the new json type/features.

merlin