KEMBAR78
PostgreSQL Data Base Creation Part 2 | PDF | Boolean Data Type | Data Type
Scribd
Upload
13 views32 pages

PostgreSQL Data Base Creation Part 2

The document provides an introduction to PostgreSQL data types, covering categories such as text, numeric, temporal, and boolean. It includes examples of creating tables with various data types and explains the characteristics of each type, including VARCHAR, CHAR, INTEGER, and BOOLEAN. Additionally, it discusses the importance of defining specific data types to restrict values and ensure data integrity in databases.

Uploaded by

sleonsalome
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
13 views32 pages

PostgreSQL Data Base Creation Part 2

The document provides an introduction to PostgreSQL data types, covering categories such as text, numeric, temporal, and boolean. It includes examples of creating tables with various data types and explains the characteristics of each type, including VARCHAR, CHAR, INTEGER, and BOOLEAN. Additionally, it discusses the importance of defining specific data types to restrict values and ensure data integrity in databases.

Uploaded by

sleonsalome
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 32

Introduction to

PostgreSQL data
types
C R E AT I N G P O S T G R E S Q L D ATA B A S E S

Darryl Reeves
Industry Assistant Professor, New York
University
Data categories in PostgreSQL
Text
Numeric

Temporal

Boolean

Others: Geometric, Binary, Monetary

CREATING POSTGRESQL DATABASES


Example 1: representing birthdays
Cathy: May 3rd, 2006

Possible representations
"May 3, 2006" (text)
"5/3/2006" (text)

2006-05-03 (date)

CREATING POSTGRESQL DATABASES


Example 2: tracking payment status
Did attending member pay?
Possible representations:
"Yes"/"No" (text)

"Y"/"N" (text)

'true'/'false' (boolean)

Specific types provide restriction on values

CREATING POSTGRESQL DATABASES


Example 3: trip distances
Mark flew 326 miles for client meeting

Possible representations:
"326 miles" (text)

"326" (text)

326 (numeric)

CREATING POSTGRESQL DATABASES


Let's practice!
C R E AT I N G P O S T G R E S Q L D ATA B A S E S
Defining text
columns
C R E AT I N G P O S T G R E S Q L D ATA B A S E S

Darryl Reeves
Industry Assistant Professor, New York
University
Using text in PostgreSQL
CREATE TABLE book (
isbn CHAR(13) NOT NULL,
author_first_name VARCHAR(50) NOT NULL,
author_last_name VARCHAR(50) NOT NULL,
content TEXT NOT NULL
);

Text data types: TEXT , VARCHAR(N) , CHAR(N)

CREATING POSTGRESQL DATABASES


The TEXT data type
Strings of variable length

Strings of unlimited length

Good for text-based values of unknown length

CREATING POSTGRESQL DATABASES


The VARCHAR data type
Strings of variable length

Strings of unlimited length

Restriction can be imposed on column values


VARCHAR(N)

N - maximum number of characters stored


Column can store strings with less than N characters

Inserting string longer than N is error

VARCHAR without N specified equivalent to TEXT

first_name VARCHAR(50) NOT NULL;

CREATING POSTGRESQL DATABASES


The CHAR data type
CHAR(N) values consist of exactly N characters

Strings are right-padded with spaces

CHAR equivalent to CHAR(1)

isbn CHAR(13) NOT NULL;

CREATING POSTGRESQL DATABASES


Let's practice!
C R E AT I N G P O S T G R E S Q L D ATA B A S E S
Defining numeric
data columns
C R E AT I N G P O S T G R E S Q L D ATA B A S E S

Darryl Reeves
Industry Assistant Professor, New York
University
Numeric data with discrete values
CREATE TABLE people.employee {
id SERIAL PRIMARY KEY,
first_name VARCHAR(10) NOT NULL,
last_name VARCHAR(10) NOT NULL
}

CREATING POSTGRESQL DATABASES


Numeric data with discrete values
CREATE TABLE people.employee {
id SERIAL PRIMARY KEY,
first_name VARCHAR(10) NOT NULL,
last_name VARCHAR(10) NOT NULL,
num_sales INTEGER
}

CREATING POSTGRESQL DATABASES


Integer types
Type Description Range
SMALLINT small-range integer -32768 to +32767

1 https://www.postgresql.org/docs/9.1/datatype-numeric.html

CREATING POSTGRESQL DATABASES


Integer types
Type Description Range
SMALLINT small-range integer -32768 to +32767

