liveBook · Manning

Distinct keyword

As explained in DuckDB in Action

The DISTINCT keyword removes duplicate rows from the result produced by a query projection.

Use DISTINCT when loading data from another relation and duplicate rows would make insertion fail. It returns one row for each unique combination of the selected columns. section 3.4.1

Load a unique system list

INSERT INTO systems(id, name) SELECT DISTINCT system_id, system_public_name FROM 'https://oedi-data-lake.s3.amazonaws.com/pvdaq/csv/systems.csv' ORDER BY system_id ASC; Here, uniqueness applies to the pair of projected columns: repeated system_id and system_public_name pairs are inserted only once. section 3.4.1

Listing 3.6 Inserting a distinct set of rows from another table
INSTALL 'httpfs';
LOAD 'httpfs';

INSERT INTO systems(id, name)
SELECT DISTINCT system_id, system_public_name
FROM 'https://oedi-data-lake.s3.amazonaws.com/pvdaq/csv/systems.csv'
ORDER BY system_id ASC;

Inspect the complete DuckDB example for installing the HTTP extension, reading a remote CSV, selecting distinct rows, and ordering the inserted result. section 3.4.1

How DuckDB in Action covers it

How deep it goes
Defines uniqueness across all selected columns and demonstrates it in a remote CSV-to-table INSERT.
Examples
Uses duplicated systems.csv records and inserts DISTINCT system_id and system_public_name pairs into systems.
What it assumes
Assumes familiarity with SELECT projections and INSERT statements.
What you can do after
Apply DISTINCT in SQL to prevent duplicate projected rows from causing insertion failures.
How it connects
Connects SELECT projection, INSERT statements, remote CSV reading, ordering, and duplicate rows.
Cover of DuckDB in Action
liveBookmanning.com
Read DuckDB in Action on liveBook Get the book at manning.com

Also explained in

Compare how 4 books explain distinct keyword →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of DuckDB in Action. The section links point to the parts of the book it draws on.