PostgreSQL Basic Data Types (Integer, Float, Character, Date, etc.)

Integer Types

Integer types are used to store integer values, including positive and negative integers. PostgreSQL supports the following integer types:

  • INT: The range of integer values is from -2^31 to 2^31-1.
  • SMALLINT: The range of integer values is from -2^15 to 2^15-1.
  • SERIAL: Auto-incrementing integer type, used for columns that create primary key or unique constraints.
  • BIGINT: The range of integer values is from -2^63 to 2^63-1.

Floating Point Types

Floating point types are used to store decimal numbers. PostgreSQL supports the following floating point types:

  • FLOAT: Single precision floating-point number, with a precision of 32 bits.
  • DOUBLE PRECISION: Double precision floating-point number, with a precision of 64 bits.

Character Types

Character types are used to store string data. PostgreSQL supports the following character types:

  • VARCHAR(n): Variable-length string type, capable of storing strings whose length is measured in units of n.
  • CHAR(n): Fixed-length string type, capable of storing a string consisting of n characters.
  • TEXT: Variable-length text type, capable of storing an arbitrary number of characters.

Date Types

Date types are used to store date values. PostgreSQL supports the following date types:

  • DATE: Unit time with precision in days.
  • TIMESTAMP: Timestamp including both time and date information.
  • TIMETZ: Timestamp including timezone information.

Time Types

Time types are used to store time values. PostgreSQL supports the following time types:

  • TIME: Unit time with precision of hours, minutes, and seconds.
  • HOUR: Hour value with precision in hours.
  • MINUTE: Minute value with precision in minutes.

Time Zone Types

Time zone types are used to store time values represented by the UTC timezone. PostgreSQL supports the following time zone types:

  • TIME ZONE: Timestamp and timezone information, including UTC timezone information.
  • TIMETZ: Timestamp and timezone information, including UTC timezone information.

Examples 

-- Create a table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data into the table
INSERT INTO users (name, email)
VALUES ('John Doe', 'johndoe@example.com');

Note: The above example is a basic one after PostgreSQL installation.