The Distributed SQL Blog

Thoughts on distributed databases, open source and cloud native

Working with PostgreSQL Data Types in YugabyteDB

VP Developer Relations

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 we’ll be focusing on PostgreSQL datatypes, and test their compatibility with YugabyteDB. You can find a quick list of supported data types on our docs here.

What’s Yugabyte DB? 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 datatype is what PostgreSQL uses to store true, false and null values. Here’s 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 Yugabyte DB by first creating a table with a boolean column.

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

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

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:

Next, let’s load data into the table:

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.

and

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.

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.

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:

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 Yugabyte DB.

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.

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:

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

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.

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:

Stay tuned for a detailed blog post that explores these special data types.

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.

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:

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

Finally we call the function:

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.

Related Posts

VP Developer Relations