Transcript

To create new tables in Postgres, we use the "create table" command. The create table command in its most basic usage expects a table name, followed by a comma separated list of columns for the table. The columns should be in the format of column name followed by column type.

create table test (active boolean, title varchar, data text);

During the table creation process, we can also set the primary key, along with any constraints we may want. We're going to use the editor metacommand to make it clear what we're going to be doing here.

For our example, let's create a users table.

First, we're going to set an ID column of type serial. The serial type is a convenience shorthand in Postgres. It allows us to create an autoincrementing integer column.

After defining our column type, Postgres allows us to add column constraints. Here we're going to tell Postgres that the id column is our primary key.

We'll follow up our id column with some name columns, all of which will have not null constraints.

For our last column, let's create a boolean, with a default value of true.

Finally, we're going to add a table constraint, that prevents users from being saved with the same first, last, and user name combo. To do this, we start by specifying that we are creating a constraint, and pass in the name of the constraint we're adding.

If we choose not to provide a name for our constraint, Postgres will auto generate one for us. We then specify the type of constraint we're creating, which in this case is a "unique" constraint, followed by the column or columns that the constraint will be checking against.

create table users (
  id serial primary key,
  first_name varchar not null,
  last_name varchar not null,
  user_name varchar not null,
  active boolean default true,
  constraint unique_name_user_name unique (first_name, last_name, user_name)
);

Once we have our table fully configured, we can save and exit the editor.

We can verify that our table was created properly by using the "\d" metacommand.

\d users

If we try to create another table with the same name as an existing table in Postgres, an error will be raised. This error can be avoided by passing "if not exists" after the create table command. We can see this in action by trying to create another users table

create table users (id serial);

Postgres gives us an error informing us that the table already exists.

Now let's try this command again, passing the "if not exists" option.

create table if not exists users (id serial);

This time we see a note from Postgres telling us that the table already exists, and the create statement is being skipped.

DROP TABLE

To drop a table, we use the "drop table" command, passing the table name we want to get rid of

drop table test;

We can verify that it's gone using the "\d" metacommand again.

If we try to drop a table that's already been removed from the database, Postgres will raise an error. To avoid these kinds of errors, the drop table command has an "if exists" flag.

drop table if exists test;

We can see from the output here that the "if exists" option behaves like the "if not exists" option on the create table command. It gives us a note telling us that the table does not exist, and that the drop statement is being skipped.

Thanks for watching!