Introduction to Databases
A database allows applications to store data permanently. Relational databases use Structured Query Language (SQL) to manage and interact with the data.
Installing and Accessing PostgreSQL
- After installing PostgreSQL, launch it from a bash prompt with
psql
. - Commands are issued at the
postgres=#
prompt.
Navigating PostgreSQL
- Use
\list
to display all databases currently in PostgreSQL. - If command output is long,
:▮
indicates more content. Scroll withreturn
(line by line) orspace
(page by page), and pressQ
to exit.
Creating Databases
- Create a new database with the following command:
CREATE DATABASE database_name;
- A semicolon is required at the end of SQL commands.
- Confirmation is shown as
CREATE DATABASE
.
Managing Databases
- Switch to a new database with:
\connect database_name
- The prompt will change to reflect the new database context, e.g.,
database_name=#
.
Creating Tables
- Example of creating a table named
contacts
:
CREATE TABLE contacts (
id SERIAL PRIMARY KEY,
first_name TEXT,
last_name TEXT,
date_of_birth DATE,
street_address_1 TEXT,
street_address_2 TEXT,
city TEXT,
state TEXT,
zip TEXT,
phone TEXT,
notes TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
Viewing Tables
- To see all tables, use:
\dt
Data Types
- Common data types include:
-
TEXT
: Similar to String. -
INTEGER
: A whole number. -
BOOLEAN
: true, false, or nil. -
DATE
: A date value without a time part. -
TIME
: A time value without a date part. -
TIMESTAMP
: For values that have both date and time parts. -
DECIMAL
: Similar to Float.
-
Inserting Data
- To insert data into the
contacts
table:
INSERT INTO contacts (
first_name,
last_name,
date_of_birth,
street_address_1,
street_address_2,
city,
state,
zip,
phone,
notes
) VALUES (
'Carol',
'Reynolds',
'2016-10-20',
'4556 Mirna Shores',
'Apt. 111',
'Stromanhaven',
'DE',
'13654-8312',
'308-571-8066 x3565',
'We met at the networking event'
);
Top comments (0)