Schemas are used for the following reasons:

In the car web portal, let us assume that we would like to create a schema named car_portal_app, owned by car_portal_app role. This can be done as follows:

CREATE SCHEMA car_portal_app AUTHORIZATION car_portal_app;
--The schema owner is the same as the schema name if not given
CREATE SCHEMA AUTHORIZATION car_portal_app;

For more information about the syntax of the CREATE SCHEMA command, one can use the psql \h meta-command, which displays the psql client tool inline help, or take a look at the PostgreSQL manual at http://www.postgresql.org/docs/current/static/sql-createschema.html.

When designing a database table, one should take care in picking the appropriate data type. When the database goes to production, changing the data type of a column might become a very costly operation, especially for heavily loaded tables. The cost often comes from locking the table, and in some cases, rewriting it. When picking a data type, consider a balance between the following factors:

PostgreSQL provides a very extensive set of data types. Some of the native data type categories are:

These data types are almost common for all relational databases. Moreover, they are often sufficient for modeling traditional applications.

The following table shows various numeric types:

PostgreSQL supports various mathematical operators and functions, such as geometric functions and bitwise operations. The smallint data type can be used to save disk space, while bigint can be used if the integer range is not sufficient.

Serial types, namely smallserial, serial, and bigserial are wrappers on top of smallint, int, and biginteger respectively. Serial types are often used as surrogate keys, and by default, they are not allowed to have a null value. The serial type utilizes the sequences behind the scene. A sequence is a database object that is used to generate sequences by specifying the minimum, maximum, and increment values.

For example, the following code creates a table customer with a column customer_id:

CREATE TABLE customer (
 customer_id SERIAL
);

This will generate the following code behind the scene:

CREATE SEQUENCE custome_customer_id_seq;
CREATE TABLE customer (
customer_id integer NOT NULL DEFAULT nextval('customer_customer_id_seq')
);
ALTER SEQUENCE customer_customer_id_seq OWNED BY customer.Customer_id;

When creating a column with type serial, remember the following things:

Similar to the C language, the result of an integer expression is also an integer. So, the results of the mathematical operations 3/2 and 1/3 is 1 and 0 respectively. Thus, the fractional part is always truncated. Unlike C language, the postgres rounds off the numbers when casting a double value to int:

postgres=# SELECT CAST (5.9 AS INT) AS rounded_up, CAST(5.1 AS INTEGER) AS rounded_down, 5.5::INT AS another_syntax;
 rounded_up | rounded_down | another_syntax
------------+--------------+----------------
 6 | 5 | 6
(1 row)

postgres=# SELECT 2/3 AS "2/3", 1/3 AS "1/3", 3/2 AS "3/2";
 2/3 | 1/3 | 3/2
-----+-----+-----
 0 | 0 | 1
(1 row)

The numeric and decimal types are recommended for storing monetary and other amounts where precision is required. There are three forms for defining a numeric or a decimal value:

Precision is the total number of digits, while scale is the number of digits of the fraction part. For example, the number 12.344 has a precision of five and a scale of three. If a numeric type is used to define a column type without precision or scale, then the column can store any number with any precision and scale.

Floating point and double precision are inexact; that means that the values in some cases cannot be represented in the internal binary format, and are stored as approximation.

The following table shows various character types:

PostgreSQL provides two general text types, which are char(n) and varchar(n) data types, where n is the number of characters allowed. In the char data type, if a value is less than the specified length, then trailing spaces are padded at the end of the value. Operations on the char data types ignore the trailing spaces. Take a look at the following example:

postgres=# SELECT 'a'::CHAR(2) = 'a '::CHAR(2);
 ?column?
----------
 t
(1 row)

postgres=# SELECT length('a '::CHAR(10));
 length
--------
 1
(1 row)

It is not recommended to perform binary operations on varchar or text and char strings due to trailing spaces.

For both char and varchar data types, if the string is longer than the maximum allowed length, then:

The following example shows how mixing different data types might cause problems:

postgres=# SELECT 'a '::VARCHAR(2)='a '::text;
 ?column?
----------
 t
(1 row)
postgres=# SELECT 'a '::CHAR(2)='a '::text;
 ?column?
----------
 f
(1 row)
postgres=# SELECT 'a '::CHAR(2)='a '::VARCHAR(2);
 ?column?
----------
 t
(1 row)
postgres=# SELECT length ('a '::CHAR(2));
 length
--------
 1
(1 row)
postgres=# SELECT length ('a '::VARCHAR(2));
 length
--------
 2
(1 row)

