But if the NOT VALID option is used, this potentially-lengthy scan is skipped. Normally, this form will cause a scan of the table to verify that all existing rows in the table satisfy the new constraint. This form adds a new constraint to a table using the same constraint syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. Note that SET STORAGE doesn't itself change anything in the table, it just sets the strategy to be pursued during future table updates. Use of EXTERNAL will make substring operations on very large text and bytea values run faster, at the penalty of increased storage space. EXTENDED is the default for most data types that support non- PLAIN storage. EXTERNAL is for external, uncompressed data, and EXTENDED is for external, compressed data. PLAIN must be used for fixed-length values such as integer and is inline, uncompressed. This controls whether this column is held inline or in a secondary TOAST table, and whether the data should be compressed or not. This form sets the storage mode for a column. For more information on the use of statistics by the PostgreSQL query planner, refer to Section 14.2.Ĭhanging per-attribute options acquires a SHARE UPDATE EXCLUSIVE lock. Specify a value of 0 to revert to estimating the number of distinct values normally. This can be useful when the size of the table changes over time, since the multiplication by the number of rows in the table is not performed until query planning time. For example, a value of -1 implies that all values in the column are distinct, while a value of -0.5 implies that each value appears twice on the average. When set to a negative value, which must be greater than or equal to -1, ANALYZE will assume that the number of distinct nonnull values in the column is linear in the size of the table the exact count is to be computed by multiplying the estimated table size by the absolute value of the given number. When set to a positive value, ANALYZE will assume that the column contains exactly the specified number of distinct nonnull values. n_distinct affects the statistics for the table itself, while n_distinct_inherited affects the statistics gathered for the table plus its inheritance children. Currently, the only defined per-attribute options are n_distinct and n_distinct_inherited, which override the number-of-distinct-values estimates made by subsequent ANALYZE operations. This form sets or resets per-attribute options. SET STATISTICS acquires a SHARE UPDATE EXCLUSIVE lock. For more information on the use of statistics by the PostgreSQL query planner, refer to Section 14.2. The target can be set in the range 0 to 10000 alternatively, set it to -1 to revert to using the system default statistics target ( default_statistics_target). This form sets the per-column statistics-gathering target for subsequent ANALYZE operations. You can only use SET NOT NULL when the column contains no null values. These forms change whether a column is marked to allow null values or to reject null values. Default values only apply in subsequent INSERT or UPDATE commands they do not cause rows already in the table to change. These forms set or remove the default value for a column. A USING clause must be provided if there is no implicit or assignment cast from old to new type. The optional USING clause specifies how to compute the new column value from the old if omitted, the default conversion is the same as an assignment cast from old data type to new. The optional COLLATE clause specifies a collation for the new column if omitted, the collation is the default for the new column type. Indexes and simple table constraints involving the column will be automatically converted to use the new column type by reparsing the originally supplied expression. This form changes the type of a column of a table. If IF EXISTS is specified and the column does not exist, no error is thrown. You will need to say CASCADE if anything outside the table depends on the column, for example, foreign key references or views. Indexes and table constraints involving the column will be automatically dropped as well. If IF NOT EXISTS is specified and a column already exists with this name, no error is thrown. This form adds a new column to the table, using the same syntax as CREATE TABLE. RENAME CONSTRAINT constraint_name TO new_constraint_nameĪLTER TABLE ALL IN TABLESPACE name ]ĪDD column_name data_type ]ĭROP column_name ĪLTER column_name TYPE data_type ĪLTER column_name SET DEFAULT expressionĪLTER column_name DROP DEFAULTĪLTER column_name USING INDEX index_name
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |