how to convert an "asset32type" to string?

Question

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"?

Answer 1

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.

Answer 2

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.