Getting Started with PostgreSQL: Basic Queries — CREATE, INSERT, & SELECT

Are you new to PostgreSQL? Using a new database technology for the first time can be fun, yet intimidating! But rest assured! This article will take you to the amusing world of PostgreSQL step by step, by exploring strengths and usefulnesses as a relational database management system. Without further ado, let’s dive into the three principle queries, CREATE, INSERT, and SELECT.

CREATE Query
New database objects can be created with the CREATE query, including tables, indexes, views, and even whole databases. We’ll concentrate on creating a table in this article. Let’s create a simple table called “users” to store user information:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50)NOT NULL,
  email VARCHAR(100)UNIQUE NOT NULL,
  age INT
);

The “users” table in this example has four columns: “id,” “username,” “email,” and “age.” Since “id” is specified to be a serial type, it will automatically increase for each new record.

INSERT Query
We can insert new records (rows) into the table we made using the CREATE query by using the INSERT query.

INSERT INTO users (username, email, age)
VALUES ('john_doe','john@example.com',30);

In this example, we inserted a new user, John Doe, 30 years old with the email address, “ john@example.com.”

SELECT Query
Data from the database is retrieved using the SELECT query. You can use it to sort, filter, and get particular data from the tables.

Let’s retrieve all users from the “users” table:

SELECT*FROM users;

If you would like to retrieve specific columns, you can specify them in the SELECT sstatement such as name and age:

SELECT username, age FROM users;

Let’s summarize!

The CREATE query allows you to create new database objects, such as tables.
The INSERT query enables you to add new records to the tables.
And finally, the SELECT query enables you to filter and get specific data by allowing you to retrieve data from the databases.

Now, you should be able to CREATE, INSERT, and SELECT your data into and from the table! Hang tight for our next post to explore more in depth about PostgreSQL!

At AGEDB, we offer extensive graph tools pluggable onto your PostgreSQL, and the enterprise-grade Postgres-powered databases. Interested in knowing more? — Visit our website to learn more!