Чем заменить number в postgresql

ORACLE NUMBER(*,0) equivalent in POSTGRESQL

I have a table in ORACLE with a column which has a data type of NUMBER(*,0) . As per my understanding it means precision will be 38 since that is the maximum limit of precision and scale value will be 0 which means no numbers will be allowed after decimal point i.e. will store integers?

I have a requirement where I have to create same table in PostgreSQL. Is it possible to write NUMERIC(*,0) in PostgreSQL? If not, what will be PostgreSQL equivalent of NUMBER(*,0 )?

Чем заменить number в postgresql

В этом разделе рассматриваются различия между языками PostgreSQL PL/pgSQL и Oracle PL/SQL , чтобы помочь разработчикам, переносящим приложения из Oracle ® в PostgreSQL .

PL/pgSQL во многих аспектах похож на PL/SQL . Это блочно-структурированный, императивный язык, в котором все переменные должны объявляться. Присваивания, циклы и условные операторы в обоих языках похожи. Основные отличия, которые необходимо иметь в виду при портировании с PL/SQL в PL/pgSQL , следующие:

Если имя, используемое в SQL-команде, может быть как именем столбца таблицы, фигурирующей в команде, так и ссылкой на переменную функции, то PL/SQL считает, что это имя столбца таблицы. Однако PL/pgSQL по умолчанию выдаёт ошибку, сообщая о такой неоднозначности. Установив plpgsql.variable_conflict = use_column , это поведение можно поменять на принятое в PL/SQL и описанное в Подразделе 43.11.1. В первую очередь, было бы правильно избегать таких двусмысленностей, но если требуется портировать большое количество кода, зависящее от данного поведения, то установка переменной variable_conflict может быть лучшим решением.

В PostgreSQL тело функции должно быть записано в виде строки. Поэтому нужно использовать знак доллара в качестве кавычек или экранировать одиночные кавычки в теле функции. (См. Подраздел 43.12.1.)

Имена типов данных часто требуют корректировки. Например, в Oracle строковые значения часто объявляются с типом varchar2 , не являющимся стандартным типом SQL. В PostgreSQL вместо него нужно использовать varchar или text . Подобным образом, тип number нужно заменять на numeric или другой числовой тип, если найдётся более подходящий.

Для группировки функций вместо пакетов используются схемы.

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

Целочисленные циклы FOR с указанием REVERSE работают по-разному. В PL/SQL значение счётчика уменьшается от второго числа к первому, в то время как в PL/pgSQL счётчик уменьшается от первого ко второму. Поэтому при портировании нужно менять местами границы цикла. Это печально, но вряд ли будет изменено. (См. Подраздел 43.6.5.5.)

Циклы FOR по запросам (не курсорам) также работают по-разному. Переменная цикла должна быть объявлена, в то время как в PL/SQL она объявляется неявно. Преимущество в том, что значения переменных доступны и после выхода из цикла.

43.13.1. Примеры портирования

Пример 43.9 показывает, как портировать простую функцию из PL/SQL в PL/pgSQL .

Пример 43.9. Портирование простой функции из PL/SQL в PL/pgSQL

Функция Oracle PL/SQL :

Пройдемся по этой функции и посмотрим различия по сравнению с PL/pgSQL :

Имя типа varchar2 нужно сменить на varchar или text . В примерах данного раздела мы будем использовать varchar , но обычно лучше выбрать text , если не требуется ограничивать длину строк.

Ключевое слово RETURN в прототипе функции (не в теле функции) заменяется на RETURNS в PostgreSQL . Кроме того, IS становится AS , и нужно добавить предложение LANGUAGE , потому что PL/pgSQL — не единственный возможный язык.

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

Вот как эта функция будет выглядеть после портирования в PostgreSQL :

Пример 43.10 показывает, как портировать функцию, которая создаёт другую функцию, и как обрабатывать проблемы с кавычками.

Пример 43.10. Портирование функции, создающей другую функцию, из PL/SQL в PL/pgSQL

Следующая процедура получает строки из SELECT и строит большую функцию, в целях эффективности возвращающую результат в операторах IF .

В конечном итоге в PostgreSQL эта функция может выглядеть так:

Обратите внимание, что тело функции строится отдельно, с использованием quote_literal для дублирования кавычек. Эта техника необходима, потому что мы не можем безопасно использовать знаки доллара при определении новой функции: мы не знаем наверняка, какие строки будут вставлены из referrer_key.key_string . (Мы предполагаем, что referrer_key.kind всегда имеет значение из списка: host , domain или url , но referrer_key.key_string может быть чем угодно, в частности, может содержать знаки доллара.) На самом деле, в этой функций есть улучшение по сравнению с оригиналом Oracle, потому что не будет генерироваться неправильный код, когда referrer_key.key_string или referrer_key.referrer_type содержат кавычки.

Пример 43.11 показывает, как портировать функцию с выходными параметрами ( OUT ) и манипулирующую строками. В PostgreSQL нет встроенной функции instr , но её можно создать, используя комбинацию других функций. В Подраздел 43.13.3 приведена реализации instr на PL/pgSQL , которая может быть полезна вам при портировании ваших функций.

Пример 43.11. Портирование из PL/SQL в PL/pgSQL процедуры, которая манипулирует строками и содержит OUT параметры

