NAME

DBIO::PostgreSQL::Diff::Column - Diff operations for PostgreSQL columns

VERSION

version 0.900007

DESCRIPTION

Represents a column-level diff operation: ADD COLUMN, DROP COLUMN, or ALTER COLUMN (type change, nullability change, or default change). Only columns on tables that exist in both source and target are compared, and none of a view or a materialized view.

ATTRIBUTES

table_key

The schema.table key identifying which table the column belongs to. It is where the model files the table, and what "summary" prints; it is not SQL.

schema_name

The schema of that table.

table_name

The name of that table. "as_sql" writes the table from schema_name and table_name, each quoted for itself -- the key cannot be taken apart again, a schema or a table may have a dot in its name. "diff" takes both from the tables sections. An operation built by hand without them falls back to the key ("key_names" in DBIO::PostgreSQL::Introspect).

column_name

The column name.

old_info

The source column metadata hashref (present for drop and alter).

new_info

The target column metadata hashref (present for add and alter).

using

For an alter that changes the column's type: the USING expression an extension supplied by its verdict ("diff"), written in place of the cast the diff would write. Unset when no extension answered.

METHODS

diff

my @ops = DBIO::PostgreSQL::Diff::Column->diff(
    $source_cols, $target_cols, $source_tables, $target_tables,
);

Compares column lists for tables that exist in both source and target. Detects added columns, dropped columns, and altered columns (data type, NOT NULL, default value, or identity kind).

A view or a materialized view is passed over: its columns are what its definition says, and DBIO::PostgreSQL::Diff::View compares that.

A change to how an existing column is generated is refused, with refusing to diff: ... naming the table, the column and what changes, and no statement: an ordinary column that is a generated one in the target or the other way round, a stored generated column that is a virtual one in the target or the other way round, and a generated column with another expression. For a generated column the catalog keeps the expression where it keeps a default, so such a change would come out as ALTER COLUMN ... SET DEFAULT, which PostgreSQL does not take for a generated column -- and of the statements that do change one, SET EXPRESSION exists from PostgreSQL 17 on only and no statement turns an ordinary column into a generated one.

A column to add whose name differs only in upper and lower case from one to drop in the same table -- createdAt in the target, createdat in the database -- is refused as well (DBIO::PostgreSQL::Diff::CaseOnly): the two are most likely one column, and DROP COLUMN takes what it holds in every row. The message names the table and both columns, for every table at once, and the way out: ALTER TABLE public.acct RENAME COLUMN createdat TO "createdAt".

A column that changes its type while a view reads it is not altered in place: PostgreSQL does not (cannot alter type of a column used by a view or rule). The views the target has -- the declaration's -- are dropped, with the views that stand on them, ahead of everything that is done to tables, and created again behind the columns in the order they depend on one another, from the definition of the target ("diff" in DBIO::PostgreSQL::Diff::View), all of it in the one transaction apply runs a diff in. It is the views of the database that read the column that count, and only those that read this column ("fetch" in DBIO::PostgreSQL::Introspect::Tables, depends_on_columns) -- also one that reads it in a partition or in a table that inherits it, which holds the column of the table above just the same. What a dropped view loses is what the model does not hold: privileges granted on it, its comment, its options.

Two kinds of view are not dropped for it, and the change is refused:

  • a view that only the database has -- one the target does not -- that reads the column, or stands on a view that does: it is not the declaration's to create again, and dropping it takes what nobody declared. The message names the table, the column, both types and the views, and the way out: the DROP VIEW statements for them, in order; the diff after that alters the column and creates again the views the target has.

  • a materialized view that reads the column, or stands on a view that does (and would be dropped with it): dropping it builds its data anew and takes the indexes on it with it. The message names the table, the column, both types and the materialized views (through the view it stands on, or in the partition it reads the column in), and the way out: the DROP MATERIALIZED VIEW statements for them and for the views that stand on them, in order; the diff after that alters the column, creates again the views the target has and creates the materialized view as the target has it.

A change that is none of type -- NOT NULL, the default -- is made under a view, and so is the type of a column no view reads, of one a view reads only as part of the whole row, and of one whose views the model has no columns for (a model built by hand that gives depends_on alone).

A column that changes from one composite type to another is refused when the two have other attributes (names, types or their order, from the types sections of the model): the text of a composite value is read by position, so the conversion would fail on some rows or read the same text as other fields. The message gives the attributes of both and the way out, a column of the new type beside the old one. Two composite types of the same attributes are converted through their text. A model that gives no types is not asked, and the server judges.

