Re: How to manually insert an UTF-8 character into an SQL statement?

On Thu, 20 Jan 2005 14:48:40 +0100, Alban Hertroys
<alban@magproductions.nl> wrote:
> I'm trying to insert a record that contains an ô (o circumflex) into a
> table using the psql client.
> I also tried with phppgadmin and pgadmin, but both can't do this. They
> insert a HTML entity and error out respectively. Not what I had in mind...
>
> Supposedly I should be able to type:
> INSERT INTO table (name) VALUES ('C\0x00f4te d''Azur');
> but all I manage to achieve is inserting a capital 'C'...
>
> It doesn't seem to matter to which encoding I set psql either.
> What am I doing wrong?

For a start, 0x00F4 does not represent valid UTF-8; you want 0xC3B4.

AFAIK you can insert this using two different methods in psql:

  \set myvalue '\'C\0xc3\0xb4te d\'\'Azur\''
  INSERT INTO table (name) VALUES (:myvalue);

in 8.0 also:

  \set myvalue '$$C\0xc3\0xb4te d\'Azur$$'

or:

INSERT INTO table (name) values
('C'||encode(decode('c3b4','hex'),'escape')||'te d''Azur');

Ian Barwick