Postgres Database Setup

Some of the code examples in the Dagster Glossary use SQL and database functions. In these situations, we will be using Postgres as it a widely used free database format. Here are the steps for installing Postgres and creating tables.

To install Postgres on your local system, you can follow the official guide here. If you prefer more guided steps on how to install Postgres, try the W3Schols guides here.

Assuming you have PostgreSQL installed and the psql command available (in the terminal), follow these steps to create the database, and create a table:

Get your username

Start by retrieving your local username by typing whoami into your terminal window.

 % whoami
 dagster

Create a Database:

Open a terminal or command prompt and run the following command to create a new PostgreSQL database:

% createdb myexampledb

Replace myexampledb with your desired database name. Note that all the examples in glossary will use dagsterdb as the database name.

Connect to the Database:

You can connect to the newly created database using psql with the following command:

psql -U your_username -d dagsterdb

Replace your_username with your PostgreSQL username as show in in the whoami command.

Create a Table:

Once connected to the database, you can create a table to store employee information with the following SQL command:

CREATE TABLE employees (
    employee_id serial PRIMARY KEY,
    name VARCHAR (255),
    salary DECIMAL (10, 2)
);

This SQL command creates a table named employees with columns for employee_id, name, and salary. Adjust the column names and data types to match your specific requirements.

Now, you should have a PostgreSQL database with a table named employees ready for use with our Python examples.

The Radiohead Table

For some examples, we will use a dataset based on the members of the British band Radiohead, which can be created and populated as follows:

Create the table

CREATE TABLE band_members (
    name VARCHAR(255),
    role VARCHAR(50),
    created_date DATE
);

Populate the table

INSERT INTO band_members (name, role, created_date) VALUES
('Thom Yorke', 'vocals', '1968-10-07'),
('Jonny Greenwood', 'guitar', '1971-10-05'),
('Ed OBrien', 'guitar', '1968-04-15'),
('Philip Selway', 'drums', '1967-05-23'),
('Colin Greenwood', 'bass', '1969-06-26');