PostgreSQL is an open source Relational Database Management System (RDBMS). In this article, I’ll provide an introduction to getting started with PostgreSQL. The mother site for PostgreSQL is http://www.postgresql.org you can read more.
Installing PostgreSQL
You can run the command below on your terminal to quickly install PostgreSQL in Ubuntu.
Ubuntu
// Refresh the apt-get repository
$ sudo apt-get update
// Install PostgreSQL
$ sudo apt-get install postgresql postgresql-contrib
To verify you’ve got PostgreSQL installed, run the following command to check your PostgreSQL version:
postgres --version
Others can download the latest version of PostgreSQL here and follow the installation steps.
Getting Started
1. Login to the PostgreSQL Server
$ sudo -u postgres psql
-- Run command "psql" as UNIX USER "postgres".
-- Enter the CURRENT SUPERUSER password for sudo.
psql (14.2 (Ubuntu 14.2-1.pgdg21.10+1))
Type "help" for help
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
-- Display version
postgres=# SELECT version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.2 (Ubuntu 14.2-1.pgdg21.10+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-7ubuntu2) 11.2.0, 64-bit
press q to quit display
-- Quit
postgres=# \q
2. Create Database, Create Table, CURD (Create-Update-Read-Delete) Records
-- Login to server
$ sudo -u postgres psql
......
-- List all databases via \l (or \list), or \l+ for more details
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | python=CTc/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
-- Create a new database called testdb
postgres=# CREATE DATABASE testdb;
CREATE DATABASE
-- Connect to testdb database via \c (or \connect)
-- Take note of the change of database name in the command prompt.
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
-- Display all tables (aka relations) via \dt or \dt+ for more details
testdb=# \dt
Did not find any relations.
testdb=# CREATE TABLE IF NOT EXISTS accounts (
user_id serial PRIMARY KEY, -- AUTO_INCREMENT integer, as primary key
username VARCHAR ( 50 ) UNIQUE NOT NULL,
password VARCHAR ( 50 ) NOT NULL,
email VARCHAR ( 255 ) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);
-- Display all tables in the current database, via \dt or \dt+ for more details
testdb=# \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------+-------+----------+-------------+---------------+---------+-------------
public | accounts | table | postgres | permanent | heap | 0 bytes |
-- Quit
testdb=# \q
- You need to end your SQL command with a semi-colon (;) every-time or \g. If you forget to enter a semi-colon (;), the command-prompt changes to "dbname-#" to indicate continuation. You can enter the semi-colon on the new line.
More on commands
- \?: show all psql commands.
- \h sql-command: show syntax on SQL command.
- \c dbname [username]: Connect to database, with an optional username (or \connect).
-
Display Commands: You can append + to show more details.
- \l: List all database (or \list). - \d: Display all tables, indexes, views, and sequences. - \dt: Display all tables. - \dv: Display all views. - \di: Display all indexes. - \ds: Display all sequences. - \dS: Display all system tables. - \dT: Display all types. - \du: Display all users. See more commands
3. Commonly-used SQL Data Types
The commonly-used SQL data types in PostgreSQL are:
-
INT, SMALLINT:whole number. -
DATE, TIME, TIMESTAMP:date and time. -
NULL:Represent known value of 0 and empty string). -
SERIAL:auto-increment integer (AUTO_INCREMENT in MySQL). -
DOUBLE:single and double precision floating-point number. -
NUMERIC(m,n):decimal number with m total digits and n decimal places (DECIMAL(m,n) in MySQL). -
CHAR(n) and VARCHAR(n):fixed-length string of n characters and variable-length string of up to n characters. String are enclosed by single quotes, e.g., 'Fredson', 'Python, django'. -
User-defined types:The ones that don't come defautly.
Top comments (0)