The preceding example shows that 'a '::CHAR(2) equals to 'a '::VARCHAR(2), but both have different lengths, which is not logical. Also, it shows that 'a '::CHAR(2) is not equal to 'a '::text. Finally, 'a '::VARCHAR(2) equals 'a '::text. The preceding example causes confusion because if a variable a is equal to b and b is equal to c, then a is equal to c according to mathematics.

The PostgreSQL text storage size depends on several factors, namely, the length of the text value, and the text decoding and compression. The text data type can be considered as an unlimited varchar() type. The maximum text size that can be stored is 1 GB, which is the maximum column size.

For fixed length strings, the character data type and the character varying data type consume the same amount of hard disk space. For variable length character, the character varying data type consumes less space, because character type appends the string with space. The following code shows the storage consumption for fixed and variable length texts for the character and character varying data types. It simply creates two tables, populates the tables with fictional data using fixed and variable length strings, and finally gets the table size in a human readable form:

CREATE TABLE char_size_test (
 size CHAR(10)
);
CREATE TABLE varchar_size_test(
 size varchar(10)
);
WITH test_data AS (
 SELECT substring(md5(random()::text), 1, 10) FROM generate_series (1, 1000000)
),
cahr _data_insert AS ( 
 INSERT INTO char_size_test SELECT * FROM test_data
)
INSERT INTO varchar_size_test SELECT * FROM test_date;
-- Get the table size in human readable form
SELECT pg_size_pretty(pg_relation_size ('char_size_test')) AS char_size_test , pg_size_pretty(pg_relation_size ('varchar_size_test')) AS varchar_size_test;
-- Delete the tables data
TRUNCATE char_size_test;
TRUNCATE varchar_size_test;
-- Insert data with fixed length 
WITH test_date AS (
 SELECT substring(md5(random()::text), 1, (random()* 10)::int) FROM generate_series (1, 1000000)
),
cahr _data_insert AS ( 
 INSERT INTO char_size_test SELECT * FROM test_date
)
INSERT INTO varchar_size_test SELECT * FROM test_date;

SELECT pg_size_pretty(pg_relation_size ('char_size_test')) AS char_size_test , pg_size_pretty(pg_relation_size ('varchar_size_test')) AS varchar_size_test; 
-- Create tables

The varchar data type can be emulated by the text data type and a check constraint to check the text length. For example, the following code snippets are semantically equivalent:

CREATE TABLE emulate_varchar(
test VARCHAR(4)
);
--semantically equivalent to
CREATE TABLE emulate_varchar (
test TEXT,
CONSTRAINT test_length CHECK (length(test) <= 4)
);

In PostgreSQL, there is no difference in performance between the different character types, so it is recommended to use the text data type. It allows the developer to react quickly to the changes in business requirements. For example, one common business case is changing the text length, such as changing the length of a customer ticket number from six to eight characters due to length limitation, or changing how certain information are stored in the database. In such a scenario, if the data type is text, this could be done by amending the check constraint without altering the table structure.

The date and time data types are commonly used to describe the occurrence of events such as birth date. PostgreSQL supports the following date and time types:

PostgreSQL stores timestamp with and without time zone in the universal coordinated time (UTC) format, and only time is stored without the time zone. This explains the identical storage size for both timestamp with time zone and time stamp without time zone.

There are two approaches for handling timestamp correctly. The first approach is to use timestamp without time zone, and let the client side handle the time zone differences. This is useful for in-house development, applications with only one time zone, and when the clients know the time zone differences.

The other approach is to use timestamp with time zone. The following are some of the best practices to avoid the common pitfalls when using timestamptz:

PostgreSQL has two important settings: timezone and datestyle. The datestyle has two purposes:

The views pg_timezone_names and pg_timezone_abbrevs provide a list of the time zone names and abbreviations respectively. They also provide information regarding the time offset from UTC, and if the time zone is a DST. For example, the following code snippet sets the timezone setting to Jerusalem, and then retrieves the local date and time in Jerusalem:

postgres=> SET timezone TO 'Asia/jerusalem';
SET
postgres=> SELECT now();
 now 
-------------------------------
 2014-08-27 23:49:49.611633+03
(1 row)

The PostgreSQL AT TIME ZONE statement converts the timestamp with or without the timezone to a specified time zone; its behavior depends on the converted type. The following example clarifies this construct:

postgres=> SHOW timezone;
 TimeZone
----------
 UTC
(1 row)

postgres=> \x
Expanded display is on.
postgres=> SELECT now(), now()::timestamp, now() AT TIME ZONE 'CST', now()::timestamp AT TIME ZONE 'CST';
-[ RECORD 1 ]--------------------------
now | 2014-08-27 21:00:18.36009+00
now | 2014-08-27 21:00:18.36009
timezone | 2014-08-27 15:00:18.36009
timezone | 2014-08-28 03:00:18.36009+00

