Thread: How can I insert a UTF-8 character with psql?

How can I insert a UTF-8 character with psql?

From

Roland Glenn McIntosh

Date:

12 June 2003, 22:13:17

I would like to insert a EuroSign as two byte character number 20AC.
Is there a way I can do this from an ISO-8859-1 (latin1) terminal emulator via the psql tool?
Is there an entity scheme?

I tried various permutations of the following to no avail:
    INSERT INTO mytable VALUES('currency_symbol','\u20AC');

Note that I don't actually care how postgres stores this.  I'm reading it via JDBC like this:
    String curr = new String(rs.getBytes(1), "UTF-8");

Which I think is totally compatible with normal 7 bit characters.
Your help is appreciated.

-rgm

Re: How can I insert a UTF-8 character with psql?

From

Ian Barwick

Date:

13 June 2003, 07:05:07

On Friday 13 June 2003 00:13, Roland Glenn McIntosh wrote:
> I would like to insert a EuroSign as two byte character number 20AC.
> Is there a way I can do this from an ISO-8859-1 (latin1) terminal emulator
> via the psql tool? Is there an entity scheme?
>
> I tried various permutations of the following to no avail:
>     INSERT INTO mytable VALUES('currency_symbol','\u20AC');

Use escaped octal codes:
INSERT INTO my_tbl (unitxt) VALUES('\303\244')
(replace '\303\244' with whatever 20AC is in octal), or if you prefer typing
to maths:

INSERT INTO my_tbl (unitxt) VALUES(encode(decode('c3a4','hex'), 'escape'))

Ian Barwick
barwick@gmx.net

Re: How can I insert a UTF-8 character with psql?

From

Roland Glenn McIntosh

Date:

13 June 2003, 13:57:31

Ian - Thank you very much for your reply.  It only seems to half work, however.
This Euro sign is giving me a headache.
I tried both of the following:

        UPDATE my_tbl SET value='\40\254' WHERE NAME LIKE 'curr%';
and
        UPDATE my_tbl SET value=encode(decode('20ac','hex'), 'escape') WHERE NAME LIKE 'curr%';
and
        UPDATE my_tbl SET value='\040\254' WHERE NAME LIKE 'curr%';

Note that the documentation states this:
"Anything contained in single quotes is furthermore subject to C-like
substitutions for \n (new line), \t (tab), \digits, \0digits, and \0xdigits
(the character with the given decimal, octal, or hexadecimal code)."

That appears to just be incorrect.  Is there any form of this behavior that I can rely on in 7.3.3?  Note that the
"\254"part (AC in hex) appears to work correctly.  As far as I can tell, hex value AC is getting inserted.  It's the
"20"part I'm having difficulty with. 

Anyone have a solution for me?

-rgm


At 09:03 AM 06.13.2003 +0200, you wrote:
>On Friday 13 June 2003 00:13, Roland Glenn McIntosh wrote:
>> I would like to insert a EuroSign as two byte character number 20AC.
>> Is there a way I can do this from an ISO-8859-1 (latin1) terminal emulator
>> via the psql tool? Is there an entity scheme?
>>
>> I tried various permutations of the following to no avail:
>>       INSERT INTO mytable VALUES('currency_symbol','\u20AC');
>
>Use escaped octal codes:
>INSERT INTO my_tbl (unitxt) VALUES('\303\244')
>(replace '\303\244' with whatever 20AC is in octal), or if you prefer typing
>to maths:
>
>INSERT INTO my_tbl (unitxt) VALUES(encode(decode('c3a4','hex'), 'escape'))
>
>Ian Barwick
>barwick@gmx.net

Re: How can I insert a UTF-8 character with psql?

From

Dennis Gearon

Date:

13 June 2003, 15:27:36

Any chance there's ever going to be HEX escaping?

Ian Barwick wrote:

> On Friday 13 June 2003 00:13, Roland Glenn McIntosh wrote:
>
>>I would like to insert a EuroSign as two byte character number 20AC.
>>Is there a way I can do this from an ISO-8859-1 (latin1) terminal emulator
>>via the psql tool? Is there an entity scheme?
>>
>>I tried various permutations of the following to no avail:
>>    INSERT INTO mytable VALUES('currency_symbol','\u20AC');
>
>
> Use escaped octal codes:
> INSERT INTO my_tbl (unitxt) VALUES('\303\244')
> (replace '\303\244' with whatever 20AC is in octal), or if you prefer typing
> to maths:
>
> INSERT INTO my_tbl (unitxt) VALUES(encode(decode('c3a4','hex'), 'escape'))
>
> Ian Barwick
> barwick@gmx.net
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Re: How can I insert a UTF-8 character with psql?

From

Ian Barwick

Date:

13 June 2003, 16:29:47

On Friday 13 June 2003 15:57, Roland Glenn McIntosh wrote:
> Ian - Thank you very much for your reply.  It only seems to half work,
> however. This Euro sign is giving me a headache.
> I tried both of the following:
>
>         UPDATE my_tbl SET value='\40\254' WHERE NAME LIKE 'curr%';
> and
>         UPDATE my_tbl SET value=encode(decode('20ac','hex'), 'escape')
> WHERE NAME LIKE 'curr%'; and
>         UPDATE my_tbl SET value='\040\254' WHERE NAME LIKE 'curr%';

<reminder_to_self>think before posting</reminder_to_self>

You will of course need to convert the two-byte Unicode UCS-2 value into
the UTF-8 equivalent.


Ian Barwick
barwick@gmx.net

Re: How can I insert a UTF-8 character with psql?

From

Peter Eisentraut

Date:

16 June 2003, 12:42:24

Roland Glenn McIntosh writes:

> I would like to insert a EuroSign as two byte character number 20AC.
> Is there a way I can do this from an ISO-8859-1 (latin1) terminal emulator via the psql tool?

Set the client encoding to latin9 and enter it normally.

> Is there an entity scheme?

Not in the way you imagine, but there should be.

--
Peter Eisentraut   peter_e@gmx.net