Working with data types

Introduction to Relational Databases in SQL

Timo Grossenbacher

Data Journalist

Working with data types

  • Enforced on columns (i.e. attributes)
  • Define the so-called "domain" of a column
  • Define what operations are possible
  • Enforce consistent storage of values
Introduction to Relational Databases in SQL

The most common types

  • text: character strings of any length
  • varchar [ (x) ]: a maximum of x characters
  • char [ (x) ]: a fixed-length string of x characters
  • boolean: can only take three states, e.g. TRUE, FALSE and NULL (unknown)

From the PostgreSQL documentation.

Introduction to Relational Databases in SQL

The most common types (cont'd.)

  • date, time and timestamp: various formats for date and time calculations
  • numeric: arbitrary precision numbers, e.g. 3.1457
  • integer: whole numbers in the range of -2147483648 and +2147483647

From the PostgreSQL documentation.

Introduction to Relational Databases in SQL

Specifying types upon table creation

CREATE TABLE students (
 ssn integer, 
 name varchar(64), 
 dob date, 
 average_grade numeric(3, 2), -- e.g. 5.54
 tuition_paid boolean 
);
Introduction to Relational Databases in SQL

Alter types after table creation

ALTER TABLE students
ALTER COLUMN name 
TYPE varchar(128);

ALTER TABLE students
ALTER COLUMN average_grade
TYPE integer
-- Turns 5.54 into 6, not 5, before type conversion
USING ROUND(average_grade);
Introduction to Relational Databases in SQL

Let's apply this!

Introduction to Relational Databases in SQL

Preparing Video For Download...