programing

Postgre에서 PL/pgSQL 출력 저장CSV 파일에 대한 SQL

subpage 2023. 4. 29. 09:19

Postgre에서 PL/pgSQL 출력 저장CSV 파일에 대한 SQL

Postgre에서 PL/pgSQL 출력을 저장하는 가장 쉬운 방법은 무엇입니까?SQL 데이터베이스를 CSV 파일로?

저는 Postgre를 사용하고 있습니다.쿼리를 실행하는 pgAdmin III 및 PSQL 플러그인이 포함된 SQL 8.4.

결과 파일을 서버에 저장하시겠습니까, 아니면 클라이언트에 저장하시겠습니까?

서버측

재사용하거나 자동화하기 쉬운 것을 원한다면 Postgresql의 내장된 복사 명령을 사용할 수 있습니다.

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

이 접근 방식은 전적으로 원격 서버에서 실행되며 로컬 PC에 쓸 수 없습니다.또한 Postgres는 해당 시스템의 로컬 파일 시스템에서 불쾌한 작업을 수행하는 것을 멈출 수 없기 때문에 Postgres "superuser"(일반적으로 "root"이라고 함)로 실행해야 합니다.

이 옵션을 사용하면 슈퍼 사용자처럼 실행되는 기능을 만들 수 있기 때문에 슈퍼 사용자로 연결(다른 종류의 보안 위험이 되는 자동화)해야 한다는 것을 의미하지는 않습니다.

중요한 부분은 보안을 무시하는 것이 아니라 추가 검사를 수행하기 위한 기능이 있다는 것입니다. 따라서 필요한 데이터를 정확하게 내보내는 기능을 작성하거나 엄격한 화이트리스트를 충족하는 한 다양한 옵션을 허용할 수 있는 기능을 작성할 수 있습니다.다음 두 가지 사항을 확인해야 합니다.

디스크에서 읽고 쓸 수 있는 파일을 선택합니다.예를 들어, 이것은 특정 디렉토리일 수 있으며 파일 이름에는 적절한 접두사 또는 확장자가 있어야 합니다.
사용자가 데이터베이스에서 읽고 쓸 수 있는 테이블은 무엇입니까?이는 일반적으로 다음과 같이 정의됩니다.GRANTs는 데이터베이스에 있지만 이 함수는 현재 슈퍼 사용자로 실행되고 있으므로 일반적으로 "경계를 벗어난" 테이블에 완전히 액세스할 수 있습니다.다른 사용자가 "사용자" 테이블 끝에 사용자의 함수를 호출하고 행을 추가하지 못하도록 하고 싶지 않을 수도 있습니다.

저는 엄격한 조건을 충족하는 파일과 테이블을 내보내거나 가져오는 기능의 몇 가지 예를 포함하여 이 접근 방식을 확장하는 블로그 게시물을 작성했습니다.

클라이언트 측

다른 접근 방식은 클라이언트 측에서 파일 처리를 수행하는 것입니다. 즉, 응용 프로그램 또는 스크립트에서 파일 처리를 수행합니다.Postgres 서버는 복사할 파일을 알 필요가 없습니다. 데이터를 뱉어내고 클라이언트가 어딘가에 저장합니다.

은 이대한기구다같습다니음과문입니다.COPY TO STDOUT명령어와 pgAdmin과 같은 그래픽 도구가 멋진 대화상자로 감싸줍니다.

명령줄 클라이언트에는 "실제"와 동일한 옵션을 모두 사용하는 "메타 명령"이라는 특수 "메타 명령이 있습니다.COPY클라이언트 내부에서 실행됩니다.

\copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER

종료되지 않음에 유의하십시오.;SQL 명령과 달리 메타 명령은 줄 바꿈으로 종료되기 때문입니다.

문서에서:

복사와 psql 명령 \copy를 혼동하지 마십시오.\copy는 COPY FROM STDIN 또는 COPY TO STDOUT를 호출한 다음 psql 클라이언트가 액세스할 수 있는 파일로 데이터를 가져오거나 저장합니다.따라서 \copy를 사용할 때 파일 접근성과 접근 권한은 서버가 아닌 클라이언트에 따라 달라집니다.

