Operation bytea type

If you want to store binary data in PostgreSQL, such as Word, Excel files, and image files, you can use bytea columns. Bytea is a field type unique to PostgreSQL for storing binary data. It is similar to blob and Binary Large Object Types in SQL standards. This is described in the bytea type introduction in the PostgreSQL document.

Next, let's talk about how to insert and update bytea data to a table.

PostgreSQL allows SQL commands to contain bytea data, so that you can use insert to insert records containing binary data into the table, use update and call functions related to the bytea type to update and operate data of the bytea type. Binary data is a byte sequence, but SQL commands are text strings. How do I write binary data in SQL? The answer is simple. Each byte is converted into an octal string of the corresponding three-digit decimal number, with a double slash as the prefix, that is, 0x00 represents \ 000, 0x2c represents \ 02c, 0xff represents \ 377, and according to the requirements of the bytea type, e is specified in the single quotation marks at the front end of the string. Example:

Insert into Table1 (fileid, filename, content)
Values (1, 'filename.doc ', e' \ 000 \ 001 \ 002 ');
Insert into Table1 (fileid, filename, content)
Values (2, 'anotherfile.jpg ', e' \ 000 \ 377 ');
Update Table1 set content = E' \ 000 \ 000 \ 000'
Where fileid = 1;
Update Table1 set content = content | E' \ 377 \ 377 \ 100'
Where fileid = 2;

You can include the bytea type string of the entire file in insert into, or you can append parts as in the fourth row above. For short binary data, you cannot edit SQL commands in the command console. However, if you want to store a large binary data such as an image file or Word document, you need to use the data access excuse or write a byte Conversion Program yourself to directly operate SQL statements.

After bytea data is inserted, you can use the SELECT statement to obtain it. As follows:

Select Content
From Table1;

In the command console, we can see that the binary data is output in the string format at the time of input, which is a conversion made by PostgreSQL. The psycopg2 module is used in Python. After the SELECT statement is executed, the original binary byte string can be obtained and binary files can be written directly.

By the way. For byte conversion, the PostgreSQL documentation describes in detail the zero-byte, single quotes, diagonal lines, and printable characters. The reason is that you need to escape single quotes and diagonal lines. In addition, printable characters can appear directly without conversion.