{"id":534,"date":"2024-12-01T09:09:35","date_gmt":"2024-12-01T02:09:35","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=534"},"modified":"2025-01-02T11:26:16","modified_gmt":"2025-01-02T04:26:16","slug":"postgresql-char","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-char\/","title":{"rendered":"PostgreSQL CHAR Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the PostgreSQL CHAR<\/code> data type to store fixed-length characters in the database.<\/p>\n\n\n\n

Getting Started with the PostgreSQL CHAR type #<\/a><\/h2>\n\n\n\n

In PostgreSQL, the CHAR<\/code> type, also known as CHARACTER<\/code>, is a fixed-length and blank-padded character type.<\/p>\n\n\n\n

Here’s the syntax for defining a column with the CHAR<\/code> type:<\/p>\n\n\n

column_name<\/span> CHAR<\/span>(n)<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the column_name<\/code> will store precisely n<\/code> characters.<\/p>\n\n\n\n
If you store a string that is less than n<\/code>, PostgreSQL will pad it with spaces to meet the required length.<\/p>\n\n\n\n
For example, if you have a CHAR(5)<\/code> column and insert a string with two characters ('hi'<\/code>), PostgreSQL will pad spaces so the stored string will have a 5-character length.<\/p>\n\n\n\n
In practice, you often use the CHARACTER VARYING<\/code> (VARCHAR<\/code>) type instead of the char type for storing text strings.<\/p>\n\n\n\n
PostgreSQL CHAR example #<\/a><\/h2>\n\n\n\n
First, create a new table<\/a> called products<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> products (\n  id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  name<\/span> VARCHAR<\/span> NOT<\/span> NULL<\/span>,\n  upc CHAR<\/span>(12<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The upc<\/code> (universal product code) column in the books table has the type CHAR(12)<\/code>. It can store a string of up to 12 characters.<\/p>\n\n\n\n
Second, insert some rows into the products<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  products (name<\/span>, upc)\nVALUES<\/span>\n  ('iPhone 16 Pro'<\/span>, '194252714096'<\/span>),\n  ('iPhone 16 Pro Max'<\/span>, '194252697559'<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 id |       name        |     upc\n----+-------------------+--------------\n  1 | iPhone 16 Pro     | 194252714096\n  2 | iPhone 16 Pro Max | 194252697559<\/code><\/span><\/pre>\n\n\n
Third, insert products with UPC less than 13 characters:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  products (name<\/span>, upc)\nVALUES<\/span>\n  ('iPhone 17 Pro'<\/span>, '194252714'<\/span>),\n  ('iPhone 17 Pro Max'<\/span>, '194252697'<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 id |       name        |     upc\n----+-------------------+--------------\n  3 | iPhone 17 Pro     | 194252714\n  4 | iPhone 17 Pro Max | 194252697<\/code><\/span><\/pre>\n\n\n
PostgreSQL pads these UPC values with spaces.<\/p>\n\n\n\n
Note that if you use the length()<\/code> function, PostgreSQL internally trim the spaces before returning the number of characters:<\/p>\n\n\n
SELECT<\/span>\n  upc,\n  length (upc)\nFROM<\/span>\n  products;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
     upc      | length\n--------------+--------\n 194252714096 |     12\n 194252697559 |     12\n 194252714    |      9\n 194252697    |      9<\/code><\/span><\/pre>\n\n\n
To find the size of the stored characters, you can use the octet_length()<\/code> function, which returns the number of bytes:<\/p>\n\n\n
SELECT<\/span>\n  upc,\n  octet_length(upc)\nFROM<\/span>\n  products;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
     upc      | octet_length\n--------------+--------------<\/span>\n 194252714096<\/span> |           12<\/span>\n 194252697559<\/span> |           12<\/span>\n 194252714<\/span>    |           12<\/span>\n 194252697<\/span>    |           12<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want the upc<\/code> column to have exactly 12 characters, no more, no less, you can use a CHECK<\/code> constraint<\/a> to enforce the rule.<\/p>\n\n\n\n
Fourth, recreate the products table with the UPC column having a CHECK<\/code> constraint:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> products;\n\nCREATE<\/span> TABLE<\/span> products (\n  id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  name<\/span> VARCHAR<\/span> NOT<\/span> NULL<\/span>,\n  upc CHAR<\/span>(12<\/span>) NOT<\/span> NULL<\/span> CHECK<\/span> (length (upc) = 12<\/span>)\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, attempt to insert a value that is less than 12 into the products<\/code> table will result in an error:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  products (name<\/span>, upc)\nVALUES<\/span>\n  ('iPhone 17 Pro Max'<\/span>, '194252697'<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Error:<\/p>\n\n\n
new<\/span> row<\/span> for<\/span> relation \"products\" violates check<\/span> constraint<\/span> \"products_upc_check\"<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
If you insert<\/a> or update<\/a> a string with a length more than the specified length in the CHAR<\/code> column, PostgreSQL will issue an error. So, you limit the number of characters a CHAR<\/code> column can store as a validation rule to maintain the data integrity.<\/p>\n\n\n\n