Results of 1 - 10 of about 157 for Columns (0.005 sec.)
- Relational API 11494
- The Relational API builds a query one step at a time from Python. A relation, a DuckDBPyRelation , is a piece of SQL text: the methods of the Transformation and Functions sections return a new relation whose text extends the one they were c
...alled on. Lazy Evaluation Building a relation runs nothing: DuckDB binds the text once to learn the columns and their types, and holds no data. For example, a relation over a billion rows is built at once: import duckdb duckdb_con...or 'zstd' . Detected from the file extension when omitted. * sep : str, keyword-only, default: None Delimiter between the columns, up to 4 bytes; cannot be combined with delimiter . Detected when omitted. * delimiter : str, keyword-only, default: None...olumn names; for example, My Col becomes my_col . * null_padding : bool, keyword-only, default: None Pad a line that lacks columns with NULL values on the right. * strict_mode : bool, keyword-only, default: None Whether to refuse a file that is not stru... - https://man.mtool.work/duckdb/2.0/clients/python/relational_api.html
- Star Expression 11425
- Syntax The * expression can be used in a SELECT statement to select all columns that are projected in the FROM clause. SELECT * FROM tbl; TABLE.* and STRUCT.* The * expression can be prepended by a table name to select only columns from tha
...tbl JOIN other_tbl USING (id); Similarly, the * expression can also be used to retrieve all keys from a struct as separate columns. This is particularly useful when a prior operation creates a struct of unknown shape, or if a query must handle any poten...ELECT st.* FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS st); x y z 1 2 3 EXCLUDE Clause EXCLUDE allows you to exclude specific columns from the * expression. SELECT * EXCLUDE (col) FROM tbl; New As of DuckDB 2.0, EXCEPT is accepted as a synonym for EXCLUDE...may be mixed in the same query. sql SELECT * EXCEPT (col) FROM tbl; REPLACE Clause REPLACE allows you to replace specific columns by alternative expressions. SELECT * REPLACE (col1 / 1_000 AS col1, col2 / 1_000 AS col2) FROM tbl; RENAME Clause RENAME a... - https://man.mtool.work/duckdb/2.0/sql/expressions/star.html
- UNPIVOT Statement 9786
- The UNPIVOT statement allows multiple columns to be stacked into fewer columns. In the basic case, multiple columns are stacked into two columns: a NAME column (which contains the name of the source column) and a VALUE column (which contain
...source column). DuckDB implements both the SQL Standard UNPIVOT syntax and a simplified UNPIVOT syntax. Both can utilize a COLUMNS expression to automatically detect the columns to unpivot. PIVOT_LONGER may also be used in place of the UNPIVOT keyword....Jun 60 3 cars Jan 100 3 cars Feb 200 3 cars Mar 300 3 cars Apr 400 3 cars May 500 3 cars Jun 600 UNPIVOT Dynamically Using COLUMNS Expression In many cases, the number of columns to unpivot is not easy to predetermine ahead of time. In the case of this...dataset, the query above would have to change each time a new month is added. The COLUMNS expression can be used to select all columns that are not empid or dept . This enables dynamic unpivoting that will work r... - https://man.mtool.work/duckdb/2.0/sql/statements/unpivot.html
- Python Client API 9313
- Connection class duckdb. Appender Bases: object append_row ( self , * args ) → None Buffer one row, one value per column, each converted as an executemany parameter would be append_rows ( self , rows : object ) → None Buffer each row of an
...= False ) → _duckdb.DuckDBPyConnection Append the passed DataFrame to the named table appender ( self , table : str , * , columns : list | None = None , action : str = 'insert' , key : list | None = None ) → _duckdb.Appender Create an appender that buf...ives a relation over what it returned. Statements before the last one run immediately. query_appender ( self , dml : str , columns : dict , * , source : str | None = None ) → _duckdb.Appender Create an appender that buffers rows of the declared columns...an be found at Relational API page . any_value ( expression : str , groups : str = '' , window_spec : str = '' , projected_columns : str = '' ) → DuckDBPyRelation Returns the first non-null value from a given expression. Detailed examples can be found a... - https://man.mtool.work/duckdb/2.0/clients/python/reference/
- PIVOT Statement 9278
- The PIVOT statement allows distinct values within a column to be separated into their own columns. The values within those new columns are calculated using an aggregate function on the subset of rows that match each distinct value. DuckDB i
...mplements both the SQL Standard PIVOT syntax and a simplified PIVOT syntax that automatically detects the columns to create while pivoting. PIVOT_WIDER may also be used in place of the PIVOT keyword. For details on how the PIVOT stateme...but the simplified PIVOT syntax can be summarized using spreadsheet pivot table naming conventions as: PIVOT ⟨dataset⟩ ON ⟨columns⟩ USING ⟨values⟩ GROUP BY ⟨rows⟩ ORDER BY ⟨columns_with_order_directions⟩ LIMIT ⟨number_of_rows⟩; The ON , USING , and GROU...n for each year and calculate the total population in each. The ON clause specifies which column(s) to split into separate columns. It is equivalent to the columns parameter in a spreadsheet pivot table. The USING clause determines how to aggregate the... - https://man.mtool.work/duckdb/2.0/sql/statements/pivot.html
- SELECT Clause 9140
- The SELECT clause specifies the list of columns that will be returned by the query. While it appears first in the clause, logically the expressions here are executed only at the end. The SELECT clause can contain arbitrary expressions that
...transform the output, as well as aggregates and window functions. Examples Select all columns from the table called tbl : SELECT * FROM tbl; Perform arithmetic on the columns in a table, and provide an alias: SELECT...CT city FROM addresses; Return the total number of rows in the addresses table: SELECT count(*) FROM addresses; Select all columns except the city column from the addresses table: SELECT * EXCLUDE (city) FROM addresses; Select all columns from the addre...sses table, but replace city with lower(city) : SELECT * REPLACE (lower(city) AS city) FROM addresses; Select all columns matching the given regular expression from the table: SELECT COLUMNS('number\d+') FROM addresses; Compute a function on al... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/select.html
- CSV Auto Detection 7824
- When using read_csv , the system tries to automatically infer how to read the CSV file using the CSV sniffer . This step is necessary because CSV files are not self-describing and come in many different dialects. The auto-detection works ro
...ughly as follows: Detect the dialect of the CSV file (delimiter, quoting rule, escape). Detect the types of each of the columns. Detect whether or not the file has a header row. By default the system will try to auto-detect all options. However, opti...ment Comment character comment # SkipRows Number of rows skipped skip 1 HasHeader Whether the CSV has a header header true Columns Column types encoded as a LIST of STRUCT s columns ({'name': 'VARCHAR', 'age': 'BIGINT'}) DateFormat Date format dateforma...pt = FROM read_csv('my_file.csv', auto_detect=false, delim=',', quote='"', escape='"', new_line='\n', skip=0, header=true, columns={...}); Detection Steps Dialect Detection Dialect detection works by attempting to parse the samples using the set of cons... - https://man.mtool.work/duckdb/2.0/data/csv/auto_detection.html
- Appender 7709
- Use an appender to insert rows that your Python code produces. It collects rows and writes them together in one SQL statement; this is called flushing. The with block below flushes on successful exit: import duckdb con = duckdb.connect() co
...tly Accessing DataFrames and Arrow Objects . Use con.appender(table) for table operations or con.query_appender(statement, columns) for custom SQL. For the appender in other clients, see the Appender page . Both functions are also available on the duckd...table) resolves the table once, when the appender is created, the way SQL resolves a table name. It appends to the table's columns in declared order and skips generated columns. append_row takes one value per column, and append_rows takes any iterable o...f rows. Values are converted exactly as executemany parameters are. To append to some of the columns, list them in columns . The other columns take their default value, per row: con.execute("CREATE SEQUENCE event_ids") con.... - https://man.mtool.work/duckdb/2.0/clients/python/appender.html
- query and query_table Functions 7570
- The query_table and query functions enable powerful and more dynamic SQL. The query_table function returns the table whose name is specified by its string argument; the query function returns the table obtained by executing the query specif
...ARE select_from_table AS SELECT * FROM query_table($1); EXECUTE select_from_table('my_table'); i 42 When combined with the COLUMNS expression , we can write very generic SQL-only macros. For example, below is a custom version of SUMMARIZE that computes...the min and max of every column in a table: CREATE OR REPLACE MACRO my_summarize(table_name) AS TABLE SELECT unnest([*COLUMNS('alias_.*')]) AS column_name, unnest([*COLUMNS('min_.*')]) AS min_value, unnest([*COLUMNS('max_.*')]) AS max_value FROM (...SELECT any_value(alias(COLUMNS(*))) AS "alias_\0", min(COLUMNS(*))::VARCHAR AS "min_\0", max(COLUMNS(*))::VARCHAR AS "max_\0" FROM query_table(table_name... - https://man.mtool.work/duckdb/2.0/guides/sql_features/query_and_query_table_func...
- INSERT Statement 7570
- The INSERT statement inserts new data into a table. Examples Insert the values 1, 2, 3 into tbl : INSERT INTO tbl VALUES (1), (2), (3); Insert the result of a query into a table: INSERT INTO tbl SELECT * FROM other_tbl; Insert values into t
...he i column, inserting the default value into other columns: INSERT INTO tbl (i) VALUES (1), (2), (3); Explicitly insert the default value into a column: INSERT INTO tbl (i) VALUES (...pe, automatic type conversion will be attempted. INSERT INTO ... [BY POSITION] The order that values are inserted into the columns of the table is determined by the order that the columns were declared in. That is, the values supplied by the VALUES clau...termine the order that values should be inserted into the table. This allows inserting even in cases when the order of the columns in the table differs from the order of the values in the SELECT statement or certain columns are missing. For example: CRE... - https://man.mtool.work/duckdb/2.0/sql/statements/insert.html
DuckDB 2.0 Documentation / man.mtool.work