Exploring Alternatives to Integer Data Types in PostgreSQL

• Common Choice
  Because of its balanced storage and value range, integer is the default choice for storing whole numbers in PostgreSQL unless you have a specific reason to use a different data type.
• Value Range
  It can store numbers from -2,147,483,648 to 2,147,483,647.
• Storage Size
  An integer takes up 4 bytes of space in your database.

Here are some additional points to consider:

• Alternative Data Types
  PostgreSQL offers other integer data types like smallint (for smaller ranges) and bigint (for much larger ranges). You can choose the one that best suits the specific needs of your data.
• SQL Standards
  integer (or int) is a standard data type across many SQL database systems, so your code will likely be portable if you use it.

---

Creating a Table with an Integer Column

CREATE TABLE products (
  id SERIAL PRIMARY KEY,  -- Auto-incrementing integer for product ID
  name VARCHAR(255) NOT NULL,
  price INTEGER NOT NULL,
  stock_count INTEGER DEFAULT 0 -- Default value for stock
);

This code creates a table named products with three columns:

• stock_count: This column also uses integer to represent the number of items in stock, with a default value of 0.
• price: This column uses the integer data type to store the product price as a whole number.
• name: This stores the product name as text with a maximum length of 255 characters.
• id: This is an auto-incrementing integer that serves as the primary key for the table. The SERIAL keyword ensures a unique integer is generated for each new product.

Inserting Data with Integer Values

INSERT INTO products (name, price, stock_count)
VALUES ('T-Shirt', 20, 50), ('Coffee Mug', 15, 25);

This code inserts two rows into the products table. The price and stock_count columns use integer values for the product price and stock levels.

Selecting Data with Integer Operations

SELECT * FROM products
WHERE price > 18;

This code retrieves all products from the products table where the price (stored as an integer) is greater than 18.

Updating Data with Integer Values

UPDATE products
SET stock_count = stock_count - 10
WHERE id = 1;

This code updates the stock_count for the product with id 1 by subtracting 10 (an integer value) from the current stock level.

---

1. Smallint
   This data type is ideal for storing whole numbers within a smaller range compared to integer. It takes up only 2 bytes of space and can hold values from -32,768 to 32,767. Use smallint when you are certain the data will always fall within this range and storage efficiency is a top priority.

2. Bigint
   On the other hand, if you anticipate needing to store much larger whole numbers than what integer can handle, then bigint is the way to go. It uses 8 bytes and offers a range from -9223372036854775808 to 9223372036854775807. Choose bigint when dealing with very large datasets or expecting values outside the integer range.

3. Numeric
   This data type provides high precision for decimal values. While it can also store whole numbers, it's specifically designed for calculations involving decimals. It offers more flexibility than integer but takes up more space. Use numeric if precise decimal calculations are crucial for your data.

Data Type	Storage Size	Value Range	Use Case
integer	4 bytes	-2,147,483,648 to 2,147,483,647	General purpose for whole numbers
smallint	2 bytes	-32,768 to 32,767	Small range of whole numbers, storage efficiency needed
bigint	8 bytes	-9223372036854775808 to 9223372036854775807	Very large whole numbers
numeric	Varies	Depends on configuration	High-precision decimal calculations