Getting Started - Basic PostgreSQL Commands

Introduction

PostgreSQL supports a wide range of SQL commands for managing databases, tables, and data. Here are some essential commands to get started.

To create a new database in PostgreSQL, use the following command:


CREATE DATABASE mydatabase;

CREATE DATABASE

Replace mydatabase with your desired database name.

Connect to an existing database using the following command:


\c mydatabase;

You are now connected to database "mydatabase" as user "postgres".

Replace mydatabase with the name of the database you want to connect to.

Create a new table within the connected database using the following syntax:


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

CREATE TABLE

This example creates a table named users with columns id, username, and email.

Insert data into a table using the INSERT INTO command:


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

INSERT 0 1

This inserts a new record into the users table with specified username and email.

Retrieve data from a table using the SELECT command:


SELECT * FROM users;

 id | username |         email         
----+----------+------------------------
  1 | john_doe | john.doe@example.com
(1 row)

This query returns all rows and columns from the users table.

Update existing data in a table using the UPDATE command:


UPDATE users
SET email = 'johndoe@example.com'
WHERE username = 'john_doe';

UPDATE 1

This example updates the email for the user with username 'john_doe'.

Delete data from a table using the DELETE FROM command:


DELETE FROM users
WHERE username = 'john_doe';

DELETE 1

This deletes the record from the users table where username is 'john_doe'.