PostgreSQL

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
as
select * from users where active is true;