How to Create a Table in PostgreSQL

Here’s an example of creating a users table in PostgreSQL:

create table users (
  id serial primary key, -- Auto incrementing IDs
  name character varying, -- String column without specifying a length
  preferences jsonb, -- JSON columns are great for storing unstructured data
  created_at timestamp without time zone -- Always store time in UTC

This is also a chance to specify not null constraints and default values:

create table users (
  id serial primary key,
  name character varying not null,
  active boolean default true

You can also create temporary tables that will stick around for the duration of your session. This is helpful to break down your analysis into smaller pieces.

-- Create a temporary table called `scratch_users` with just an `id` column
create temporary table scratch_users (id integer);

-- Or create a temporary table based on the output of a select
create temp table active_users
select * from users where active is true;