Defining text columns

Creating PostgreSQL Databases

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)

The TEXT data type

Strings of variable length
Strings of unlimited length
Good for text-based values of unknown length

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;

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;
```

Let's practice!

Creating PostgreSQL Databases