varchar::bytea fails when varchar contains backslash
Hello,
I wanted to look into a column of type varchar converting the content
with ::bytea to a hex string, but this fails when the column contains a
backslash:
psql -Usisis -dtestdb
psql (14.1)
Geben Sie »help« für Hilfe ein.
testdb=# select trenn from sik_fstab where name='EdvSelKenn';
trenn
-------------------------------
; :,.-!@%&/()=?'*+#<>[\]{|}&"
; :,.-!@%&/()=?'*+#<>[\]{|}&"
(2 Zeilen)
testdb=# select trenn::bytea from sik_fstab where name='EdvSelKenn';
ERROR: invalid input syntax for type bytea
Why is this?
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Matthias Apitz wrote:
I wanted to look into a column of type varchar converting the content
with ::bytea to a hex string, but this fails when the column contains a
backslash:
Yes, casting from text to bytea cannot be used for that.
The convert_to() function must be used instead.
From the doc at
https://www.postgresql.org/docs/current/functions-binarystring.html
convert_to ( string text, dest_encoding name ) → bytea
Converts a text string (in the database encoding) to a binary string encoded
in encoding dest_encoding (see Section 24.3.4 for available conversions).
convert_to('some_text', 'UTF8') → \x736f6d655f74657874
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite