Book Image

PostgreSQL for Data Architects

By : Jayadevan M
Book Image

PostgreSQL for Data Architects

By: Jayadevan M

Overview of this book

This book is for developers and data architects who have some exposure to databases. It is assumed that you understand the basic concepts of tables and common database objects, including privileges and security.
Table of Contents (14 chapters)
13
Index

Understanding transactions

Transaction is a set of one or more SQL statements that take the database from one consistent state to another. The most common example used to explain transactions is of transferring money from one account to another. Let's use this in the following example.

Let's create a table to simulate a few possible scenarios:

[root@MyCentOS ~]# psql
psql (9.3.0)
Type "help" for help.

postgres=# CREATE USER bank PASSWORD 'bank';
CREATE ROLE
postgres=# ALTER USER bank WITH createdb;
ALTER ROLE
postgres=# \c test bank
You are now connected to database "test" as user "bank".

test=> CREATE DATABASE accounts;
CREATE DATABASE
test=> \c accounts;
You are now connected to database "accounts" as user "bank".
accounts=> CREATE TABLE account (id serial PRIMARY KEY , first_name varchar(100), last_name varchar(100), account_bal numeric (10,2));
CREATE TABLE

Once this is done, we can populate the table with...