January 2020

Postgres 12.1
We’ve upgraded the database from postgres 11 to postgres 12.1. This should result in performance improvements across the board.
ERC20 Transfer and Approval tables
You can now query the erc20."ERC20_evt_Transfer" and erc20."ERC20_evt_Approval" tables to get decoded token transfers and approvals. These tables should include all events that could be decoded using the ABI of the ERC20 standard. This means that all transfers from all tokens can be queried through this table. The table is large (240M rows at time of writing), so please let us know your experience of the query performance.
erc20."ERC20_evt_Transfer" schema:
Column
Type
"from"
bytea
"to"
bytea
value
numeric
contract_address
bytea
evt_tx_hash
bytea
evt_index
bigint
erc20."ERC20_evt_Approval" schema:
Column
Type
owner
bytea
spender
bytea
value
numeric
contract_address
bytea
evt_tx_hash
bytea
evt_index
bigint
Here contract_address refers to the contract emmitting the event, i.e. the token address, while evt_tx_hash and evt_index conveniently lets you join with ethereum.logs using a query like
1
SELECT *
2
FROM erc20."ERC20_evt_Approval" apps
3
INNER JOIN ethereum.logs 
4
ON apps.evt_tx_hash = logs.tx_hash AND apps.evt_index = logs.index
5
LIMIT 100;
Copied!
Also note that you can join these tables with erc20.tokens to get human readable token symbols and the number of decimals like
1
SELECT value/10^decimals, tr.*
2
FROM erc20."ERC20_evt_Transfer" tr 
3
LEFT JOIN erc20.tokens t
4
ON t.contract_address = tr.contract_address
5
WHERE symbol = 'MKR'
6
LIMIT 10
Copied!
Note though that
Examples
Top token holders
1
WITH transfers AS (
2
    SELECT
3
    evt_tx_hash AS tx_hash,
4
    tr."from" AS address,
5
    -tr.value AS amount
6
     FROM erc20."ERC20_evt_Transfer" tr
7
     WHERE contract_address = '\x6810e776880c02933d47db1b9fc05908e5386b96' --GNO
8
UNION ALL
9
    SELECT
10
    evt_tx_hash AS tx_hash,
11
    tr."to" AS address,
12
    tr.value AS amount
13
     FROM erc20."ERC20_evt_Transfer" tr 
14
     WHERE contract_address = '\x6810e776880c02933d47db1b9fc05908e5386b96' --GNO
15
)
16
SELECT address, sum(amount/1e18) as balance
17
FROM transfers
18
GROUP BY 1
19
ORDER BY 2 desc
20
LIMIT 10
Copied!
Token Balances
1
WITH transfers AS (
2
    SELECT
3
    evt_tx_hash AS tx_hash,
4
    contract_address AS token_address,
5
    -tr.value AS amount
6
     FROM erc20."ERC20_evt_Transfer" tr
7
     WHERE tr."from" = '\xdeadbeef' -- insert real address here
8
UNION ALL
9
    SELECT
10
    evt_tx_hash AS tx_hash,
11
    contract_address AS token_address,
12
    tr.value AS amount
13
     FROM erc20."ERC20_evt_Transfer" tr 
14
     WHERE tr."to" = '\xdeadbeef' -- insert real address here
15
)
16
SELECT token_address, sum(amount/1e18) as balance
17
FROM transfers
18
GROUP BY 1
19
ORDER BY 2 desc;
Copied!
Fallback decoding
The above tables are generated with a new feature we call “fallback decoding”. Essentially it breaks down to being able to decode logs regardless of the events contract address or contract bytecode. If you know other cases where this decoding can be useful feel free to let us know at [email protected]​
Misc
Data for Gnosis sight, safe and dfusion can now be found in gnosis_sight, gnosis_safe and gnosis_dfusion schemas respectively.
Synthetix token-contracts now have the correct name Synthetix and are found in the synthetix schema
prices.usd_dai have been renamed to prices.usd_sai, and the symbol changed to SAI - we still don’t have DAI prices unfortunately, as our price provider has not listed it.
prices.usd_rep now has the correct token address for REP for entries after the migration. Previsouly all entries had the old address.

March 2020

October 2019

Last modified 9mo ago

Copy link

Contents

Postgres 12.1

ERC20 Transfer and Approval tables

Fallback decoding

Misc