pgAdmin

PgAdmin is an application that provides a GUI for our postgres database. It should have been installed with PostgreSQL together. Otherwise go to this website and download pgAdmin.

After starting pgAdmin we should see a dashboard like the following one:

Click on the postgres database in the left sidebar. This is the default database that is created by PostgreSQL. We will work with that database. Next click on the Query Tool icon, which is the icon right to the "Browser" text. Now a query console should be visible like you see it in the image. Past the following SQL code into the query console:

CREATE TABLE IF NOT EXISTS "users" (
   "id" SERIAL PRIMARY KEY,
   "username" VARCHAR(255) UNIQUE,
   "hashed_password" VARCHAR(255),
   "height" VARCHAR(255),
   "weight" VARCHAR(255),
   "role" VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS "messages" (
   "id" SERIAL PRIMARY KEY,
   "sender" varchar(255),
   "title" VARCHAR(255),
   "message" VARCHAR(255),
   "time" TIMESTAMP NOT NULL DEFAULT NOW()
);
-- athlete und user zusammengefasst

CREATE TABLE IF NOT EXISTS "tasks" (
   "task_id" SERIAL PRIMARY KEY, 
   "description" VARCHAR(255),
   "assignee" VARCHAR(255),
   "link" VARCHAR(255),
   "due" TIMESTAMPTZ NOT NULL,
   CONSTRAINT fk_tasks_username FOREIGN KEY (assignee) REFERENCES users(username)
);

Press F5 or click on the triangle above the query console. This should create three tables in our database.

Explaining the SQL code

The given SQL code is creating three tables in a PostgreSQL database: "users", "messages", and "tasks". Let's go through each table and explain their columns and constraints.

Table "users":

Columns:

• id: SERIAL data type, which is an auto-incrementing integer used as the primary key for each user.

• username: VARCHAR(255) data type, representing the username of the user. It is also marked as UNIQUE, ensuring that each username is unique in the table.

• hashed_password: VARCHAR(255) data type, representing the hashed password of the user.

• height: VARCHAR(255) data type, representing the height of the user.

• weight: VARCHAR(255) data type, representing the weight of the user.

• role: VARCHAR(255) data type, representing the role of the user.

Table "messages":

Columns:

• id: SERIAL data type, serving as the primary key for each message.

• sender: VARCHAR(255) data type, representing the sender of the message.

• title: VARCHAR(255) data type, representing the title of the message.

• message: VARCHAR(255) data type, representing the content of the message.

• time: TIMESTAMP data type, NOT NULL constraint, with a default value of the current timestamp (using the NOW() function). This column captures the time when the message is created.

Table "tasks":

Columns:

• task_id SERIAL data type, serving as the primary key for each task.

• description: VARCHAR(255) data type, representing the description of the task.

• assignee: VARCHAR(255) data type, representing the username of the user to whom the task is assigned.

• link: VARCHAR(255) data type, representing a link related to the task.

• due: TIMESTAMPTZ data type, NOT NULL constraint, representing the due date and time of the task.

Constraints:

• fk_tasks_username: This is a foreign key constraint that references the "username" column in the "users" table. It ensures that the "assignee" column in the "tasks" table contains values that already exist in the "username" column of the "users" table. Overall, these tables and their columns provide a basic structure for storing information about users, messages, and tasks in a PostgreSQL database.

What is a VARCHAR?

In SQL, VARCHAR is a data type used to store variable-length character strings. The number inside parentheses after VARCHAR specifies the maximum length of the string that can be stored in that column.

In the given SQL code, VARCHAR(255) is used to define several columns, such as "username", "hashed_password", "height", "weight", "role", "sender", "title", "message", "description", "assignee", and "link".

VARCHAR(255) means that these columns can store character strings with a maximum length of 255 characters. The actual length of the stored string can be any value up to 255 characters. If you try to insert a string longer than 255 characters into a column defined as VARCHAR(255), the database will truncate the string to fit the specified length.

The choice of 255 as the maximum length is common but not necessarily the optimal choice for every situation. The length specified should be based on the expected data requirements and constraints of your specific application. If you anticipate that the stored strings will typically be shorter, you can reduce the maximum length to save storage space. On the other hand, if you anticipate longer strings, you can increase the maximum length accordingly.

It's worth noting that the length specified in VARCHAR does not directly impact the storage space. The actual storage space used will depend on the length of the data being stored.

What are primary keys?

A primary key is a column or a set of columns in a table that uniquely identifies each row or record in that table. It serves as a unique identifier for the data in the table and ensures that there are no duplicate or null values in the primary key column(s). Each table in a relational database should have a primary key defined.

Here are some key characteristics of primary keys:

Uniqueness: Each value in the primary key column(s) must be unique, meaning no two rows in the table can have the same primary key value. This uniqueness constraint helps to differentiate and identify individual records.

Non-nullability: Primary key columns cannot contain null values. Every row must have a valid and non-null value in the primary key column(s).

Indexing: Primary keys are typically indexed automatically by the database system. This indexing improves the performance of queries that involve searching, joining, or referencing the primary key column(s).

Referential integrity: Primary keys often play a role in establishing relationships with other tables through foreign keys. Foreign keys in other tables can reference the primary key column(s) of a table, creating a link between related data and enforcing referential integrity.

In the given SQL code, the "id" column in the "users" table, the "id" column in the "messages" table, and the "task_id" column in the "tasks" table are all defined as primary keys. These primary keys uniquely identify each row in their respective tables and ensure data integrity and efficient querying.

What are foreign keys?

Foreign keys are a fundamental concept in relational databases, including PostgreSQL. A foreign key is a column or a set of columns in a table that establishes a link or relationship between the data in two tables. It enforces referential integrity, meaning that it ensures that the data in the referencing table (child table) corresponds to the data in the referenced table (parent table).

In the context of the given SQL code, the "fk_tasks_username" constraint in the "tasks" table is an example of a foreign key. It references the "username" column in the "users" table. This means that the "assignee" column in the "tasks" table can only contain values that already exist in the "username" column of the "users" table. If you try to insert or update a row in the "tasks" table with an "assignee" value that does not exist in the "users" table, it will result in a foreign key constraint violation and the operation will fail.

Foreign keys are useful for maintaining data integrity and enforcing relationships between tables. They help ensure that data remains consistent and accurate across different tables in a database. They also facilitate join operations, allowing you to retrieve related data from multiple tables based on their established relationships.

Looking at the tables

Now click on Schemas in the left sidebar. Here you can see the tables (also called schemas) of your database. Expand the public list and then click on Tables (3). Here you can see the tables we have created.

You can also view the columns of a table by clicking on Columns under the table name.

Look at the data of the table

Now we want to inspect the data in our table. For that click on the table name (messages in this example) and then on the View Data button above the sidebar.

We now see the data inside our messages table. Because we haven't added any data yet we won't see any records.

pgAdmin also displays the SQL query that can be used for producing the shown result. In this example the query for querying all messages would be:

SELECT * FROM public.messages
ORDER BY id ASC

Now let's add some data. For that we click on the Query Toolbutton again and insert the following query into the SQL console:

INSERT INTO messages (sender, title, message, time)
VALUES ('John', 'Call at 2pm', 'We arranged a call at 2pm', now());

Press F5 or click on the triangle button to execute this query. You should see the message Query returned successfully. This means the data has been added to our table.

Now again select the messages table and click on the View Data button. You should now see the record displayed in the table of results.

Using ChatGPT to create SQL queries

A new software named ChatGPT has arrived to the market which will be part of every developer's daily used tools. ChatGPT is a powerful chatbot that is able to write code based on a natural language question the user asks. Let's see how we can use ChatGPT to create new SQL queries.

First let's ask ChatGPT the following question:

I have a PostgreSQL database with a schema named "messages". The schema has the following attributes:

- "id" which is a primary key that is set automatically
- "sender" which is of type character varying
- "title" which is of type character varying
- "message" which is of type character varying
- "time" which is a timestamp without timezone

I want to add new records to this schema. Can you write me the SQL query for that task?

ChatGPT created the following answer for me:

Now let's ask ChatGPT how we can update an existing record in the database.

I want to update an existing record. How can I do this?

This is the response of ChatGPT:

And finally let's ask ChatGPT how we can delete records from our database.

I want to delete the record with the ID 5. How can I do this?

And the answer is:

Asking ChatGPT more questions

It's your turn now. Ask ChatGPT the following questions and try the SQL code that is generated by ChatGPT. How do you like it?

Quesions:

• I want to get all users with the name "John"

• I want to get all messages of the last 24 hours

• I want to update the timestamp of the message with the ID 2 to the current time

• I want to delete the messages with the IDs 2, 4 and 7

• I want to update all messages where the user equals "John" to "Jane"

• How can I order the results by the timestamp?

• How can I add a new column named "read" of type boolean to the schema?

Prerequisites - Using programming languages to query databases

Now that we successfully have installed PostreSQL, created our tables and inserted some data into it, we want to use the data of those tables in our programs. For PostgreSQL nearly any modern programming language has libraries that allow us to query these tables. In this section we want to try to query the Database first with Python and then with JavaScript.

Using Pyhon to query the database

For this example I assume you have installed Python v3 on your machine. You also need to install the psycopg2 library by running the command pip install psycopg2.

Lets create a file named app.py on our local machine with the following code:

import psycopg2

# Establish a connection to the PostgreSQL database
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    database="postgres",
    user="postgres",
    password="root"
)

# Create a cursor object to execute SQL queries
cur = conn.cursor()

# Execute the SELECT query to fetch all records from the "messages" table
cur.execute("SELECT * FROM messages")

# Fetch all the rows returned by the query
rows = cur.fetchall()

# Iterate over the rows and print the data
for row in rows:
    print("ID:", row[0])
    print("Sender:", row[1])
    print("Title:", row[2])
    print("Message:", row[3])
    print("Time:", row[4])
    print()

# Close the cursor and connection
cur.close()
conn.close()

Now if we run the script by using the command python app.py we should get the following output:

$ python app.py 
ID: 2
Sender: John
Title: Call at 2pm
Message: We arranged a call at 2pm
Time: 2023-05-03 17:09:21.098497