I am querying the Cardano Postgres DB and can't figure out how to remove NFTs (multi-assets) that have been burned. In the SQL query below, I am selecting the mint transactions for an asset fingerprint that I know to be a duplicate (meaning there was a burn associated with it). The SQL output is below.

After doing some manual research I was able to figure out that the 3rd row in the output is a mint transaction that 'burned' the 1st row's mint transaction (Hence the -1 quantity). The 2nd row is the NFT that is active today.

However, I am unsure how to know the 3rd transaction burned the 1st row and not the 2nd row. I would like to have my SQL script automatically remove the 1st row and only show active (not burned) NFTs.

Any help would be greatly appreciated. Thanks! :)

P.S. Here are the same mint transactions on Cardano scan: https://cardanoscan.io/tokenminttransactions?assetId=b92f6473f18d4b78733d022fd89f3cacc1484fab6eddfd3c5d4b949444503030383436

with policy_assets as (
        id as ma_id
        , encode(policy, 'hex') as policy_id
        , name
        , fingerprint
    from multi_asset
    where encode(policy, 'hex') = 'b92f6473f18d4b78733d022fd89f3cacc1484fab6eddfd3c5d4b9494'
    order by id

-- Dupe asset fingerprint
select *
from ma_tx_mint m
inner join policy_assets p
    on m.ident = p.ma_id
where fingerprint in ('asset1x0hrrjs6xtmhnwy8ee60j2jrn4k005awqum4zd')
order by m.id

SQL Output

  • Welcome to Cardano SE! As a new user be sure to take the Tour.
    – gRebel
    Jan 16 at 3:57

All tables (other than the epoch table) are designed to be append only (ignoring rollbacks). That means that you need to modify your SQL query to filter out the items that you do not want to see.

  • Do you know how to modify the SQL to filter out a burned NFT?
    – Kyle M
    Jan 14 at 22:53
  • I don't know how to do it, but you would start with a query that only returns the burned NFTs and then use that as the filter. Jan 14 at 23:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.