INSERT INTO table SELECT column1,column2,column3 FROM table2 

여러 행을 한번에 insert

'DB > PostgreSQL' 카테고리의 다른 글

[PostgreSQL] COPY  (0) 2016.10.21
[PostgreSQL]postgres_fdw  (0) 2016.09.27
[PostgreSQL] SELECT 결과 INSERT하기  (0) 2016.07.29
[PostgreSQL] SELECT 결과로 테이블 생성하기  (0) 2016.07.29
[PostgreSQL] select 시 serial(auto increment) 추가하기  (0) 2016.07.29
[PostgreSQL]dblink  (0) 2016.07.11


CREATE TABLE table AS(
SELECT column1, column2, column3
FROM other_table
)

from에서 받아온 테이블 내 컬럼의 속성을 그대로 타서 생성이 된다.


SELECT ROW_NUMBER() OVER (ORDER BY column1) AS NUM, column1, column2, column3
FROM SBO_BIZM

다음과 같이 할 경우, column1의 순서에 따라 serial 컬럼이 추가되어 표시가 된다.


select결과로 바로 테이블을 생성할 때, 위와 같은 방법으로 serial을 추가해주는 효과를 볼 수 있다.

dblink는 물리적으로 떨어져 있는 원격 테이블에 접속하기 위해 사용합니다. 또한 같은 물리 공간에 있더라도 다른 DB에 접근 시에는 dblink 를 사용해야 합니다.

dblink 

dblink 설치 및 명령어

CREATE EXTENSION [ IF NOT EXISTS ] extension_name 
    [ WITH ] [ SCHEMA schema_name ] 
             [ VERSION version ] 
             [ FROM old_version ]

간단하게 설치하는 방법은 CREATE EXTENSION dblink 입니다. 또한 스키마를 지정하지 않으면 default로 public에 설치가 됩니다.

여기서 유의해야 할 것은 dblink extension이 데이터베이스 내에 설치가 되면 (사용자가 정한 디비 스키마라던지, default인 public 스키마 라던지..) 지우지 않은 이상 재설치가 불가능합니다.

따라서 스키마 별로 사용이 필요한 부분에서는 alter로 schema를 변경하여 사용하거나 지운 다음 사용 합니다.

CREATE EXTENSION  IF NOT EXISTS dblink SCHEMA other_db -- default 스키마 (public)에 설치를 하지 않을 경우엔 SCHEMA를 정의
-- IF NOT EXISTS는 해당 디비에 EXTENSION이 없을 경우에만 설치합니다. 만약 해당 명령어가 없는데 이미 설치가 되어 있을 경우엔 에러를 뱉습니다.
 
ALTER EXTENSION SCHEMA other_db1 -- 다른 스키마로 dblink을 이동시켜 사용할 때 사용합니다.
 
DROP EXTENSION dblink -- dblink를 삭제합니다.

dblink 사용법

사용법은 여러 방법이 있지만 다음과 같이 사용하는 것이 좀 더 깔끔하게 분리하여 관리할 수 있는 것 같은 생각이므로 한 가지 방법만 소개합니다.

SELECT dblink_connect('{1}', 'hostaddr={2} user={3} password={4} dbname={5} port={6}')

{1} : 앞으로 dblink를 사용할 id ( 사용자 임의로 입력 가능)

{2} : 연결할 원격디비의 주소

{3} : 연결할 원격디비의 유저

{4} : 연결할 원격디비 유저의 비밀번호

{5} : 연결할 원격디비의 이름

{6} : 연결할 원격디비의 포트번호


 SELECT {3}.* FROM dblink('{1}', '{2}')AS {3} ({4})

{1} : 앞에서 정의한 dblink id

{2} : 연결한 원격디비에 날릴 쿼리

{3} : 원격디비에 날린 쿼리 결과에 대한 임의의 이름 

{4} : 원격디비에 날린 테이블 스키마


