you've got to find what you love.

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) 중간에 다른 문자를 포함해서 저장하는 케이스 

예시 4 ) 탭으로 구분자를 할 경우 

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

칼럼 추가 방식 

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

참고) postgresql numeric type

예시 

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

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

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

- numeric 형의 칼럼 추가하기

ALTER TABLE 테이블이름 ADD COLUMN "ERROR" numeric(5,3);

칼럼 이름을 대문자로 할 경우 ""로 감싸줍니다. 

- 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 테이블 사이즈 확인하기

* 전체 테이블 사이즈 확인하기 

 select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace;

* 테이블별 사이즈 확인하기 

select pg_total_relation_size('테이블이름');

pg_size_pretty

# select pg_total_relation_size('테이블이름');

 pg_total_relation_size

------------------------

             9829687296

(1 row)

MB, GB같은 단위로 보고싶으면 pg_size_pretty를 사용한다. 

# select pg_size_pretty(pg_total_relation_size('테이블이름'));

 pg_size_pretty

----------------

 9374 MB

(1 row)

postgresql 오류 처리 

어느날 서버가 갑자기 다운되어서 재시작을 한 후 

매번 잘 돌아가던 python 스크립트에서 갑자기 오류를 내기 시작했다. 

참고로 스크립트에서 변경된 사항은 전혀 없었다. 

ps afx | grep postgres로 postgresql 서버가 떠 있는것도 확인했다.  

  심지어 crontab에 등록해놓은 기존 스크립트는 문제없이 돌아가고 있었는데 

python3 scriptname.py로 실행할때만 저런 오류 메세지가 나왔다. 

혹시 몰라서 postgresql 도 다시 시작해보고 이런 저런 삽질을 하고 있었는데

문제는 간단하게 풀렸다. 

psycopg2 db 접속 함수에 host=localhost만 추가로 입력해주니 문제없이 작동을 했다 ㅠㅠ (내시간 ㅠㅠ) 

뭐가 원인이였는지는 모르겠지만 앞으로는 host도 명시적으로 명기해야겠다고 생각했다(...)

postgresql 테이블 업데이트 방법 

( how to update postgresql table ) 

다른 테이블 필드 값으로 업데이트 

같은 테이블 필드를 분리해서 업데이트 

특정 값으로 전체 필드 업데이트 

PostgreSQL 사용법 

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

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

다른 테이블 구조만 복사하기

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

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

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

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에서 문자열을 구분자로 자를 경우 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/