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)

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!

Creating PostgreSQL Databases

Preparing Video For Download...