응용 프로그램 프로그래밍 언어에서도 데이터를 푸시하거나 가져오는 기능이 지원될 수 있지만 일반적으로 사용할 수는 없습니다.COPY FROM STDIN/TO STDOUT입력/출력 스트림을 연결할 방법이 없기 때문에 표준 SQL 문 안에 있습니다.PHP의 PostgrePDO가 아닌 SQL 처리기에는 PHP 배열로 복사하거나 PHP 배열에서 복사하는 매우 기본적인 기능이 포함되어 있어 대용량 데이터 세트에는 효율적이지 않을 수 있습니다.

몇 가지 솔루션이 있습니다.

1 `psql`

psql -d dbname -t -A -F"," -c "select * from users" > output.csv

은 SSH를 할 수 큰. 를 들어, SSH를 통해 사용할 수 있습니다. 예를 들어,ssh postgres@host command사용자가 데이터를 얻을 수

2 포스트그레스`copy`

COPY (SELECT * from users) To '/tmp/output.csv' With CSV;

3 psql 대화형(또는 없음)

>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q

모두 스크립트에서 사용할 수 있지만 저는 1번이 더 좋습니다.

4pgadmin이지만 스크립트로 작성할 수 없습니다.

터미널 내(DB에 연결된 동안) cvs 파일에 대한 출력 설정

를 " " " " " 로 설정합니다.',':

\f ','

정렬되지 않은 출력 형식 설정:

\a

튜플만 표시:

\t

출력 설정:

\o '/tmp/yourOutputFile.csv'

쿼리 실행:

:select * from YOUR_TABLE

출력:

\o

그러면 다음 위치에서 csv 파일을 찾을 수 있습니다.

cd /tmp

를 사용하여 복사합니다.scpnanonano를 합니다.

nano /tmp/yourOutputFile.csv

CSV 내보내기 통합

이 정보는 제대로 표현되지 않았습니다.이것이 제가 이것을 도출하는 데 필요한 두 번째이기 때문에, 저는 다른 것이 없다면 제 자신에게 상기시키기 위해 이것을 여기에 두겠습니다.

것) " " " " " " (CSV는 " " " (CSV를 사용하는 것을 의미합니다."을 입니다.COPY ... TO STDOUTㅠㅠㅠㅠ 하지만 에 있는에 나온 여기에 나와 있는 답변과 같은 방식으로 하고 싶지는 않지만요.올바른 명령 사용 방법은 다음과 같습니다.

COPY (select id, name from groups) TO STDOUT WITH CSV HEADER

명령 하나만 기억하세요!

SSH를 통해 사용하기 좋습니다.

$ ssh psqlserver.example.com 'psql -d mydb "COPY (select id, name from groups) TO STDOUT WITH CSV HEADER"' > groups.csv

SSH를 통해 도커 내부에서 사용하기 좋습니다.

$ ssh pgserver.example.com 'docker exec -tu postgres postgres psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

로컬 시스템에서도 매우 유용합니다.

$ psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

아니면 로컬 컴퓨터의 도커 내부?:

docker exec -tu postgres postgres psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

또는 쿠버네티스 클러스터의 도커에서 HTTPS를 통해?:

kubectl exec -t postgres-2592991581-ws2td 'psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

아주 다용도로, 많은 쉼표!

그래요?

네, 그렇습니다. 여기 제 노트가 있습니다.

카피즈

용사를 합니다./copy는 어떤 에서든 파일 합니다.psql명령이 실행 중인 사용자1로 실행 중입니다.원격 서버에 연결하는 경우 시스템에서 데이터 파일을 복사하여psql원격 서버로/원격 서버에서.

COPY백엔드 프로세스 사용자 계정으로 서버에서 파일 작업을 실행합니다(기본값).postgres 및 그에 따라 합니다.), 파일 경로 및 권한은 다음과 같습니다.사용하는 경우TO STDOUT그러면 파일 권한 검사가 무시됩니다.

이 두 옵션 모두 다음과 같은 경우 후속 파일 이동이 필요합니다.psql결과 CSV가 최종적으로 상주할 시스템에서 실행되고 있지 않습니다.제 경험에 따르면, 대부분의 경우 원격 서버로 작업할 때 가장 가능성이 높습니다.

