Table description
Thebitcoin.transactions table represents the transactions on the Bitcoin blockchain. A transaction is a transfer of Bitcoin value that is broadcast to the network and collected into blocks. A transaction typically references previous transaction outputs as new transaction inputs and dedicates all input Bitcoin values to new outputs. Transactions are not encrypted, so it is possible to browse and view every transaction ever collected into a block.
Table Schema
| Column | Type | Description |
|---|---|---|
block_time | TIMESTAMP | Timestamp of the block containing this transaction |
block_date | DATE | Date of the block for daily aggregations |
block_height | BIGINT | Height of the block containing this transaction |
index | INTEGER | Position of this transaction within the block |
block_hash | VARBINARY | Hash of the block containing this transaction |
id | VARBINARY | Unique transaction identifier (txid) |
lock_time | BIGINT | Earliest time or block height at which this transaction can be included |
size | BIGINT | Total size of the serialized transaction in bytes |
virtual_size | BIGINT | Virtual size in vbytes, accounting for SegWit discount |
coinbase | VARBINARY | Coinbase data if this is a coinbase transaction, otherwise empty |
is_coinbase | BOOLEAN | Whether this is a coinbase transaction (block reward) |
version | BIGINT | Transaction version number |
input_count | INTEGER | Number of inputs in this transaction |
output_count | INTEGER | Number of outputs in this transaction |
input_value | DOUBLE | Total BTC value of all inputs |
output_value | DOUBLE | Total BTC value of all outputs |
fee | DOUBLE | Transaction fee in BTC (input_value minus output_value) |
hex | VARBINARY | Full raw transaction in serialized hex format |
input | ARRAY(ROW(VALUE DOUBLE, COINBASE VARBINARY, HEIGHT BIGINT, TX_ID VARBINARY, OUTPUT_NUMBER BIGINT, SCRIPT_PUB_KEY ROW(ADDRESS VARCHAR, ASM VARCHAR, DESC VARCHAR, HEX VARBINARY, TYPE VARCHAR), SCRIPT_SIGNATURE ROW(ASM VARCHAR, HEX VARBINARY), SEQUENCE BIGINT, WITNESS_DATA ARRAY(VARBINARY))) | Array of transaction inputs with full details including spent output info |
output | ARRAY(ROW(INDEX BIGINT, SCRIPT_PUB_KEY ROW(ADDRESS VARCHAR, ASM VARCHAR, DESC VARCHAR, HEX VARBINARY, TYPE VARCHAR), VALUE DOUBLE)) | Array of transaction outputs with script and value details |
Table Sample
Struct definitions
Within several of these columns is a data type of STRUCT which allows for representing nested hierarchical data and has key-value pairs. It’s similar to a dictionary in Python and can be used to group fields together to make them more accessible. Note that you can work with these columns with the syntaxinput[1].witness_data[2] or input[3].script_pub_key.address depending on lengths of arrays within each value. It is an array(row(map)) type, and while it looks like just an array in the returned table - it is more than that!
input
| Field | Data type | Description |
|---|---|---|
| value | double | The number of Satoshis attached to this output |
| height | bigint | The height of the output |
| tx_id | string | The transaction id of the output that is here used as input |
| output_number | bigint | The number (index) of the output in transaction tx_id’s outputs |
| coinbase | string | The data specified in this transaction, if it was a coinbase transaction |
| sequence | bigint | Sequence number |
| witness_data | array<string> | Array of hex encoded witness data |
| script_signature | struct | The script signature |
| script_pub_key | struct | The script public key |
input.script_signature
| Field | Data type | Description |
|---|---|---|
| hex | string | The transaction’s script operations, in hex |
| asm | string | The transaction’s script operations, in symbolic representation |
input.script_pub_key
| Field | Data type | Description |
|---|---|---|
| asm | string | The transaction’s script operations, in symbolic representation |
| desc | string | The transaction’s script operations, in symbolic representation |
| address | string | The transaction’s script operations, in symbolic representation |
| hex | string | The transaction’s script operations, in hex |
| type | string | The address type of the output |
output
| Field | Data type | Description |
|---|---|---|
| index | bigint | 0-indexed number of an output within a transaction used by a later transaction to refer to that specific output |
| value | double | The number of Satoshis attached to this output |
| script_pub_key | struct | The public key |
output.script_pub_key
| Field | Data type | Description |
|---|---|---|
| asm | string | The transaction’s script operations, in symbolic representation |
| hex | string | The transaction’s script operations, in hex |
| address | string | The address the BTC came from |
| type | string | The address type of the output |