PostgreSQL's integer, floating point, fixed precision numerical values ​​and sequences

PostgreSQL (PGSQL for short) is a powerful open source relational database management system that is widely used in enterprise-level applications. When modeling data and designing databases, choosing the right number type is crucial because it not only affects the storage efficiency of data, but also directly affects query performance and data accuracy. This article will explore in-depth numerical types in PostgreSQL, including integer, floating point, fixed precision numerical and sequence types, and display their usage through code examples.

Integer Types

PostgreSQL provides a variety of integer data types for storing values ​​without fractional parts. The main types include:

• SMALLINT: 2 bytes, range from -32768 to 32767.
• INTEGERorINT: 4 bytes, ranging from -2,147,483,648 to 2,147,483,647.
• BIGINT: 8 bytes, ranging from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Code Sample：

CREATE TABLE employees (  
    id SERIAL PRIMARY KEY,  
    employee_id BIGINT NOT NULL,  
    department_id SMALLINT  
);  
  
INSERT INTO employees (employee_id, department_id) VALUES (1234567890123, 10);  
  
SELECT * FROM employees;

Floating-Point Types

Floating point types are used to store numeric values ​​with decimal points, and there are two main types:

• REALorFLOAT4: 4 bytes single-precision floating point number.
• DOUBLE PRECISIONorFLOAT8: 8 byte double precision floating point number.

Code Sample：

CREATE TABLE products (  
    product_id SERIAL PRIMARY KEY,  
    price DOUBLE PRECISION NOT NULL  
);  
  
INSERT INTO products (price) VALUES (199.99);  
INSERT INTO products (price) VALUES (123456789.0123456789);  
  
SELECT * FROM products;

Fixed-Point Numeric Types

For scenarios that require high-precision computing (such as financial applications), PostgreSQL providesNUMERICandDECIMALType (both are equivalent in PostgreSQL). These types can store very precise values, including very large values ​​and very small values. When using it, you need to specify the accuracy (total number of digits) and the scale (number of digits after the decimal point).

Code Sample：

CREATE TABLE financial_transactions (  
    transaction_id SERIAL PRIMARY KEY,  
    amount NUMERIC(10, 2) NOT NULL  
);  
  
INSERT INTO financial_transactions (amount) VALUES (1234567.89);  
  
SELECT * FROM financial_transactions;

Serial Types

Sequences are a special type in PostgreSQL and are often used to automatically generate unique identifiers (such as primary keys).SERIAL、BIGSERIAL、SMALLSERIALis a shortcut to automatically growing integer fields, which use the sequence generator at the bottom.

Notice: In newer versions of PostgreSQL, it is recommended to use itIDENTITYcolumn asSERIAL、BIGSERIALetc., as it provides more flexibility and control.

Code Sample(useSERIAL）:

-- useSERIALCreate a table  
CREATE TABLE users (  
    id SERIAL PRIMARY KEY,  
    username VARCHAR(50) NOT NULL  
);  
  
-- When inserting data，No need to specifyidValue of，PostgreSQLWill be generated automatically  
INSERT INTO users (username) VALUES ('john_doe');  
  
SELECT * FROM users;

Summarize

PostgreSQL provides a rich number type to meet the needs of different application scenarios. From basic integers to high-precision numerical models to automatically generate sequences of unique values, these types of choices have a critical impact on the design, performance and accuracy of the database. By rationally selecting and applying these types, an efficient, stable and easy-to-maintain database system can be built.

This is the introduction to this article about PostgreSQL's integer, floating point, numerical and sequence types. For more information about PostgreSQL numerical types, please search for my previous articles or continue browsing the related articles below. I hope you will support me in the future!