databasetime-seriesmetadataquestdb

Programatically getting information about table DEDUP


I have created a table like this

CREATE TABLE 'btc_trades3' (
  symbol INT,
  side SYMBOL capacity 256 CACHE,
  price DOUBLE,
  amount DOUBLE,
  timestamp TIMESTAMP
) timestamp (timestamp) PARTITION BY DAY WAL DEDUP UPSERT KEYS(symbol, timestamp);

I know I can use the "Copy Schema To Clipboard" link in the web console to get the schema, but I would like to find programmatically if a table has deduplication active and what's the configuration. I tried using the wal_tables() function, but that gives me some metadata that does not include deduplication.

Are there any other functions I could use? Thanks!


Solution

  • The wal_tables function gives only information about WAL processing, like table suspension, transaction info and so on. But it does not provide schema metadata.

    We can use instead these two separate functions:

    select * from tables() where table_name = 'your_table';
    select * from table_columns('your_table');
    

    The first one tells you if dedup is active, the second one lets you see which are the upsert/deduplication columns. Please see the screenshots below with one of my own tables:

    Output from 1st statement, showing a dedup = true column

    Outout from 2nd statement, showing the column names, types, and an upsertKey column which is true for some of the columns