PostgreSQL — Hierarchy
In PostgreSQL, a server consists of multiple databases, each database contains multiple schemas, and each schema is a collection of…
In PostgreSQL, a server consists of multiple databases, each database contains multiple schemas, and each schema is a collection of various database objects. The Image 1 lists only a few of these objects, but there are others as well, such as triggers and indexes. Now let’s dive deep into what a database and schema are and also look into some commonly used database objects.
Database
A database is an organized collection of structured data, typically stored electronically in a computer system, designed for easy access, management, and updating. In the context of PostgreSQL, a database is a structured repository of information that can include multiple schemas.
Schema
A schema in PostgreSQL is a named collection of logical structures of data, including tables, views, functions, constraints, indexes, sequences, data types, and operators. PostgreSQL allows multiple schemas within a single database, enabling you to organize different features separately. The same object name can exist in different schemas without conflict, such as having a table named “table1” in both “schema1” and “schema2.” Unlike databases, schemas are not strictly isolated; users can access objects across schemas within the same database, provided they have the necessary privileges.
Table — View — Materialized View
Tables: Tables store data in an organized format using rows and columns. Each column has a name and data type, and each row holds the actual data. Data in tables is stored physically, and you can insert, update, or delete this data.
Views: Views are saved queries that make accessing the results of common queries easier. They represent the result of a query on one or more tables. Views simplify complex queries by letting you define them once and query the view directly. When you query a view, it runs the stored query and shows the result. The data isn’t stored in the view itself; only the query is.
Materialized Views: Materialized views store the result of a query physically, making complex queries faster. Unlike regular views, they do not automatically update with the latest data from the underlying tables. You need to refresh materialized views to update the data. When queried, materialized views return the stored result from the most recent refresh.
In summary:
Use tables for direct data storage and manipulation.
Use views to simplify and reuse complex queries without storing data.
Use materialized views for faster access to complex query results that don’t need to be updated in real-time.
Function — Stored Procedure
Functions: Named pieces of code that take input, perform actions and return a result. They are used to get data and are called with a SELECT statement.
Stored Procedures: Routines that execute a set of SQL statements to perform tasks. They do not return results directly and are called with a CALL statement. They can manage transactions within their execution.
In PostgreSQL, the key differences between functions and stored procedures are:
Return Values: Functions can return result sets, including tables and cursors, while stored procedures do not return results.
Transaction Control: Functions cannot manage transactions directly but participate in the broader transaction. Stored procedures can commit or roll back transactions.
Invocation: Functions are called with a SELECT statement, whereas stored procedures are called with a CALL statement.
Index
In PostgreSQL, an index is like a specialized data structure that helps you find and retrieve data from a table more quickly. Think of it like the index in a book, where you look up a topic to find the pages where it’s discussed. Similarly, an index in a database helps you quickly locate the information you need without having to search through the entire table.
The default type of index in PostgreSQL is called a B-Tree index. This kind of index arranges data in a way that makes searching and sorting fast and efficient, similar to how an alphabetical list makes finding names in a contact list easy.
Constraint
In PostgreSQL, constraints are rules applied to columns in a database table to ensure data integrity and consistency. They define conditions that data must meet before being entered into the database, helping prevent invalid or incorrect data from being stored. Here are the main types of constraints you can use:
NOT NULL: Ensures that a column cannot have a null value. Every row must include a value for this column.
UNIQUE: Ensures that all values in a column or a set of columns are unique across the table. No two rows can have the same value in this column or combination of columns.
PRIMARY KEY: A combination of NOT NULL and UNIQUE constraints. It uniquely identifies each row in a table and ensures that the column(s) used as the primary key are both unique and not null.
FOREIGN KEY: Ensures referential integrity between two tables. It requires that a value in a column or set of columns matches a value in the primary key or a unique key of another table.
CHECK: Allows you to specify a condition that must be true for any value in a column. For example, it can ensure that values fall within a specific range.
DEFAULT: Provides a default value for a column when no value is specified during an insert operation, ensuring that the column always has a value.
NULL: Allows a column to accept null values, though this is usually the default behavior and not commonly specified explicitly.
By applying these constraints, PostgreSQL helps maintain accurate and reliable data throughout the database.
Conclusion
In conclusion, PostgreSQL’s hierarchy — from servers and databases to schemas, tables, and beyond — creates a well-structured environment for managing data. This organized approach not only simplifies data handling but also enhances the efficiency of your database operations. If this breakdown helps you see the layers of PostgreSQL a bit clearer, you’re on your way to mastering this powerful tool!
 |
|