출력을 TCP이 더 출력 의에는 "CSV" "TCP/IP" "SSH" "SH" "SH" "SH" "SH" "SH" "SH" "SH" "SH/IP 터널과 같은 것이 더 수 ./copy연결을 "" " " " " " 을 합니다.psql맥락에서 가져오기의 하고 비한맥락서에, 슷기의파서일버이을동다를 사용합니다.COPY가장 높은 성능의 옵션일 것입니다.

PSQL 매개 변수

psql 매개 변수를 사용하면 출력을 CSV처럼 포맷할 수 있지만 호출기를 사용하지 않도록 설정하고 헤더를 가져오지 않는 등의 단점이 있습니다.

$ psql -P pager=off -d mydb -t -A -F',' -c 'select * from groups;'
2,Technician,Test 2,,,t,,0,,                                                                                                                                                                   
3,Truck,1,2017-10-02,,t,,0,,                                                                                                                                                                   
4,Truck,2,2017-10-02,,t,,0,,

기타 도구

아니요, 도구를 컴파일하거나 설치하지 않고 CSV를 서버에서 제거하고 싶습니다.

버전 - 12 - 인 새버 - psql 12 - 지--csv.

psql - devel

--csv

CSV(Comma-Separated Values) 출력 모드로 전환합니다.이는 \pset 형식 csv에 해당합니다.

csv_fieldsep

CSV 출력 형식에서 사용할 필드 구분 기호를 지정합니다.필드 값에 구분 문자가 나타나면 표준 CSV 규칙에 따라 해당 필드가 큰따옴표로 묶인 채로 출력됩니다.기본값은 쉼표입니다.

용도:

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv -P csv_fieldsep='^'  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres > output.csv

머리글과 함께 특정 표의 모든 열에 관심이 있는 경우

COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

이것은 보다 조금 더 간단합니다.

COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

제가 아는 한, 그것은 동등한 것입니다.

다음 오류 메시지가 표시되어 \COPY를 사용해야 했습니다.

ERROR:  could not open file "/filepath/places.csv" for writing: Permission denied

그래서 저는 다음을 사용했습니다.

\Copy (Select address, zip  From manjadata) To '/filepath/places.csv' With CSV;

그리고 그것은 작동하고 있습니다.

psql다음을 수행할 수 있습니다.

edd@ron:~$ psql -d beancounter -t -A -F"," \
                -c "select date, symbol, day_close " \
                   "from stockprices where symbol like 'I%' " \
                   "and date >= '2009-10-02'"
2009-10-02,IBM,119.02
2009-10-02,IEF,92.77
2009-10-02,IEV,37.05
2009-10-02,IJH,66.18
2009-10-02,IJR,50.33
2009-10-02,ILF,42.24
2009-10-02,INTC,18.97
2009-10-02,IP,21.39
edd@ron:~$

» man psql여기에 사용되는 옵션에 대한 도움말을 참조하십시오.

는 AWS Redshift를 Redshift를 하고 COPY TO특징.

BI 툴은 탭 구분 CSV를 지원하므로 다음을 사용했습니다.

 psql -h dblocation -p port -U user -d dbname -F $'\t' --no-align -c "SELECT * FROM TABLE" > outfile.csv

pgAdmin III에는 쿼리 창에서 파일로 내보내기 옵션이 있습니다.메인 메뉴에서 쿼리 -> 파일로 실행 또는 동일한 작업을 수행하는 버튼이 있습니다(쿼리를 실행하는 일반 녹색 삼각형과 반대로 파란색 플로피 디스크가 있는 녹색 삼각형입니다).쿼리 창에서 쿼리를 실행하지 않으면 IMSoP가 제안한 대로 수행하고 copy 명령을 사용합니다.

여러 가지를 시도해 보았지만 헤더 세부사항이 포함된 원하는 CSV를 제공할 수 있는 것은 거의 없었습니다.

여기 제게 효과가 있었던 것이 있습니다.

psql -d dbame -U username \
  -c "COPY ( SELECT * FROM TABLE ) TO STDOUT WITH CSV HEADER " > \
  OUTPUT_CSV_FILE.csv

