i want to convert an "asset32type" to a string, reason being the datatype in the "name" column of dbsync's multi_asset table is "asset32type".
what is an "asset32type"?
Cardano Stack Exchange is a question and answer site for users and developers of the Cardano cryptocurrency ecosystem. It only takes a minute to sign up.
Sign up to join this communityi want to convert an "asset32type" to a string, reason being the datatype in the "name" column of dbsync's multi_asset table is "asset32type".
what is an "asset32type"?
An asset32type
is a raw Postgres octet string, but limited to a maximum of 32 bytes. It is defined in schema/migration-1-0004-20201026.sql
as:
CREATE DOMAIN asset32type AS bytea CHECK (octet_length (VALUE) <= 32);
eg:
select * from multi_asset limit 3 ;
id | policy | name | fingerprint
----+------------------------------------------------------------+----------------------+----------------------------------------------
1 | \x00000002df633853f6a47465c9496721d2d5b1291b8398016c0e87ae | \x6e7574636f696e | asset12h3p5l3nd5y26lr22am7y7ga3vxghkhf57zkhd
2 | \x3a9241cd79895e3a8d65261b40077d4437ce71e9d7c8c6c00e3f658e | \x4669727374636f696e | asset1aq460y056c3mm4jmuv9vg39n7w8y5gykywjwge
3 | \x02f68378e37af4545d027d0a9fa5581ac682897a3fc1f6d8f936ed2b | \x4154414441 | asset1tm62f04gz390cjxttq4gz0s43lswr9estr2jcz
(3 rows)
It is stored as a raw Postgres string because there is not guaranetee about the encoding. It might be UTF-8 but it also might not be.
The above answer does a great job of explaining what it is, but doesn't answer how you get the value as a string. To pull the value as hex (which is the only thing that makes sense for a policy hash for example), you would encode it like so:
SELECT encode(policy::bytea, 'hex') AS policy_hex FROM multi_asset LIMIT 3;
Likewise, if you want to use a WHERE clause against it, you would decode it:
SELECT * FROM multi_asset WHERE policy = decode('00000002df633853f6a47465c9496721d2d5b1291b8398016c0e87ae', 'hex');
The asset name itself may make sense as ASCII/UTF8 or it might not. There's no guarantees in the system that an asset name resembles anything so should always be treated as raw hex as well and get the actual asset information from the metadata server or metadata on the minting transaction that explains what it actually represents.