Working with PostgreSQL Data Types in YugabyteDB

In the world of databases, data types restrict what can be considered as valid values in a table’s column. For example, if we want a column to store only integer values, we can specify that the column be an int column. Enforcing what type of data can go into a column has the added benefit of helping with storage and in some cases, query performance.

Generically, SQL data types can be broadly divided into following categories.

• Numeric data types such as int, tinyint, bigint, float, and real
• Date and Time data types such as date, time, and datetime
• Character and String data types such as char, varchar, and text
• Unicode character string data types such as nchar, nvarchar, and ntext
• Binary data types such as binary and varbinary
• Miscellaneous data types such as clob, blob, xml, cursor, and table

However, not all data types are supported by every relational database vendor, so your mileage may vary depending on the RDBMS you use. In this blog post we’ll be focusing on PostgreSQL data types, and test their compatibility with YugabyteDB. You can find a quick list of supported data types in our docs.

What’s YugabyteDB? It is an open source, high-performance distributed SQL database built on a scalable and fault-tolerant design inspired by Google Spanner. Yugabyte’s SQL API (YSQL) and drivers are PostgreSQL wire compatible.

Boolean

The boolean data type is what PostgreSQL uses to store true, false, and null values. Here are the accepted boolean values in PostgreSQL:

• True: true, ‘t’, ‘true’, ‘y’, ‘yes’, ‘1’
• False: false, ‘f’, ‘false’, ‘n’, ‘no’, ‘0’

Let’s test boolean compatibility in YugabyteDB by first creating a table with a boolean column.

CREATE TABLE ticket_sales (
concert_id INT NOT NULL PRIMARY KEY,
available BOOLEAN NOT NULL
);

Next, let’s insert into the table all the acceptable boolean values.

INSERT INTO ticket_sales (concert_id, available)
VALUES
(100, TRUE),
(101, FALSE),
(102, 't'),
(103, 'f'),
(104, 'true'),
(105, 'false'),
(106, 'y'),
(107, 'n'),
(108, 'yes'),
(109, 'no'),
(110, '1'),
(111, '0');

Finally, let’s select out just the values that are TRUE, to verify it works as expected.

SELECT
*
FROM
ticket_sales
WHERE
available = 'yes';

CHAR, VARCHAR, and TEXT

In PostgreSQL there are three primary character types (where n is a positive integer.)

• varchar(n): variable-length with limit
• char(n): fixed-length, blank padded
• text, varchar: variable unlimited length

To test YugabyteDB’s support for character types, let’s create a table that has columns with these types specified:

CREATE TABLE char_types (
id serial PRIMARY KEY,
a CHAR (4),
b VARCHAR (16),
c TEXT
);

Next, let’s load data into the table:

INSERT INTO char_types (a, b, c)
VALUES
(
'four',
'Test varchar',
'This is a test data for the text column'
);

The insert above should succeed. However, if we run the insert below, it will fail because the first two values are too big for the columns.

INSERT INTO char_types (a, b, c)
VALUES
   (
      'This char test data',
      'This is varchar test data ',
      'This is a test data for the text column'
   );

SQL Error [22001]: ERROR: value too long for type character(4)

and

SQL Error [22001]: ERROR: value too long for type character varying(16)

Integers

There are three kinds of integers in PostgreSQL:

• SMALLINT: a 2-byte signed integer that has a range from -32,768 to 32,767.
• INT: a 4-byte integer that has a range from -2,147,483,648 to 2,147,483,647.
• SERIAL: Same as an integer except that PostgreSQL will automatically generate and populate values similar to AUTO_INCREMENT in MySQL.

In this example we’ll create a table that makes use of these three integer data types and insert some values to make sure they work as expected.

CREATE TABLE albums (
    album_id SERIAL PRIMARY KEY,
    title VARCHAR (255) NOT NULL,
    play_time SMALLINT NOT NULL,
    library_record INT NOT NULL
);

INSERT INTO albums
values (default,'Funhouse', 3600,2146483645 ),
(default,'Darkside of the Moon', 4200, 214648348);

When we view the data in the table, we can see that the album_id column has been correctly incremented.

Floating-Point Number

In PostgreSQL there are three main types of floating-point numbers:

• float(n): is a floating-point number whose precision is at least, n, up to a maximum of 8 bytes
• real: is a 4-byte floating-point number
• numeric or numeric(p,s): is a real number with p digits with s number after the decimal point. The numeric(p,s) is the exact number

In this example we’ll create a table that makes use of these three floating-point data types and insert some values to make sure they work as expected.

CREATE TABLE floating_point_test (
    floatn_test float8 not NULL,
    real_test real NOT NULL,
    numeric_test NUMERIC (3, 2)
);

INSERT INTO floating_point_test (floatn_test, real_test, numeric_test)
VALUES
    (9223372036854775807, 2147483647, 5.36), 
    (9223372036854775800, 2147483640, 9.99);

When we view the data in the table, we can see that the data has been correctly inserted.

Temporal Data Types

Temporal data types allow us to store date and/or time data. There are five main types in PostgreSQL.

