Skip to content
Search

PostgreSQL:AlterTable

lastmod 2025-02-15created 2021-12-0110 edits

Creating/removing SERIAL

SERIAL also creates a Sequance.

Renaming a column 

ALTER TABLE recc_user RENAME COLUMN is_admin TO admin;

Setting NOT NULL on a column 

ALTER TABLE public.a4y_service ALTER COLUMN "time" SET NOT NULL;

Setting NOT NULL and a default value on a column 

ALTER TABLE recc_info ALTER COLUMN value SET NOT NULL;
ALTER TABLE recc_info ALTER COLUMN value SET DEFAULT '';

Setting a Timstampz default to today + 1DAY 

alter table public.progress alter column expired_at set default now() + '1 day'::interval;

Renaming a column 

ALTER TABLE public.recc_permission RENAME COLUMN "name" TO slug;

Adding a column 

ALTER TABLE public.recc_permission ADD "name" varchar(128) NULL;
ALTER TABLE public.recc_permission ADD hidden bool NOT NULL DEFAULT false;
ALTER TABLE public.recc_permission ADD "lock" bool NOT NULL DEFAULT false;

Dropping a column 

ALTER TABLE recc_port DROP COLUMN description;

Dropping a constraint 

ALTER TABLE recc_port DROP CONSTRAINT recc_port_pkey;  -- remove `PRIMARY KEY`

Adding a unique constraint on two keys 

-- Unique constraint `number, sock` in `recc_port` table.
ALTER TABLE recc_port
ADD CONSTRAINT recc_port_number_sock_key
UNIQUE (number, sock);

Creating/removing a PRIMARY KEY 

create table recc_port (
    number INTEGER PRIMARY KEY,
    ...
);

Creating a table like the one above is equivalent to the following alter table queries. 

ALTER TABLE recc_port ADD "number" int4 NOT NULL;
ALTER TABLE recc_port ADD CONSTRAINT recc_port_pkey PRIMARY KEY (number);

So if you only want to remove the PRIMARY KEY, just drop the CONSTRAINT. 

ALTER TABLE recc_port DROP CONSTRAINT recc_port_pkey;

That leaves only the following table. 

create table recc_port (
    number INTEGER NOT NULL,
    ...
);

ON DELETE CASCADE

With the ON DELETE CASCADE option, deleting a row from the parent table also deletes the linked rows in the child table. Linked data can be removed at once, which makes data easier to manage and keeps it consistent.

Syntax 

ALTER TABLE 테이블명 ADD CONSTRAINT 포린키이름 FOREIGN KEY 자식속성 REFERENCES 부모테이블명(자식속성이 참고할 부모속성) ON DELETE CASCADE;

Example: 

ALTER TABLE feed ADD CONSTRAINT FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE;

Applying it in CREATE TABLE 

CREATE TABLE IF NOT EXISTS group (
    uid SERIAL PRIMARY KEY,

    slug VARCHAR(128) UNIQUE NOT NULL,
    name VARCHAR(128),
    description TEXT,
    features VARCHAR(128)[],
    visibility INTEGER NOT NULL DEFAULT 0,
    extra JSONB,

    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS project (
    uid SERIAL PRIMARY KEY,

    group_uid INTEGER NOT NULL
        REFERENCES group (uid)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    slug VARCHAR(128) NOT NULL,
    UNIQUE(group_uid, slug),

    name VARCHAR(128),
    description TEXT,
    features VARCHAR(128)[],
    visibility INTEGER NOT NULL DEFAULT 0,
    extra JSONB,

    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ
);

See also

Favorite site

author Zer0 sitemap