PostgreSQL - Data Types

PostgreSQL offers a rich set of native data types for users. Here's an overview of some of the most commonly used data types in PostgreSQL:

Numeric Types:
- Small integer (int2 or smallint): A 2-byte integer with a range of -32,768 to 32,767.
- Integer (int4 or integer): A 4-byte integer with a range of -2,147,483,648 to 2,147,483,647.
- Big integer (int8 or bigint): An 8-byte integer with a big range.
- Decimal (decimal or numeric): An exact numeric type where you can specify the total number of digits and the number of fractional digits.
- Real (float4 or real): A 4-byte floating-point number.
- Double precision (float8 or double precision): An 8-byte floating-point number.
- Serial: Auto-incrementing integer, useful for primary keys.
Character Types:
- Character (char or character[n]): A fixed-length string.
- Character varying (varchar or character varying[n]): A variable-length string, up to a specified limit.
- Text (text): A variable-length string without a specified limit.
Binary Data Types:
- Bytea: A variable-length binary string.
- BLOB (binary large object): Large binary string used to store binary data.
Date/Time Types:
- Timestamp: Date and time (without time zone).
- Timestamp with time zone: Date and time (with time zone).
- Date: Date (no time of day).
- Time: Time of day (without time zone).
- Time with time zone: Time of day (with time zone).
- Interval: Time span.
Boolean Type:
- Boolean (bool): Can hold one of the possible values: true, false, or null.
Enumerated Types:
- User-defined types consisting of a static, ordered set of values.
Geometric Types:
- Point, line, lseg, box, path, polygon, circle: Various types to represent points, lines, and shapes in 2D space.
Bit Strings:
- Bit (n), bit varying (n): Strings of binary digits.
Text Search Type:
- TSVector, TSQuery: Used for text search operations.
UUID Type:
- UUID: Universally unique identifier.
JSON Types:
- JSON, JSONB: Used to store and query JSON data. While JSON stores data as plain text, JSONB stores it in a binary format.
Array Types:
- One-dimensional arrays of any data type, including other arrays.
Composite Types:
- Represents the structure of a row or record.
OIDs (Object Identifiers):
- OID: A system data type used to store the unique identifier of rows in a database table.
Range Types:
- Represents a range of values of a specified data type.
Domain Types:
- User-defined data types based on an existing type but with constraints.
Network Address Types:
- CIDR, INET, MACADDR, MACADDR8: Types to represent IPv4, IPv6 addresses, and MAC addresses.

These are just some of the primary data types available in PostgreSQL. The database also supports the creation of custom data types using the CREATE TYPE command. Always choose the data type that best fits the nature and range of the data you're storing. This ensures optimal storage efficiency and query performance.

Numeric data types in PostgreSQL:
- Description: Represents numeric values with varying precision and scale.
- Code:
```
INTEGER, SMALLINT, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION
```
Character data types in PostgreSQL:
- Description: Stores character strings of variable or fixed length.
- Code:
```
VARCHAR(n), CHAR(n), TEXT
```
Date and time data types in PostgreSQL:
- Description: Handles date, time, and timestamp values.
- Code:
```
DATE, TIME, TIMESTAMP, INTERVAL
```
Binary data types in PostgreSQL:
- Description: Stores binary data like images or documents.
- Code:
```
BYTEA
```
Array data types in PostgreSQL:
- Description: Represents arrays of any data type.
- Code:
```
INTEGER[], VARCHAR[], TEXT[]
```
JSON and JSONB data types in PostgreSQL:
- Description: Stores JSON data in either text or binary format.
- Code:
```
JSON, JSONB
```
Geometric data types in PostgreSQL:
- Description: Represents geometric shapes and points.
- Code:
```
POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE
```
Network address data types in PostgreSQL:
- Description: Handles IP addresses and MAC addresses.
- Code:
```
INET, CIDR, MACADDR
```
Custom and composite data types in PostgreSQL:
- Description: Allows creating user-defined types and structures.
- Code:
```
CREATE TYPE custom_type AS (field1 INT, field2 VARCHAR);
```
Choosing the right data types in PostgreSQL:
- Description: Considers factors like storage size, range, and precision.
- Code:
```
-- Choose appropriate data types based on requirements
```
Working with ENUM data types in PostgreSQL:
- Description: Defines a static set of values for a column.
- Code:
```
CREATE TYPE status AS ENUM ('active', 'inactive', 'pending');
```
Handling NULL values with data types in PostgreSQL:
- Description: Data types can be nullable or not, allowing or disallowing NULL values.
- Code:
```
CREATE TABLE your_table (
    column1 INTEGER,
    column2 VARCHAR NULL,
    column3 DATE NOT NULL
);
```