How to convert text to bytea in Postgres?

[japananh]

• BYTEA is a Postgres data type to store binary strings.
• Ref: https://www.postgresql.org/docs/current/datatype-binary.html

This query will return an error: [22P02] ERROR: invalid input syntax for type bytea.

select sha256((theme_page_id::text || component)::bytea) from page_sections where shop_id = 7287358554485297152;

The error occurs because the direct casting of a TEXT value to BYTEA using ::BYTEA can fail if the input TEXT contains characters that aren't valid for a BYTEA representation. PostgreSQL expects BYTEA input to be encoded in a specific format, such as hexadecimal or escaped strings.

Solution: using CONVERT_TO function that take a TEXT value and an encoding, then returns a BYTEA value.

select convert_to(theme_page_id::text || component, 'UTF8') from page_sections where shop_id = 7287358554485297152;