In this part, we will examine about the information types utilized in PostgreSQL. While making table, for every segment, you determine an information type, i.e., what sort of information you need to store in the table fields.
This empowers a few advantages −
- Consistency − Operations against segments of same information type give steady outcomes and are generally the quickest.
- Approval − Proper utilization of information types suggests design approval of information and dismissal of information outside the extent of information type.
- Minimization − As a section can store a solitary kind of significant worth, it is put away in a reduced way.
- Execution − Proper utilization of information types gives the most effective stockpiling of information. The qualities put away can be prepared rapidly, which improves the presentation.
PostgreSQL underpins a wide arrangement of Data Types. Plus, clients can make their own custom information type utilizing CREATE TYPE SQL order. There are various classifications of information types in PostgreSQL. They are examined underneath.
Numeric Types
Numeric sorts comprise of two-byte, four-byte, and eight-byte whole numbers, four-byte and eight-byte drifting point numbers, and selectable-exactness decimals. The accompanying table records the accessible sorts.
Name | Storage Size | Description | Range |
---|---|---|---|
smallint | 2 bytes | small-range integer | -32768 to +32767 |
integer | 4 bytes | typical choice for integer | -2147483648 to +2147483647 |
bigint | 8 bytes | large-range integer | -9223372036854775808 to 9223372036854775807 |
decimal | variable | user-specified precision,exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
numeric | variable | user-specified precision,exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
real | 4 bytes | variable-precision,inexact | 6 decimal digits precision |
double precision | 8 bytes | variable-precision,inexact | 15 decimal digits precision |
smallserial | 2 bytes | small autoincrementing integer | 1 to 32767 |
serial | 4 bytes | autoincrementing integer | 1 to 2147483647 |
bigserial | 8 bytes | large autoincrementing integer | 1 to 9223372036854775807 |
Monetary Types
The cash type stores a money sum with a fixed fragmentary accuracy. Estimations of the numeric, int, and bigint information types can be cast to cash. Utilizing Floating point numbers isn't prescribed to deal with cash because of the potential for adjusting blunders.
Name | Storage Size | Description | Range |
---|---|---|---|
money | 8 bytes | currency amount | -92233720368547758.08 to +92233720368547758.07 |
Character Types
The table given beneath records the broadly useful character types accessible in PostgreSQL.
S. No. | Name & Description |
---|---|
1 |
character varying(n), varchar(n) variable-length with limit |
2 |
character(n), char(n) fixed-length, blank padded |
3 |
text variable unlimited length |
Binary Data Types
The bytea information type permits stockpiling of twofold strings as in the table given underneath.
Name | Storage Size | Description |
---|---|---|
bytea | 1 or 4 bytes plus the actual binary string | variable-length binary string |
Date/Time Types
PostgreSQL underpins a full arrangement of SQL date and time types, as demonstrated in table underneath. Dates are tallied by the Gregorian schedule. Here, all the kinds have goal of 1 microsecond/14 digits aside from date type, whose goal is day.
Name | Storage Size | Description | Low Value | High Value |
---|---|---|---|---|
timestamp [(p)] [without time zone ] | 8 bytes | both date and time (no time zone) | 4713 BC | 294276 AD |
TIMESTAMPTZ | 8 bytes | both date and time, with time zone | 4713 BC | 294276 AD |
date | 4 bytes | date (no time of day) | 4713 BC | 5874897 AD |
time [ (p)] [ without time zone ] | 8 bytes | time of day (no date) | 00:00:00 | 24:00:00 |
time [ (p)] with time zone | 12 bytes | times of day only, with time zone | 00:00:00+1459 | 24:00:00-1459 |
interval [fields ] [(p) ] | 12 bytes | time interval | -178000000 years | 178000000 years |
Boolean Type
PostgreSQL gives the standard SQL type Boolean. The Boolean information type can have the states valid, bogus, and a third state, obscure, which is addressed by the SQL invalid worth.
Name | Storage Size | Description |
---|---|---|
boolean | 1 byte | state of true or false |
Enumerated Type
Listed (enum) types are information types that involve a static, requested arrangement of qualities. They are identical to the enum types upheld in various programming dialects.
In contrast to different sorts, Enumerated Types should be made utilizing CREATE TYPE order. This sort is utilized to store a static, requested arrangement of qualities. For instance compass bearings, i.e., NORTH, SOUTH, EAST, and WEST or days of the week as demonstrated beneath −
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
Counted, once made, can be utilized like some other sorts.
Geometric Type
Mathematical information types address two-dimensional spatial items. The most key sort, the point, shapes the reason for the entirety of different kinds.
Name | Storage Size | Representation | Description |
---|---|---|---|
point | 16 bytes | Point on a plane | (x,y) |
line | 32 bytes | Infinite line (not fully implemented) | ((x1,y1),(x2,y2)) |
lseg | 32 bytes | Finite line segment | ((x1,y1),(x2,y2)) |
box | 32 bytes | Rectangular box | ((x1,y1),(x2,y2)) |
path | 16+16n bytes | Closed path (similar to polygon) | ((x1,y1),...) |
path | 16+16n bytes | Open path | [(x1,y1),...] |
polygon | 40+16n | Polygon (similar to closed path) | ((x1,y1),...) |
circle | 24 bytes | Circle | <(x,y),r> (center point and radius) |
Network Address Type
PostgreSQL offers information types to store IPv4, IPv6, and MAC addresses. It is smarter to utilize these sorts rather than plain content sorts to store network addresses, in light of the fact that these kinds offer info blunder checking and concentrated administrators and capacities.
Name Storage Size Description
cidr 7 or 19 bytes IPv4 and IPv6 networks
inet 7 or 19 bytes IPv4 and IPv6 hosts and networks
macaddr 6 bytes MAC addresses
Bit String Type
Touch String Types are utilized to store bit veils. They are either 0 or 1. There are two SQL bit types: bit(n) and bit varying(n), where n is a positive whole number.
Text Search Type
This sort underpins full content pursuit, which is the movement of looking through an assortment of common language records to find those that best match an inquiry. There are two Data Types for this −
S. No. | Name & Description |
---|---|
1 |
tsvector This is a sorted list of distinct words that have been normalized to merge different variants of the same word, called as "lexemes". |
2 |
tsquery This stores lexemes that are to be searched for, and combines them honoring the Boolean operators & (AND), | (OR), and ! (NOT). Parentheses can be used to enforce grouping of the operators. |
UUID Type
A UUID (Universally Unique Identifiers) is composed as a succession of lower-case hexadecimal digits, in a few gatherings isolated by hyphens, explicitly a gathering of eight digits, trailed by three gatherings of four digits, trailed by a gathering of 12 digits, for a sum of 32 digits addressing the 128 pieces.
An illustration of a UUID is − 550e8400-e29b-41d4-a716-446655440000
XML Type
The XML information type can be utilized to store XML information. For putting away XML information, first you need to make XML esteems utilizing the capacity xmlparse as follows −
XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
<topics>...</topics>
</tutorial>')
XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')
JSON Type
The json information type can be utilized to store JSON (JavaScript Object Notation) information. Such information can likewise be put away as text, yet the json information type has the benefit of watching that each put away worth is a legitimate JSON esteem. There are additionally related help capacities accessible, which can be utilized straightforwardly to deal with JSON information type as follows.
Example Example Result
array_to_json('{{1,5},{99,100}}'::int[]) [[1,5],[99,100]]
row_to_json(row(1,'foo')) {"f1":1,"f2":"foo"}
Array Type
PostgreSQL offers the chance to characterize a section of a table as a variable length multidimensional exhibit. Varieties of any inherent or client characterized base sort, enum type, or composite sort can be made.
Declaration of Arrays
Cluster type can be pronounced as
CREATE TABLE monthly_savings (
name text,
saving_per_quarter integer[],
scheme text[][]
);
or on the other hand by utilizing the watchword "Exhibit" as
CREATE TABLE monthly_savings (
name text,
saving_per_quarter integer ARRAY[4],
scheme text[][]
);
Inserting values
Cluster esteems can be embedded as a strict consistent, encasing the component esteems inside wavy supports and isolating them by commas. A model is appeared beneath −
INSERT INTO monthly_savings
VALUES (‘Manisha’,
‘{20000, 14600, 23500, 13250}’,
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);
Accessing Arrays
A model for getting to Arrays is appeared beneath. The order given beneath will choose the people whose reserve funds are more in second quarter than final quarter.
SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];
Modifying Arrays
An illustration of adjusting exhibits is as demonstrated beneath.
UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';
or then again utilizing the ARRAY articulation linguistic structure −
UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';
Searching Arrays
An illustration of looking through exhibits is as demonstrated underneath.
SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;
On the off chance that the size of exhibit is known, the hunt strategy given above can be utilized. Else, the accompanying model tells the best way to look through when the size isn't known.
SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);
Composite Types
This sort addresses a rundown of field names and their information types, i.e., design of a column or record of a table.
Declaration of Composite Types
The accompanying model tells the best way to pronounce a composite kind
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);
This information type can be utilized in the make tables as beneath −
CREATE TABLE on_hand (
item inventory_item,
count integer
);
Composite Value Input
Composite qualities can be embedded as a strict steady, encasing the field esteems inside brackets and isolating them by commas. A model is appeared beneath −
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
This is legitimate for the inventory_item characterized previously. The ROW catchphrase is really discretionary as long as you have more than one field in the articulation.
Accessing Composite Types
To get to a field of a composite segment, utilize a speck followed by the field name, similar as choosing a field from a table name. For instance, to choose some subfields from our on_hand model table, the question would be as demonstrated beneath −
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
You can even utilize the table name too (for example in a multitable question), this way −
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
Range Types
Reach types address information types that utilizes a scope of information. Reach type can be discrete reaches (e.g., all number qualities 1 to 10) or nonstop ranges (e.g., any point in time somewhere in the range of 10:00am and 11:00am).
The implicit reach types accessible incorporate the accompanying reaches −
- int4range − Range of whole number
- int8range − Range of bigint
- numrange − Range of numeric
- tsrange − Range of timestamp without time region
- tstzrange − Range of timestamp with time region
- daterange − Range of date
Custom reach types can be made to make new sorts of reaches accessible, for example, IP address ranges utilizing the inet type as a base, or buoy ranges utilizing the buoy information type as a base.
Reach types uphold comprehensive and elite reach limits utilizing the [ ] and ( ) characters, separately. For instance '[4,9)' addresses all the numbers beginning from and including 4 up to yet excluding 9.
Object Identifier Types
Item identifiers (OIDs) are utilized inside by PostgreSQL as essential keys for different framework tables. In the event that WITH OIDS is indicated or default_with_oids setup variable is empowered, really at that time, in such cases OIDs are added to client made tables. The accompanying table records a few nom de plume types. The OID moniker types have no activities of their own aside from particular info and yield schedules.
Name | References | Description | Value Example |
---|---|---|---|
oid | any | numeric object identifier | 564182 |
regproc | pg_proc | function name | sum |
regprocedure | pg_proc | function with argument types | sum(int4) |
regoper | pg_operator | operator name | + |
regoperator | pg_operator | operator with argument types | *(integer,integer) or -(NONE,integer) |
regclass | pg_class | relation name | pg_type |
regtype | pg_type | data type name | integer |
regconfig | pg_ts_config | text search configuration | English |
regdictionary | pg_ts_dict | text search dictionary | simple |
Pseudo Types
The PostgreSQL type framework contains various unique reason passages that are all things considered called pseudo-types. A pseudo-type can't be utilized as a segment information type, however it tends to be utilized to proclaim a capacity's contention or result type.
The table given beneath records the current pseudo-types.
S. No. | Name & Description |
---|---|
1 |
any Indicates that a function accepts any input data type. |
2 |
anyelement Indicates that a function accepts any data type. |
3 |
anyarray Indicates that a function accepts any array data type. |
4 |
anynonarray Indicates that a function accepts any non-array data type. |
5 |
anyenum Indicates that a function accepts any enum data type. |
6 |
anyrange Indicates that a function accepts any range data type. |
7 |
cstring Indicates that a function accepts or returns a null-terminated C string. |
8 |
internal Indicates that a function accepts or returns a server-internal data type. |
9 |
language_handler A procedural language call handler is declared to return language_handler. |
10 |
fdw_handler A foreign-data wrapper handler is declared to return fdw_handler. |
11 |
record Identifies a function returning an unspecified row type. |
12 |
trigger A trigger function is declared to return trigger. |
13 |
void Indicates that a function returns no value. |