Back to Code Snippets
Gábor Szárnyas
@gabor-1
Updating CSV files in-place
DuckDB allows updating CSV files in-place.
Execute this SQL
COPY (FROM VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4) t(s, x)) TO 'example.csv'; COPY (SELECT s FROM 'example.csv') TO 'example.csv';
Copy code
Gábor Szárnyas
Expand
Share link
Specifying types in the CSV loader
We can specify types for the `read_csv` function on a per-column basis.
Execute this SQL
COPY (FROM VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4) t(s, x)) TO 'example.csv'; CREATE OR REPLACE TABLE example AS FROM read_csv('example.csv', types = {'x': 'DECIMAL(15, 3)'});
Copy code
Gábor Szárnyas
Expand
Share link
Deterministically shuffling data
Sometimes it's beneficial to shuffle data sets in a deterministic, repeatable way. We can achieve this by using the `hash` function on the `rowid` pseudocolumn.
Execute this SQL
COPY (FROM VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4) t(s, x)) TO 'example.csv'; CREATE OR REPLACE TABLE example AS FROM 'example.csv'; FROM example ORDER BY hash(rowid + 42);
Copy code
Gábor Szárnyas
Expand
Share link
Copying the schema of a table
We can use `LIMIT` to copy the schema of an existing table without repeating the specification of its column names/types.
Execute this SQL
COPY (FROM VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4) t(s, x)) TO 'example.csv'; CREATE TABLE tbl AS FROM example LIMIT 0;
Copy code
Gábor Szárnyas
Expand
Share link
Pretty-printing floating-pont numbers
By casting the result to a `DECIMAL` with a fixed number of digits after the decimal point, we can pretty-print it while keeping it as a numeric value.
Execute this SQL
COPY (FROM VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4) t(s, x)) TO 'example.csv'; SELECT x::DECIMAL(15, 3) AS x FROM 'example.csv';
Copy code
Gábor Szárnyas
Expand
Share link