Introduction to PostgreSQL blob
PostgreSQL blob data type is defined as binary large object, basically blob data type is not available in PostgreSQL instead of blob we have using bytea data type. Blob data type in PostgreSQL is basically used to store the binary data such as content of file in PostgreSQL. The storage size of blob data type in PostgreSQL is 1 to 4 bytes plus the string of actual binary, input format of blob data type is different in PostgreSQL.
Syntax
Below is the syntax :
Create table name_of_table (name_of_column1 data_type, name_of_column2 data_type, name_of_column3 data_type, …, name_of_columnN bytea);
Name_of_variable or name_of_column bytea;
Alter table name_of_tableadd columnname_of_column bytea;
Below is the parameter description syntax of blob/bytea data type in PostgreSQL:
- Create table: This is defined as the create table in PostgreSQL by defining data type as bytea to the column. We can create any table and defining data type as bytea to the column.
- Name of table: This is defined as create table in PostgreSQL by defining data type as bytea to the column. We can define bytea data type to the column at the time of table creation. Also we are defining data type as bytea after table creation using alter command.
- Name of column 1 to name of column N: This is defined as create column on table and defined bytea data type for the same.
- Data type: This is defined as assign data type to the table column at the time of table creation. We can define data type as per which data we are storing into the table.
- blob/bytea: This is defined as data type which we have used to the column at the time of table creation. Using blob data type we are storing binary type of data or file into the table.
- Name of variable: This is nothing but column name which we have used at the time of table creation.
- Alter table: This command is used to alter table in PostgreSQL, using alter command we are changing the data type of column from one data type to other data type.
- Add column: This command is used to add new column in PostgreSQL table, using add column command we are adding new column to the table and defining data type as blob/bytea.
How blob Data Type works in PostgreSQL?
- From PostgreSQL 7.2 version we can store binary type of data into table by using the bytea data type.
- The storage size of blob data type is basically 1 to 4 bytes, but it depends on string which was used at the time of insertion.
- For inserting data into the blob column we need to first create function. After creating function we are calling the same and restoring data into the table.
- PostgreSQL is supporting the binary data type but we need to be careful while using the same.
- At the time of insertion SQL statement in blob data type column we need to have binary string with the values. Also we have to escape some characters from the binary string as per rules provided in PostgreSQL.
- Binary string in PostgreSQL are divided in character string in two ways. PostgreSQL bytea is supporting two external format for output and input.
- Output format of bytea data type depends on the configuration parameter, the default value of bytea data type parameter is hex in PostgreSQL.
- There is no any specific data type of blob available in PostgreSQL instead of blob we are using bytea data type.
Example:
Below example shows that we are using bytea data type instead of blob data type in PostgreSQL.
Code:
create table test_blob (text_blob blob, id int, address varchar, phone int);
create table test_blob (text_blob bytea, id int, address varchar, phone int);
\d+ test_blob;
Output:
- In above first example we have used blob data type at the time of table creation, but it will issue error that blob data type does not exist.
- In second example instead of blob we have used bytea data type, using bytea data type we have created table. Using bytea data type we can restore binary type data in PostgreSQL.
Examples
Given below are the examples mentioned :
Example #1
Create table by using blob/bytea data type.
- Below example shows that create table by using blob/bytea data type.
- We have defined bytea data type to blob_test column.
Code:
create table test_blob1 (blob_test bytea, id int, address varchar, phone int, name varchar);
\d+ test_blob1
Output:
Example #2
Create table by defining blob/bytea data type on multiple column.
- Below example shows that create table by using blob/bytea data type on multiple column.
- We have defined bytea data type to blob_test, blob_test1 and blob_test2 column.
Code:
create table test_blob2 (blob_test bytea, blob_test1 bytea, blob_test2 bytea,id int, address varchar, phone int, name varchar);
\d+ test_blob2
Output:
Example #3
Add new column and define data type as blob/bytea.
- Below example shows that add new column and define data type as blob/bytea.
- In below example we are adding column name as blob_test1 and defining data type as blob/bytea for the same.
Code:
Alter table test_blob1 ADD COLUMN blob_test1 bytea;
\d+ test_blob1;
Output:
Recommended Articles
This is a guide to PostgreSQL blob. Here we discuss the introduction to PostgreSQL blob, how blob data type works with respective query examples. You may also have a look at the following articles to learn more –
