postgres alter table rewrite clone like - ghdrako/doc_snipets GitHub Wiki

• https://dev.to/mongodb/alter-table-add-column-2nlp

Table rewrite - jak zweryfikowac czy alter spowoduje przepisanie tabeli czy nie

Every table in Postgres is mapped to one or more physical files in the Postgres data directory. We can see the file for the table with the pg_relation_filenode function. If the output changes, we know that it has been rewritten.

greg=> select pg_relation_filenode('gregtest');
 pg_relation_filenode
----------------------
              1495135
greg=> alter table gregtest add monkeys bigint;
ALTER TABLE

greg=> select pg_relation_filenode('gregtest');
 pg_relation_filenode
----------------------
              1495135

Access Exclusive lock isneeded, but a rewrite did not happen

greg=> select pg_relation_filenode('gregtest');
 pg_relation_filenode
----------------------
              1495135

greg=> alter table gregtest add foobar4 bigint default random(1,10);
ALTER TABLE

greg=> select pg_relation_filenode('gregtest');
 pg_relation_filenode
----------------------
              1495140

Per the documentation, a non-static default will force a rewrite.

changing from an int to a bigint always requires a rewrite

greg=> select pg_relation_filenode('gregtest');
 pg_relation_filenode
----------------------
              1495140
greg=> alter table gregtest alter column bid type bigint;
ALTER TABLE

greg=> select pg_relation_filenode('gregtest');
 pg_relation_filenode
----------------------
              1495144

There are a few other operations that can cause a full table rewrite, but they are mostly things you probably will not encounter very often:

• VACUUM FULL
• CLUSTER
• REFRESH MATERIALIZED VIEW
• Changing a table from LOGGED to UNLOGGED, or vice-versa.

Fast ALTER TABLE

-- Adding a nullable column with no default is fast
ALTER TABLE large_table ADD COLUMN new_column VARCHAR(255);

-- Dropping a column is fast (doesn't immediately reclaim space)
ALTER TABLE large_table DROP COLUMN unused_column;

-- Renaming a column or table is fast
ALTER TABLE large_table RENAME COLUMN old_name TO new_name;

These operations are fast because:

• Adding nullable columns only updates the system catalog, not the table data
• Dropping columns marks them as invisible without physically removing data
• Renames only change metadata

ALTER

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;
ALTER TABLE users ALTER COLUMN age TYPE TEXT USING age::TEXT; -- change data type with data conversion if needed
ALTER TABLE ohio.cust_address ALTER COLUMN country_ocd TYPE char(3) USING country_ocd::char(3); -- from TEXT type need conversion
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;
ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;
ALTER TABLE old_table_name RENAME TO new_table_name;

ALTER TABLE users
ALTER COLUMN username SET NOT NULL,
ALTER COLUMN username TYPE VARCHAR(100),
ALTER COLUMN username SET DEFAULT 'guest';

Monitoring ALTER Progress

• Using pg_stat_progress_alter_table (PostgreSQL 13+)

Monitoring ALTER Progress Using pg_stat_progress_alter_table (PostgreSQL 13+)

-- Monitor ALTER TABLE progress
SELECT
    pid,
    datname,
    relid::regclass AS table_name,
    command,
    phase,
    blocks_total,
    blocks_done,
    tuples_total,
    tuples_done,
    round(100.0 * blocks_done / nullif(blocks_total, 0), 2) AS "% complete"
FROM pg_stat_progress_alter_table;

Cloning table

• https://www.postgresql.org/docs/current/sql-createtable.html

CREATE TABLE nowa_tabela (LIKE stara_tabela); -- Kopiowanie tylko struktury (bez danych) , nie kopiuje indeksów, kluczy, sekwencji
CREATE TABLE nowa_tabela (LIKE stara_tabela INCLUDING ALL); -- Kopiowanie tylko struktury (bez danych) , kopiuje indeksy, klucze, sekwencje
CREATE TABLE nowa_tabela AS TABLE stara_tabela; -- same struktury
CREATE TABLE nowa_tabela AS SELECT * FROM stara_tabela WHERE id < 1000; -- struktury z danymi

Klonowane z dopasowaniem sekwencji

CREATE TABLE nowa_tabela (LIKE stara_tabela INCLUDING ALL);

INSERT INTO nowa_tabela SELECT * FROM stara_tabela;

-- Dopasowanie sekwencji do ostatniego ID
SELECT setval(pg_get_serial_sequence('nowa_tabela', 'id'),
              COALESCE(MAX(id), 1), true)
FROM nowa_tabela;

Klonowanie struktury z dodatkowymi polami

CREATE TABLE users_history (
  LIKE users,
  modified_at timestamptz NOT NULL,
  change_type text NOT NULL
);

By default, LIKE only copies over column names and types. By specifying the optional setting INCLUDING <x>, you can also copy over specified properties:

-- Copy over everything mentioned below
CREATE TABLE users_history (LIKE users INCLUDING ALL);
 
-- Copy over comments
CREATE TABLE users_history (LIKE users INCLUDING COMMENTS);
 
-- Copy over the compression method of the columns
CREATE TABLE users_history (LIKE users INCLUDING COMPRESSION);
-- Copy over CHECK constraints
CREATE TABLE users_history (LIKE users INCLUDING CONSTRAINTS);
 
-- Copy over column defaults
CREATE TABLE users_history (LIKE users INCLUDING DEFAULTS);
 
-- Copy over generation expressions of copied columns
CREATE TABLE users_history (LIKE users INCLUDING GENERATED);
 
-- Copy over identity specifications of copied columns
CREATE TABLE users_history (LIKE users INCLUDING IDENTITY);
 
-- Copy over indexes, as well as primary key, unique, and exclude constraints
CREATE TABLE users_history (LIKE users INCLUDING INDEXES);
 
-- Copy over extended statistics
CREATE TABLE users_history (LIKE users INCLUDING STATISTICS);
 
-- Copy over storage settings
CREATE TABLE users_history (LIKE users INCLUDING STORAGE);

You can also specify EXCLUDING <x> to exclude certain properties. This is most useful when used in conjunction with INCLUDING ALL

-- Copy over everything, except for indexes and
-- primary key + unique + exclude constraints
CREATE TABLE users_history (LIKE users INCLUDING ALL EXCLUDING INDEXES);

CREATE TABLE t_new ( LIKE t_test INCLUDING CONSTRAINTS INCLUDING INDEXES EXCLUDING DEFAULTS);

CREATE TABLE my_table_bk AS (SELECT *, 1 as my_new_column FROM my_table WHERE false);

CREATE TABLE my_table_bk AS (SELECT *, 1 as my_new_column FROM my_table with no data;

select * into newtable from oldtable;

Copy table struct with remove not null constraints

pg_dump -Ox --schema-only -t myTable myDatabase |
  sed -ne'/^CREATE TABLE/,/);/p' -e's/^NOT NULL//';

pg_dump dbname -s -t table_to_clone > /tmp/temp.sql

psql:

begin work;
\i /tmp/temp.sql
-- verify
commit

pg_dump -s -t old databases | sed 's/old/new/g' | psql

Using INHERIT

CREATE TABLE child_table(
col_name data_type constraint
) 
INHERITS (parent_table);

CREATE TABLE child_table()
INHERITS (parent_table);

CREATE TABLE author_info_copy(
author_age SMALLINT
) 
INHERITS (author_info);