PostgreSQL 테이블 생성 및 변경

# DATA TYPE

 

이름	설명
character varying(n), varchar(n)	길이 제한 있는 가변 길이 문자열
character(n), char(n)	공백 채움 고정 길이 문자열
text	길이 제한 없는 가변 길이 문자열

 

# CREATE TABLE

1. 자동 증가 컬럼 생성

MySQL의 AUTO_INCREMENT 와 같이 자동증가 값을 사용하기 위해선 컬럼을 serial 또는 bigserial 로 생성하면 됨.

CREATE TABLE TEST1 (SEQ SERIAL, NAME VARCHAR(20));
CREATE TABLE TEST2 (SEQ BIGSERIAL, NAME VARCHAR(20));

• SERIAL : 4byte integer
• BIGSERIAL : 8byte integer

★Unique constraint / Primary Key가 되길 원한다면 따로 지정해 주어야 함.

 

2. Primary key

 보통 primary key는 AUTO_INCREMENT로 된 key를 사용.

CREATE TABLE TEST1 (SEQ SERIAL primary key, NAME varchar(20));

 

3. Foreign key

CREATE TABLE TEST1 (
        city     varchar(80) primary key,
        location point
);
 
CREATE TABLE TEST2 (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

3. Sequences

테이블에 값을 등록한 다음 바로 Primary key의 값을 알고 싶을 때!

예) calendar 테이블에 값을 INSERT한 다음, 'no'의 값을 알고싶다. 그러나, unique한 값이 pk인 'no'뿐이다. 이럴 때는 Sequence를 등록하여 INSERT할 때, 자동적인 컬럼 증가값이 아닌 자신이 가져온 값(SELECT nextval('serial_calendar_no'))으로 등록하도록 해주면 된다.

• Sequence 추가

CREATE SEQUENCE auto_increment_seq

-- DEFAULT가 nextval이므로 INSERT시 값을 넣어주지 않으면 자동으로 증가한다.

 

• 기존 table에 추가할 때

UPDATE table_name SET no = NEXTVAL('auto_increment_seq')

 

4. Schemas 경로

SET search_path TO __스키마 네임__;
--Create table 쿼리

데이터베이스에 테이블을 만들 때, Public에만 생성된다.

하지만, 스키마를 여러 개 사용할 경우, 경로를 지정해 주어야 한다.

Create table 쿼리를 날리기 전에 Set search_path TO 명령어를 작성 후에 쿼리를 날려주면 된다.

 

# INSERT TABLE

1. 기본 방식

insert into test1 values ('a01', nextval('seq'), 10);
insert into test1 values ('a02', nextval('seq'), 10);
insert into test1 values ('a03', currval('seq'), 10);

2. 자동 증가 컬럼 사용시 방식

만약, table의 형태가 다음과 같다고 가정해보자.

no is serial(auto_increment)

column 
no serial,
id varchar(10),
pw varchar(10)

insert into test1 (id , pw) values('test1','a1234')

-- no를 지정하지 않고 insert하면 됨.

# ALTER TABLE

1. 테이블 변경 Syntax

ALTER TABLE table_name
[ADD|DROP|ALTER|SET|RENAME]
    column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ];

[ADD | DROP | ALTER | SET | RENAME]은 테이블을 수정할 때 변경할 내용을 선택합니다.

• ADD : 컬럼 추가
• DROP : 컬럼 삭제
• ALTER : 존재하는 컬럼의 데이터 타입을 변경
• SET : 테이블을 다은 tablespace나 schema로 이동
• RENAME : 케이블, 컬럼, 제약조건의 이름을 변경

2. ADD COLUMN

test 테이블에 데이터타입이 varchar(50)인 address컬럼을 추가

ALTER TABLE test ADD COLUMN address varchar(50);

 

3. RENAME COLUMN

test 테이블에 address 컬럼의 이름을 address01 로 변경

ALTER TABLE distributors RENAME COLUMN address TO address01;

4. ALTER COLUMN

test 테이블에서 address컬럼의 데이터 타입을 varchar(80)으로 변경하고 name컬럼의 데이터 타입을 varchar(100)으로 변경

ALTER TABLE test
ALTER COLUMN address TYPE varchar(80),
ALTER COLUMN name TYPE varchar(100);

5. ADD CONSTRAINT

• UNIQUE

ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

 

• ForeignKey

ALTER TABLE calendar ADD CONSTRAINT calendar_member_no_fkey FOREIGN KEY(member_no) REFERENCES member(no);

calendar_member_no_fkey : constraint name
member_no : foreign key
member(no) : 참조당해지는 테이블 member와 Primary key인 no

6. REMOVE CONSTRAINT

ALTER TABLE distributors DROP CONSTRAINT dist_id_zipcode_key;

 

7. ADD PRIMARY KEY

ALTER TABLE distributors ADD PRIMARY KEY (dist_id);