Binary Types

Table 1 lists the M-compatible binary types.

**Table 1** Binary types
Name	Description	Storage Space
BINARY[(n)]	Fixed-length binary string. Empty characters are filled in with blank spaces. n indicates the byte length. If it is not specified, the default precision 1 is used.	Up to 255 bytes
VARBINARY(n)	Variable-length binary string. n indicates the byte length.	Up to 65532 bytes
BLOB[(n)]	Binary large object (BLOB). n indicates the optional byte length of the type. After n is specified, a column is created as the BLOB type that has the minimum length but is sufficient to hold n bytes.	Up to 65535 bytes
TINYBLOB	Binary large object (BLOB).	Up to 255 bytes
MEDIUMBLOB	Binary large object (BLOB).	Up to 16777215 bytes
LONGBLOB	Binary large object (BLOB).	Up to 1 GB minus 1 byte

If the value of sql_mode contains strict_trans_tables, an error is reported if the input value is invalid or exceeds the range.
If the value of sql_mode does not contain strict_trans_tables and the input value is invalid or exceeds the range, the truncated value is returned.
The '||' connector is not supported.

Examples

Binary types are displayed using strings.

   
    
      
      
        -- Create a table.
m_db=# CREATE TABLE blob_type_t1  
(
    BT_COL1 BINARY(10),
    BT_COL2 VARBINARY(10),
    BT_COL3 BLOB,
    BT_COL4 BLOB(10)
) DISTRIBUTE BY REPLICATION;

-- Insert data.
m_db=# INSERT INTO blob_type_t1 VALUES ('blob_test1', 'blob_test2','blob_test3','blob_test4');

-- Query data in the table.
m_db=# SELECT *  FROM blob_type_t1;
  bt_col1   |  bt_col2   |  bt_col3   |  bt_col4
------------+------------+------------+------------
 blob_test1 | blob_test2 | blob_test3 | blob_test4
(1 row)

-- Drop the table.
m_db=# DROP TABLE blob_type_t1;

-- Example: BINARY type
-- Create a table.
m_db=# CREATE TABLE blob_type_t2  
(
    BT_COL1 BINARY(5)
) DISTRIBUTE BY REPLICATION;

-- With the strict mode enabled, an error is reported if the length of an inserted string exceeds the length specified for the type.
m_db=# SET SQL_MODE = 'strict_trans_tables,only_full_group_by,no_zero_in_date,no_zero_date,error_for_division_by_zero,
no_auto_create_user,no_engine_substitution';
SET
m_db=# INSERT INTO blob_type_t2 VALUES ('too long');
ERROR:  value too long for type binary(5)
CONTEXT:  referenced column: bt_col1

-- With the strict mode disabled, the value is truncated if the length of an inserted string exceeds the length specified for the type.
m_db=# SET SQL_MODE = '';
SET
m_db=# INSERT INTO blob_type_t2 VALUES ('too long');
INSERT 0 1

-- Query data in the table.
m_db=# SELECT * FROM blob_type_t2;
 bt_col1
---------
 too l
(1 row)

-- Drop the table.
m_db=# DROP TABLE blob_type_t2;

       

     

   
  