PostgreSQL instructions

Postgresql 基本:

建立表:

CREATE TABLE student (
    id bigint NOT NULL,
    name character varying(255),
    created_At timestamp without time zone,
    updated_At timestamp without time zone,
    class_id bigint NOT NULL
);

CREATE TABLE class (
    id bigint NOT NULL,
    class_name character varying(255),
    teacher_id bigint NOT NULL
);

CREATE TABLE teacher (
    id bigint NOT NULL,
    name character varying(255)
);

ALTER TABLE student OWNER TO postgres;    #change owner
ALTER TABLE class OWNER TO postgres;
ALTER TABLE teacher OWNER TO postgres;

# 設定 PK
ALTER TABLE ONLY student
    ADD CONSTRAINT student_pk PRIMARY KEY (id);
ALTER TABLE ONLY class 
    ADD CONSTRAINT class_pk PRIMARY KEY (id);
ALTER TABLE ONLY teacher 
    ADD CONSTRAINT teacher_pk PRIMARY KEY (id);

# 設定 FK
ALTER TABLE ONLY student
    ADD CONSTRAINT fk_student_class_id FOREIGN KEY (class_id);
ALTER TABLE ONLY class 
    ADD CONSTRAINT fk_class_teacher_id FOREIGN KEY (teacher_id);

修改資料:

UPDATE table SET column= ' ' WHERE ... ,  
EX: 
UPDATE icem_department SET province='北京市' WHERE id=3

插入資料:

insert into icem_department values(nextval('seq_department'),'台北車站','url',null,'台灣','台北市','address', 121.456, 23.456, 2208)

seq_deparment 是 .sql 檔案裡定義的規則

CREATE SEQUENCE seq_department
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

用來規定如何增加 id，cahce 表示在 postgresql 的暫存有幾個

刪除資料:

• 刪除一筆資料(a row) : DELETE FROM student WHERE id=4;
• 刪除一個 column : alter table icem_sms_conf drop column smsdynamicvarpair3
• 刪除一張表: DROP TABLE student

JOIN

https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

視圖 (view)

在 DBVisual 的操作是直接更改資料庫， .sql 檔案則是在部屬時讓機器去自動跑資料庫的欄位產生

現在我想要把某個 column 從 varchar 改成 text

我可以先產生新的 column 後，把舊的值 copy 過去，再把兩個 column rename 為想要的名字

// create a column
alter table icem_sms_conf
add column smsdynamicvarpair2 text;

// copy old val to new column
update icem_sms_conf
set smsdynamicvarpair2=smsdynamicvarpair;

// rename old
alter table icem_sms_conf
rename smsdynamicvarpair to smsdynamicvarpair3

// rename new to old_name
alter table icem_sms_conf
rename smsdynamicvarpair2 to smsdynamicvarpair

//delete original column
alter table icem_sms_conf
drop column smsdynamicvarpair3