In this article we look at how to automatically enclose values in single quotes when outputting DuckDB query results as a list. Using this method, each value is formatted as SQL literals. This can be useful when you want to escape the values for SQL or for some other purpose.
We also look at how CSV output is sometimes quoted with double quotes, and how we can adjust the results by doing things like removing the headers and changing the list separator.
Continue readingDuckDB’s SAMPLE clause is a handy feature that allows us to work with a random subset of our data. This is particularly useful when dealing with large datasets where processing the entire dataset might be time-consuming or unnecessary for exploratory data analysis, testing queries, or creating representative samples.
When we use this clause, we can specify the absolute number of rows to return, or a percentage of rows. We also have an option of sampling method to use.
Continue readingIf you find yourself running a lot of ad-hoc queries that start with SELECT * FROM, you might be interested in this interesting feature of DuckDB.
DuckDB allows us to start queries with the FROM clause. When we do this, DuckDB will return all columns without us needing to specify SELECT *.
This article demonstrates how to output your DuckDB query results in LaTeX table format, which can be useful when preparing academic papers, technical documents, or any content that needs to be typeset using LaTeX.
Continue readingDuckDB’s CLI allows you to output query results in different formats, including NDJSON (Newline Delimited JSON).
NDJSON is similar to JSON, except that with NDJSON, each line contains its own self-contained JSON document.
This article shows you how to check your current output mode, and then change it to NDJSON.
Continue readingWhen using DuckDB’s command line interface (CLI), we can use the .mode command to change how query results are formatted. For example, we can output query results as a table, in CSV format, or even JSON. Another option is to output it in “line” mode, which outputs the query results vertically, as opposed to horizontally across the screen.
This article demonstrates how to enable vertical query output in the DuckDB CLI with line mode.
DuckDB’s CLI provides a convenient way to generate INSERT statements from query results. This can be particularly useful for data migration, creating test data, or backing up specific data subsets. Here’s how to do it.
DuckDB’s command line interface (CLI) provides a convenient .mode csv command that allows you to output all query results directly in CSV format. This approach differs from the COPY statement as it affects the output format of all subsequent queries until changed.
The article takes a quick look at CSV mode in the DuckDB CLI.
Continue readingIf you’re getting an error that reads something like “1052 (23000): Column ‘name’ in field list is ambiguous” in MySQL, it looks like you could be referencing a column name in a query without qualifying it with the table name.
This can happen when you perform a join between tables that use the same name for one or more columns.
To fix this issue, be sure to qualify column names with the table names when performing joins across tables.
Continue readingIf you’re getting an error that reads something like “ERROR 1066 (42000): Not unique table/alias: ‘d’” in MySQL, it could be that you’re trying to assign a duplicate alias to a table. Or it could be that you’re doing a self-join without assigning table aliases.
Table names and aliases must be unique when doing queries in MySQL.
The error can also happen if you use HANDLER to open a table, but then try to open it again before closing it.
To fix this issue, be sure to use unique table aliases in your query. And if you’re using HANDLER, either close the table or continue working with it (without trying to open it again).