Postgresql 설치
sudo apt-get install postgresql
2.사용자 및 데이터베이스 생성, 권한부여
Postgresql
‘sudo -u postgres psql’ 명령어로 콘솔에 접속
psql -U 사용자이름 -d 데이터베이스 -h 서버아이피(예:127.0.0.1)
기본적으로는 사용자이름과 같은 이름의 데이터베이스에 접속한다.
포트는 5432
원격접속 설정
- /etc/postgresql/9.x/main/postgresql.conf 파일에 ‘listen_addresses = ‘*’ ‘ 추가
- /etc/postgresql/9.x/main/pg_hba.conf 파일에 ‘host all all 0.0.0.0/0 md5’ 추가. ‘Host database user address method’ 형태이니, 적당히 수정해서 추가.
- service postgresql restart 로 서비스 재시작
php-pgsql 설치
- sudo apt install php-pgsql
\l 데이터베이스 보기
\dn 스키마 확인
\dt 테이블 보기
\du 사용자 목록 보기
\? 도움말
\d+ 표이름 표에 대한 상세정보 확인
pg_tables 테이블에 테이블 목록 있음
1.사용자 추가/제거
사용자 및 권한 생성: CREATE USER 사용자명 WITH 권한1 권한2 … ; —여기서 권한은 CREATEDB, REPLICATION, CREATEROLE, SUPERUSER 등이 있다.
또는 CREAT ROLE 사용자명 WITH LOGIN 권한~
비밀번호 있는 아이디 만들기 CREATE ROLE 사용자명 WITH LOGIN PASSWORD ‘암호’ 권한1 권한2;
비밀번호 변경:
| ROLE | 기능 |
| SUPERUSER | USER들을 생성하고 권한을 부여해 주는 USER |
| CREATE ROLE | USER가 새로운 ROLE을 정의하는 기능을 생성 |
| CREATE DB | USER가 DB를 생성하는 권한을 부여하는 기능 |
| REPLICATION | USER가 DB를 실시간으로 복사하는 기능 |
사용자 제거: DROP USER ‘사용자명’; 또는 DROP USER ‘사용자명’;
권한 추가: ALTER USER ‘사용자명’ WITH 권한1 권한2;
권한 제거: ??
2.데이터베이스 추가
CREATE DATABASE 데이터베이스 이름OWNER 사용자;
3.테이블 추가 및 변경
CREATE TABLE 테이블 이름
(
컬럼1 data_type NULL/NOT NULL PRIMARY KEY,
컬럼2 data_type NULL/NOT NULL
);
주요 data_type는 다음과 같다.
char(n) : n바이트 문자열
integer, int: 정수, 10자까지 가능
int8, bigint: 8비트 정수, 19자까지 가능
date: 날짜
boolean: 참/거짓
float: 소수점16자리 유리수
real: 소수점7자리 유리수
text: 가변길이 텍스트
테이블 제거
DROP TABLE 테이블이름;
테이블이름 변경
ALTER TABLE 테이블이름 RENAME TO 테이블새이름;
테이블 컬럼 추가
ALTER TABLE 테이블이름 ADD COLUMN컬럼이름 data_type;
테이블 컬럼 제거
ALTER TABLE 테이블이름 DROP COLUMN컬럼이름;
테이블 컬럼 이름변경
ALTER TABLE 테이블이름 RENAME 컬럼이름 TO 새로운컬럼이름;
테이블 컬럼 데이터타입 변경
ALTER TABLE 테이블이름 ALTER COLUMN 컬럼이름 TYPE data_type;
주 키 지정
ALTER TABLE 테이블이름 ADD PRIMARY KEY (컬럼이름);
4.테이블에 열 추가 및 삭제
INSERT INTO 테이블이름(컬럼1, 컬럼2,…) VALUES(값1,값2,…);
INSERT SELECT: SELECT로 가져온 결과를 테이블에 삽입하기 위해 사용.
INSERT INTO 테이블이름(컬럼1, 컬럼2…) SELECT 컬럼a, 컬럼b… FROM 테이블이름1, 테이블이름2… WHERE … ;
DELETE FROM 테이블이름 WHERE …;
기록된 내용 변경하기
UPDATE 테이블이름 SET 컬럼=’변경할데이터’ WHERE…;
5.테이블에서 원하는 내용 가져오기
SELECT 컬럼이름 FROM 테이블이름 WHERE … ;
SELECT SUM(필드명) FROM 테이블명 WHERE …;
json 처리
SELECT * FROM table WHERE (col_name::json->0->’key’)::text=’key_value’;
6.인덱스 설정
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ]이름] ON [ ONLY ]테이블이름[ USING색인방법] ( {칼럼이름| (표현식) } [ COLLATE문자정렬규칙] [연산자클래스] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ INCLUDE (칼럼이름[, ...] ) ] [ WITH (저장_매개변수=값[, ... ] ) ] [ TABLESPACE테이블스페이스이름] [ WHERE조건절
일반적으로 다음과 같이 활용할 수 있다.
CREATE INDEX CONCURRENTLY index_name ON table_name (column_name1, column_name2,...
여기서 CONCURRENTLY 옵션은 인덱스를 만드는 도중에도 테이블을 잠그지 않도록 해준다. postgresql 한정 옵션이다.
생성된 인덱스 보기
SELECT * FROM pg_indexes WHERE tablename = '테이블명'
기타.
외래키 지정
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
Json 데이터 입력과 출력
select col1->0,col1->1 from test where cast(col1->>0 as INTEGER) >= -1;
insert into test(col1) values ('[]');