INTEGER typical choice for integer -2147483648 to +2147483647

1 https://www.postgresql.org/docs/9.1/datatype-numeric.html

CREATING POSTGRESQL DATABASES


Integer types
Type Description Range
SMALLINT small-range integer -32768 to +32767

INTEGER typical choice for integer -2147483648 to +2147483647

BIGINT large-range integer -9223372036854775808 to 9223372036854775807

1 https://www.postgresql.org/docs/9.1/datatype-numeric.html

CREATING POSTGRESQL DATABASES


Integer types
Type Description Range
SMALLINT small-range integer -32768 to +32767

INTEGER typical choice for integer -2147483648 to +2147483647

BIGINT large-range integer -9223372036854775808 to 9223372036854775807

SERIAL autoincrementing integer 1 to 2147483647

1 https://www.postgresql.org/docs/9.1/datatype-numeric.html

CREATING POSTGRESQL DATABASES


Integer types
Type Description Range
SERIAL autoincrementing integer 1 to 2147483647

BIGSERIAL large autoincrementing integer 1 to 9223372036854775807

1 https://www.postgresql.org/docs/9.1/datatype-numeric.html

CREATING POSTGRESQL DATABASES


Numeric data with continuous values
CREATE TABLE people.employee {
id SERIAL PRIMARY KEY,
first_name VARCHAR(10) NOT NULL,
last_name VARCHAR(10) NOT NULL,
num_sales INTEGER
}

CREATING POSTGRESQL DATABASES


Numeric data with continuous values
CREATE TABLE people.employee {
id SERIAL PRIMARY KEY,
first_name VARCHAR(10) NOT NULL,
last_name VARCHAR(10) NOT NULL,
num_sales INTEGER,
salary DECIMAL(8,2) NOT NULL
}

DECIMAL (precision, scale)

CREATING POSTGRESQL DATABASES


Floating-point types
Type Description Range
DECIMAL or user-specified 131072 digits before the decimal point;16383 digits after
NUMERIC precision the decimal point

1 https://www.postgresql.org/docs/9.1/datatype-numeric.html

CREATING POSTGRESQL DATABASES


Floating-point types
Type Description Range
DECIMAL ( user-specified up to 131072 digits before the decimal point; up to 16383
NUMERIC ) precision digits after the decimal point

variable-
REAL
precision 6 decimal digits precision

1 https://www.postgresql.org/docs/9.1/datatype-numeric.html

CREATING POSTGRESQL DATABASES


Floating-point types
Type Description Range
DECIMAL ( user-specified up to 131072 digits before the decimal point; up to 16383
NUMERIC ) precision digits after the decimal point

variable-
REAL
precision 6 decimal digits precision

DOUBLE variable
15 decimal digits precision
PRECISION precision

1 https://www.postgresql.org/docs/9.1/datatype-numeric.html

CREATING POSTGRESQL DATABASES


Let's practice!
C R E AT I N G P O S T G R E S Q L D ATA B A S E S
Defining boolean
and temporal data
columns
C R E AT I N G P O S T G R E S Q L D ATA B A S E S

Darryl Reeves
Industry Assistant Professor, New York
University
Boolean and temporal data
CREATE TABLE book (
isbn CHAR(13) NOT NULL,
author_first_name VARCHAR(50) NOT NULL,
author_last_name VARCHAR(50) NOT NULL,
content TEXT NOT NULL
);

CREATING POSTGRESQL DATABASES


Boolean and temporal data
CREATE TABLE book (
isbn CHAR(13) NOT NULL,
author_first_name VARCHAR(50) NOT NULL,
author_last_name VARCHAR(50) NOT NULL,
content TEXT NOT NULL,
originally_published DATE NOT NULL,
out_of_print BOOLEAN DEFAULT FALSE
);

CREATING POSTGRESQL DATABASES


The BOOLEAN data type
Three possible values
true state

false state

NULL (unknown state)

Common for representing yes-or-no scenarios

Can be defined with keyword BOOL or BOOLEAN

in_stock BOOL DEFAULT TRUE;

CREATING POSTGRESQL DATABASES


Temporal data types
Type Descriptions Format
TIMESTAMP represents a date and time 2010-09-21 15 47 16

DATE represents a date 1972-07-08

TIME represents a time 05 30 00

CREATING POSTGRESQL DATABASES


Let's practice!
C R E AT I N G P O S T G R E S Q L D ATA B A S E S

You might also like