Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Alban Hertroys
Date:
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?

Alban.

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

From
Martijn van Oosterhout
Date:
On Thu, Jan 20, 2005 at 02:48:40PM +0100, Alban Hertroys 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...

My guess is that the \0x00 is being expanded to a null. If you want a
circumflex, why not just set the encoding to "latin9" or whatever and
send it normally. Unicode characters don't have embedded nulls iirc so
that can't be a properly encoded character anyway...

> 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'...

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
Attachment

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

From
Ian Barwick
Date:
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