How to add bytea type large object data to postgreSQL

If you want to store binary data in PostgreSQL, such as Word, Excel documents, picture files, etc., you can use bytea type columns. The bytea type is a PostgreSQL-specific field type for storing binary data. It is similar to the BLOB and BINARY

LARGE OBJECT types in the SQL standard. This is explained in the introduction to the bytea type in the PostgreSQL documentation.

Next, let’s talk about how to insert and update bytea data into the table.

PostgreSQL allows you to include bytea type data in SQL commands, 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 bytea type data. Binary data is a byte sequence, but SQL commands are text strings. How to write binary data in SQL? The answer is very simple. Convert each byte into the corresponding octal number string representation of three decimal digits, with a double slash as the prefix, that is, 0x00 is represented as \\000, 0x2C is represented as \\02C, and 0xFF is represented as \\377, and indicate E outside the single quotes at the front of the string according to the requirements of the bytea type. For 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\\377' WHERE fileid

= 2;

You can include the bytea type string of the entire file in INSERT

INTO, or you can append it in chunks like the fourth line above. For short binary data, it is not a bad idea to edit SQL commands in the command console. But if you want to store large binary data such as a picture file or Word document, you need to use a data access interface, or write a byte conversion program yourself to directly operate the SQL statement.

After inserting bytea data, you can use the SELECT statement to obtain it. As shown below:

SELECT content FROM table1;

In the command console, we will see that binary data is output in the string format when input. This is the conversion done by PostgreSQL. Using the psycopg2 module in Python, after executing the above SELECT statement, the original binary byte string can be obtained, which can be written directly to the binary file.

By the way. Regarding byte conversion, the PostgreSQL documentation is very detailed and discusses zero bytes, single quotes, slashes, and the printability of characters. The reason is that single quotes and slash characters need to be escaped, and printable characters can appear directly without conversion.