Sample Header Ad - 728x90

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