pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing psql to insert character codes
8+ messages / 3 participants
[nested] [flat]
* Using psql to insert character codes
@ 2003-05-10 14:20 Ian Barwick <barwick@gmx.net>
0 siblings, 3 replies; 8+ messages in thread
From: Ian Barwick @ 2003-05-10 14:20 UTC (permalink / raw)
To: pgsql-sql
Say using psql I wish to insert a character into a VARCHAR / TEXT / whatever
column using the its hexadecimal representation in the relevant character set
/ encoding. E.g.: C3A4, which represents the character 'ä' (lower case a with
umlaut) in UTF-8.
I can do this:
INSERT INTO my_tbl (unitxt) VALUE(encode(decode('c3a4','hex'), 'escape'))
Is there any other, shorter way of doing the same?
Ian Barwick
barwick@gmx.net
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using psql to insert character codes
@ 2003-05-10 16:15 Randall Lucas <rlucas@tercent.net>
parent: Ian Barwick <barwick@gmx.net>
2 siblings, 1 reply; 8+ messages in thread
From: Randall Lucas @ 2003-05-10 16:15 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; +Cc: pgsql-sql
Yes, use a Mac!
I'm not being entirely facetious -- if you can use a Mac OS X terminal
prompt (and if your pg config is substantially similar to mine),
inserting any unicode stuff is quite easy. Simply typing or
cutting-and-pasting at the terminal let me visually put in accented
Latin, Cyrillic, and Chinese (don't ask me what kind, I am a
sinoignoramus) no sweat.
Also, Java works well for this -- if you can write or use a Java app to
take advantage of its I18N, it will handle the encoding issues via JDBC
quite nicely as far as I can tell.
Best,
Randall
On Saturday, May 10, 2003, at 10:20 AM, Ian Barwick wrote:
>
> Say using psql I wish to insert a character into a VARCHAR / TEXT /
> whatever
> column using the its hexadecimal representation in the relevant
> character set
> / encoding. E.g.: C3A4, which represents the character 'ä' (lower case
> a with
> umlaut) in UTF-8.
>
> I can do this:
> INSERT INTO my_tbl (unitxt) VALUE(encode(decode('c3a4','hex'),
> 'escape'))
>
> Is there any other, shorter way of doing the same?
>
>
> Ian Barwick
> barwick@gmx.net
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using psql to insert character codes
@ 2003-05-10 16:53 Ian Barwick <barwick@gmx.net>
parent: Randall Lucas <rlucas@tercent.net>
0 siblings, 1 reply; 8+ messages in thread
From: Ian Barwick @ 2003-05-10 16:53 UTC (permalink / raw)
To: Randall Lucas <rlucas@tercent.net>; +Cc: pgsql-sql
On Saturday 10 May 2003 18:15, Randall Lucas wrote:
> Yes, use a Mac!
>
> I'm not being entirely facetious -- if you can use a Mac OS X terminal
> prompt (and if your pg config is substantially similar to mine),
> inserting any unicode stuff is quite easy. Simply typing or
> cutting-and-pasting at the terminal let me visually put in accented
> Latin, Cyrillic, and Chinese (don't ask me what kind, I am a
> sinoignoramus) no sweat.
Thanks, but right answer to wrong question ;-). "mlterm" in Linux / FreeBSD
works fine for me; the "problem" is not inputting in general, but the
ability to use psql to input using the hexadecimal (or decimal) character
code, which I need to do occasionally for some obscure character not
reachable by other means.
The solution I posted works, I was just wondering whether there was some more
elegant method I had overlooked.
Ian Barwick
barwick@gmx.net
> On Saturday, May 10, 2003, at 10:20 AM, Ian Barwick wrote:
> > Say using psql I wish to insert a character into a VARCHAR / TEXT /
> > whatever
> > column using the its hexadecimal representation in the relevant
> > character set
> > / encoding. E.g.: C3A4, which represents the character 'ä' (lower case
> > a with
> > umlaut) in UTF-8.
> >
> > I can do this:
> > INSERT INTO my_tbl (unitxt) VALUE(encode(decode('c3a4','hex'),
> > 'escape'))
> >
> > Is there any other, shorter way of doing the same?
> >
> >
> > Ian Barwick
> > barwick@gmx.net
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using psql to insert character codes
@ 2003-05-10 17:35 Ian Barwick <barwick@gmx.net>
parent: Ian Barwick <barwick@gmx.net>
2 siblings, 0 replies; 8+ messages in thread
From: Ian Barwick @ 2003-05-10 17:35 UTC (permalink / raw)
To: pgsql-sql
On Saturday 10 May 2003 16:20, Ian Barwick wrote:
> Say using psql I wish to insert a character into a VARCHAR / TEXT /
> whatever column using the its hexadecimal representation in the relevant
> character set / encoding. E.g.: C3A4, which represents the character 'ä'
> (lower case a with umlaut) in UTF-8.
>
> I can do this:
> INSERT INTO my_tbl (unitxt) VALUE(encode(decode('c3a4','hex'), 'escape'))
correction:
INSERT INTO my_tbl (unitxt) VALUES(encode(decode('c3a4','hex'), 'escape'))
Ian Barwick
barwick@gmx.net
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using psql to insert character codes
@ 2003-05-10 17:36 Randall Lucas <rlucas@tercent.net>
parent: Ian Barwick <barwick@gmx.net>
0 siblings, 0 replies; 8+ messages in thread
From: Randall Lucas @ 2003-05-10 17:36 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; +Cc: pgsql-sql
Ah, I see. </mac_over_enthusiasm> In that case, I would recommend
just making a custom function that handles that for you, maybe:
create function hex2esc(text) returns text as '
select encode(decode($1,''hex''), ''escape'');
' language sql;
Not a whole lot more elegant but shorter.
Best,
Randall
On Saturday, May 10, 2003, at 12:53 PM, Ian Barwick wrote:
> On Saturday 10 May 2003 18:15, Randall Lucas wrote:
>> Yes, use a Mac!
>>
>> I'm not being entirely facetious -- if you can use a Mac OS X terminal
>> prompt (and if your pg config is substantially similar to mine),
>> inserting any unicode stuff is quite easy. Simply typing or
>> cutting-and-pasting at the terminal let me visually put in accented
>> Latin, Cyrillic, and Chinese (don't ask me what kind, I am a
>> sinoignoramus) no sweat.
>
> Thanks, but right answer to wrong question ;-). "mlterm" in Linux /
> FreeBSD
> works fine for me; the "problem" is not inputting in general, but the
> ability to use psql to input using the hexadecimal (or decimal)
> character
> code, which I need to do occasionally for some obscure character not
> reachable by other means.
>
> The solution I posted works, I was just wondering whether there was
> some more
> elegant method I had overlooked.
>
> Ian Barwick
> barwick@gmx.net
>
>> On Saturday, May 10, 2003, at 10:20 AM, Ian Barwick wrote:
>>> Say using psql I wish to insert a character into a VARCHAR / TEXT /
>>> whatever
>>> column using the its hexadecimal representation in the relevant
>>> character set
>>> / encoding. E.g.: C3A4, which represents the character 'ä' (lower
>>> case
>>> a with
>>> umlaut) in UTF-8.
>>>
>>> I can do this:
>>> INSERT INTO my_tbl (unitxt) VALUE(encode(decode('c3a4','hex'),
>>> 'escape'))
>>>
>>> Is there any other, shorter way of doing the same?
>>>
>>>
>>> Ian Barwick
>>> barwick@gmx.net
>>>
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 2: you can get off all lists at once with the unregister command
>>> (send "unregister YourEmailAddressHere" to
>>> majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using psql to insert character codes
@ 2003-05-11 16:54 Peter Eisentraut <peter_e@gmx.net>
parent: Ian Barwick <barwick@gmx.net>
2 siblings, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2003-05-11 16:54 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; +Cc: pgsql-sql
Ian Barwick writes:
> I can do this:
> INSERT INTO my_tbl (unitxt) VALUE(encode(decode('c3a4','hex'), 'escape'))
>
> Is there any other, shorter way of doing the same?
Use octal escapes: '\303\244';
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using psql to insert character codes
@ 2003-05-14 21:18 Ian Barwick <barwick@gmx.net>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Ian Barwick @ 2003-05-14 21:18 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql
On Sunday 11 May 2003 18:54, Peter Eisentraut wrote:
> Ian Barwick writes:
> > I can do this:
> > INSERT INTO my_tbl (unitxt) VALUE(encode(decode('c3a4','hex'), 'escape'))
> >
> > Is there any other, shorter way of doing the same?
>
> Use octal escapes: '\303\244';
Aha, thanks.
That seems to imply though that this statement in the docs:
"...where xxx is an octal number, is the character with the corresponding
ASCII code."
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=sql-syntax.html
is not completely correct. Maybe it should read something like:
"...where xxx is an octal number which corresponds to a valid character in the
database encoding."
Ian Barwick
barwick@gmx.net
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using psql to insert character codes
@ 2003-05-15 15:52 Peter Eisentraut <peter_e@gmx.net>
parent: Ian Barwick <barwick@gmx.net>
0 siblings, 0 replies; 8+ messages in thread
From: Peter Eisentraut @ 2003-05-15 15:52 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; +Cc: pgsql-sql
Ian Barwick writes:
> That seems to imply though that this statement in the docs:
> "...where xxx is an octal number, is the character with the corresponding
> ASCII code."
> is not completely correct. Maybe it should read something like:
>
> "...where xxx is an octal number which corresponds to a valid character in the
> database encoding."
What it actually does is insert exactly one byte of the given value. The
problem is that you can easily construct invalid code sequences that way,
and I that should not be allowable.
What I would like better is if \xxx took xxx as a code point and converted
it to the appropriately encoded byte sequence. For single-byte encodings
that would mean no change, for Unicode it would make a lot more sense than
what it does now, but I don't know if that concept can be applied to all
character set encodings.
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-05-15 15:52 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-10 14:20 Using psql to insert character codes Ian Barwick <barwick@gmx.net>
2003-05-10 16:15 ` Randall Lucas <rlucas@tercent.net>
2003-05-10 16:53 ` Ian Barwick <barwick@gmx.net>
2003-05-10 17:36 ` Randall Lucas <rlucas@tercent.net>
2003-05-10 17:35 ` Ian Barwick <barwick@gmx.net>
2003-05-11 16:54 ` Peter Eisentraut <peter_e@gmx.net>
2003-05-14 21:18 ` Ian Barwick <barwick@gmx.net>
2003-05-15 15:52 ` Peter Eisentraut <peter_e@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox