postgres ddl table - ghdrako/doc_snipets GitHub Wiki

• Rename table

ALTER TABLE suppliers RENAME TO product_suppliers;

• Change the data type of the column
  • https://notso.boringsql.com/posts/how-not-to-change-postgresql-column-type/
  • https://brandur.org/postgres-default

ALTER TABLE products  ALTER COLUMN discount_percentage SET DATA TYPE integer;

An alter native way to change the data type of a column in PostgreSQL is by utilizing the USING clause within the ALTER TABLE statement. This clause allows you to specify a conversion expression for more complex type transformations.

ALTER TABLE sales   ALTER COLUMN order_date TYPE DATE  USING order_date::date;

order_date::date is the conversion expression applied to each i example text value in the order_date column to cast it into the desired date format. This method is particularly useful for handling more intricate data type conversions.

• add column

• add column with default

  • https://brandur.org/postgres-default Before Postgres 11 requires a full rewrite of the table and all its indexes. Because there’s now a non-null value involved, the database ensures data integrity by going back and injecting it into every existing row.

In bigger installations where it becomes a problem. Rewriting a table with a large body of existing data will take about as long as you’d expect, and in the meantime, the rewrite will take an ACCESS EXCLUSIVE lock on the table. ACCESS EXCLUSIVE is the coarsest granularity of table lock possible, and it’ll block every other operation until it’s released; even simple SELECT statements have to wait. In any system with a lot of ongoing access to the table, that’s a huge problem.

Because it’s not possible to cheaply add a DEFAULT column, it’s also not possible to add a column set to NOT NULL. By definition non-null columns need to have values for every row, and you can’t add one to a non-empty table without specifying what values the existing data should have, and that takes DEFAULT.

The change adds two new fields to pg_attribute, a system table that tracks information on every column in the database:

• atthasmissing: Set to true when there are missing default values.
• attmissingval: Contains the missing value.

As scans are returning rows, they check these new fields and return missing values where appropriate. New rows inserted into the table pick up the default values as they’re created so that there’s no need to check atthasmissing when returning their contents.

The pg_attribute fields are only used as long as they have to be. If at any point the table is rewritten, Postgres takes the opportunity to insert the default value for every row and unset atthasmissing and attmissingval.

Due to the relative simplicity of attmissingval, this optimization only works for default values and function calls that are non-volatile 1. Using it with a volatile function like random() won’t set atthasmissing and adding the default will have to rewrite the table like it did before. Non-volatile function calls work fine though. For example, adding DEFAULT now() will put the transaction’s current value of now() into atthasmissing and all existing rows will inherit it, but any newly inserted rows will get a current value of now() as you’d expect.

• drop column

ALTER TABLE product_suppliers  DROP COLUMN address;

• Add a unique constraint

ALTER TABLE products  ADD CONSTRAINT unique_product_name UNIQUE (product_name);