postgresql 의 기본키 자동 증가





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] 다른 테이블 복사하기





PostgreSQL 사용법 


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






데이터만 복사하기 




CREATE TABLE newtable AS SELECT * FROM oldtable;





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


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



 


create table newtable (like "oldtable" including all);

insert into newtable ( select * from "oldtable");




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



insert into items_ver(item_id, item_group, name)

select * from items where item_id=2;



insert into items_ver (item_id, name, item_group)

select item_id, name, item_group from items where item_id=2;








  • 1122 2018.02.28 11:35 ADDR 수정/삭제 답글

    초대장 하나 받고싶어요 도와주세요 ramen1225@naver.com

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






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

https://www.postgresql.org/docs/10/datatype-numeric.html









postgresql 에서 문자열 처리 방법 ( concat, split_part)



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



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 타입으로 변환하기




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')





참고: http://www.postgresqltutorial.com/postgresql-to_date/




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




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





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

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

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

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

 



import psycopg2 as pc

import pandas as pd



dataReceive = request.get_json()

df = get_data(dataReceive)


def get_data(req_data):

    try:

      conn = pc.connect("dbname=디비이름 user=계정 password=암호")

      curs = conn.cursor()

      sql = "Select {0}, date, region_cd, {1} From 테이블이름 where {2} > 0 order by date".format(req_data['name'], fieldstr, req_data['name'])

      df = pd.read_sql(sql, conn)

      df.to_csv("/experiment/result.csv", encoding='utf-8', header = True, doublequote = True, sep=',', index=False)

      print("CSV File has been created")

      conn.close()

    except Exception as error:

      logger.warn("ERROR in get_data function")

      logger.warn(error)

    return df




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



postgreSQL - .pgpass 파일 설정법






.pgpass 파일 설정 방법





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

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

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



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


포맷은 아래와 같다. 



hostname:port:database:username:password



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

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


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





#hostname:port:database:username:password

*:*:*:postgres:passwdhere





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

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




chmod 0600 /home/userdirectory/.pgpass




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

리눅스(ubuntu)에 postgreSQL 설치하기




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


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


sudo apt-get install postgresql postgresql-contrib








# sudo apt-get install postgresql postgresql-contrib

Reading package lists... Done

Building dependency tree

Reading state information... Done

The following packages were automatically installed and are no longer required:

  duplicity librsync1 python-lockfile

Use 'apt-get autoremove' to remove them.

The following extra packages will be installed:

  libossp-uuid16 libpq5 postgresql-9.3 postgresql-client-9.3

  postgresql-client-common postgresql-common postgresql-contrib-9.3

Suggested packages:

  uuid oidentd ident-server locales-all postgresql-doc-9.3 libdbd-pg-perl

The following NEW packages will be installed:

  libossp-uuid16 libpq5 postgresql postgresql-9.3 postgresql-client-9.3

  postgresql-client-common postgresql-common postgresql-contrib

  postgresql-contrib-9.3

0 upgraded, 9 newly installed, 0 to remove and 578 not upgraded.

Need to get 4,137 kB of archives.

After this operation, 17.7 MB of additional disk space will be used.

Do you want to continue? [Y/n]

...




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


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




root# psql

psql: FATAL:  role "root" does not exist



아직 root라는 role이 생성이 안되어 있으니
아래와같이 postgresql 의 슈퍼 유저인 postgres 로 실행을 해야한다. 


sudo -u postgres psql

postgres에 들어오면 아직 암호가 설정이 안되어 있으니 
마지막으로 password로 암호를 설정하고 \q로 종료한다. 



postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q






postgres 데이터 파일로 저장하기


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 ) , 를 구분자로 해서 파일로 저장하는 케이스 





COPY (

    SELECT language, title FROM cms_title WHERE language != 'en' AND title != 'Blog'

) TO '/path/to/csv/cms_title_dump.csv' WITH CSV HEADER DELIMITER ';';







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




copy (  select name, name, bldnm from table1 ,table2 where table1.id = table2.id order by namecnt desc)

to '/home/housename.csv' with csv header DELIMITER ' ' FORCE QUOTE *;



output 

name,name,bldnm
"엘지메트로시티2","엘지메트로시티2","엘지메트로시티"
"파크리오","파크리오","파크리오"
"성원","성원","성원아파트"
"잠실엘스","잠실엘스","잠실엘스"



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



copy (  select name, '{', name, bldnm, '}'  from table1 ,table2 where table1.id = table2.id order by namecnt desc)

to '/home/housename.csv' with csv header DELIMITER ' ';




postgresql csv 파일에서 import 하기


postgreSQL 사용법 



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



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


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

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

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






copy table이름(id,필드1,필드2) from '/home/definition.csv' with delimiter ',' csv header;




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


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

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

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





ERROR:  extra data after last expected column

CONTEXT:  COPY tablename, line 2: "001 가로구역별 최고높이 제한지역 정 의  도시의 위생적이고 쾌적한 환경 ..."

In statement:



ERROR:  value too long for type character varying(100)

CONTEXT:  COPY tablename, line 10, column title: "개발행위허가제한지역>> 정 의 도시계획 상 공익을 위해 특히 필요하다고 ..."

In statement: