DuckDB commands

Duckdb is an embedded relational database management system (RDBMS)

• Embedded means that duckdb does not require a separate database server process to run.
• Similar to SQLite, its database is bundled with the app rather than running as a separate service like PostgreSQL or MySQL.

Open or create a persistent database:

-- default
duckdb 'path/to/your/database.db';
 
-- if db is existing:
ATTACH 'path/to/your/database.db' AS db;

List all tables in the database:

SHOW TABLES;

View the schema of a specific table:

DESCRIBE table_name;

or

SHOW CREATE TABLE table_name;

Show Tables/Views Across All Schemas

SELECT * FROM information_schema.tables;
 
-- alternatively
 
FROM information_schema.tables;

Preview the data in a table (first 10 rows):

SELECT * FROM table_name LIMIT 10;

Get basic statistics about a table:

SUMMARIZE SELECT * FROM table_name;

List all columns in a table:

PRAGMA table_info(table_name);
 
-- or
 
FROM pragma_table_info(table_name)

Count the number of rows in a table:

SELECT COUNT(*) FROM table_name;

View the first few rows of all tables in the database:

SELECT * FROM (
    SELECT name FROM sqlite_master WHERE type='table'
) AS tables
CROSS JOIN LATERAL (
    SELECT * FROM (SELECT * FROM tables.name LIMIT 5)
);

Exit or close connection

.exit 
 
# or 
 
.quit

Querying Tables from Multiple Databases

1. Attach another database:

ATTACH 'path_to_another_database.db' AS other_db;

2. Select from other_db:

SELECT *
FROM other_db.schema_name.table_name;

Creating a table from an excel file

CREATE TABLE <table_name> AS 
	SELECT * FROM read_xlsx('test_excel.xlsx', ALL_VARCHAR=true);