When working with datasets in the DuckDB CLI, inspecting table structure and data samples is one of the most common tasks. Running SELECT * FROM table_name LIMIT 10; over and over can get tedious. Fortunately, you can easily create a reusable shortcut or function that persists across all your DuckDB terminal sessions.

Method 1: DuckDB Table Macros with ~/.duckdbrc (Recommended)

DuckDB supports SQL macros, which act like functions. Using the built-in query() dynamic execution function, you can define a macro that takes a table name as an argument and returns the first 10 rows.

Step 1: Define the Macro in SQL

Inside DuckDB, you can write a table macro like this:

CREATE MACRO topten(tbl_name) AS TABLE SELECT * FROM query(tbl_name) LIMIT 10;

Once defined, you can run:

SELECT * FROM topten('my_table');

Step 2: Make it Persistent Across Sessions

To ensure this function is always available whenever you launch DuckDB, add the definition to your DuckDB startup configuration file: ~/.duckdbrc.

Run the following command in your Unix terminal to append the macro to your configuration file:

echo "CREATE MACRO topten(tbl_name) AS TABLE SELECT * FROM query(tbl_name) LIMIT 10;" >> ~/.duckdbrc

Now, every time you start DuckDB, the topten() macro will automatically be available in your session.

Method 2: Unix Shell Function (Terminal-native Approach)

If you prefer running a command directly from your Unix shell (Bash or Zsh) without entering the DuckDB interactive prompt first, you can define a custom shell function.

Step 1: Add a Shell Function to your Profile

Open your shell configuration file (~/.bashrc or ~/.zshrc) and add the following function:

topten() { 
    if [ -z "$1" ] || [ -z "$2" ]; then
        echo "Usage: topten  "
        return 1
    fi
    duckdb "$1" -c "SELECT * FROM $2 LIMIT 10;"
}

Step 2: Reload Shell Configuration

Apply the changes by running:

source ~/.zshrc  # or source ~/.bashrc

You can now preview any table directly from your terminal using:

topten my_database.duckdb my_table

Summary

Both methods provide efficient, time-saving shortcuts for inspecting your data:

  • Use DuckDB Macros + ~/.duckdbrc if you spend most of your time inside the interactive DuckDB prompt.
  • Use a Unix Shell Function if you prefer querying databases directly from your bash/zsh command line.