ALTER COLUMN

DOCS • LANG • ALTER

---

See APIS ➞ client.query(), database.alterTable()

See related ➞ ALTER TABLE ➞ Manage Columns

Manage Basic Details

Change column type:

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

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

Note

The MySQL MODIFY clause requires rebuilding the whole column, as denoted by the ... above.

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

Manage Constraints

See also ➞ ALTER TABLE ➞ Manage Constraints

DEFAULT

Add/modify column default:

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

// (b): Function-based syntax
await client.database('database_1').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
await client.query(
    `ALTER TABLE database_1.table_1
        ALTER COLUMN col_1
            DROP DEFAULT`,
    { desc: 'Alter description' }
);

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

IDENTITY (PostgreSQL):

Add IDENTITY constraint:

// (a): SQL syntax
await client.query(
    `ALTER TABLE database_1.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 table = await client.database('database_1').alterTable(
    'table_1',
    (schema) => {
        schema.column('col_1').identity({ always: true });
        schema.column('col_2').identity(true);
    },
    { desc: 'Alter description' }
);

Note

Where an IDENTITY constraint already exists, the constraint is modified with the diff between the existing schema and the new schema.

Alter IDENTITY constraint:

// (a): SQL syntax
await client.query(
    `ALTER TABLE database_1.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 table = await client.database('database_1').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
await client.query(
    `ALTER TABLE database_1.table_1
        ALTER COLUMN col_1
            DROP IDENTITY`,
    { desc: 'Alter description' }
);

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

EXPRESSION (PostgreSQL):

Alter EXPRESSION constraint:

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

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

Note

Where an EXPRESSION constraint already exists, the constraint is modified with the diff between the existing schema and the new schema.

Drop EXPRESSION constraint:

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

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

NOT_NULL (PostgreSQL):

Alter NOT_NULL constraint:

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

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

Drop NOT_NULL constraint:

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

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