PostgreSQL: Basic Concepts

This a continuation of my PostgreSQL series (an installation guide for Ubuntu can be found here). Here I will briefly outline some basic concepts about the database.

You can also check out my notes on designing relational databases.

• Views are a concept that is common to almost all relational database (RDBMS) systems. The best way to think of a view is akin to an object-orientated interface; an abstraction that takes away all the underlying structural details and provides a generic way to access data and functions.


• Foreign keys help maintain the referential integrity of your data. It links a record to another record stored in a different relation.You can additionally define the constraints and associations for the link.


• Transactions are atomic operations where all the steps happen or none of the steps happen. A transaction also guarantees the the result of the operation has been logged to physical storage so that no data is lost in the case of a system crash.


• In PostgreSQL, a transaction block starts at the BEGIN command and completes when it reaches the COMMIT command. You can place as many SQL commands as you wish between these two block commands. If you want to cancel the update and reset the database, you just issue a ROLLBACK command.
  
  An example of a transaction block is as follows:
  

  BEGIN;
  UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
  UPDATE branches SET balance = balance - 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
  UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
  UPDATE branches SET balance = balance + 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
  COMMIT;

• Window functions perform a calculation across a relational table. It is similar to aggregate functions like count() and min(), however where these functions return a single output row a window function retains the identities of all rows in the relation. PostgreSQL defines some in-built aggregate functions that can be used within a window function.


• The OVER clause in a window function specifies how the rows are split up and processed. For instance, you can specify a PARTITION BY list that groups records by a field value. The aggregate function then processes these records by the group rather than all records in the relation. This is a relatively complex topic, so it is strongly suggested that you read the PostgreSQL documentation on this topic.


• Inheritance is a concept that has carried over from object-orientated databases. However, it is not feature-complete and has some serious caveats that severely limits the functionality of this feature. You should read the documentation to see if the inheritance feature is suitable for your needs.


• You can alternatively use views, foreign key constraints and other database concepts to mimic inheritance in your database.


• PostgreSQL supports the idea of constraints for table fields. This allows us to fine-tune the allowable data that can be stored beyond just defining data types. The following constraints have been defined:
  
  

  Type	Description
  CHECK	The value for a column must satisfy a Boolean condition. You can also specify a name by using the optional CONSTRAINT clause. CREATE TABLE products( price numeric CONSTRAINT positive_price CHECK (price >0) );
  NOT NULL	Simply states that a value cannot be NULL
  UNIQUE	Simply states that the value is unique across all record rows
  PRIMARY KEY	Similar to a combination of UNIQUE and NOT NULL constraints, however it specifies that the field can be used as a unique identifier for the row. There can only be one PRIMARY KEY entry for each table.
  REFERENCES (Foreign Key)	The REFERENCES clause is used to specify a foreign key relationship between two tables, which will maintain referential integrity of the data. When this clause is used, it will become impossible to create a row when the foreign key does not exist in the referenced table. You can also specify what happens to the row when the referenced field is updated or deleted by using the ON UPDATE and ON DELETE clauses, respectively. The actions you can define are RESTRICT, CASCADE, NO ACTION, SET NULL and SET DEFAULT. See the documentation for more information on how this works.

  
  


• Each table is created with several system columns. These are reserved and cannot be used as names for user-defined columns. System columns store information such as the unique id of the table, the unique id of the record in the table, and the physical location of the row.
• The ALTER TABLE command allows you to modify an existing table. This is useful if the table already contains data or is being referenced by other tables.
• PostgreSQL Schemas can be thought of as table namespaces. You can logically group tables in a database by schema, and you can have identically named tables existing in two different schemas.
  By Default, a new database will automatically have a 'public' schema that is accessible by all users with privileges on the database.
• Partitioning a table is done via table inheritance (so read up on how PostgreSQL does inheritance before using this feature!). By physically partitioning a table you can increase query times (since the server only needs to search the partition and not the whole table), and you can move less-used data to cheaper disks.

References:

• The PGAdmin3 main web-page
• The PostgreSQL 9.1 documentation
• An extensive (but still a work in progress) comparison of the SQL implementations of various RDBMS' from troels.arvin.dk
• A decent explanation as to why there is a public schema created by default from stackoverflow.com