Geeignete Datentypen auswählen

Choosing the correct data type for your columns in PostgreSQL is a fundamental step in designing an efficient database.

It impacts storage, query performance, and data integrity. Selecting the right type ensures your data is stored efficiently and processed quickly.

PostgreSQL offers several integer types, each with a different storage size and value range:

• SMALLINT: 2 bytes, range -32,768 to +32,767
• INTEGER (or INT): 4 bytes, range -2,147,483,648 to +2,147,483,647
• BIGINT: 8 bytes, range -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807

Always choose the smallest integer type that can safely store your expected data range to minimize disk space and improve performance.

Let's see how different integer types are defined. Notice how we pick the smallest type that fits the value.

When dealing with decimal numbers, especially monetary values, precision is key:

• NUMERIC(p, s): Stores exact numbers. p is the total number of digits (precision), s is the number of digits after the decimal point (scale). Essential for financial data.
• REAL (4 bytes) and DOUBLE PRECISION (8 bytes): Store approximate floating-point numbers. They are faster but can introduce tiny rounding errors, making them unsuitable for money.

Use NUMERIC when exactness is critical; use REAL or DOUBLE PRECISION for scientific or less critical calculations where approximation is acceptable.

For storing text strings, PostgreSQL offers:

• VARCHAR(n): Variable-length string with a user-defined maximum length n. If you try to insert a longer string, it will be truncated or an error will occur.
• TEXT: Variable-length string with no explicit maximum length. It's often the most flexible choice.
• CHAR(n): Fixed-length string. If the string is shorter than n, it's padded with spaces. Generally discouraged due to potential performance issues and space waste.

For most modern applications, VARCHAR (without a length, acting like TEXT) or simply TEXT are preferred for their flexibility and efficient storage.

Here's an example demonstrating VARCHAR with a length constraint and TEXT without one. Both store data efficiently based on actual length.

PostgreSQL provides robust date/time types:

• DATE: Stores date only (year, month, day).
• TIME: Stores time of day only (hour, minute, second, fractional seconds).
• TIMESTAMP: Stores date and time. It does NOT store timezone information.
• TIMESTAMPTZ (TIMESTAMP WITH TIME ZONE): Stores date and time, and converts it to UTC upon storage. It's the recommended type for most applications to avoid timezone headaches.

Always prefer TIMESTAMPTZ for event timestamps unless you have a very specific reason not to.

Two other useful data types:

• BOOLEAN: Stores true/false values. It's highly efficient, requiring only 1 byte of storage. PostgreSQL accepts 'true', 'false', 't', 'f', '1', '0', 'yes', 'no' as inputs.
• UUID: Stores Universally Unique Identifiers. These are 128-bit quantities, useful for generating unique primary keys without database sequence contention, especially in distributed systems.

UUIDs can be generated by PostgreSQL using functions like gen_random_uuid() from the pgcrypto extension.

While PostgreSQL often handles implicit type conversions (e.g., converting a string '123' to an integer), this comes with a performance cost.

When you compare or join columns of different data types, PostgreSQL might need to perform a conversion on one or both sides, which can prevent indexes from being used and slow down queries.

Always strive to compare and join columns that have identical data types. If conversion is necessary, use explicit casting (e.g., column::INTEGER) to make it clear and sometimes help the query planner.

Imagine you are designing a table to store user registration details. One column needs to store whether a user has verified their email, and another needs to store a unique, globally identifiable user ID that can be generated anywhere.

We've covered the importance of choosing appropriate data types for performance, storage, and integrity.

• Choose the smallest integer type that fits your data.
• Use NUMERIC for exact decimal values (like money).
• Prefer TIMESTAMPTZ for storing dates and times with timezone awareness.
• BOOLEAN is best for true/false flags, and UUID for globally unique identifiers.
• Avoid unnecessary type conversions to maintain query performance.

Careful data type selection is a cornerstone of efficient database design.