Следующая процедура на языке Oracle PL/SQL разбирает URL и возвращает составляющие его элементы (сервер, путь и запрос).

Вот возможная трансляция в PL/pgSQL :

Эту функцию можно использовать так:

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

Пример 43.12. Портирование процедуры из PL/SQL в PL/pgSQL

An introduction to PostgreSQL data types

One of the primary features of relational databases in general is the ability to define schemas or table structures that exactly specify the format of the data they will contain. This is done by prescribing the columns that these structures contain along with their data type and any constraints.

Data types specify a general pattern for the data they accept and store. Values must adhere to the requirements that they outline in order to be accepted by PostgreSQL. While it is possible to define custom requirements, data types provide the basic building blocks that allow PostgreSQL to validate input and work with the data using appropriate operations.

PostgreSQL includes a wide range of data types that are used to label and validate that values conform to appropriate types. In this guide, we will discuss the most common data types available in PostgreSQL, the different input and output formats they use, and how to configure various fields to meet your applications' needs.

What are the data types in PostgreSQL?

Before going into detail, let's take a broad view of what data types PostgreSQL provides.

PostgreSQL supports a wide range of data types suitable for various types of simple and complex data. These include:

• integer
• smallint
• bigint
• serial
• smallserial
• bigserial
• numeric
• float
• double precision
• money
• char
• varchar
• text
• boolean
• date
• time
• timestamp
• timestamptz
• interval
• enum
• uuid
• json
• jsonb
• xml
• inet (network address)
• cidr (network address)
• macaddr
• polygon
• line
• lseg (line segment)
• box (rectangular box)
• bytea (hex format)
• tsvector (text search)
• tsquery (text search)

We'll cover the most common of these in more depth throughout this guide.

Getting started with PostgreSQL data types

PostgreSQL comes with a large number of types built-in to the software itself. It also allows you to define your own complex types by combining types of different kinds and specifying their parameters. This allows administrators to precisely define the types of data they expect each column to accept when using CREATE TABLE among other commands. PostgreSQL can then automatically check proposed values to ensure they match the provided criteria.

As you get started with types, it's important to remember that types alone are not always a complete solution to data validation, but a component. Other database tools, like constraints also have a role to play in defining correctness. Still, data types are often the first line of defense against invalid data.

For many cases, the general types provided by PostgreSQL are appropriate for the kinds of data you'll be storing. However, sometimes, more specific types are available that can provide additional operators, associated functions, or built-in constraint-like validation. For example, while you could store the coordinates of a geometric point in two different number columns, the provided point type is purpose built to store and validate exactly this type of information. When choosing types, check to see that you are using the most specific type applicable to your use case.

Numbers and numeric values

PostgreSQL includes a good range of numeric data types suitable for different scenarios. These include integers, floating points, arbitrary precision, and a special integer type with additional features called serial.

The integer data type is a category of types used to store numbers without any fractions or decimals. These can be either positive or negative values, and different integer types can store different ranges of numbers. Integer types with smaller ranges of acceptable values take up space than those with wider ranges.

The basic list of integer types includes the following:

Integer type	Length	Applicable range	Comment
integer	4 bytes	-2147483648 to 2147483647	This is the most common integer type to use as it represents a good trade off between storage and expressiveness for many practical applications.
smallint	2 bytes	-32768 to 32767	It is rare to use this except in places with tight storage constraints.
bigint	8 bytes	-9223372036854775808 to 9223372036854775807	Typically this type is reserved for scenarios where the integer type would have an insufficient range.

The types above are limited by their valid range. Any value outside of the range will result in an error.

In addition to the standard integer types mentioned above, PostgreSQL includes a set of special integer types called serial types. These types are primarily used to create unique identifiers, or primary keys, for records.

By default, serial types will automatically use the next integer in an internally tracked sequence when new records are added. So if the last integer used in a serial column was 8559, by default, the next record will automatically use 8560. To guarantee that each value is unique, you can further add a UNIQUE constraint on the column.

There is a serial type for each of the integer sizes mentioned earlier, which dictates the type of integer used in the sequence:

• serial : a serial type that automatically increments a column with the next integer value.
• smallserial : a serial type that automatically increments a column with the next smallint value.
• bigserial : a serial type that automatically increments a column with the next bigint value.

Arbitrary precision types are used to control the amount of precision or specificity possible for a number with decimals. In PostgreSQL, this can be controlled by manipulating two factors: precision and scale.

Precision is the maximum amount of total digits that a number can have. In contrast, scale is the number of digits to the right of the decimal point. By manipulating these numbers, you can control how large the fractional and non-fractional components of a number are allowed to be.

These two arguments are used to control arbitrary precision using the numeric data type. The numeric type takes zero to two arguments.

With no arguments, the column can store values of any precision and scale:

When a single argument is provided, it is interpreted as the precision of the column with scale set to 0. Though not stored this way on disk, this effectively allows you to specify the maximum number of digits in an integer-like number (no fractional or decimal components). For example, if you need a 5 digit whole number, you can specify:

Specify precision followed by scale when configuring a column using both controls. PostgreSQL will round the decimal component of any input to the correct number of digits using the scale number. Afterwards, it will check whether the complete rounded number (both the whole and decimal components) exceeds the given precision number. If it does, PostgreSQL will produce an error.

For example, we can specify a column with a total precision of 5 and a scale of 2:

