f to be equivalent, allowing tricks such as writing a function and then using it as though it were a computed-on-demand column. When x is a table name or composite column, PostgreSQL has traditionally considered the syntactic forms f( x) and x. Pg_dumpall -clean now restores the original locale and encoding settings of the postgres and template1 databases, as well as those of user-created databases.Ĭonsider syntactic form when disambiguating function versus column references (Tom Lane) Previously, CREATE DATABASE would be emitted without these specifications if the database locale and encoding matched the old cluster's defaults. Pg_dumpall's output script will now always create databases with their original locale and encoding, and hence will fail if the locale or encoding name is unknown to the destination system. Pg_dump and pg_restore, without -create, no longer dump/restore database-level comments and security labels those are now treated as properties of the database. pg_dumpall's complete output (without -g) is unchanged. pg_dumpall -g now only dumps role- and tablespace-related attributes. Now pg_dump -create and pg_restore -create will restore these database properties in addition to the objects within the database. Previously, attributes of the database itself, such as database-level GRANT/ REVOKE permissions and ALTER DATABASE SET variable settings, were only dumped by pg_dumpall. Postgres.Make pg_dump dump the properties of a database, not just its contents (Haribabu Kommi) This adds an additional level of quality control to I am happy to see the addition of UNIQUE NULLS NOT DISTINCT coming This fits with my mental model of how I think unique constraints should work Of course, a changing the value in val1 to a new value will allowĪnother NULL to be added in val2. SQL Error : ERROR: duplicate key value violates unique constraint "uq_val1_val2_new"ĭetail: Key (val1, val2)=(Hello, null) already exists. Now results in a violation of the unique constraint. INSERT INTO null_new_style (val1, val2)Īttempting to add a second row with 'Hello' in val1 and NULL in val2 Is more restrictive by not allowing repeated NULL values.Īdd one row to start. With the new NULLS NOT DISTINCT option, the unique constraint That said, I never really liked the above behavior because This behavior is documented and expected, and is even part of theĪNSI SQL Standard. This is illustrated by adding 5 identical rows to the null_old_style table. To one another, they do not violate UNIQUE constraints. Because NULL values are of unknown equality It is impossible to determine if one unknown isĮqual to another unknown. This is consistent with the SQL Standard handling of NULL in general, When an index is declared unique, multiple table rows with equal indexed values are not allowed. In Postgres 14 and prior, unique constraints treated NULL values as The only difference from the previous table is the addition of the The null_new_style table uses the new option: UNIQUE NULLS NOT DISTINCT. Id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
The null_old_style table has a 2-column UNIQUE constraint To take a look at what this change does, we create two tables. Previously NULL values were always indexed as distinct values, but this can now be changed by creating constraints and indexes using UNIQUE NULLS NOT DISTINCT." Two styles of UNIQUE "Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut) Improving the database developer's control over data quality is always a good benefit.
While the nuances of unique constraints are not as flashyĪs making sorts faster (that's exciting!), To UNIQUE constraints on columns with NULL values. By Ryan Lambert - Published July 11, 2022
0 kommentar(er)
