[ postgreSQL] 다른 테이블 복사하기





PostgreSQL 사용법 


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








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




CREATE TABLE newtable AS SELECT * FROM oldtable;






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



CREATE TABLE newtable ( LIKE 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 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');






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 - 시퀀스 초기화하기 (nextval 사용 이슈)







postgresql 에서 간혹 테이블 구조를 복사하고 새로 데이터를 입력할 경우 

id를 mysql의 auto increment에 해당되는 postgres의 nextval 함수를 사용해서 자동 증가 시킬경우  

예전 테이블 id의 최대값에 이어서 값이 증가될 때가 있다. 


예전 테이블 구조를  복사하면서 이전 테이블의 시퀀스 값이 id 증가에 사용이 되서 발생하는 일이다. 


예시 ) 

예전 테이블의 시퀀스 아이디 값에서 증가 되고 있을 경우 id nextval 설정




그럴 경우 새로운 테이블의 시퀀스를 아래와 같이 만들어 줘야 함 


디비이름# create sequence table2_id_seq;

CREATE SEQUENCE


아니면 테이블을 비우고 나서 다시 데이터를 입력할 때도 같은 현상이 일어 날 때가 있다. 

이럴 경우 시퀀스의 값을 아래와 같이 1로 재설정 해준다. 



디비이름# select setval('table2_id_seq',1,false); # 시퀀스가 1부터 시작하도록 설정


여기서 false는 설정값 다음부터 시작하는지 여부를 결정한다. true이면 2부터 시퀀스 값이 시작한다. 




참고: http://www.postgresdba.com/bbs/board.php?bo_table=B10&wr_id=28




  • 2018.05.18 14:45 ADDR 수정/삭제 답글

    비밀댓글입니다

    • adana 2018.05.18 15:09 신고 수정/삭제

      네 초대장 보내드렸습니다!

postgreSQL lock 해결법



가끔 postgresql 에서 오래 걸리는 작업을 

강제로 중단할 때 사용하는 명령어들을 기록해둔다. 



 query 실행되는 상황 보기 (psql command line) 





# select pid,query,wait_event_type,wait_event from pg_stat_activity where state = 'active';
  pid  |                                           query                                           | wait_event_type | wait_event
-------+-------------------------------------------------------------------------------------------+-----------------+------------
 13021 | select pid,query,wait_event_type,wait_event from pg_stat_activity where state = 'active'; |                 |
(1 row)




위 명령어로 active하게 되어 있는 postgres job의 pid를 확인하고 

아래 명령어로 lock이 걸려있는 pid를 취소한다.



select pg_cancel_backend(pid 번호);






참고: https://blog.gaerae.com/2015/09/postgresql-pg-stat-activity.html

postgreSQL command line에서 사용하기



postgreSQL command line에서 사용하기



postgreSQL 접속하기 


psql 커멘드를 사용하여 postgreSQL에 접속한다.

-U 로 사용자 이름, -d로 접속 데이터베이스를 지정할 수 있다. 



psql -U username -d dbname -W

Password for user username:
psql (9.6.6)

Type "help" for help.







터미널 접속하기 & 데이터베이스 목록 보기 (\l)



dbname=> \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
------------+----------+----------+-------------+-------------+-----------------------
 db1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
            |          |          |             |             | postgres=CTc/postgres+
            |          |          |             |             | land=CTc/postgres

...





DB 변경하기 (\c)


db1=> \c db2
Password for user username:
You are now connected to database "db2" as user "username".

db2->


user 권한 확인하기(\du)




db1->\du
List of roles 
Role name | Attributes | Member of
------------------------------------------
user1 |                           | {}
user2 | Superuser, Create role, Create DB, ... | {}
 










참조: https://www.tutorialspoint.com/postgresql/postgresql_select_database.htm





  • 2018.04.14 20:53 ADDR 수정/삭제 답글

    비밀댓글입니다

  • 2018.04.15 02:55 ADDR 수정/삭제 답글

    비밀댓글입니다

    • adana 2018.04.15 03:07 신고 수정/삭제

      아니에요 티스토리 블로그 잘 운영하시길 바래요 ^^/

postgreSQL - 필드에서 공백 제거하기


postgreSQL 의 필드에서 공백 제거하기 





보통 trim()을 사용해서 제거하지만 

