CENG301 Database Management Systems

Session-9
Asst. Prof. Mustafa YENIAD
myeniad@gmail.com
 Remember - Data Types
• PostgreSQL supports many data types:
• boolean
• Character types such as char, varchar, and text.
• Numeric types such as integer, floating-point, serial number.
• Temporal types such as date, time, timestamp, and interval
DBMS

• UUID for storing Universally Unique Identifiers

• Array for storing array strings, numbers, etc.
• JSON stores JSON data
• hstore stores key-value pair
• Special types such as network address and geometric data
 See All Data Types:

Review the PostgreSQL Documentation Page:

DBMS

https://www.postgresql.org/docs/current/datatype.html
 Data Type: boolean
• PostgreSQL supports a single boolean data type: boolean that can have three values:
• True
• False
• null
• In PostgreSQL, the “bool” or”boolean” keyword is used to initialize a Boolean data type.
These data types can hold true, false, and null values.
DBMS

• A boolean data type is stored in the database according to the following:

• 1, yes, y, t, true values are converted to true
• 0, no, false, f values are converted to false

• When queried for these boolean data types are converted and returned according to the
following:
• t to true
• f to false
• space to null
 Data Type: boolean
True False
true false
't' 'f'
'true' 'false'
'y' 'n'
'yes' 'no'
DBMS

'1' '0'

• An example of using the PostgreSQL boolean data type:

DROP TABLE IF EXISTS stock;
CREATE TABLE stock (
product_id SERIAL NOT NULL PRIMARY KEY,
availability BOOLEAN NOT NULL); # Create a new table stock to log which products are available;
# If you insert a record without specifying the value for the availability column, PostgreSQL uses FALSE

INSERT INTO stock(availability) VALUES (TRUE),(FALSE), ('t'), ('y'), ('no'), ('0'), ('1'); # insert some sample data into the stock table

SELECT * FROM stock WHERE availability = 'yes'; # check for the availability of products

SELECT * FROM stock WHERE availability = 'no'; # check for the products that are not available
 Data Type: numeric types
• PostgreSQL provides two distinct types of numbers:
1. integers
• There are three kinds of integers in PostgreSQL:
• Small integer (SMALLINT)
• Integer (INT) is synoym of (INTEGER)
• Serial (SERIAL) is the same as integer except that PostgreSQL will automatically generate and
populate values into the serial column.
DBMS

(This is similar to AUTO_INCREMENT column in MariaDB or AUTOINCREMENT column in SQLite)

2. floating-point numbers
• There three main types of floating-point numbers:
• float(n) is a floating-point number whose precision, at least, n, up to a maximum of 8 bytes.
• real or float8 is a 4-byte floating-point number.
• numeric or numeric(p,s) is a real number with p (precision) total number of digits that can be
stored in numeric type and s (scale) is number of digits to the right after the decimal point.

Note: The NUMERIC and DECIMAL types are equivalent in PostgreSQL and upto the SQL standard.
It is recommended to not use the NUMERIC type if precision is not required.
Calculation on NUMERIC values is slower than integers, floats, and double precision.
 Data Type: numeric types
• Numeric types consist of two-, four-, and eight-byte integers, four- and eight-byte floating-point numbers, and selectable-precision decimals:

Name Storage Size Description Range

smallint 2 bytes small-range integer -32768 to +32767
integer 4 bytes typical choice for integer -2147483648 to +2147483647

bigint 8 bytes large-range integer -9223372036854775808 to +9223372036854775807

DBMS

up to 131072 digits before the decimal point; up to 16383 digits after the
decimal variable user-specified precision, exact
decimal point

up to 131072 digits before the decimal point; up to 16383 digits after the
numeric variable user-specified precision, exact
decimal point

real 4 bytes variable-precision, inexact 6 decimal digits precision

double precision 8 bytes variable-precision, inexact 15 decimal digits precision

smallserial 2 bytes small autoincrementing integer 1 to 32767

serial 4 bytes autoincrementing integer 1 to 2147483647

bigserial 8 bytes large autoincrementing integer 1 to 9223372036854775807

 Data Type: numeric types
• Examples of storing numeric data:
DROP TABLE IF EXISTS products;
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
price NUMERIC (5, 2)
);
DBMS

INSERT INTO products (name, price)

VALUES
('Phone', 100.3456),
('Printer', 550.5174),
('Tablet', 400.1234); # PostgreSQL rounds the values up or down depended on the scale value

SELECT * FROM products;

 Data Type: character types
• PostgreSQL has three character data types namely, CHAR(n), VARCHAR(n), and TEXT:
• CHAR(n): is used for data(string) with a fixed-length of characters with padded spaces.
In case the length of the string is smaller than the value of "n", then the rest of the remaining spaces are padded
automatically.
For a string with a length greater than the value of "n", PostgreSQL throws an error.
• VARCHAR(n): is the variable-length character string.
Similar to CHAR(n), it can store "n" length data but unlike CHAR(n) no padding is done in case the
data length is smaller than the value of "n".
• TEXT is the variable-length character string. It can store data with unlimited length.
DBMS

➢ Use VARCHAR(n) if you want to validate the length of the string (n) before inserting into or updating to a column.
➢ VARCHAR (without the length specifier) and TEXT are equivalent.
• An example of using the PostgreSQL character data type:
DROP TABLE IF EXISTS characters;
CREATE TABLE characters (
id SERIAL PRIMARY KEY,
x CHAR (5),
y VARCHAR (50),
z TEXT);

INSERT INTO characters (x, y, z) VALUES('A','This is a test for varchar','This is a very long text for the PostgreSQL text column');
SELECT * FROM characters;
 Creating a table & declaring columns