나는 그것을 캡슐화하는 작은 도구를 썼습니다.COPY query TO STDOUT올바른 CSV를 생성하는 패턴입니다.인페이다유사다니합음과는스와 .psql.

psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY

쿼리는 STDIN의 내용(있는 경우) 또는 마지막 인수로 간주됩니다.다음을 제외한 다른 모든 인수는 psql로 전달됩니다.

-h, --help           show help, then exit
--encoding=ENCODING  use a different encoding than UTF8 (Excel likes LATIN1)
--no-header          do not output a header

더 긴 쿼리가 있고 psql을 사용하려면 쿼리를 파일에 넣고 다음 명령을 사용합니다.

psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv

Postgres 12 이후 출력 형식을 변경할 수 있습니다.

\pset format csv

다음 형식이 허용됩니다.

aligned, asciidoc, csv, html, latex, latex-longtable, troff-ms, unaligned, wrapped

요결를내면다사수있다습니용을 .\o filename특징.

예:

\pset format csv

\o file.csv
SELECT * FROM table LIMIT 10;
\o

\pset format aligned

열 이름이 HEADER인 CSV 파일을 다운로드하려면 다음 명령을 사용합니다.

Copy (Select * From tableName) To '/tmp/fileName.csv' With CSV HEADER;

나는 그것을 발견했습니다.psql --csvUTF8 문자로 CSV 파일을 생성하지만 UTF8 바이트 순서 표시(0xEF 0xBB 0xBF)가 없습니다.이를 고려하지 않으면 이 CSV 파일의 기본 가져오기로 인해 CJK 문자와 같은 국제 문자가 손상됩니다.

그것을 고치기 위해 저는 다음과 같은 스크립트를 고안했습니다.

# Define a connection to the Postgres database through environment variables
export PGHOST=your.pg.host
export PGPORT=5432
export PGDATABASE=your_pg_database
export PGUSER=your_pg_user

# Place credentials in $HOME/.pgpass with the format:
# ${PGHOST}:${PGPORT}:${PGUSER}:master:${PGPASSWORD}

# Populate long SQL query in a text file:
cat > /tmp/query.sql <<EOF
SELECT item.item_no,item_descrip,
invoice.invoice_no,invoice.sold_qty
FROM item
LEFT JOIN invoice
ON item.item_no=invoice.item_no;
EOF

# Generate CSV report with UTF8 BOM mark
printf '\xEF\xBB\xBF' > report.csv
psql -f /tmp/query.sql --csv | tee -a report.csv

이렇게 하면 자동화를 위한 CSV 생성 프로세스를 스크립팅하고 단일 소스 파일에서 스크립트를 간소화하여 유지 관리할 수 있습니다.

쿼리가 너무 길어서 인라인으로 쓸 수 없는 경우 다음과 같은 임시 테이블을 사용할 수 있습니다.

CREATE TABLE tmp_table as (

    SELECT *
    FROM my_table mt
    WHERE ...

);

\COPY tmp_table TO '~/Desktop/tmp_table.csv' DELIMITER ';' CSV HEADER;
DROP TABLE tmp_table;

import json
cursor = conn.cursor()
qry = """ SELECT details FROM test_csvfile """ 
cursor.execute(qry)
rows = cursor.fetchall()

value = json.dumps(rows)

with open("/home/asha/Desktop/Income_output.json","w+") as f:
    f.write(value)
print 'Saved to File Successfully'

웹 브라우저의 데이터베이스 클라이언트인 JackDB는 이것을 정말 쉽게 만듭니다.특히 당신이 헤로쿠에 있다면요.

원격 데이터베이스에 연결하고 데이터베이스에 대한 SQL 조회를 실행할 수 있습니다.

^원천
_{(출처: jackdb.com )}

DB가 연결되면 쿼리를 실행하여 CSV 또는 TXT로 내보낼 수 있습니다(오른쪽 아래 참조).

jackdb-export

^{참고: 저는 결코 JackDB와 관련이 없습니다.저는 현재 그들의 무료 서비스를 이용하고 있으며 그것이 훌륭한 제품이라고 생각합니다.}