trim 함수는 모든 공백을 다 제거하는 것이 아니라 

ascii 코드가 32인 공백만 제거한다. 



참고로 공백 부분의 ascii 코드 확인 쿼리는 아래와 같다. 



select ascii(left(name, 1))

from tablename

where id = 723;



검사해보니 9(탭문자)가 나왔다. 


필드에서 탭 등의 모든 공백을 다 제거하기 위해서는 

정규식 표현을 사용해야 한다. 




update tablename set name = REGEXP_REPLACE(name, '^\s+', '');




 정규식 ^\s+에서 ^은 문장의 시작을, \s는 공백을, +은 1번 이상을 의미한다. 



기존의 필드와 새로 업데이트한 필드를 비교해보니

이름 앞의 공백이 제대로 제거된 것을 확인되었다. 




# select name, name_api from 테이블이름 where name != name_api;



                 name                   |                     name_api

------------------------------------------+--------------------------------------------------

 (893-15)                                 |         (893-15)

 (주)HTC 까사빌                           |         (주)HTC 까사빌

 (주)HTC 까사빌                           |         (주)HTC 까사빌

 (주)HTC 까사빌                           |         (주)HTC 까사빌

 (주)HTC 까사빌                           |         (주)HTC 까사빌

 (주)HTC 까사빌                           |         (주)HTC 까사빌

 (주)HTC 까사빌                           |         (주)HTC 까사빌

 (주)HTC 까사빌                           |         (주)HTC 까사빌

 (주)HTC 까사빌                           |         (주)HTC 까사빌

 (주)HTC 까사빌                           |         (주)HTC 까사빌







참고: http://www.postgresqltutorial.com/postgresql-trim-function/


postgresql 의 반올림 함수들




postgreSQL 에서 반올림 함수들 사용 예시 



반올림 관련 함수들 


round : 일반적인 반올림 함수

ceil : 소수점 자리의 숫자를 무조건 올린다. (방의) 천정으로 만든다는 의미. 

floor: 소수점 자리의 숫자를 무조건 버린다. (방의) 바닥으로 만든다는 의미.





아래는 특정 빌라의  전용 면적 필드 값을

 각각 floor, cell, round 함수를 적용한 결과이다. 





>> select area, floor(area), ceil(area), round(area) from 테이블;



전용면적 | floor | ceil | round

------------------+-------+------+-------

            59.34 |    59 |   60 |    59

            59.34 |    59 |   60 |    59

            59.35 |    59 |   60 |    59

            59.35 |    59 |   60 |    59

            59.35 |    59 |   60 |    59

            59.36 |    59 |   60 |    59

            59.36 |    59 |   60 |    59

            59.40 |    59 |   60 |    59

            59.47 |    59 |   60 |    59

            59.47 |    59 |   60 |    59

            59.47 |    59 |   60 |    59

            59.48 |    59 |   60 |    59

            59.54 |    59 |   60 |    60

            59.56 |    59 |   60 |    60

            59.61 |    59 |   60 |    60

            59.61 |    59 |   60 |    60

            59.67 |    59 |   60 |    60

            59.67 |    59 |   60 |    60

            59.78 |    59 |   60 |    60

            59.78 |    59 |   60 |    60

            59.78 |    59 |   60 |    60

            59.78 |    59 |   60 |    60

            59.78 |    59 |   60 |    60

            59.78 |    59 |   60 |    60

            59.78 |    59 |   60 |    60

            59.79 |    59 |   60 |    60

            59.82 |    59 |   60 |    60

            59.95 |    59 |   60 |    60

            59.95 |    59 |   60 |    60

            59.95 |    59 |   60 |    60

            59.96 |    59 |   60 |    60

            59.96 |    59 |   60 |    60

            59.96 |    59 |   60 |    60

            59.96 |    59 |   60 |    60

            65.30 |    65 |   66 |    65

            65.30 |    65 |   66 |    65

            77.46 |    77 |   78 |    77

            78.27 |    78 |   79 |    78

            78.77 |    78 |   79 |    79

            79.17 |    79 |   80 |    79

            80.38 |    80 |   81 |    80

            81.01 |    81 |   82 |    81

            81.12 |    81 |   82 |    81

(43 rows)







참고: https://www.postgresql.org/docs/8.1/static/functions-math.html