PostgreSQL Data Types and Examples
Uploaded by
learnazurehariPostgreSQL Data Types and Examples
Uploaded by
learnazurehariCommon questions
Powered by AIThe UUID data type in PostgreSQL provides a globally unique identifier, which differs from traditional numerical identifiers by being not sequential and designed for universality across systems. Its main advantages are that it avoids conflicts in a distributed environment and enhances security by not exposing item counts as easily as sequential IDs.
PostgreSQL uses the BIGINT data type to handle large integers, which uses 8 bytes of storage. The maximum value it can store is 9,223,372,036,854,775,807.
The SERIAL data type in PostgreSQL is used for auto-incrementing integer fields. It is commonly applied in cases where unique identifiers for rows (such as primary keys) are required, allowing the database to automatically generate sequential numbers as new rows are inserted.
Conditional logic in PostgreSQL can be implemented using the CASE statement. For example, CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END classifies employee salaries as 'High' if they exceed 50,000, otherwise they are classified as 'Low'.
The UPPER() function in PostgreSQL is used to convert a given string to uppercase. For example, converting 'abc' using UPPER('abc') results in 'ABC'.
In PostgreSQL, the JSON data type stores data in a textual form, while JSONB stores data in a binary format. JSONB offers advantages in terms of performance because it allows for faster access and manipulation of JSON data due to its binary storage format.
PostgreSQL supports multi-dimensional arrays by allowing one-dimensional arrays to be nested. The array_length() function is used to determine the length of a specific dimension in an array. For example, array_length('{1,2,3}', 1) returns the size of the first dimension.
In PostgreSQL, window functions can compute a cumulative sum by using the SUM() function with an OVER clause that partitions data by department. For example, using SUM(salary) OVER(PARTITION BY dept) allows the calculation of a running total of salaries within each department without resetting across departments.
The COALESCE function in PostgreSQL is used to return the first non-null value from a list of arguments. It is useful in handling NULL values by providing a default value when actual data is missing. For example, COALESCE(NULL, 'default') would return 'default'.
The NUMERIC data type in PostgreSQL offers exact precision and is preferred for financial data to avoid rounding errors common in floating-point representations. Unlike floating-point types, which can introduce small precision errors due to binary computation, NUMERIC is designed for precise arithmetic operations.