• DATE: stores the dates only
• TIME: stores the time of day values
• TIMESTAMP: stores both date and time values
• TIMESTAMPTZ: is a timezone-aware timestamp data type
• INTERVAL: stores intervals of time

Let’s take a look and see how these data types work in YugabyteDB:

CREATE TABLE temporal_types (
   	date_type DATE,
	time_type TIME,
	timestamp_type TIMESTAMP,
	timestampz_type TIMESTAMPTZ,
	interval_type INTERVAL
);

INSERT INTO temporal_types (
   	date_type,
	time_type,
	timestamp_type,
	timestampz_type,
	interval_type)
VALUES
	('2000-06-28', '06:23:00', '2016-06-22 19:10:25-07', '2016-06-22 19:10:25-07', '1 year'),
	('2010-06-28', '12:32:12','2016-06-22 19:10:25-07', '2016-06-22 19:10:25-07', '10 years 3 months 5 days');

When we view the data in the table, we can see that the data has been correctly inserted.

Array

Every data type in PostgreSQL has a companion array type for example integer has integer[ ].

Let’s look to see if the examples from the PostgreSQL documentation will work as advertised in YugabyteDB.

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

Now, let’s select the data out from the sal_emp table.

JSON

PostgreSQL provides two JSON data types: JSON and JSONB for storing JSON data. The JSON data type stores plain JSON data that requires reparsing for each processing, while JSONB data type stores JSON data in a binary format which is faster to process but slower to insert. In addition, JSONB supports indexing. Let’s create a table with JSON types in YugabyteDB and insert some JSON data.

CREATE TABLE json_table (
 	json_type JSON,
	jsonb_type JSONB
);

INSERT INTO json_table
VALUES
('{"title": "Influence","genres": ["Marketing & Sales","Self-Help","Psychology"],"published": true}', '{"title": "Sleeping Beauties","genres": ["Fiction","Thriller","Horror"],"published": false}');

Now, let’s select the data out.

UUID

The UUID (Universally Unique Identifier) data type guarantees better uniqueness than SERIAL and can also be used to hide sensitive data. There are two ways to get started with PostgreSQL UUIDs in YugabyteDB.

Pgcrypto

The pgcrypto module provides cryptographic functions for PostgreSQL, including the ability to create UUIDs. First we’ll need to create the extension:

SELECT gen_random_uuid();

We are now ready to generate UUIDs using the following command:

SELECT gen_random_uuid();

UUID-OSSP

The second option is to utilize the uuid-ossp extension. Getting this extension working requires the following steps:

• Create an alias
• Copy the required files from a default PostgreSQL install over the the appropriate Yugabyte DB directory
• Issue a CREATE EXTENSION command
• Generate the UUIDs

For detailed instructions on installing extensions check this doc section and for UUID specifically, check out this doc section.

SELECT uuid_generate_v1(), uuid_generate_v4(), uuid_nil();

Special Data Types

PostgreSQL provides several special data types useful when working with geometric and networking data.

• Box: a rectangular box.
• Line: a set of points.
• Point: a geometric pair of numbers.
• Lseg: a line segment.
• Polygon: a closed geometric.
• Inet: an IP4 address.
• Macaddr: a MAC address.

You can specify these each data types in YugabyteDB using an example like the one shown below:

CREATE table special_types (
point_type POINT,
line_type LINE,
lseg_type Lseg,
box_type BOX,
path_type PATH,
polygon_type polygon,
circle CIRCLE,
inet_type INET,
macaddr_type MACADDR);

User Defined Data Types

PostgreSQL also permits the creation of user defined data types. These data types are created using the CREATE DOMAIN and CREATE TYPE commands. The CREATE DOMAIN statement creates a user-defined data type with constraints such as NOT NULL and CHECK. In the example below we use a CHECK constraint to ensure values are not null and also do not contain a space.

CREATE DOMAIN customer_name AS 
    VARCHAR NOT NULL CHECK (value !~ '\s');

CREATE TABLE customer_list (
    id serial PRIMARY KEY,
    first_name contact_name,
    last_name contact_name,
    email VARCHAR NOT NULL
);

Meanwhile The CREATE TYPE statement allows you to create a composite type, which can be used as the return type of a function. For example:

CREATE TYPE book_summary AS (
    book_id INT,
    title VARCHAR,
    publish_year DATE
); 

Next we create a book table, insert some data, and use the book_summary data type as a return type of a function.

CREATE TABLE book(
    book_id INT,
    title VARCHAR,
    publish_year DATE
); 

INSERT into book
values (
    2, 'Infinite Jest', '1998-06-06'
); 

CREATE OR REPLACE FUNCTION get_book_summary (f_id INT) 
    RETURNS book_summary AS 
$ 
SELECT
    book_id,
    title,
    publish_year
FROM
    book
WHERE
    book_id = f_id ; 
$ 
LANGUAGE SQL;

Finally we call the function:

SELECT * FROM
    get_book_summary (2);

What’s Next?

• Compare Yugabyte DB in depth to databases like CockroachDB, Google Cloud Spanner, and MongoDB.
• Get started with Yugabyte DB on macOS, Linux, Docker, and Kubernetes.
• Contact us to learn more about licensing, pricing, or to schedule a technical overview.