@skeller88님의 요청에 따라, 모든 응답을 읽지 않는 사람들에 의해 댓글이 유실되지 않도록 답변으로 다시 게시합니다...

DataGrip의 문제는 지갑을 꽉 잡는다는 것입니다.그것은 무료가 아닙니다.dbeaver.io 에서 DBeaver 커뮤니티 버전을 사용해 보십시오.SQL 프로그래머, DBA 및 분석가를 위한 FOSS 다중 플랫폼 데이터베이스 툴로, 널리 사용되는 모든 데이터베이스를 지원합니다.MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Hive, Presto 등

DBever Community Edition을 사용하면 데이터베이스에 연결하고 쿼리를 실행하여 데이터를 검색한 다음 결과 집합을 다운로드하여 CSV, JSON, SQL 또는 기타 일반적인 데이터 형식으로 저장하는 것이 간단합니다.Postgres용 Toad, SQL Server용 Toad 또는 Oracle용 Toad와 경쟁할 수 있는 FOSS입니다.

저는 DBeaver와 관계가 없습니다.가격과 기능은 마음에 들지만, 애플리케이션 내에서 그래프와 차트를 직접 생성하기 위해 연간 구독 비용을 지불해야 하는 대신 DBeaver/Eclipse 애플리케이션을 더 많이 열고 분석 위젯을 DBeaver/Eclipse에 쉽게 추가할 수 있기를 바랍니다.Java 코딩 기술이 미숙하여 Eclipse 위젯 구축 방법을 다시 배우는 데 몇 주가 걸릴 것 같지 않습니다. 하지만 DBeaver가 DBea Community Edition에 타사 위젯을 추가하는 기능을 사용할 수 없게 되었습니다.

DBeaver 사용자는 DBeaver의 Community Edition에 추가할 분석 위젯을 생성하는 단계에 대한 통찰력이 있습니까?

언급URL : https://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file

'programing' 카테고리의 다른 글

Mongorestore에서 "db/collection.bson" 파일을 사용하여 수행할 작업을 알 수 없습니다. 건너뛰기 (0)	2023.04.29
Xcode 빌드 오류 "아키텍처 x86_64에 대한 정의되지 않은 기호" (0)	2023.04.29
하나의 CTE를 여러 번 사용 (0)	2023.04.29
DISTINCT를 사용하여 파티션 함수 카운트() OVER 가능 (0)	2023.04.29
WPF 텍스트 상자에서 포커스에 있는 모든 텍스트를 자동으로 선택하는 방법은 무엇입니까? (0)	2023.04.29

현재글Postgre에서 PL/pgSQL 출력 저장CSV 파일에 대한 SQL

각종 프로그래밍 정보를 다루는 블로그입니다.

jQuery, Excel, powershell, spring-boot, MongoDB, Wordpress, Python, MariaDB, C, MySQL, CSS, json, JavaScript, AJAX, git, REACTJS, sql-server, asp.net, oracle, AngularJS,

Today :
Yesterday :

subpage

Postgre에서 PL/pgSQL 출력 저장CSV 파일에 대한 SQL

Postgre에서 PL/pgSQL 출력 저장CSV 파일에 대한 SQL

서버측

클라이언트 측

1 `psql`

2 포스트그레스`copy`

3 psql 대화형(또는 없음)

4pgadmin이지만 스크립트로 작성할 수 없습니다.

CSV 내보내기 통합

명령 하나만 기억하세요!

그래요?

카피즈

PSQL 매개 변수

기타 도구

'programing' 카테고리의 다른 글

'programing'의 다른글

티스토리툴바

« 2025/03 »
일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

Postgre에서 PL/pgSQL 출력 저장CSV 파일에 대한 SQL

Postgre에서 PL/pgSQL 출력 저장CSV 파일에 대한 SQL

서버측

클라이언트 측

1 psql

2 포스트그레스copy

3 psql 대화형(또는 없음)

4pgadmin이지만 스크립트로 작성할 수 없습니다.

CSV 내보내기 통합

명령 하나만 기억하세요!

그래요?

카피즈

PSQL 매개 변수

기타 도구

'programing' 카테고리의 다른 글

'programing'의 다른글

관련글

티스토리툴바

1 `psql`

2 포스트그레스`copy`