Results of 1 - 10 of about 26 for Constraints (0.002 sec.)
- Constraints 14762
- In SQL, constraints can be specified for tables. Constraints enforce certain properties over data that is inserted into a table. Constraints can be specified along with the schema of the table as part of the CREATE TABLE statement . In cert
...ain cases, constraints can also be added to a table using the ALTER TABLE statement , but this is not currently supported for all constraints. Wa...rning Constraints have a strong impact on performance: they slow down loading and updates but speed up certain queries. Please consult the P...erformance Guide for details. Syntax Check Constraint Check constraints allow you to specify an arbitrary Boolean expression. Any columns that do not satisfy this expression violate the constrai... - https://man.mtool.work/duckdb/2.0/sql/constraints.html
- CREATE TABLE Statement 10596
- The CREATE TABLE statement creates a table in the catalog. Examples Create a table with two integer columns ( i and j ): CREATE TABLE t1 (i INTEGER, j INTEGER); Create a table with a primary key: CREATE TABLE t1 (id INTEGER PRIMARY KEY, j V
...te primary key: CREATE TABLE t1 (id INTEGER, j VARCHAR, PRIMARY KEY (id, j)); Create a table with various different types, constraints and default values: CREATE TABLE t1 ( i INTEGER NOT NULL DEFAULT 0, decimalnr DOUBLE CHECK (decimalnr < 10), date DATE UNI...t1 AS FROM t2 LIMIT 0; Note that only the column names and types are copied to t1 , other pieces of information (indexes, constraints, default values, etc.) are not copied. Temporary Tables Temporary tables are session scoped, meaning that only the specifi...OM t2 WITH NO DATA; Or: CREATE TABLE t1 AS FROM t2 LIMIT 0; It is not possible to create tables using CTAS statements with constraints (primary keys, check constraints, etc.). Check Constraints A CHECK constraint is an expression that must be satisfied by t... - https://man.mtool.work/duckdb/2.0/sql/statements/create_table.html
- DuckDB_% Metadata Functions 10431
- DuckDB offers a collection of table functions that provide metadata about the current database. These functions reside in the main schema and their names are prefixed with duckdb_ . The resultset returned by a duckdb_ table function may be
...r of open connections to the DuckDB instance. Column Description Type count The number of open connections. UBIGINT duckdb_constraints The duckdb_constraints() function provides metadata about the constraints available in the DuckDB instance. Column Descrip...ARCHAR referenced_column_names The column names referenced by the constraint. VARCHAR[] The information_schema.referential_constraints and information_schema.table_constraints system views provide a more standardized way to obtain metadata about constraints..., but the duckdb_constraints function also returns metadata about DuckDB internal objects. (In fact, information_schema.referential_constraints and inf... - https://man.mtool.work/duckdb/2.0/sql/meta/duckdb_table_functions.html
- Schema 9637
- Types It is important to use the correct type for encoding columns (e.g., BIGINT , DATE , DATETIME ). While it is always possible to use string types ( VARCHAR , etc.) to encode more specific values, this is not recommended. Strings use mor
...this technique mainly pays off for query-heavy workloads that repeatedly group or join on a low-cardinality string column. Constraints DuckDB allows defining constraints such as UNIQUE , PRIMARY KEY , and FOREIGN KEY . These constraints can be beneficial fo...s not rely on these indexes for join and aggregation operators (see indexing for more details). Bestpractice Do not define constraints unless your goal is to ensure data integrity. Microbenchmark: The Effect of Primary Keys We illustrate the effect of using...will not have an effect on join and aggregation operators. Bestpractice For best bulk load performance, avoid primary key constraints. If they are required, define them after the bulk loading step.... - https://man.mtool.work/duckdb/2.0/guides/performance/schema.html
- ALTER TABLE Statement 9637
- The ALTER TABLE statement changes the schema of an existing table in the catalog. Examples CREATE TABLE integers (i INTEGER, j INTEGER); Add a new column with name k to the table integers , it will be filled with the default value NULL : AL
...COLUMN i TO j; ALTER TABLE integers RENAME i TO j; The RENAME [COLUMN] clause renames a single column within a table. Any constraints that rely on this name (e.g., CHECK constraints) are automatically updated. However, note that any views that rely on this...is includes any indexes created as part of a PRIMARY KEY or UNIQUE constraint. Columns that are part of multi-column check constraints cannot be dropped either. If you attempt to drop a column with an index on it, DuckDB will return the following error mess...t columns can only have their type changed if they do not have any indexes that rely on them and are not part of any CHECK constraints. Handling Structs There are two options to change the sub-schema of a STRUCT -typed column. ALTER TABLE with struct_insert... - https://man.mtool.work/duckdb/2.0/sql/statements/alter_table.html
- Information Schema 8926
- The views in the information_schema are SQL-standard views that describe the catalog entries of the database. These views can be filtered to obtain information about a specific column or table. DuckDB's implementation is based on PostgreSQL
...osition of the referenced column within its unique constraint (count starts at 1 ); otherwise NULL . INTEGER 1 referential_constraints : Referential Constraints Column Description Type Example constraint_catalog Name of the database containing the constrain...is_typed YES if the table is a typed table, NO if not. VARCHAR 'NO' commit_action Not yet implemented. VARCHAR 'NO' table_constraints : Table Constraints Column Description Type Example constraint_catalog Name of the database that contains the constraint (...S if the constraint treats NULL s as distinct or NO if it treats NULL s as not distinct, otherwise NULL for other types of constraints. VARCHAR 'YES' Catalog Functions Several functions are also provided to see details about the catalogs and schemas that ar... - https://man.mtool.work/duckdb/2.0/sql/meta/information_schema.html
- DuckDB Environment 8695
- DuckDB provides a number of functions and PRAGMA options to retrieve information on the running DuckDB instance and its environment. Version The version() function returns the version number of DuckDB. SELECT version() AS version; version v
...e following built-in table functions to obtain metadata about available catalog objects: duckdb_columns() : columns duckdb_constraints() : constraints duckdb_databases() : lists the databases that are accessible from within the current DuckDB process duckdb... - https://man.mtool.work/duckdb/2.0/guides/meta/duckdb_environment.html
- Indexing 8612
- DuckDB has two types of indexes: zonemaps and ART indexes. Zonemaps DuckDB automatically creates zonemaps (also known as min-max indexes) for the columns of all general-purpose data types . Operations like predicate pushdown into scan opera
...T) indexes in two ways. First, such an index is created implicitly for columns with PRIMARY KEY , FOREIGN KEY , and UNIQUE constraints . Second, explicitly running the CREATE INDEX statement creates an ART index on the target column(s). The tradeoffs of hav...an existing database. Bestpractice We recommend following these guidelines: Only use primary keys, foreign keys, or unique constraints, if these are necessary for enforcing constraints on your data. Do not define explicit indexes unless you have highly sele... - https://man.mtool.work/duckdb/2.0/guides/performance/indexing.html
- My Workload Is Slow 7819
- If you find that your workload in DuckDB is slow, we recommend performing the following checks. More detailed instructions are linked for each point. Do you have enough memory? DuckDB works best if you have 1-4 GB memory per thread . Is you
...workloads in cloud environments, it is recommended to use instance-attached storage (NVMe SSDs). Are you using indexes or constraints (primary key, unique, etc.)? If possible, try disabling them , which boosts load and update performance. Are you using the... - https://man.mtool.work/duckdb/2.0/guides/performance/my_workload_is_slow.html
- Friendly SQL 7819
- DuckDB offers several advanced SQL features and syntactic sugar to make SQL queries more concise. We refer to these colloquially as “friendly SQL”. Several of these features were first introduced by DuckDB, while some are inspired by other
...ad of positions. INSERT OR IGNORE INTO ... : insert the rows that do not result in a conflict due to UNIQUE or PRIMARY KEY constraints. INSERT OR REPLACE INTO ... : insert the rows that do not result in a conflict due to UNIQUE or PRIMARY KEY constraints. F... - https://man.mtool.work/duckdb/2.0/sql/dialect/friendly_sql.html
DuckDB 2.0 Documentation / man.mtool.work