• In database theory, NULL represents unknown or information missing. NULL is not the same as an empty string or the number zero. To
control whether a column can accept NULL, you use the NOT NULL constraint.
• A column can be assigned a DEFAULT value. When a new row is created and no values are specified for some of the columns, those
columns will be filled with their respective default values.
CREATE TABLE table_name(
column_name_1 data_type NOT NULL,
column_name_2 data_type,
column_name_3 data_type DEFAULT 'default_value',
…);
DBMS

DROP TABLE IF EXISTS invoices;

CREATE TABLE invoices(
id SERIAL PRIMARY KEY,
product_id INT NOT NULL,
quantity NUMERIC NOT NULL CHECK(quantity > 0),
tax NUMERIC DEFAULT 8,
purchase_date DATE DEFAULT CURRENT_DATE,
net_price NUMERIC CHECK(net_price > 0)
);
Additional info:
• A CHECK constraint is a kind of constraint that allows you to specify if values in a column must meet a specific requirement.
• The CHECK constraint uses a boolean expression to evaluate the values before they are inserted or updated to the column:
➢ If the values pass the check, PostgreSQL will insert or update these values to the column.
➢ Otherwise, PostgreSQL will reject the changes and issue a constraint violation error.
 Inserting a new row
• The PostgreSQL INSERT statement allows you to insert a new row into a table.
• The following illustrates the most basic syntax of the INSERT statement:

INSERT INTO table_name (column_1, column_2, ...)

VALUES (value_1, value_2, ...);

• First, specify the name of the table (table_name) that you want to insert data after the INSERT INTO keywords and a list
of comma-separated columns (column1, column2, ....).
DBMS

• Second, supply a list of comma-separated values in a parentheses (value1, value2, ...) after the VALUES keyword.

Important: The columns and values in the column and value lists must be in the same order.
DROP TABLE IF EXISTS distros;
CREATE TABLE distros(
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description VARCHAR(255)
);
INSERT INTO distros (name,description) VALUES('Alma', 'Provide a community-supported enterprise OS');
INSERT INTO distros (name,description) VALUES('Gentoo', 'Optimized for the specific type of computers');
 Inserting a new row
RETURNING clause:
• The INSERT statement also has an optional RETURNING clause that returns the information of the inserted row.
• If you want to return the entire inserted row, you use an asterisk (*) after the RETURNING keyword:
INSERT INTO distros (name,description)
VALUES
('Ubuntu', 'Officially released in multiple editions: Desktop, Server, and Core for IoT devices and also Robots')
RETURNING *;
DBMS

• If you want to return just some information of the inserted row, you can specify one or more columns after the
RETURNING clause.
• For example, the following statement returns id of the inserted row:
INSERT INTO distros (name,description)
VALUES
('Debian', 'Popular, free and open-source software, developed by the community-supported Debian Project')
RETURNING id;
 Inserting multiple rows
• To insert multiple rows into a table using a single INSERT statement, you use the following syntax:

INSERT INTO table_name (column_list)

VALUES
(value_list_1),
(value_list_2),
...
(value_list_n);
DBMS

INSERT INTO distros (name,description)

VALUES
('Fedora', 'Developed in 2003 as a continuation of the Red Hat Linux project'),
('Mint', 'Comes fully equipped with the apps most people need'),
('Suse', 'Built on top of the Linux kernel and is a secure & modern distro')
RETURNING *;
 Updating a row
• The PostgreSQL UPDATE statement allows you to modify data in a table. The following illustrates the syntax of the UPDATE statement:
UPDATE table_name
SET
column_1= value_1,
column_2= value_2,
...
(update_list_n)
WHERE condition;
DBMS

• First, specify the name of the table that you want to update data after the UPDATE keyword.
• Second, specify columns and their new values after SET keyword. The columns that do not appear in the SET clause retain their original values.
• Third, determine which rows to update in the condition of the WHERE clause.
• The WHERE clause is optional but if you omit the WHERE clause, the UPDATE statement will update all rows in the table!
• When the UPDATE statement is executed successfully, it returns the update count.
UPDATE distros
SET name='RedHat'
WHERE id=4;

# optionally, to return the updated row to the client:

UPDATE distros
SET name='RedHat'
WHERE id=4
RETURNING *;
 Deleting a row
• The PostgreSQL DELETE statement allows you to delete one or more rows from a table.
• The following shows basic syntax of the DELETE statement:

DELETE FROM table_name

WHERE condition;

• First, specify the name of the table from which you want to delete data after the DELETE FROM keywords.
• Second, use a condition in the WHERE clause to specify which rows from the table to delete.
• The WHERE clause is optional but if you omit the WHERE clause, the DELETE statement will delete all rows in the table!
DBMS

• The DELETE statement returns the number of rows deleted.

• It returns zero if the DELETE statement did not delete any row.

DELETE FROM distros

WHERE id=4;

# optionally, to return the deleted row to the client:

DELETE FROM distros
WHERE id=4
RETURNING *;

# To delete multiple rows:

DELETE FROM distros
WHERE id IN (1,3)
RETURNING *;
 Drop a table
• To drop a table from the database, you use the DROP TABLE statement as follows:

DROP TABLE IF EXISTS table_name;

• First, specify the name of the table that you want to drop after the DROP TABLE keywords.
• Second, use the IF EXISTS option to remove the table only if it exists.
• If you remove a table that does not exist, PostgreSQL issues an error. To avoid this situation, you can use the IF EXISTS
DBMS

option.

DROP TABLE distros;

# To drop multiple tables:

DROP TABLE products, invoices;