将大 psql 表导出为 JSON

发布于 2025-01-13 06:25:30 字数 845 浏览 0 评论 0原文

我如何将大表导出到 json，但输出文件超过 1Gb

copy (SELECT json_agg(export_data)::text FROM "table_name" export_data) TO '{{ path_name }}/{{ table_name }}.json' with csv quote E'\t' encoding 'UTF8'

我收到：内存不足，无法将包含 1073741822 字节的字符串缓冲区扩大 1 个字节。

表列：
第一个	uuid
第二个	时间戳
3 个	uuid
4 个	时间戳
5 个	uuid
6	个int4
7 个	文本
8 个	uuid
9 个	int4
10	个uuid
11 个	uuid
12	varchar(50)

也许有一种方法可以按行分割输出？

原文

how i can export big table to json, but output file is over 1Gb

copy (SELECT json_agg(export_data)::text FROM "table_name" export_data) TO '{{ path_name }}/{{ table_name }}.json' with csv quote E'\t' encoding 'UTF8'

I receive: out of memory, Cannot enlarge string buffer containing 1073741822 bytes by 1 more bytes.

Table column:
First	uuid
Second	timestamp
Three	uuid
Four	timestamp
Five	uuid
Six	int4
Seven	text
Eight	uuid
Nine	int4
Ten	uuid
Eleven	uuid
Twelve	varchar(50)

Maybe there is a way to split the output by lines?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

嘴硬脾气大 2025-01-20 06:25:31

将表分成几个部分并分别导出每个部分：

假设大小与行数成正比，并确定总行数的 n 分之一是合理的大小。然后，您可以执行如下所示的过程，并将获得 n 个结果文件：

CREATE OR REPLACE PROCEDURE table_export (table_name text, path_name text, n integer) LANGUAGE plpgsql AS
$
DECLARE
  total_count bigint ;
  i integer ;
BEGIN
  EXECUTE FORMAT
  ( 'SELECT count(*)
       INTO total_count 
       FROM %I'
  , table_name 
  ) ;

  FOR i IN 0 .. (n-1) LOOP
    EXECUTE FORMAT
    (E'COPY ( SELECT json_agg(export_data)::text 
                FROM %1I export_data
               LIMIT %4s
               OFFSET (%3s * %4s)
            ) 
         TO \'%2s/%1s_%3s.json\' with csv quote E\'\\t\' encoding \'UTF8\''
    , table_name
    , path_name 
    , i
    , ceil(total_count/n)
    ) ;
  END LOOP ;
END ;
$ ;

Splitting the table into several parts and exporting each part separately :

Let's assume that the size is proportional to the number of rows and decide that one n-th of the total number of rows is a reasonnable size. You can then execute a procedure like the following one and will get n resulting files :

CREATE OR REPLACE PROCEDURE table_export (table_name text, path_name text, n integer) LANGUAGE plpgsql AS
$
DECLARE
  total_count bigint ;
  i integer ;
BEGIN
  EXECUTE FORMAT
  ( 'SELECT count(*)
       INTO total_count 
       FROM %I'
  , table_name 
  ) ;

  FOR i IN 0 .. (n-1) LOOP
    EXECUTE FORMAT
    (E'COPY ( SELECT json_agg(export_data)::text 
                FROM %1I export_data
               LIMIT %4s
               OFFSET (%3s * %4s)
            ) 
         TO \'%2s/%1s_%3s.json\' with csv quote E\'\\t\' encoding \'UTF8\''
    , table_name
    , path_name 
    , i
    , ceil(total_count/n)
    ) ;
  END LOOP ;
END ;
$ ;

回复收藏 0 原文

~没有更多了~