type_changed_readers

my $views = DBIO::PostgreSQL::Diff::Column->type_changed_readers(
    $source_cols, $target_cols, $source_tables, $target_tables);

The views and materialized views of the database that read a column whose type changes in the target, as a hashref of their keys. Only those: a view that reads another column of the table, or the table as a whole row, is not among them. "rebuilt" in DBIO::PostgreSQL::Diff::View drops and creates again the views among these that the target has, and so the diff asks one place what the type change is read by; a materialized view among them is refused by "diff".

lossy

True for an alter that changes the column's type in a way that may alter or lose a stored value; "apply" in DBIO::Deploy::Base then runs only with allow_destructive ("destructive_operations" in DBIO::Diff::Base). The answer is made once, when the operation is built, with the statement's USING.

A change is lossless between smallint, integer and bigint in the widening direction, from varchar of any limit to text, to a larger limit or to none (varchar(n) to varchar(m), m >= n, and text to varchar without a limit; the same for the precision of timestamp, timestamptz, time and timetz), and for numeric(p,s) to numeric(p',s') when the scale does not shrink and the digits before the point do not (s' >= s and p'-s' >= p-s), or to numeric without limit. Everything else is lossy: a narrower type or limit, a change between other types, any change to or from an enum or composite type, and every change that carries a USING. A change of nullability, default or identity is not lossy. An enum that gains values is a change of DBIO::PostgreSQL::Diff::Type, not of a column, and not lossy.

register_using_hook

DBIO::PostgreSQL::Diff::Column->register_using_hook(
    my_hook => sub {
        my ($ctx) = @_;
        return $ctx->{new_type} eq 'integer'
          ? "$ctx->{column}::integer" : undef;
    },
);

Registers a hook, class-wide, that may supply the USING expression of the ALTER COLUMN ... TYPE of a column whose type changes -- how a layer or extension converts a value the server cannot cast on its own. The hook is called with one hashref: table_key, column_name, column (the quoted identifier), old_info, new_info, old_type and new_type (the rendered type strings). It returns the expression or undef.

Hooks are asked in registration order, and only when the rendered type changes and the target is no enum or composite type (that path writes its own USING). The first defined, non-empty answer is written as USING EXPRESSION. A hook that dies stops the diff, and an answer that is not a plain string is refused. Registering a name again replaces that hook and puts it last.

unregister_using_hook

DBIO::PostgreSQL::Diff::Column->unregister_using_hook('my_hook');

Removes the hook of that name; a name nobody registered is ignored.

as_sql

Returns one or more ALTER TABLE statements for this operation. For alter, may return multiple statements (one per changed attribute).

Table and column are written as identifiers by the server's rule ("quote_ident" in DBIO::PostgreSQL::Util). The column type and the default come from the catalog (format_type, pg_get_expr) and are written as given. A model that keeps a size beside the type has it written as the type's modifier, where the server takes it ("type_with_modifier" in DBIO::PostgreSQL::Util): timestamp with time zone and a size of 3 are timestamp(3) with time zone.

ADD COLUMN writes a generated column as one, GENERATED ALWAYS AS (expr) STORED or ... VIRTUAL, as "as_sql" in DBIO::PostgreSQL::Diff::Table does in CREATE TABLE.

A default that calls a sequence (nextval('t_id_seq'::regclass), what serial comes to in the catalog) is written as it is, in ADD COLUMN and in SET DEFAULT. The sequence is not made here: DBIO::PostgreSQL::Diff::Sequence creates it ahead of the column and DBIO::PostgreSQL::Diff::SequenceOwner gives it to the column behind it.

An identity and a default do not stand on one column, so an alter takes the one away before it puts the other: DROP IDENTITY comes ahead of SET DEFAULT and DROP DEFAULT (a column made serial from an identity column), and ADD GENERATED ... AS IDENTITY or SET GENERATED behind them (a column made an identity column from serial).

An alter whose old_info and new_info differ in nothing this method writes -- type, NOT NULL, default, identity -- has no statement, and neither has an action this class does not know: as_sql dies ("throw" in DBIO::PostgreSQL::Diff::NoStatement) and returns no empty string in its place. The same holds for an add whose new_info has no data_type: ADD COLUMN without a type is no SQL, so it dies instead of writing it.

A type that changes to an enum or a composite type is written with USING column::text::type: PostgreSQL has no cast from the old type to it, and the value goes through its text. A value the new type does not take -- text that is no label of the enum -- is refused by the server, the upgrade is rolled back and the column stays as it was. A default the column has is dropped ahead of that statement, because the server casts it to the new type as well and cannot, and set again behind it as the target has it. A change to text or to another type the server converts on its own is written without USING.

A type change an extension answered with a USING ("using") is written with it, in place of the cast above (the default of an enum or composite target is dropped and set again around it as before). Any other type change asks the hooks of "register_using_hook"; the first expression one gives is written as USING expression. With none registered, or none answering, the statement has no USING.

summary

Returns a one-line description such as +column: auth.users.avatar (text).

A column that changes its type while something else reads it

A policy (USING, WITH CHECK), a trigger (WHEN, UPDATE OF col), a generated column (GENERATED ALWAYS AS (expr) STORED) and a view or materialized view outside the schemas of the model (pg_schemas) each stop PostgreSQL from altering the type of a column they read: cannot alter type of a column used in a policy definition, ... in a trigger definition, ... used by a generated column, ... used by a view or rule. The model carries these as depends_on_columns of the policy, the trigger and the generated column and as the outside_readers section (DBIO::PostgreSQL::Introspect::Policies, DBIO::PostgreSQL::Introspect::Triggers, DBIO::PostgreSQL::Introspect::Columns, DBIO::PostgreSQL::Introspect::OutsideReaders), all read from pg_depend.

Such a type change is refused when the operations are built, also where the target keeps the object, and also where it drops it in the same diff (the drops come behind the changes). The message names the table, the column, both types and each object, and gives the statements that take them out of the way; the diff after that alters the column and creates again the policies, triggers and generated columns the target has. A view outside the declared schemas is not the target's, so it is not created again. The diff does not drop and create these itself as it does the views of the declaration: a generated column's values and a view's privileges and comment are not in the model. A reader on a partition or on a table that inherits the column counts as well. What a trigger's function or any function body reads is not known. Only the views that read the column directly are named for the outside case; one that stands on them is named by the server when they are dropped.

Partitions and tables that inherit

A partition has its columns from the partitioned table, and a table that inherits has some from the tables it inherits from ("fetch" in DBIO::PostgreSQL::Introspect::Tables, partition_of and inherits, as the model of the database has them). PostgreSQL changes such a column where it comes from and carries the change down; at the table that only has it, it refuses the statement (cannot add column to a partition, cannot drop inherited column, cannot alter inherited column). So a table comes behind the tables it has columns from, and is compared not as the database has it but as the statements for those leave it:

  • a column added above is in it, as it is above;

  • a column dropped above is gone from it -- unless the table defines the column itself as well (is_local, "fetch" in DBIO::PostgreSQL::Introspect::Columns), in which case it stays, as the table's own, and gets a DROP COLUMN of its own if the target does not have it;

  • a type altered above is altered in it;

  • a default set or dropped above is set or dropped in it, also where it had one of its own -- which is then set again, behind;

  • NOT NULL set above is set in it.

What is left to do is the table's own, and is written for it: a default of its own, a NOT NULL of its own, a column of its own in a table that inherits. Two things are not carried down for certain and are asked of the table itself. DROP NOT NULL above leaves a NOT NULL the table has of its own, and the model cannot tell that from an inherited one: where the target has the column nullable, the statement is written for the table too, behind the one above, and does nothing where that one did it already. An identity is a partition's as it is the partitioned table's and is not inherited by a table that inherits; the table is taken to have got it where the target has it the same as above, so neither is assumed. All of this is as measured on PostgreSQL 18.6.

A statement that could only be for the table above and is asked for the table below alone -- a column to add to a partition, a column to drop from, or a type to alter in, a table that has it from another which keeps it as it is -- is refused. No database has a table differ from its parent that way; a target model built by hand can.

The extensions the schema declares (ADR 0066) are asked about each change of a column's type, through the protocol the diff was built with (the argument extensions of DBIO::PostgreSQL::Diff, a DBIO::Extension::Protocol; without one nothing is asked). The change is a hashref

{ kind        => 'column_type',
  table       => $table_key,       schema_name => $schema, table_name => $table,
  column_name => $column,
  old_info    => \%column_info,    # the model's column, with the annotations
  new_info    => \%column_info,
  old_type    => $rendered_type,   new_type => $rendered_type }

The answer { using => $expr } is written as the USING of the statement in place of the cast the diff would write ("using"); { refuse => [...] } refuses the diff -- every refusing column and every message, refusing to diff: the extensions of the schema refuse ..., no statement. The class-wide hooks of "register_using_hook" are driver-internal and are not asked for an extension; a verdict's using goes before them.

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.