PostgreSQL

Object Manipulation Changes

• Allow multiple ALTER actions in a single ALTER TABLE command

  This is particularly useful for ALTER commands that rewrite the table (which include ALTER COLUMN TYPE and ADD COLUMN with a default). By grouping ALTER commands together, the table need be rewritten only once.

• Add ALTER COLUMN TYPE to change column's type

  It is now possible to alter a column's data type without dropping and re-adding the column.

• Allow ALTER ... ADD COLUMN with defaults and NOT NULL constraints; works per SQL spec

  It is now possible for ADD COLUMN to create a column that is not initially filled with NULLs, but with a specified default value.

Contrib Changes

• Improve adddepend

• Improve pgstattuple

Object Manipulation Changes

• Allow copying table schema using LIKE subtable, also SQL:2003 feature INCLUDING DEFAULTS

• Improve automatic type casting for domains

• Add ALTER SEQUENCE to modify minimum, maximum, increment, cache, cycle values

• Add ALTER TABLE ... WITHOUT OIDS

• Have ALTER TABLE ... ADD PRIMARY KEY add not-null constraint

  In prior releases, ALTER TABLE ... ADD PRIMARY would add a unique index, but not a not-null constraint. That is fixed in this release.

• Add ALTER DOMAIN

  This allows manipulation of existing domains.

• Add check constraints for domains

  This greatly increases the usefulness of domains by allowing them to use check constraints.

Query Changes

• Allow UPDATE ... SET col = DEFAULT

  This allows UPDATE to set a column to its declared default value.

Utility Command Changes

• Have COMMENT ON DATABASE on nonlocal database generate a warning, rather than an error

  Database comments are stored in database-local tables so comments on a database have to be stored in each database.

• Make TRUNCATE transaction-safe

  TRUNCATE can now be used inside a transaction. If the transaction aborts, the changes made by the TRUNCATE are automatically rolled back.

psql Changes

• Improve tab completion

Changes

• Add constraint names in a few places in pg_dump

Data Types and Functions

• Add current_database()

• Add domain support

Object Manipulation

• Add /contrib/adddepend to handle pre-7.3 object dependencies

• Automatically drop constraints/functions when object is dropped

• Prevent column dropping if column is used by foreign key

• Autodestroy sequence on DROP of table with SERIAL

• Add ALTER TABLE DROP for non-CHECK CONSTRAINT

• Add RESTRICT/CASCADE to DROP commands

• Add object dependency tracking

Queries

• Fail on INSERTs with column lists that don't supply all column values, e.g., INSERT INTO tab (col1, col2) VALUES ('val1');

• Add DEFAULT key word to INSERT, e.g., INSERT ... (..., DEFAULT, ...)

Utility Commands

• Disallow TRUNCATE on tables that are involved in referential constraints

• Dump UNIQUE and PRIMARY KEY constraints as ALTER TABLE

• Add COMMENT ON CONSTRAINT

• Make foreign key constraints clearer in dump file

• Allow comments on operators, independent of the underlying function

psql

• Allow psql \d to show foreign keys