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 ('[]');