This column would have the following behavior:

Input value	Rounded value	Accepted (fits precision)?
400.28080	400.28	Yes
8.332799	8.33	Yes
11799.799	11799.80	No
11799	11799	Yes
2802.27	2802.27	No

Floating point numbers are another way to express decimal numbers, but without exact, consistent precision. Instead, floating point types only have a concept of a maximum precision which is often related to the architecture and platform of the hardware.

For example, to limit a floating point column to 8 digits of precision, you can type:

Because of these design choices, floating point numbers can work with numbers with large number of decimals efficiently, but not always exactly. The internal representation of numbers may cause slight differences between the input and output. This can cause unexpected behavior when comparing values, doing floating point math, or performing operations that require exact values.

Double precision (floating point) vs numeric

Both floating point numbers provided by types like float and double precision and arbitrary precision numbers provided by the numeric type can be used to store decimal values. How do you know which one to use?

The general rule is that if you need exactness in your calculations, the numeric type is always the better choice. The numeric type will store values exactly as they are provided, meaning that the results are entirely predictable when retrieving or computing over values. The numeric type is called arbitrary precision because you specify the amount of precision the type requires and it will store that exact amount of digits in the field.

In contrast, types like float and double precision are variable precision types. The amount of precision they maintain depends on the input value. When they reach the end of their allowed level of precision, they may round the remaining digits, leading to differences between the submitted and retrieved values.

