Cómo agregar datos de objetos grandes de tipo bytea a postgreSQL

Si desea almacenar datos binarios en PostgreSQL, como Word, documentos de Excel, archivos de imágenes, etc., puede utilizar columnas de tipo bytea. El tipo bytea es un tipo de campo específico de PostgreSQL para almacenar datos binarios. Es similar a los tipos BLOB y BINARY

LARGE OBJECT en el estándar SQL. Esto se explica en la introducción de la documentación de PostgreSQL al tipo bytea.

A continuación, hablemos sobre cómo insertar y actualizar datos de bytea en la tabla.

PostgreSQL le permite incluir datos de tipo bytea en comandos SQL, de modo que puede usar INSERT para insertar registros que contienen datos binarios en la tabla, usar UPDATE y llamar a funciones relacionadas con el tipo bytea para actualizar y operar bytea. escriba datos. Los datos binarios son una secuencia de bytes, pero los comandos SQL son cadenas de texto. ¿Cómo escribir datos binarios en SQL? La respuesta es muy simple. Convierta cada byte en la representación de cadena de número octal correspondiente de tres dígitos decimales, con una barra doble como prefijo, es decir, 0x00 se representa como \\000, 0x2C se representa como \\02C y 0xFF. se representa como \\377 e indica E fuera de las comillas simples al principio de la cadena de acuerdo con los requisitos del tipo bytea. Por ejemplo:

INSERT INTO table1 (id de archivo, nombre de archivo, contenido) VALUES (1, 'nombre de archivo.doc', E'\\000\\001\\002');

INSERTAR EN tabla1 (id de archivo, nombre de archivo, contenido) VALORES (2, 'otro archivo.jpg', E'\\000\\377');

ACTUALIZAR tabla1 SET contenido = E'\\000\ \000\\000' WHERE fileid

= 1;

ACTUALIZAR tabla1 SET contenido = contenido || E'\\377\\377\\377' WHERE fileid

= 2;

Puedes incluir la cadena de tipo bytea de todo el archivo en INSERT

INTO, o puedes agregarlo en fragmentos como la cuarta línea anterior. Para datos binarios breves, no es mala idea editar comandos SQL en la consola de comandos. Pero si desea almacenar datos binarios grandes, como un archivo de imagen o un documento de Word, debe utilizar una interfaz de acceso a datos o escribir usted mismo un programa de conversión de bytes para operar directamente la declaración SQL.

Después de insertar datos de bytea, puede utilizar la instrucción SELECT para obtenerlos. Como se muestra a continuación:

SELECCIONE el contenido DE la tabla1;

En la consola de comandos, veremos que los datos binarios se generan en formato de cadena cuando se ingresan. Esta es la conversión realizada por PostgreSQL. . Usando el módulo psycopg2 en Python, después de ejecutar la instrucción SELECT anterior, se puede obtener la cadena de bytes binarios original, que se puede escribir directamente en el archivo binario.

Por cierto. Con respecto a la conversión de bytes, la documentación de PostgreSQL es muy detallada y analiza los bytes cero, las comillas simples, las barras diagonales y la capacidad de impresión de los caracteres. La razón es que es necesario utilizar caracteres de escape entre comillas simples y barras, y los caracteres imprimibles pueden aparecer directamente sin conversión.