The function now() returns the current timestamp with the time zone in the UTC format. Notice that the time zone offsite is +00. When casting the time stamp with the time zone to timestamp as in now()::timestamp, the time zone offsite is truncated. The now() AT TIME ZONE 'CST' expression converts the timestamp with the time zone UTC to timestamp in the specified time zone CST. Since the central standard time offset is -6, then six hours are deducted. The last expression now()::timestamp AT TIME ZONE 'CST' is reinterpreted as a timestamp as being in that time zone CST for the purpose of converting it to the connection default time zone UTC. So, the last expression is equivalent to the following:

postgres=> SELECT ('2014-08-27 21:00:18.36009'::timestamp AT time zone 'CST' AT TIME ZONE 'UTC')::timestamptz;
-[ RECORD 1 ]--------------------------
timezone | 2014-08-28 03:00:18.36009+00

One can summarize the conversion between the timestamp with and without the time zone, as follows:

The date is recommended when there is no need to specify the time such as birth date, holidays, absence days, and so on.

Time with time zone storage is 12 bytes, 8 bytes are used to store the time, and 4 bytes are used to store the time zone. The time without time zone consumes only 8 bytes. Conversions between time zones can be made using the AT TIME ZONE construct.

Finally, the interval data type is very important in handling the timestamp operations as well as describing some business cases. From the point of view of functional requirements, the interval data type can represent a period of time such as estimation time for the completion of a certain task. The result type of the basic arithmetic operations such as + and - on timestamp, timestamptz, time, and time with time zone is of the type interval. The result of the same operations on date type is an integer. The following example shows timestamptz and date subtraction. Notice the format of the specifying intervals:

SELECT '2014-09-01 23:30:00.000000+00'::timestamptz -'2014-09-01 22:00:00.000000+00'::timestamptz = Interval '1 hour, 30 minutes';
 ?column?
----------
 t
(1 row)
postgres=> SELECT '11-10-2014'::date -'10-10-2014'::date = 1;
 ?column?
----------
 t
(1 row)

At this stage, one can convert the logical model of the car web portal presented in Chapter 1, Relational Databases to a physical model. To help the developer to create a table, one can follow this minimal check list:

For creating the car web portal schema, the formal relational model will not be applied strictly. Also, surrogate keys will be used instead of natural keys for the following reasons:

Despite all these advantages of surrogate keys, it also has a few disadvantages:

In the web car portal ER diagram, there is an entity with the name user. Since user is a reserved keyword, the name account will be used for creating the table. Note that to create a database object using a PostgreSQL keyword, the name should be quoted. The following example shows how to create a table user:

postgres=# \set VERBOSITY 'verbose'
postgres=# CREATE TABLE user AS SELECT 1;
ERROR: 42601: syntax error at or near "user"
LINE 1: CREATE TABLE user AS SELECT 1;
 ^
LOCATION: scanner_yyerror, src\backend\parser\scan.l:1053
postgres=# CREATE TABLE "user" AS SELECT 1;
SELECT 1
postgres=#

You can find the full list of reserved words at http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html.

To create a table account, one can execute the following command:

CREATE TABLE account (
 account_id SERIAL PRIMARY KEY,
 first_name TEXT NOT NULL,
 last_name TEXT NOT NULL,
 email TEXT NOT NULL UNIQUE,
 password TEXT NOT NULL,
 CHECK(first_name !~ '\s' AND last_name !~ '\s'),
 CHECK (email ~* '^\w+@\w+[.]\w+$'),
 CHECK (char_length(password)>=8)
);

To summarize the user table:

Behind the scene, the following objects are created:

To create the seller account, one can execute the following statement:

CREATE TABLE seller_account (
 seller_account_id SERIAL PRIMARY KEY,
 account_id INT UNIQUE NOT NULL REFERENCES account(account_id), number_of_advertizement advertisement INT DEFAULT 0,
 user_ranking float, 
 total_rank float
);

As we can see, the seller account has a one-to-one relationship with the account. This is enforced by the account_id that consists of NOT NULL and UNIQUE constraints. Also, in this case, one can model the seller account as follows by marking the account_id as the primary key:

CREATE TABLE seller_account (
 account_id INT PRIMARY KEY REFERENCES account(account_id)
...
 );

The first design is more flexible and less ambiguous. First of all, the requirement might change, and the user account and the seller account relation might change from one-to-one to one-to-many. For example, the user concept might be generalized to handle companies where the company has several seller accounts.

Also, if a table references both the account and seller_account tables using a common column name such as account_id, then it will be difficult to distinguish which table is being referenced.