postgresql - 如何从一行postgres中获取压缩二进制字符串

Question

你好，我有这段代码可以生成一个带有压缩字符串的 txt 文件，该文件将被插入到 postgres 数据库中

def test_insert():
    str_test = '4 1 2\n 2 4 5\n'.encode('utf8')
    cmpstr = zlib.compress(str_test)
    str_test_to_write = '\\x' + cmpstr.encode('hex_codec')

    with open('outfile.txt','w') as output_file:
        output_file.write(str(1) + '|'+ str_test_to_write + '\n')
        output_file.write(str(2) + '|'+ str_test_to_write + '\n')

然后我使用命令 copy 将信息加载到我的表中:

time cat outfile.txt |psql teste3 -c "\copy zstr(id,zstr) from stdout with delimiter '|'"

这是我的 table :

drop table if exists zstr; 
    create table zstr(
    id int, 
    zstr bytea, 
    primary key(id));

然后我想选择我的字符串，但出现此错误:

>>> import psycopg2
>>> import zlib
>>> con = psycopg2.connect(host = 'X', database = 'Y', user = 'Z')
>>> con.autocommit = True
>>> cur = con.cursor()
>>> cur.execute('select * from zstr where id = 1')
>>> row = cur.fetchone()
>>> row
(1, <read-only buffer for 0x7fe19b75f270, size 41, offset 0 at 0x7fe196976f30>)
>>> a = str(row[1])
>>> q = zlib.decompress(a)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
zlib.error: Error -3 while decompressing data: incorrect header check

那么我怎样才能得到我的字符串呢？

我想要的输出:

'4 1 2\n 2 4 5\n'

Answer 1

几乎没有理由这样做。如果该值大于 TOAST_TUPLE_THRESHOLD，PostgreSQL 自然会使用 LZ 压缩文本。来自docs on TOAST

The TOAST management code is triggered only when a row value to be stored in a table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB). The TOAST code will compress and/or move field values out-of-line until the row value is shorter than TOAST_TUPLE_TARGET bytes (also normally 2 kB) or no more gains can be had. During an UPDATE operation, values of unchanged fields are normally preserved as-is; so an UPDATE of a row with out-of-line values incurs no TOAST costs if none of the out-of-line values change.

它对用户透明地执行此操作。只存储文本本身。