ALTER COLUMN

Manage Basic Details

Change column type:

// (a): SQL syntax (postgreSQL)
const savepoint = await client.query(
    `ALTER TABLE table_1
        ALTER COLUMN col_1
            SET DATA TYPE varchar`,
    { desc: 'Alter description' }
);

// (b): SQL syntax (mysql)
const savepoint = await client.query(
    `ALTER TABLE table_1
        MODIFY COLUMN col_1 varchar ...`,
    { desc: 'Alter description' }
);

Note that the MySQL MODIFY clause requires rebuilding the whole column as denoted by the ... above.

// (c): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => schema.column('col_1').type('varchar'),
    { desc: 'Alter description' }
);

Manage Constraints

See also ➞ ALTER TABLE ➞ Manage Constraints

DEFAULT

Add/change column default:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE table_1
        ALTER COLUMN col_1
            SET DEFAULT 'Some text'
        ALTER COLUMN col_2
            SET DEFAULT 20`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => {
        schema.column('col_1').default({ expr: { value: 'Some text' } });
        schema.column('col_2').default({ expr: 20 });
    },
    { desc: 'Alter description' }
);

Drop column default:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE table_1
        ALTER COLUMN col_1
            DROP DEFAULT`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => schema.column('col_1').default(false),
    { desc: 'Alter description' }
);

IDENTITY (postgreSQL):

Add IDENTITY constraint:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE table_1
        ALTER COLUMN col_1
            ADD GENERATED ALWAYS AS IDENTITY,
        ALTER COLUMN col_2
            ADD GENERATED BY DEFAULT AS IDENTITY`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => {
        schema.column('col_1').identity({ always: true });
        schema.column('col_2').identity(true);
    },
    { desc: 'Alter description' }
);

Alter IDENTITY constraint:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE table_1
        ALTER COLUMN col_1
            SET GENERATED ALWAYS,
        ALTER COLUMN col_2
            SET GENERATED BY DEFAULT`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => {
        schema.column('col_1').identity().always(true);
        schema.column('col_2').identity().always(false);
    },
    { desc: 'Alter description' }
);

Drop IDENTITY constraint:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE table_1
        ALTER COLUMN col_1
            DROP IDENTITY`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => schema.column('col_1').identity(false),
    { desc: 'Alter description' }
);

EXPRESSION (postgreSQL):

Alter EXPRESSION constraint:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE table_1
        ALTER COLUMN col_1
            SET EXPRESSION AS ( col_1 || ' ' || col_2 ) STORED`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => {
        schema.column('col_1').expression({
            expr: { join: ['col_1', { value: ' ' }, 'col_2'] },
            stored: true
        });
    },
    { desc: 'Alter description' }
);

Drop EXPRESSION constraint:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE table_1
        ALTER COLUMN col_1
            DROP EXPRESSION`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => schema.column('col_1').expression(false),
    { desc: 'Alter description' }
);

NOT_NULL (postgreSQL):

Alter NOT_NULL constraint:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE table_1
        ALTER COLUMN col_1
            SET NOT NULL`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => schema.column('col_1').notNull(true),
    { desc: 'Alter description' }
);

Drop NOT_NULL constraint:

// (a): SQL syntax
const savepoint = await client.query(
    `ALTER TABLE table_1
        ALTER COLUMN col_1
            DROP NOT NULL`,
    { desc: 'Alter description' }
);

// (b): Function-based syntax
const savepoint = await client.database('public').alterTable(
    'table_1',
    (schema) => schema.column('col_1').notNull(false),
    { desc: 'Alter description' }
);