How to prevent implicit casting from bytea to text during the current connection in postgres?
1
vote
1
answer
6627
views
In Postgres
bytea
values are automatically converted to text
when inserted to text
/varchar
columns, based on the bytea_output
setting.
I am working with some program code which automatically converts certain values (binary strings) in the program to a bytea format. The problem is that users might accidentally try to insert these values into a text
column. By default, Postgres will allow this, but this cannot always work smoothly - for example if there are non-ASCII bytes. I think users may not realise the strange insert behaviour is due to their use of a binary string in the calling program. Therefore, if a bytea
to text
conversion happens, I want Posgres to raise an exception.
I am aware of CREATE CAST
, but as I understand, this is a system-wide action. I do not want to change the system behaviour for other connections. I could do CREATE CAST
followed by DROP CAST
but this seems dirty to me as it still not contained within the connection.
*How do I make (implicit) casts from bytea
to text
throw an exception only within the current connection?*
The sql is issued automatically, so I can add a preceding SQL statement before every statement, that's no problem.
I was a little surprised by this behaviour because Postgres usually errs on the side of strictness, which I like.
This question follows from my previous question:
- https://dba.stackexchange.com/questions/270293/
Asked by user183966
(115 rep)
Jul 3, 2020, 03:32 PM
Last activity: Jul 6, 2020, 10:56 AM
Last activity: Jul 6, 2020, 10:56 AM