PostgreSQL CREATE TABLE DROP TABLE ALTER TABLE GRANT REVOKE

<테이블 생성>

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

<테이블삭제>

DROP TABLE 테이블이름;

<테이블변경>

ALTER TABLE products ADD COLUMN description text;

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

ALTER TABLE products ADD CHECK (name <> '');

ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);

ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

ALTER TABLE products DROP COLUMN description;

ALTER TABLE products DROP COLUMN description CASCADE;

ALTER TABLE products DROP CONSTRAINT some_name;

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

ALTER TABLE products RENAME COLUMN product_no TO product_number;

ALTER TABLE products RENAME TO items;

<권한>

권한 종류 : SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE, ALL

GRANT UPDATE ON accounts TO joe;

GRANT SELECT ON mytable TO PUBLIC;

GRANT SELECT, UPDATE, INSERT ON mytable TO admin;

GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;

GRANT INSERT ON films TO PUBLIC;      -- 테이블 films 에 대해서 INSERT 권한 부여 --

GRANT ALL PRIVILEGES ON kinds TO manuel;

GRANT admins TO joe;                 --  joe에게 admins의 role을 부여.

REVOKE ALL ON accounts FROM PUBLIC;