Cardano-db-sync query metadata

Hello :slight_smile:,

Could you help me with how to query the metadata of a certain transaction? Given a transaction with metadata, like the following transaction

select * from tx
where tx.hash = ‘\x85ba80233a85f8585b09e1b5e1d8f65d8413a5bb825c6308dc7bb5b3e4cd6081’ ;

Now this table tx has no colomn that describes the attached metadata. I searched other tables but not not get a query that would give the metadata given a certain tx.hash (or id since tx.id works cross tables). Could anyone shed some light on how to query for this data? I found the table tx.metadata with a colomn json but I did not get this to work :frowning:

Tnx in advance :slight_smile:

Hi guys,

I found the solution myself. The problem was that the transaction that I was looking for was not yet included in my database (it was still syncing the last bit). So when I queried I found nothing. What I did to find a transaction that was in the database and had metadata I queried

SELECT
*
FROM
tx_metadata
WHERE
tx_id>0 LIMIT 1;

With such a transaction I was able to make a query that joins the table tx and tx_metadata, eg for others to use

SELECT
*
FROM
tx_metadata
INNER JOIN tx
ON tx.id = tx_metadata.tx_id
WHERE
tx.hash= ‘\xc220e20cc480df9ce7cd871df491d7390c6a004b9252cf20f45fc3c968535b4a’;

cheers!

One note on copying a pqsl query from this form. The forum text structure changes the format of ', this will trow an error.

image

Consider using Preformatted text or code block to preserve your syntax like you would on SO.
e.g.

SELECT * FROM tx_metadata
WHERE tx_id > 0 LIMIT 1;
1 Like

tnx for the tip, did not know that was possible. It doesnt suprise me that it is :slight_smile: