you've got to find what you love.

postgreSQL 사용법

기본키 자동증가 설정하기 

최근 버전

ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;

1. 시퀀스를 생성한다. 

create sequence sequence_name owned by tablename.columname;

2. 기본키의 기본 값 수정

기본 키의 기본값에 nextval('sequence_name'::regclass) 를 추가한다. 

alter table test alter column id set default nextval('sequence_name');

PostgreSQL 사용법 

다른 테이블 구조 & 데이터 복사하기 

데이터만 복사하기 

데이터 & 인덱스 &  constraint 등의 정보 다 같이 복사하기 

아래 두개의 sql 문을 차례로 실행한다. 복사 속도는 위의 방식이 더 빠르지만 인덱스 정보가 같이 복사됨.

다른 테이블의 일부 필드만 복사하기 

postgresql 테이블에 칼럼 추가 + 커멘트 달기

칼럼 추가 방식 

ALTER TABLE 테이블명 ADD COLUMN 컬럼명 데이터타입 제약조건

참고) postgresql numeric type

예시 

- int 형의 칼럼 추가하고 코멘트 달기

ALTER TABLE 데이블이름 ADD 칼럼이름 integer;

COMMENT ON COLUMN 테이블이름.칼럼이름 IS '코멘트';

- varchar 칼럼 추가하면서 default 값 설정하기  

ALTER TABLE test_tb ADD COLUMN name varchar(50) DEFAULT 'NONAME'

- character varying 사이즈 120인 칼럼 추가하기 

ALTER TABLE test_tb ADD COLUMN name character varying(120)

default 값이 있는 칼럼을 추가할 경우 

postgresql 10 이하 버전에서는 테이블을 다시 생성하기 때문에 

시간도 오래 걸리고 그 동안에 access exclusive lock 에 걸리게 됩니다. 

access exclusive lock 은 lock level 중 제일 높은 레벨로 select 까지 막히게 됩니다. 

(실서비스 하는 데이터베이스에서 매우 곤란한 상황 ; - ; ) 

그럴 경우 한번에 칼럼을 추가하고 default 값을 설정하는 것보다는

아래의 예시와 같이 칼럼을 추가하고 기본 값을 설정하는 것이 좋습니다. 

ALTER TABLE test_tb ADD COLUMN name varchar(50);

update test_tb set name = 'NONAME';

ALTER TABLE items ADD COLUMN updated_at timestamptz;

UPDATE items SET updated_at = now();

참고

https://americanopeople.tistory.com/272

https://americanopeople.tistory.com/292?category=695027

문자열을 구분자로 나누는 경우 

postgresql에서 문자열을 구분자로 자를 경우 split_part 함수를 사용한다. 

사용법은 split_part('원래 문자열', '자를 문자', 위치) 로 사용한다. 

예를들어 1998-01-04 문자열일 경우 

split_part('1998-01-04', '-', 1)을 리턴받으면 1998이 나오게 된다. 

필드 업데이트 예시 : 

update 테이블이름 set year = split_part(date::TEXT, '-', 1)::SMALLINT, month=split_part(date::TEXT, '-', 2)::SMALLINT

문자열을 합칠 경우 

postgresql 에서 문자열 또는 필드를 붙일 경우는 concat 함수를 사용한다. 

사용법은 간단하다. 

concat()안에 붙이기 원하는 필드 또는 문자열을 구분자 , 을 사용하여 적어주면 된다. 

이렇게 생성된 새로운 필드의 이름은 뒤에 적어주면 된다. ("" 은 옵션) 

간단하게 예시를 보자. 

SELECT employee_id,first_name,last_name,

concat(first_name,'-',last_name) "Name of the Employee" 

FROM employees

WHERE department_id=100;

postgresql 테이블을 사용하다 날짜 정보 필드가  문자열 타입으로 되어있어서 

date 타입 필드를 생성하고 to_date 함수를 사용하여 업데이트 하고 과정을 기록해 둡니다. 

우선 데이트 타입 필드를 생성합니다. 

to_date 함수는 text문자열을 받아서 지정해둔 format에 따라 date 타입을 생성해주는 함수입니다. 

to_date(text,format);

SELECT to_date('20170103','YYYYMMDD');

to_date('20170103','YYYYMMDD');

---

업데이트 예시

update 테이블이름 set 필드이름 = to_date( concat(year,'-', month, '-01'), 'YYYY-MM-DD')

postgresql 테이블을 csv로 저장하기 (with pandas)

