Postgre에서 특정 행 내보내기SQL 테이블을 INSERT SQL 스크립트로 사용
이름이 다음인 데이터베이스 스키마가 있습니다.nyummy그리고 이름이 붙은 테이블cimory:
create table nyummy.cimory (
id numeric(10,0) not null,
name character varying(60) not null,
city character varying(50) not null,
CONSTRAINT cimory_pkey PRIMARY KEY (id)
);
는 수하고싶다니습을 .cimory테이블의 데이터를 SQL 스크립트 파일 삽입으로 사용합니다.그러나 도시가 '도쿄'와 동일한 기록/데이터만 내보내고 싶습니다(도시 데이터가 모두 소문자라고 가정).
어떻게 하는 거지?
솔루션이 프리웨어 GUI 도구에 있는지, 명령줄에 있는지는 중요하지 않습니다(GUI 도구 솔루션이 더 낫지만).저는 pgAdmin III를 사용해 보았지만 이를 위한 옵션을 찾을 수 없습니다.
내보낼 세트로 테이블을 만든 다음 명령줄 유틸리티 pg_dump를 사용하여 파일로 내보냅니다.
create table export_table as
select id, name, city
from nyummy.cimory
where city = 'tokyo'
$ pg_dump --table=export_table --data-only --column-inserts my_database > data.sql
--column-inserts열 이름을 가진 삽입 명령으로 덤프합니다.
--data-only스키마를 덤프하지 않습니다.
아래에서 설명한 바와 같이 테이블 대신 뷰를 작성하면 새 내보내기가 필요할 때마다 테이블을 작성할 수 없습니다.
데이터만 내보내려면 다음을 사용합니다.
COPY (SELECT * FROM nyummy.cimory WHERE city = 'tokio') TO '/path/to/file.csv';
표시된 대로 전체 테이블, 선택한 열만 또는 쿼리 결과를 내보낼 수 있습니다.테이블을 명시적으로 작성할 필요가 없습니다.
한 줄에 테이블 행이 하나씩 있는 파일을 일반 텍스트로 가져옵니다(그렇지 않음).INSERT명령)을 선택합니다.보다작빠보다 작고 INSERT명령을 실행합니다.
일치하는 구조의 다른 Postgres 테이블로 같은 내용을 가져오려면(같은 순서의 열, 데이터 유형 호환!):
COPY other_tbl FROM '/path/to/file.csv';
COPY는 서의 버컬쓰읽고습과 같은 .pg_dump또는psql클라이언트의 로컬 파일을 읽고 씁니다.둘 다 같은 컴퓨터에서 실행되는 경우에는 크게 문제가 되지 않지만 원격 연결에서는 문제가 됩니다.
psql 명령도 있습니다.
프런트 엔드(클라이언트) 복사본을 수행합니다.이것은 SQL 명령을 실행하는 작업이지만 서버가 지정된 파일을 읽거나 쓰는 대신 psql은 파일을 읽거나 쓰고 서버와 로컬 파일 시스템 간에 데이터를 라우팅합니다.즉, 파일 액세스 가능성과 권한은 서버가 아닌 로컬 사용자의 권한이며 SQL 수퍼유저 권한은 필요하지 않습니다.
위의 구문과 동일합니다.교체하기만 하면 됩니다.COPY와 함께\copy.
이것은 추가 설치 없이 수동으로 pgAdmin이 있는 스크립트로 테이블을 내보내는 쉽고 빠른 방법입니다.
- 대상 테이블을 마우스 오른쪽 버튼으로 클릭하고 "백업"을 선택합니다.
- 백업을 저장할 파일 경로를 선택합니다.형식으로 "일반"을 선택합니다.
- 하단의 "Dump Options #2" 탭을 열고 "Use Column Inserts"를 선택합니다.
- Backup(백업) 버튼을 클릭합니다.
- 텍스트 판독기(예: 메모장++)로 결과 파일을 열면 전체 테이블을 만드는 스크립트가 나타납니다.여기서 생성된 INSERT-문을 복사하기만 하면 됩니다.
이 방법은 @Clodaaldo Neto의 답변에서 설명한 것처럼 export_table을 만드는 기술과도 작동합니다.
저는 사용 사례를 위해 단순히 파이프를 grep에 연결할 수 있었습니다.
pg_dump -U user_name --data-only --column-inserts -t nyummy.cimory | grep "tokyo" > tokyo.sql
SQL Workbench에는 이러한 기능이 있습니다.
쿼리 실행 후 쿼리 결과를 마우스 오른쪽 단추로 클릭하여 "Copy Data As SQL > SQL Insert"를 선택합니다.
저는 @PhilHibbs 코드를 기반으로 다른 방법으로 절차를 작성하려고 했습니다.확인 및 테스트 부탁드립니다.
CREATE OR REPLACE FUNCTION dump(IN p_schema text, IN p_table text, IN p_where text)
RETURNS setof text AS
$BODY$
DECLARE
dumpquery_0 text;
dumpquery_1 text;
selquery text;
selvalue text;
valrec record;
colrec record;
BEGIN
-- ------ --
-- GLOBAL --
-- build base INSERT
-- build SELECT array[ ... ]
dumpquery_0 := 'INSERT INTO ' || quote_ident(p_schema) || '.' || quote_ident(p_table) || '(';
selquery := 'SELECT array[';
<<label0>>
FOR colrec IN SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = p_table and table_schema = p_schema
ORDER BY ordinal_position
LOOP
dumpquery_0 := dumpquery_0 || quote_ident(colrec.column_name) || ',';
selquery := selquery || 'CAST(' || quote_ident(colrec.column_name) || ' AS TEXT),';
END LOOP label0;
dumpquery_0 := substring(dumpquery_0 ,1,length(dumpquery_0)-1) || ')';
dumpquery_0 := dumpquery_0 || ' VALUES (';
selquery := substring(selquery ,1,length(selquery)-1) || '] AS MYARRAY';
selquery := selquery || ' FROM ' ||quote_ident(p_schema)||'.'||quote_ident(p_table);
selquery := selquery || ' WHERE '||p_where;
-- GLOBAL --
-- ------ --
-- ----------- --
-- SELECT LOOP --
-- execute SELECT built and loop on each row
<<label1>>
FOR valrec IN EXECUTE selquery
LOOP
dumpquery_1 := '';
IF not found THEN
EXIT ;
END IF;
-- ----------- --
-- LOOP ARRAY (EACH FIELDS) --
<<label2>>
FOREACH selvalue in ARRAY valrec.MYARRAY
LOOP
IF selvalue IS NULL
THEN selvalue := 'NULL';
ELSE selvalue := quote_literal(selvalue);
END IF;
dumpquery_1 := dumpquery_1 || selvalue || ',';
END LOOP label2;
dumpquery_1 := substring(dumpquery_1 ,1,length(dumpquery_1)-1) || ');';
-- LOOP ARRAY (EACH FIELD) --
-- ----------- --
-- debug: RETURN NEXT dumpquery_0 || dumpquery_1 || ' --' || selquery;
-- debug: RETURN NEXT selquery;
RETURN NEXT dumpquery_0 || dumpquery_1;
END LOOP label1 ;
-- SELECT LOOP --
-- ----------- --
RETURN ;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
그리고 나서:
-- for a range
SELECT dump('public', 'my_table','my_id between 123456 and 123459');
-- for the entire table
SELECT dump('public', 'my_table','true');
혼합 필드 데이터 유형(텍스트, 이중, int, 시간대가 없는 테이블 등)을 사용하여 postgres 9.1에서 테스트했습니다.
그래서 TEXT 타입의 CAST가 필요합니다.내 테스트는 약 9M 라인에서 올바르게 실행되며, 실행 18분 전에 실패한 것처럼 보입니다.
ps : WEB에서 mysql에 해당하는 것을 찾았습니다.
특정 레코드를 사용하여 테이블을 보고 sql 파일을 덤프할 수 있습니다.
CREATE VIEW foo AS
SELECT id,name,city FROM nyummy.cimory WHERE city = 'tokyo'
저는 이것을 하기 위한 빠른 절차를 생각해냈습니다.단일 행에만 작동하므로 원하는 행만 선택하는 임시 보기를 만든 다음 pg_temp.temp_view를 삽입할 실제 테이블로 바꿉니다.
CREATE OR REPLACE FUNCTION dv_util.gen_insert_statement(IN p_schema text, IN p_table text)
RETURNS text AS
$BODY$
DECLARE
selquery text;
valquery text;
selvalue text;
colvalue text;
colrec record;
BEGIN
selquery := 'INSERT INTO ' || quote_ident(p_schema) || '.' || quote_ident(p_table);
selquery := selquery || '(';
valquery := ' VALUES (';
FOR colrec IN SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = p_table and table_schema = p_schema
ORDER BY ordinal_position
LOOP
selquery := selquery || quote_ident(colrec.column_name) || ',';
selvalue :=
'SELECT CASE WHEN ' || quote_ident(colrec.column_name) || ' IS NULL' ||
' THEN ''NULL''' ||
' ELSE '''' || quote_literal('|| quote_ident(colrec.column_name) || ')::text || ''''' ||
' END' ||
' FROM '||quote_ident(p_schema)||'.'||quote_ident(p_table);
EXECUTE selvalue INTO colvalue;
valquery := valquery || colvalue || ',';
END LOOP;
-- Replace the last , with a )
selquery := substring(selquery,1,length(selquery)-1) || ')';
valquery := substring(valquery,1,length(valquery)-1) || ')';
selquery := selquery || valquery;
RETURN selquery;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
이렇게 호출됨:
SELECT distinct dv_util.gen_insert_statement('pg_temp_' || sess_id::text,'my_data')
from pg_stat_activity
where procpid = pg_backend_pid()
주입 공격에 대해 테스트하지 않았습니다. quote_literal 호출이 충분하지 않으면 알려주십시오.
또한 :: 텍스트로 간단히 캐스팅하고 다시 되돌릴 수 있는 열에 대해서만 작동합니다.
또한 이것은 Greenplum을 위한 것이지만 Postgres, CMIIW에서 작동하지 않는 이유를 생각할 수 없습니다.
(생산 중인) 임시 테이블을 만들지 않고 삽입문을 생성할 수 있는 방법이 필요했습니다.나는 위의 pg_dump에 대한 몇 가지 유용한 주장을 보았지만 여전히 다음과 같은 방법을 고안해냈습니다.
삽입 문을 생성하고 파일에 덤프합니다.
아래의 진술은 제가 여기서 비슷한 대답을 하는 사람들에게 유용할 것이라고 생각하는 속임수를 했습니다.
$ /usr/local/bin/pg_dump -h the_db_host_address -d the_db_name --table=schema_name.table_name --data-only --column-inserts -U postgres -p the_port_number -v -f /Users/the_user/folder_name/insrt_stmts_file_name.sql
다음은 비밀번호를 묻는 프롬프트입니다. 위의 경우에 제가 제공했습니다.postgres암호를 사용하지만 읽기 기능이 있는 다른 사용자는 정상적으로 작동해야 합니다.
이름이 지정된 파일에서 just insert 문을 실행했습니다.
insrt_stmts_file_name.sqlDBeaver 클라이언트에 있습니다.
그러나 다음 스크립트를 사용하여 명령 프롬프트에서도 동일하게 실행할 수 있습니다.
/usr/local/bin/psql -h the_db_host_address -d the_db_name -v -U postgres -f /Users/the_user/folder_name/insrt_stmts_file_name.sql
pg_filename / psql 플래그에 대한 참조: -h = host -d = db name -v = verbose (진행하면서 출력됨) -U = db user name -f = file / path
단순하지만 수동적인 방법을 추가하기 위해서입니다.
PGADMIN 4를 사용하여 데이터를 쿼리한 후 csv로 데이터를 다운로드합니다.
모든 메모장에서 csv를 열고 온라인 csv에 데이터를 복사하여 sql 컨버터에 붙여넣습니다.예: https://www.convertcsv.com/csv-to-sql.htm .대상 테이블의 이름을 설정할 수 있습니다.출력은 SQL 삽입 스크립트입니다.
INSERT 스크립트를 대상 db 쿼리 창에서 PGADMIN4에 다시 복사합니다.
pgadmin에서 쿼리 실행을 시도한 적이 있습니까? " EXECUTE QUERY WRITE RESULT TO FILE " 선택
데이터만 내보내고, 그렇지 않으면 다음과 같이 시도합니다.
pg_dump -t view_name DB_name > db.sql
-t 옵션은 ==> 일치하는 테이블(또는 뷰 또는 시퀀스)만 덤프합니다. 참조
언급URL : https://stackoverflow.com/questions/12815496/export-specific-rows-from-a-postgresql-table-as-insert-sql-script
'programing' 카테고리의 다른 글
| Python에서 try/except를 사용하여 문자열을 Int로 변환 (0) | 2023.05.14 |
|---|---|
| 모서리가 둥글고 그림자가 드리워진 UIView? (0) | 2023.05.14 |
| os.path.join에 대한 Pathlib 대안이 있습니까? (0) | 2023.05.14 |
| wpf에서 사용자 지정 윈도우 크롬을 만드는 방법은 무엇입니까? (0) | 2023.05.14 |
| 원격 컴퓨터의 디스크 용량 및 여유 공간을 얻는 방법 (0) | 2023.05.09 |