만약 {2}의 쿼리에서 select * 와 같이 테이블 전체를 호출하였으면 {4}에 명시할 테이블 스키마는 전체이고 select name, id 와 같이 되어 있다면, 명시할 테이블 스키마는 name과 id 2개만 해주면 됩니다.

dblink 사용예시

CREATE EXTENSION dblink
ALTER EXTENSION dblink SCHEMA other_db
 
SELECT dblink_connect('using_name', 'hostaddr=199.199.199.199 user=user password=1234 dbname=db2 port=5432')
 
SELECT * FROM dblink('using_name', 'SELECT * FROM abcd') AS name (name character varying(5),  etc character varying(20))
-- 원격 db에 존재하는 abcd 테이블 전체 출력
 
SELECT name.name FROM dblink('using_name', 'SELECT id FROM abcd') AS name (id character varying(5))
-- 원격 db에 존재하는 abcd 테이블에서 id라는 컬럼의 값만 가져와 출력
 
SELECT * FROM (dblink ('using_name', 'SELECT * FROM abcd') AS name (name character varying(5),  etc character varying(20))) AS remote_table, table2
-- 다음과 같이 원격 db에 존재하는 abcd테이블을 가져와 remote_table이라고 이름을 지은 다음, 기존 db에 있는 table2라는 테이블과 join하여 전체 출력
 
SELECT dblink DISCONNECT('using_name')
-- 연결한 원격 db 종료

문제점

  1. dblink를 사용하기 위해선 주체가 되는 디비의 유저가 superuser 권한을 가지고 있어야 함
  2. 원격 데이터베이스의 정보가 노출될 가능성이 있음
  3. 문제라기보단 귀찮은 건데 extension은 디비에 1개밖에 설치가 안되므로 다른 스키마에서 사용하려고 하면 해당 스키마로 extension을 전부 alter 시키던지, 그대로 냅두고 쿼리 상에 데이터베이스 스키마를 전부 명시해 줘야함.

PYTHON(DJANGO)에서 사용

파이썬에서 사용하는 것은 위의 쿼리를 실행만 시키면 됩니다. 여기서 조심해야 할 점은 문제점 3. 에서 명시한 것처럼 스키마를 코드 상에서 변경하였다고 해서 dblink도 변경되지 않습니다. extension 자체를 변경하는 명령어를 실행하여 변경하고자 하는 디비 스키마로 옮긴 뒤 실행해야 합니다.

예를 들어, 기본 스키마(public)에서 schema1로 스키마를 변경하여 쿼리를 그대로 사용한다고 하면 ALTER EXTENSION dblink SCHEMA schema1 라고 명시해야 에러가 나지 않습니다.

우분투14.04 설치시 postgres를 선택해 설치할 수 있습니다.


권한을 postgres로 변경한 다음, postgres 데이터베이스에 들어갑니다.

1
2
$ sudo su postgres
$ psql -d postgres -U postgres
cs

psql을 입력하면 postgres데이터베이스에 현재 로그인된 계정으로 로그인을 시도합니다. 


1
2
3
postgres=# create user test with password '1234';
CREATE ROLE
 
cs

postgres 데이터베이스에 비밀번호 1234를 가진 test라는 유저를 생성합니다. 


1
2
3
postgres=# create database test;
CREATE DATABASE
 
cs

test라는 데이터베이스를 생성합니다.


다음 /etc/psotgresql/9.3/main/postgresql.conf를 편집기로 열어서 #listen_address='localhost'부분을 listen_address='*'로 바꿉니다.


/etc/psotgresql/9.3/main/pg_hba.conf를 편집기로 엽니다.


마지막 부분을 아래와 같이 변경해줍니다.

1
2
3
# TYPE DATABASE USER ADDRESS METHOD
local   all                    md5
host    all     all  0.0.0.0/32 md5
cs

만약 

local  postgres         peer 라는 줄이 있으면 지웁니다.

다음 sudo service postgresql restart 또는 sudo /etc/init.d/postgresql restart로 재시작합니다.


pgadmin3을 실행해 서버를 추가합니다.





+ Random Posts