Статьи

Работа с типами данных PostgreSQL в базе данных распределенного SQL

Август 21, 2017

В мире баз данных типы данных ограничивают то, что можно считать допустимыми значениями в столбце таблицы. Например, если мы хотим, чтобы в столбце хранились только целочисленные значения, мы можем указать, что столбец будет столбцом типа int. Обеспечение того, какой тип данных может попасть в столбец, дает дополнительное преимущество, помогая с хранением, а в некоторых случаях и производительностью запросов.

Как правило, типы данных SQL можно широко разделить на следующие категории.

  • Числовые типы данных, такие как int, tinyint, bigint, float и real.

  • Типы данных даты и времени, такие как дата, время и дата / время.

  • Символьные и строковые типы данных, такие как char, varchar и text.

  • Типы данных строки символов Unicode, такие как nchar, nvarchar и ntext

  • Двоичные типы данных, такие как двоичные и varbinary.

  • Разные типы данных, такие как clob, blob, xml, курсор и таблица

Однако не все типы данных поддерживаются каждым поставщиком реляционных баз данных, поэтому ваш пробег может варьироваться в зависимости от используемой вами СУБД. В этом блоге мы сосредоточимся на типах данных PostgreSQL и протестируем их совместимость с YugabyteDB. Вы можете найти быстрый список поддерживаемых типов данных в наших документах здесь.

Что такое югабайт БД ? Это высокопроизводительная распределенная база данных SQL с открытым исходным кодом, построенная на масштабируемой и отказоустойчивой конструкции, созданной по мотивам Google Spanner. SQL API (YSQL) и драйверы Yugabyte совместимы с PostgreSQL.

Вам также могут понравиться: Spring Boot и PostgreSQL .

логический

Логический тип данных — это то, что PostgreSQL использует для хранения значений true, false и null. Вот допустимые логические значения в PostgreSQL:

  • True:  true, ‘t’, ‘true’, ‘y’, ‘yes’, ‘1.’

  • False: false, ‘f’, ‘false’, ‘n’, ‘no’, ‘0.’

Давайте проверим логическую совместимость в БД Yugabyte, сначала создав таблицу с логическим столбцом.

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

Далее давайте вставим в таблицу все допустимые логические значения.

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');

Наконец, давайте выберем только значения, ИСТИНА, чтобы убедиться, что они работают как положено.

SELECT
     *
FROM
     ticket_sales
WHERE
     available = 'yes';

CHAR, VARCHAR и ТЕКСТ

В PostgreSQL есть три основных типа символов (где n — положительное целое число).

  • char (n): переменная длина с ограничением.

  • varchar (n): фиксированной длины, с пробелами.

  • текст: переменная неограниченной длины.

Чтобы проверить поддержку YugabyteDB для типов символов, давайте создадим таблицу, в которой есть столбцы с указанными типами:

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

Далее, давайте загрузим данные в таблицу:

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

Вставка выше должна быть успешной. Однако, если мы запустим вставку ниже, она потерпит неудачу, потому что первые два значения слишком велики для столбцов.

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)

и

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

Целые

В PostgreSQL есть три вида целых чисел:

  • SMALLINT: 2-байтовое целое число со знаком, имеющее диапазон от -32 768 до 32 767.

  • INT: 4-байтовое целое число, имеющее диапазон от -2 147 483 648 до 2 147 483 647.

  • SERIAL: то же самое, что и целое число, за исключением того, что PostgreSQL будет автоматически генерировать и заполнять значения, подобные AUTO_INCREMENT в MySQL.

В этом примере мы создадим таблицу, которая использует эти три целочисленных типа данных, и вставим некоторые значения, чтобы убедиться, что они работают должным образом.

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);

Когда мы просматриваем данные в таблице, мы видим, что столбец album_id был правильно увеличен.

Числа с плавающей точкой

В PostgreSQL существует три основных типа чисел с плавающей точкой:

  • float (n): число с плавающей запятой, точность которого составляет не менее n до максимум 8 байтов.

  • real: 4-байтовое число с плавающей точкой.

  • числовой или числовой (p, s): является действительным числом с p цифрами и номером s после десятичной точки. Цифра (p, s) — это точное число.

В этом примере мы создадим таблицу, которая использует эти три типа данных с плавающей точкой, и вставим некоторые значения, чтобы убедиться, что они работают должным образом.

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);

Когда мы просматриваем данные в таблице, мы видим, что данные были вставлены правильно.

Временные типы данных

Временные типы данных позволяют нам хранить данные даты и / или времени. В PostgreSQL есть пять основных типов.

  • ДАТА: сохраняет только даты.

  • ВРЕМЯ: хранит значения времени суток.

  • TIMESTAMP: сохраняет значения даты и времени.

  • TIMESTAMPTZ: тип данных с отметкой времени.

  • ИНТЕРВАЛ: сохраняет интервалы времени.

Давайте посмотрим, как эти типы данных работают в 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');

Когда мы просматриваем данные в таблице, мы видим, что данные были вставлены правильно.

массив

Каждый тип данных в PostgreSQL имеет тип сопутствующего массива, например, integer имеет целое число [].

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

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 to 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);

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.

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?

Get started with Yugabyte DB on macOS, Linux, Docker, and Kubernetes.

Further Reading

86
Share: