PostgreSQL CHAR Type

Summary: in this tutorial, you will learn how to use the PostgreSQL CHAR data type to store fixed-length characters in the database.

Getting Started with the PostgreSQL CHAR type #

In PostgreSQL, the CHAR type, also known as CHARACTER, is a fixed-length and blank-padded character type.

Here’s the syntax for defining a column with the CHAR type:

column_name CHAR(n)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the column_name will store precisely n characters.

If you store a string that is less than n, PostgreSQL will pad it with spaces to meet the required length.

For example, if you have a CHAR(5) column and insert a string with two characters ('hi'), PostgreSQL will pad spaces so the stored string will have a 5-character length.

If you insert or update a string with a length more than the specified length in the CHAR column, PostgreSQL will issue an error. So, you limit the number of characters a CHAR column can store as a validation rule to maintain the data integrity.

In practice, you often use the CHARACTER VARYING (VARCHAR) type instead of the char type for storing text strings.

PostgreSQL CHAR example #

First, create a new table called products:

CREATE TABLE products (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR NOT NULL,
  upc CHAR(12) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The upc (universal product code) column in the books table has the type CHAR(12). It can store a string of up to 12 characters.

Second, insert some rows into the products table:

INSERT INTO
  products (name, upc)
VALUES
  ('iPhone 16 Pro', '194252714096'),
  ('iPhone 16 Pro Max', '194252697559') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 id |       name        |     upc
----+-------------------+--------------
  1 | iPhone 16 Pro     | 194252714096
  2 | iPhone 16 Pro Max | 194252697559

Third, insert products with UPC less than 13 characters:

INSERT INTO
  products (name, upc)
VALUES
  ('iPhone 17 Pro', '194252714'),
  ('iPhone 17 Pro Max', '194252697') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 id |       name        |     upc
----+-------------------+--------------
  3 | iPhone 17 Pro     | 194252714
  4 | iPhone 17 Pro Max | 194252697

PostgreSQL pads these UPC values with spaces.

Note that if you use the length() function, PostgreSQL internally trim the spaces before returning the number of characters:

SELECT
  upc,
  length (upc)
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

     upc      | length
--------------+--------
 194252714096 |     12
 194252697559 |     12
 194252714    |      9
 194252697    |      9

To find the size of the stored characters, you can use the octet_length() function, which returns the number of bytes:

SELECT
  upc,
  octet_length(upc)
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

     upc      | octet_length
--------------+--------------
 194252714096 |           12
 194252697559 |           12
 194252714    |           12
 194252697    |           12Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you want the upc column to have exactly 12 characters, no more, no less, you can use a CHECK constraint to enforce the rule.

Fourth, recreate the products table with the UPC column having a CHECK constraint:

DROP TABLE products;

CREATE TABLE products (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR NOT NULL,
  upc CHAR(12) NOT NULL CHECK (length (upc) = 12)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, attempt to insert a value that is less than 12 into the products table will result in an error:

INSERT INTO
  products (name, upc)
VALUES
  ('iPhone 17 Pro Max', '194252697');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Error:

new row for relation "products" violates check constraint "products_upc_check"Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• CHAR or CHARACTER is a fixed-length string type with padded spaces.

Quiz #