So when would you use variable precision types? Variable precision types like float and double precision are well suited for scenarios where exact values are not necessary (for example, if they'll be rounded anyways) and when speed is highly valuable. Variable precision will generally offer performance benefits over the numeric type.

PostgreSQL includes a special money type that is used to store numeric values representing monetary units.

The money type does not take arguments, so the column definitions use only the type:

The money type has a fixed fractional component that takes its precision from the lc_monetary PostgreSQL localization option. If that variable is undefined, the precision is taken from the LC_MONETARY environment variable in Linux or Unix-like environments or equivalent locale settings in other operating systems. In many instances, the precision will be set to use two decimal places to match common usage.

Because of this precision, it is recommended to only use the money type when fractions of cents are not possible or important. Similarly, since no currency is attached to the type, it is not well suited for situations where currency conversions are necessary. The money type has great performance for simple use cases, however, so in spite of these constraints, it can still be valuable.

Because of the dependency on locale settings of the PostgreSQL installation or execution environment, money values, it is critical to ensure that these values match when transferring data between different systems.

Care must also be taken when casting values in and out of the money type since it can lose precision data when converting between certain types. It is safe for money values to cast to and from the numeric type (used for arbitrary precision, as shown above), so it is recommended to always use numeric as an intermediary before performing converting to other types.

Text and characters

PostgreSQL's character types and string types can be placed into two categories: fixed length and variable length. The choice between these two affects how PostgreSQL allocates space for each value and how it validates input.

The simplest character-based data type within PostgreSQL is the char type. With no arguments, the char type accepts a single character as input:

When a positive integer is provided in the declaration, the char column will store a fixed length character string equal to the number of characters specified:

If a string is provided with fewer characters, blank spaces will be appended to pad the length:

Input	# of input characters	Stored value	# of stored characters
'tree'	4	'tree '	10

If a string is given with greater than the allowed number of characters, PostgreSQL will raise an error. As an exception to this rule, if the overflowing characters are all spaces, PostgreSQL will simply truncate the excess spaces to fit the field. PostgreSQL doesn't recommend using char unless these characteristics are specifically desirable.

The alternative to fixed length character fields are variable length fields. For this, PostgreSQL provides the varchar type. The varchar type stores characters with no fixed size. By default, with no integer given, varchar columns will accept strings of any length:

By defining a varchar with a positive integer, you can set a maximum string length:

This differs from using the char type with an integer in that varchar will not pad the value if the input does not meet the maximum field length:

Input	# of input characters	Stored value	# of stored characters
'tree'	4	'tree'	4

If the string is greater than the maximum length, PostgreSQL will throw an error. The same truncation behavior that's present in char fields occurs here: if the overflowing characters are spaces, they will be truncated to fit inside the maximum character length.

The third data type that PostgreSQL provides for strings and character storage is called text . This type operates exactly like the varchar type without a maximum field length. It is used to store strings of any length:

There is no difference between these two type declarations, so they can be used interchangeably.

PostgreSQL uses the boolean or bool type to express true and false values:

In keeping with SQL standards, the PostgreSQL boolean data type can actually express three states:

• true: Represented by the SQL keyword TRUE . As input values, the following strings also evaluate to true: true, yes, on, and 1. The output function represents true values with the string "t".
• false: Represented by the SQL keyword FALSE . As input values, the following strings also evaluate to false: false, no, off, and 0. The output function represents false values with the string "f".
• unknown: Represented by the SQL keyword NULL . In the context of SQL, a NULL value in a boolean column is meant to indicate that the value is unknown.

As mentioned above, PostgreSQL is somewhat flexible on boolean input values, but stores values using the dedicated TRUE , FALSE , and NULL keywords.

Care must be taken when working with the boolean NULL . While PostgreSQL can correctly interpret TRUE and FALSE as booleans, it cannot make that assumption for NULL due to its multiple uses. You can explicitly cast NULL values to the boolean type in these situations to avoid this ambiguity:

PostgreSQL has robust support for representing dates, times, and temporal intervals.

The date type can store a date without an associated time value:

When processing input for date columns, PostgreSQL can interpret many different formats to determine the correct date to store. Some formats are based on well known standards, while others are colloquial formats used in many real world contexts.

The full range of input formats for dates that PostgreSQL understands is shown in the "Date Input" table in the PostgreSQL documentation.

To deal with ambiguous input, like 07/12/2019 (which could be interpreted as either July 12, 2019 or December 07, 2019 depending on format), you can set the expected ordering using the DateStyle parameter. This can be set to DMY , MDY , or YMD to define the expected ordering. By default, PostgreSQL will set it to MDY or use the lc_time locale to determine the appropriate ordering.

PostgreSQL can also output dates using various formats:

• ISO: Outputs dates according to ISO 8601. March 18, 2009 would be represented as 2009-03-18 .
• SQL: The traditional SQL date format. March 18, 2009 would be represented as 03/18/2009 .
• Postgres: Mirrors ISO format for dates. March 18, 2009 would be represented as 2009-03-18 .
• German: The German regional style. March 18, 2009 would be represented as 18.03.2009 .

Both the SQL and Postgres formats respect the DateStyle value, mentioned earlier, to determine the ordering of the month, day, and years in output.

The time data type (also called time without time zone ) can store a specific time of day without an associated timezone or date.

PostgreSQL does not recommend using time with time zone , the time type's variant that pairs a time zone with the clock time. This is due to complications and ambiguities that arise that cannot be resolved without additional context, like an associated date. For times that require a time zone component, the timezonetz type, covered in the next section, is a good alternative that provides the date component context.

When processing input for time columns, PostgreSQL can interpret many different formats to determine the correct time to store. Most of these are variations on the ISO 8601 standard, with flexibility to catch different variations.

The full range of input formats for times that PostgreSQL understands is shown in the "Time Input" table in the PostgreSQL documentation.

PostgreSQL can store time values with a microsecond resolution. The amount of precision can be defined when the column is created by specifying a '(p)' or precision value, which can be any integer between 0 and 6.

For example, to store time values with 3 decimal places of fractional seconds, you could define the time column like this:

If no (p) value is provided, the column will store according to the input's precision, up to 6 decimal places.

When outputting times, PostgreSQL relies on the same format definitions available for date options. These are mostly result in the same or similar outputs:

• ISO: Outputs time according to ISO 8601. 04:28 PM and 52 seconds would be represented as 16:28:52 .
• SQL: The traditional SQL time format. 04:28 PM and 52 seconds would be represented as 16:28:52.00 .
• Postgres: Uses the Unix date / time format. 04:28 PM and 52 seconds would be represented as 16:28:52 .
• German: The German regional style. 04:28 PM and 52 seconds would be represented as 16:28:52.00 .

As you can see, the output format doesn't have much of an affect on time representations as it does on dates. The main difference can be seen in the timestamp output that we'll see next.

PostgreSQL can represent timestamps, a combination of a date and time used to represent a specific moment in time, in two different variations: with and without an associated time zone. Timestamps with a specified time zone can be stored in the timestamptz data type (also known as timestamp with time zone ), while the timestamp data type (can also write as timestamp without time zone ) is used for timestamps without a time zone.

Like the time type, the timestamp and timestamptz types can take a (p) value to control the amount of precision that is stored. This can again be a number between zero and six.

To declare a timestamp column with 3 digits of precision, you could type:

To do the same with a timestamp that includes a timezone, type:

When inputting values for timestamp columns, all that is needed is a valid date format followed by a valid time format, separated by a space. PostgreSQL also recognizes the "Postgres original style" format, which is similar to the default output used by the Unix date command, but with the time zone, if present, at the end:

For timestamp columns, any provided time zone values will be ignored.

Providing values for timestamptz fields are exactly the same as for timestamp but with the addition of a time zone. Time zones can be specified in a number of different formats, which use labels, abbreviations, or offsets from UTC. The time zone indicator designation is included after the date and time in timestamps.

When storing timestamptz values, PostgreSQL converts the input to UTC for storage. This simplifies the storage since the time zone used for output may be different from the input.

When outputting timestamps, the same four formats that influence date and time can influence how PostgreSQL represents timestamp values:

• ISO: Outputs timestamps according to ISO 8601. The point in time of 04:28 PM and 52 seconds on March 18, 2009 in Eastern Standard Time would be represented as 2009-03-18 16:28:52-05 . For timestamp columns, which do not include the time zone, the -05 would be omitted. Rather than separating the date and time components with a capital 'T', as ISO 8601 defines, PostgreSQL uses a space to delimit these fields.
• SQL: The traditional SQL date format. The point in time of 04:28 PM and 52 seconds on March 18, 2009 in Eastern Standard Time would be represented as 03/18/2009 16:28:52.00 EST . For timestamp columns, which do not include the time zone, the EST would be omitted.
• Postgres: Resembles the format used by the Unix date command. The point in time of 04:28 PM and 52 seconds on March 18, 2009 in Eastern Standard Time would be represented as Wed Mar 18 16:28:52 2009 EST . For timestamp columns, which do not include the time zone, the EST would be omitted.
• German: The German regional style. The point in time of 04:28 PM and 52 seconds on March 18, 2009 in Eastern Standard Time would be represented as 18.03.2009 16:28:52.00 EST . For timestamp columns, which do not include the time zone, the EST would be omitted.

PostgreSQL can also store and work with values that represent temporal intervals. These basically describe the amount of time between two specific timestamps.

Like time , timestamp and timestamptz , the interval data type can represent time differences to the microsecond level. Again, the (p) argument is used to represent the amount of precision, or decimal places, the number will record, which can range from zero to six:

While the (p) argument affects how fractions of seconds are stored, the interval type has another argument that can alter the amount of specificity more generally. By providing one of the follow values when defining an interval column, you can control the level of detail by limiting the fields used to store interval data:

• YEAR : Store only the number of years
• MONTH : Store only the number of months
• DAY : Store only the number of days
• HOUR : Store only the number of hours
• MINUTE : Store only the number of minutes
• SECOND : Store only the number of seconds
• YEAR TO MONTH : Store only the number of years and months
• DAY TO HOUR : Store only the number of days and hours
• DAY TO MINUTE : Store only the number of days, hours, and minutes
• DAY TO SECOND : Store only the number of days, hours, minutes, and seconds
• HOUR TO MINUTE : Store only the number of hours and minutes
• HOUR TO SECOND : Store only the number of hours, minutes, and seconds
• MINUTE TO SECOND : Store only the number of minutes and seconds

When specifying both the fields to store and precision, the fields come first. Therefore, to create a column with 5 digits of precision that only stores the days, hours, minutes, and seconds of a given interval, you could type:

Be aware that you can only specify precision if your declared field ranges include the seconds value, since this is the only case where this argument matters.

There are a number of different ways to format input when adding values to an interval column. The most straightforward way is to specify the amount and the unit of each column or field you wish to provide, separated by a space. For example:

To indicate that the interval is counting in the opposite direction, add the ago keyword at the end:

The interval described above can also be represented without units by providing a day field followed by the clock units separated by colons:

Similarly, intervals that only express years and months can be represented by a year, a dash, and a month. So 38 years and 4 months would look like:

PostgreSQL also understands abbreviated input based on ISO 8601 timestamps, which can be used to represent intervals that use a greater number of fields. There are two separate input formats based on this standard.

The first uses the following unit abbreviations, represented by the bold component of the following fields:

• Years
• Months
• Weeks
• Days
• Hours
• Minutes
• Seconds

You may notice that M is used to label both months and minutes. In this format, the date component is separated from the time component by a "T". If the M appears before the T, it is interpreted as months; if it occurs after the T, it means minutes.

Formats based on ISO 8601 begin with a P and then contain the interval string. So to represent an interval of 4 years, 2 months, 7 days, 3 hours, 27 minutes, and 8 seconds, the following string would work:

The other ISO 8601 format does not use unit abbreviations at all. Instead, it separates the date components with dashes and the time components with colons. Again, the string begins with a P and separates the date and time components with a T. The same interval expressed earlier could be written as:

PostgreSQL can output values from interval columns in several formats. The output style is determined by the intervalstyle setting, which can be one of the following:

• postgres: The default style. This format uses the units years , mons , and days , each separated by a space to represent date components. It uses HH:MM:SS to represent time components.
• postgres_verbose: This format begins with an ampersand (@). It includes units or unit abbreviations for all fields, separated by spaces: years , mons , days , hours , mins , and secs .
• sql_standard: Follows the SQL standard output spec. Years and months are separated by a dash: YYYY-MM . Afterwards, day and time components are represented by an independent day field and a HH:MM:SS time field. The complete representation would look like: YYYY-MM D HH:MM:SS
• iso_8601: This style produces output with ISO 8601's "format with designators" (the first ISO 8601 style described above). Replacing the pound signs with actual values, the output would look like this: P#Y#M#DT#H#M#S

Other useful types

Along with the types we covered with some depth above, there are additional types that are useful in specific scenarios. We'll cover these briefly to give you an idea of how to use them and when they may be useful.

Does PostgreSQL support user defined types?

PostgreSQL supports user defined types in a few different ways.

PostgreSQL enumerated types are user-defined types that have a set number of valid values. This functions similar to a drop down menu in that a choice can be made from a specific set of options. For example, an enum type called season could be created with the values winter , spring , summer , and autumn .

To use an enum type as a column, you must first define it to declare its name and range of values. You can create the season type we described above by typing:

The syntax specifies the type name, the ENUM category, as well as the possible values.

Afterwards, you can define a column to be of the season type as you would any other column when creating a table:

Other user defined types

Other types can also be defined with the CREATE TYPE command. These include:

• Composite types: Composite types are types that are defined as a combination of two or more different types. For instance, you could create an event type that combines a geographical location and a timestamp to pinpoint a specific time and place.
• Range types: Range types include a valid range for a specified data type. While PostgreSQL includes some range types by default, the CREATE TYPE command allows you to create your own.
• Base types: Base types are used to define a completely new type of data that isn't reliant on modifying existing types. To do this, you'll need to code up type functions in C to show PostgreSQL how to input, output, and process the data.

Universally Unique Identifiers, or UUIDs, are 128-bit numbers used to distinctively identify pieces of information. They are used in many different contexts in order to assign a global identifier that is extremely unlikely to be assigned elsewhere. PostgreSQL includes the uuid type to work with these values:

UUIDs have 32 digits, and are generally visually separated into five groups with hyphens (8 digits, 4 digits, 4 digits, 4 digits, and finally 12 digits):

Each placeholder contains a hexadecimal digit (0 through 9, plus the lower case letters "a" through "f"). PostgreSQL uses this standard format for output.

For input, PostgreSQL understands a number of formats including using upper case letters, different digit groupings, no digit groupings, and surrounding the UUID with curly brackets.

PostgreSQL supports columns in JSON using the json and jsonb format. Data stored as json is stored as-is, while data stored with jsonb is interpreted and processed and stored in binary for faster execution and processing. PostgreSQL can also index jsonb columns for better performance. In general, jsonb is recommended for JSON data for this reason:

There are some slight differences between the two formats. The json type preserves incidental white space, key ordering, and duplicate keys. The jsonb format removes insignificant white space, overwrites duplicate keys, and provides no key ordering guarantees.

PostgreSQL includes JSON operators, can index jsonb columns, test whether jsonb objects contain other jsonb objects, and can transform values to data types used in different languages. These are outside of the scope of this guide, but will be covered in a future article on working with JSON with PostgreSQL.

We've covered many of the most common data types used in PostgreSQL in this guide. While these provide a good starting point for data types in PostgreSQL, additional types are available to store other forms of data. Using the most appropriate types for your data allows you to use the database system to validate and operate on your data easily.

Understanding data types is essential when designing schemas and tables in PostgreSQL. It also affects how to interact with the database from your applications, as the type system influences how data must be formatted and how it may be expressed when outputted. Learning about the options available within PostgreSQL and the side effects your choices might have is the best way to plan ahead when designing your data structures.

If you are using Prisma Client to work with your PostgreSQL databases, you can find a mapping between some of the common PostgreSQL and Prisma types in Prisma's PostgreSQL data connectors docs.

In the data model used by Prisma schema, data types are represented by field types. You might also want to take a look at our guide on mapping fields to a specific native type.

SQL-Ex blog

Типы данных в PostgreSQL: изучаем PostgreSQL с Grant Fritchey

Когда я решил, что следующая статья должна быть посвящена типам данных, я мало представлял себе, во что ввязываюсь. Согласно официальной документации PostgreSQL, имеется двадцать категорий типов данных. В SQL Server есть всего 35 типов данных. Я думал о том, как изучить эти типы данных и написать об этом статью. Я решил, что статья будет посвящена только категориям типов данных, а затем каким-либо интересным моментам в этих категориях. Поэтому я не собираюсь сейчас углубляться в конкретные типы данных. Это просто слишком обширная тема.

Это хорошее время, чтобы следить за процессом изучения PostgreSQL и моей способности перенести этот процесс обучения на вас. Поймите, что хотя я действительно только изучаю PostgreSQL, у меня большой опыт и знания в области реляционных структур данных. Я начинал с Paradox еще в 1980-е до профессиональной работы с Sybase, Oracle, Access и SQL Server. Поэтому, хотя я изучаю PostgreSQL с нуля, мне не нужно изучать с нуля множество концепций. Раз это так, я могу упустить информацию, которая была бы жизненно важна для вас, если это ваша первая реляционная СУБД.

Итак, что касается типов данных, я собираюсь обсудить 20 категорий и выявить некоторые интересные факты. Однако даже на этом уровне, вероятно, имеются некоторые более фундаментальные аспекты типов данных, которые вам были бы необходимы, если вы только лишь приступаете к изучению.

Один момент я бы хотел отметить, и это справедливо для каждой отдельной платформы данных, с которыми я работал, вам действительно следует использовать типы данных по их назначению, а не пытаться прикручивать их к тому, для чего они не подходят. Что я имею в виду? Я имею в виду, что строка — это строка, и она отлична от даты, которая отлична от числа. Можете вы заменить одно другим? Да. Следует ли? Нет. Придерживайтесь использования соответствующего типа данных. Это важно в PostgreSQL, так же как и повсюду.

Теперь давайте начнем.

Обзор типов данных PostgreSQL

Числовые типы

Числовые типы весьма просты. Имеется три целочисленных типа для целых чисел: smallint, integer и bigint. Два точных десятичных типа: numeric и decimal. И типы с плавающей точкой: real и double precision, которые соответствуют математическим структурам с одинарной и двойной точностью. Наконец, имеется набор числовых типов данных, называемых serial (последовательный), которые в действительности не являются типами данных. Это просто автоматически инкрементируемые целые числа, но настроенные так, чтобы вам было легко их использовать при описании таблицы. Они базируются на использовании имеющейся в PostgreSQL операции SEQUENCE (последовательность).

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

За исключением типа serial, числовые типы данных довольно просты.

Денежные типы

Ну, это заблуждение. Имеется фактически один тип money. Тут интересен тот момент, что десятичная часть в money фиксирована, но вы можете ее изменить. Это параметр уровня базы данных, который вы можете настроить в пределах заданного диапазона.

Символьные типы

Здесь я ожидал получить очень длинный список, а он закончился на трех: char, varchar и text. Тип данных char имеет фиксированную длину с дополнением. Это означает, что если вы определяете столбец как pettype char(50) и поместите туда значение cat, будет добавлено 47 пробелов. Тип данных varchar аналогичен переменному символьному типу в других системах баз данных. Столбец pettype varchar(50) и значение cat означает, что будет сохраняться только cat без дополнения. Тип данных varchar имеет переменную длину в отличие от строк фиксированной длины char (другое название для character). Можно также опустить размер типа данных varchar. В этом случае он может содержать любое число символов вплоть до 1 Гб.

Тип text предназначен для неограниченных данных до 1 Гб. Он имеет ряд недостатков, как и аналогичный тип данных в SQL Server.

Самое интересное, что нет отдельного типа данных Unicode. Вместо этого каждый из этих трех типов данных поддерживает Unicode. Вот так. Легко. Честно, мне это нравится. Я всегда считал, что VARCHAR/NVARCHAR в SQL Server доставляют неудобства.

Двоичные типы данных

Здесь тоже есть только один тип данных bytea. Кажется, я вижу, к чему это идет. Некоторые из «типов» будут просто «тип». Кто-то решил, что определять их таким способом лучше, чем просто относить их к «Другие», как тип, заполненный разнообразным типами данных.

Так или иначе, двоичная строка называется bytea. Существуют некоторые правила и поведение в отношении escape-символов, но в основном он предназначены для хранения двоичных файлов, таких как изображения, в вашей базе данных.

Типы даты/времени

Мне нравятся стандарты именования в PostgreSQL. Они делаю вещи значительно более проще по сравнению с другими системами, с которыми я работал (SQL Server). Тип данных timestamp хранит как дату, так и время. Тип данных date хранит даты, но не время. Тип данных time хранит время, но не даты. Наконец, есть тип данных interval. Тип данных interval хранит интервал времени.

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

Имеется большое число конфигурационных настроек для управления часовым поясом, специальными значениями и другим поведением. Есть также специальные функции даты и времени, а также математические операции с датой/временем.

Это один из тех типов данных, над которым часто измываются. Люди хотят иметь конкретный формат для своих дат. Вместо того, чтобы зафиксировать его на уровне сервера или приложения, они сохраняют даты в виде строк, что является большой ошибкой; это лишает их значительной функциональности. Используйте эти типы данных по их назначению.

Логический тип

Имеется один логический тип, Bool. Это включено или выключено. Это 1 или 0. И все.

Перечисляемые типы

Тоже интересно. Имеется только один перечисляемый тип, или есть бесконечное число перечисляемых типов, в зависимости от точки зрения. По сути, это enum, нумерованный список. Давайте определим один:

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

Я собираюсь потратить некоторое время, чтобы поработать с ним. Он может оказаться поразительным или же отвратительным с точки зрения производительности. Пока это неясно, но может быть и то, и другое.

Геометрические типы

Геометрические типы составляют еще одну интересную категорию. Их имеется несколько, от point (точка) до line segment (линейный сегмент), circle (окружность) и совсем замечательного, пути. У вас имеется большой выбор. Также есть несколько геометрическх функций, который работают с этими типами данных. Сюда мало что можно добавить. Хотя есть только двумерные типы. Все типы выстроены в системе координат X/Y.

Типы сетевых адресов

Это замечательно. До сих пор все рассмотренные нами типы данных были довольно стандартны для большинства систем. Конечно, с небольшими добавками, но ничего похожего на эти типы сетевых адресов. Адреса IPv4, IPv6 и MAC определяются как разные типы данных. Это гарантирует, что вы всегда будете вводить их корректно в силу проверки типа. Однако вы также получаете специальные сетевые функции, встроенные в базу данных.

Честно, это круто. И это то, что вы получаете от программного обеспечения с открытым исходным кодом. Конечно, вы могли бы, вероятно, встроить правила и пользовательские типы данных в, скажем, базу данных SQL Server для их эмуляции. Однако для этого нужно поработать. Тут могут помочь также пакеты ПО. Вместо этого имеем встроенный прямо в базу данных довольно стандартный набор потребностей компьютерщиков.

• inet: это для адресов хостов IPv4 и IPv6 с дополнительной маской сети.
• cidr: спецификация сетей IPv4 и IPv6.
• macaddr: хранит MAC-адреса, например карт ethernet и других.

Типы битовых строк

Еще один интересная категория. Имеется два типа — bit(n) и bit varying(n). Подобно обычным строкам, char или varchar, вы либо получаете дополненный столбец фиксированной длины, либо переменной, но ограниченной длины. Назначение этого типа данных — хранить и/или визуализировать битовые маски. Я не уверен, что много бы работал с ними, но хорошо знать его возможности.

Типы поиска текста

Это интересно. Есть тип данных text, упомянутый ранее как один из символьных типов. Однако очевидно, что вы не можете приложить возможности полнотекстового поиска к столбцу этого типа . Вместо этого вам нужно использовать один из двух типов поиска текста: tsvector и tsquery.

Эти понятия быстро усложняются.

tsvector состоит из списка лексем (в основном хранимых слов, но варианты одного того же слова сливаются в одно). Возьмем, например, такую фразу:

‘I do not like green eggs and ham. I do not like them Sam-I-Am’

Это будет сохранено так:

‘and’ ‘do’ ‘eggs’ ‘green’ ‘ham’ ‘I’ ‘like’ ‘not’ ‘Sam-I-Am’ ‘them’

Видно, что второй набор ‘I do not like’ был исключен, и слова упорядочены в алфавитном порядке. Это делается на заднем плане. Ну, и это становится более сложным. Есть также способы определения местоположения в пределах строк для поиска близости. Вы можете также обновлять ваши лексемы для придания им веса для изменения того, что выводить из полнотекстовых запросов, а что убрать. Затем вы переходите к функциям для работы с поиском.

Говоря о поиске, tsquery — это место, где они хранятся. Это хранит лексемы, подобные приведенным выше, и затем группирует их вместе с использованием логических операторов $ (AND), | (OR) и ! (NOT). Вы можете также управлять логикой с помощью скобок.

Я мог бы долго спускаться в эту кроличью нору. Но пока оставлю это, поскольку тут есть еще много чего.

Тип UUID

Универсальные уникальные идентификаторы (UUID), которые являются тем же, что и глобальные уникальные идентификаторы (GUID). Интересно, что система баз данных сама не генерирует UUID, а опирается на внешние модули для этого.

Типы JSON

Есть два типа json и jsonb. Между ними есть два ключевых различия, и оба на стороне jsonb. Jsonb хранится в двоичном виде, а не в виде строки, что замедляет запись и извлечение, но делает значительно более быстрой обработку поиска в JSON. Что касается поиска, то jsonb также поддерживает индексы.

Рекомендации в документации говорят, что jsonb будет лучшим выбором для большинства случаев использования. Тип данных json сохраняет все форматирование и не позволяет какое-либо переупорядочение ключей в пределах документа JSON. Зачастую я понимаю, почему для большинства людей все это не имеет значения.

Хранилище jsonb работает также с примитивными типами данных в пределах документов JSON, поэтому вы можете быть уверены, что text — это текст, numeric, как вы знаете, числа, bool — это логические значения, а значение null — это не null, а (none), потому что null означает в SQL что-то совсем другое.

Помимо индексирования, jsonb также имеет возможность определять содержимое, когда один документ JSON сохраняется в другом.

Имеется также масса функций, поддерживающих работу с JSON.

Типы массива

Документация просто говорит «Arrays» (массивы), без сочетания со словом «Type» (тип). Я его возвращаю здесь.

Честно говоря, я бы рассматривал это как эквивалент типа данных VARIANT в SQL Server. Короче, яд.

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

Документация говорит: «Синтаксис CREATE TABLE допускает задание точного размера массивов. «. После чего следует: «Однако текущая реализация игнорирует любые указанные пределы размера массива. «

Если вы помните, я говорил, что типы сетей служили примером преимуществ, которые вы можете получить от программного обеспечения с открытыми кодами. Тут я бы сказал, что эта часть функциональности является примером наказания, которое вы можете получить от программного обеспечения с открытым исходным кодом. Я понял. Мы хотим сохранять все везде в любое время любым способом, поскольку это делает вещи «проще». Это хорошо до тех пор, пока вы не поддерживаете это хозяйство, настраиваете производительность, обеспечиваете согласованность данных, наводите порядок после плохо дисциплинированных разработчиков, которые помещают массив любого желаемого размера с любым содержимым, и нет почти никаких средств, чтобы остановить их.

Послушайте. Не делайте этого.

Примеры использования массивов, которые я продолжаю находить в Интернете: «Привет, вы знаете, что можно сделать вместо нормализации ваших структур данных. » Ага, смотрите производительность. Имеются обобщенные инвертированные индексы (GIN). Они позволяют индексировать значения таким образом, чтобы внутренние компоненты были составными.

Здесь есть где разойтись. Однако остаюсь при своем мнении, особенно выполнения поиска, какие проблемы с производительностью имеют люди при использовании массивов. Они их имеют. Я нашел такое сообщение: «Почему бы вам не нормализовать данные? Это пофиксит все проблемы, включая многие проблемы, с которыми вы еще не столкнулись.»

По мере углубления в это, в какой-то момент потребуется много тестов, чтобы понять, хорошо это или плохо.

Составные типы

Это еще один странный тип.

Когда вы создаете таблицу, вы также получаете созданный в то же время составной тип. Вы можете затем использовать составной тип в качестве столбца в другой таблице. Тем самым эта другая таблица фактически получает таблицу в своем столбце. Теперь, если это звучит сильно похожим на массив, я, скорее, соглашусь. Далее, нет ограничений, даже если они определены в вашей исходной таблице, которые будут применяться к составному типу. Кроме того, вы не можете создать никаких новых ограничений для составного типа.

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

ОК. Это довольно интересно. И опять, что происходит с индексированием? Производительностью? Тут еще многое нужно изучить.

Типы диапазонов

• int4range — диапазон целых чисел (integer)
• int8range — диапазон больших целых (big integer)
• numrange — диапазон, основанный на типе numeric
• tsrange — метки времени, дата/время, но без часового пояса
• tstzrange — то же, что и выше, но с часовым поясом
• daterange — диапазон дат (date)

Типы идентификаторов объектов

• regproc — хранимые процедуры и функции
• regrole — роли
• regtype — типы данных, наша статья

Типы доменов

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

Тип pg_lsn

Я не уверен, почему они не назвали его просто типом Log Sequence Number (LSN, последовательный номер журнала). Вы можете сохранять указатели на фактический LSN в журналах. Более того, вы можете написать функции для него, например, определить LSN, который больше (новее) заданного, и другие функции, относящиеся к журналу и записанным в нем транзакциям.