Hello i have this code that produce a txt file with compress string that will be inserted into a postgres database
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')
Then i use the command copy to load the information into my table:
time cat outfile.txt |psql teste3 -c "\copy zstr(id,zstr) from stdout with delimiter '|'"
This is my table:
drop table if exists zstr;
create table zstr(
id int,
zstr bytea,
primary key(id));
Then i want to select my strings but i'm getting this error:
>>> 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
So how can i get my strings?
The output i want:
'4 1 2\n 2 4 5\n'
There is almost no reason to do this. PostgreSQL naturally compresses text, with LZ, if the value is larger than the TOAST_TUPLE_THRESHOLD. From the 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.
It does this transparently for the user. Just store the text itself.