Defining numeric data columns

Creating PostgreSQL Databases

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 NUMERIC user-specified precision 131072 digits before the decimal point;16383 digits after the decimal point
1 https://www.postgresql.org/docs/9.1/datatype-numeric.html
Creating PostgreSQL Databases

Floating-point types

Type Description Range
DECIMAL (NUMERIC) user-specified precision up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
REAL variable-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 (NUMERIC) user-specified precision up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
REAL variable-precision 6 decimal digits precision
DOUBLE PRECISION variable precision 15 decimal digits precision
1 https://www.postgresql.org/docs/9.1/datatype-numeric.html
Creating PostgreSQL Databases

Let's practice!

Creating PostgreSQL Databases

Preparing Video For Download...