Text Type

15min

Overview
The text data type is a UTF8-encoded text with Unicode support, which stores a sequence of characters (text).
STRING is an alias for the TEXT data type. You can create a table using STRING. However, it will be stored and processed equivalently to TEXT.
Examples
Let’s create an employee table with a text data type in each column:
Pgsql
|
CREATE TABLE employee (
    employeeName TEXT,
    employeeDept TEXT,
    employeeRole TEXT
);
INSERT INTO employee (employeeName, employeeDept, employeeRole)
VALUES ('John','Finance','Staff'),
       ('Maya','Product','Staff'),
       ('Jane','Finance','Staff'),
       ('Phil','HR','Manager');
CREATE TABLE employee (
    employeeName TEXT,
    employeeDept TEXT,
    employeeRole TEXT
);
INSERT INTO employee (employeeName, employeeDept, employeeRole)
VALUES ('John','Finance','Staff'),
       ('Maya','Product','Staff'),
       ('Jane','Finance','Staff'),
       ('Phil','HR','Manager');
﻿
Insert the text value between the single quotes ' '.
The created table is shown below:
Pgsql
|
+---------------+---------------+---------------+
| employeename  | employeedept  | employeerole  |
+---------------+---------------+---------------+
| John          | Finance       | Staff         |
| Maya          | Product       | Staff         |
| Jane          | Finance       | Staff         |
| Phil          | HR            | Manager       |
+---------------+---------------+---------------+
+---------------+---------------+---------------+
| employeename  | employeedept  | employeerole  |
+---------------+---------------+---------------+
| John          | Finance       | Staff         |
| Maya          | Product       | Staff         |
| Jane          | Finance       | Staff         |
| Phil          | HR            | Manager       |
+---------------+---------------+---------------+
﻿
Text With SUBSTR Function
The substr() function extracts a specific number of characters from a text. 
Syntax
Pgsql
|
substr( text, start_position, length )
substr( text, start_position, length )
﻿
Let’s analyze the above syntax:
text is the specified text.
start_position is used as the starting position, specifying the part from which the substring will be returned. It is written as an int value. 
length is used to determine the number of characters to be extracted. It can be one or more characters.
The first position in the text is 1.
Example
Insert a value into the text column.
Pgsql
|
SELECT substr('Watermelon',6,5) AS "Fruit";
SELECT substr('Watermelon',6,5) AS "Fruit";
﻿
The updated table is shown below:
Pgsql
|
+-------------+
| Fruit       |    
+-------------+
| melon       |
+-------------+
+-------------+
| Fruit       |    
+-------------+
| melon       |
+-------------+
﻿
Text With LENGTH Function
The length() function returns the number of characters in a text. 
The number of characters might be different from the byte length.
Syntax
The length function will take a text as a parameter.
Pgsql
|
LENGTH (text);
LENGTH (text);
﻿
Example
Insert a value into the text column.
Pgsql
|
SELECT LENGTH ('UNITED STATES');
SELECT LENGTH ('UNITED STATES');
﻿
The updated table is shown below.
Pgsql
|
+---------+
| f       |
+---------+
| 13      | 
+---------+
+---------+
| f       |
+---------+
| 13      | 
+---------+
﻿
The length() function will also count spaces.
﻿
﻿
﻿

Updated 01 Sep 2023

Did this page help you?

TABLE OF CONTENTS

Overview

Examples

Text With SUBSTR Function

Syntax

Example

Text With LENGTH Function

Syntax

Example