flask에서 postgreSQL 테이블 내용을 csv로 저장하는 함수 작성을 하고 기록해둔다.

처음에는 psycopg2에서 제공하는 함수를 사용해볼까 했는데 

함수 자체의 설명도 부족하고 어차피 기계 실험해야 하면 pandas 사용해야 해서 

pandas 의 read_sql, to_csv의 함수를 사용했는데 간편하고 좋은 것 같다. 

참조: https://www.mydatahack.com/how-to-bulk-load-data-into-postgresql-with-python/

.pgpass 파일 설정 방법

pgpass 파일은 postgreSQL 계정의 암호를 기록해두는 설정파일로 

해당 파일을 생성해두면 psql 이나 pg_dump 명령어를 사용할때 암호를 입력하지 않고 사용할 수 있다. 

그리고 따로 암호를 입력할 수 없는 스크립트에서도 pg_dump 등의 명령어를 사용할 수 있게 해준다. 

우선 홈디렉토리의 계정에 .pgpass 파일을 생성한다. 

포맷은 아래와 같다. 

hostname:port:database:username:password

처음 네 개의 필드는 값을 입력하거나 * (와일드카드)를 사용한다. 암호 필드는 와일드 카드를 사용할 수 없다. 

앞의 값이 다 * 라면 현재 커넥션의 파라미터들(사용자 이름, 디비 등)이 사용된다. 

아래는 pgpass 파일 설정 예시이다. 

마지막으로 .pgpass 파일의 권한을 600으로 설정해둔다. 

해당 설정보다 약한 설정이 설정되어 있으면 postgres는 이 파일을 무시할 것이다. 

관련 링크 : https://www.postgresql.org/docs/current/libpq-pgpass.html

리눅스에 postgreSQL 설치한 과정을 정리해두겠다. 

apt-get install로 아래와 같이 패키지 설치를 한다. 

sudo apt-get install postgresql postgresql-contrib

이제 설치가 완료되었으니 psql로 command line 을 실행해보자. 

root로 psql을 실행하면 아래와 같이 root라는 role이 없다고 에러가 나온다.

https://www.postgresql.org/docs/10/static/sql-copy.html

postgresql 테이블의 데이터를 csv로 저장하려면 copy to 문을 사용하면 된다. 

아래 copy 문법과 예시 참조할 것. 

문법 ) 

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | STDIN }
    [ [ WITH ]
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'delimiter' ]
          [ NULL [ AS ] 'null string' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'quote' ]
                [ ESCAPE [ AS ] 'escape' ]
                [ FORCE NOT NULL column_name [, ...] ] ] ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | STDOUT }
    [ [ WITH ]
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'delimiter' ]
          [ NULL [ AS ] 'null string' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'quote' ]
                [ ESCAPE [ AS ] 'escape' ]
                [ FORCE QUOTE { column_name [, ...] | * } ] ] ]

delimiter는 구분자를 무엇으로 할 지 지정하는 옵션이다. 

force quote는 컬럼에서 null이 아닌 컬럼 내용을 둘러싸는 기능을 이야기 한다. 

컬럼 이름을 설정해서 그 컬럼만 할 수 있고 *를 선택하면 전체 컬럼에 반영이 된다. 

copy to 에서 csv format을 선택할때만 사용할 수 있는 옵션이다. 

If * is specified, non-NULL values will be quoted in all columns. This option is allowed only in COPY TO, and only when using CSV format.

예시 1 ) , 를 구분자로 해서 파일로 저장하는 케이스 

예시 2) 빈 문자열을 구분자로 하고 "로 칼럼을 감싸서 저장하는 케이스 

예시 3) 중간에 다른 문자를 포함해서 저장하는 케이스 

postgreSQL 사용법 

csv 파일에서 테이블로 import 하기 

import하기 전에 csv와 같은 format으로 테이블을 생성한다. 

csv 파일에서 import하는 형식은 아래와 같다. 

csv header가 있을 경우 csv hearder를 적어준다. 

delimeter가 탭일경우 '    '으로 적어준다.  

처음에 import를 수행했을 떄 아래와 같은 에러들이 나서 하나하나 수정해서 다시 실행하였다. 

mysql이였으면 warning 메세지로 넘어갔을 것 같은데 

확실히 postgreSQL이 좀 더 문법에 엄격한 것 같다. 

그나마 에러 메세지가 명쾌한 편이라서 그냥 보고 수정만 하면 되는 것은 장점인것 같다.