Notes

Getting started with PostgreSQL on Ubuntu

getting started with postgresql on ubuntu, creating roles and databases, the psql commands i need, and gotchas like quoting and escape strings

Edit on GitHub

Databases
3 minutes
PostgreSQL Ubuntu SQL
 1# PostgreSQL comes pre-installed on Ubuntu
 2sudo apt install postgresql postgresql-contrib
 3
 4# Start PostgreSQL
 5sudo systemctl start postgresql.service
 6
 7# create a user and give it super privileges
 8sudo -u postgres createuser --interactive
 9
10# create a db with the same username that postgres access by default
11# this DID NOT work for me..
12sudo -u postgres createdb sammy

PostgreSQL commands

  • Make sure you end all commands with a semicolon ;
  • Users are actually called roles
\l - list all databases
 - list all users
\q - exit out of the prompt

 1-- create a database
 2CREATE DATABASE [IF NOT EXISTS] db_name;
 3
 4-- connect to a database
 5\c database_name;
 6
 7-- drop a database permanently
 8DROP DATABASE [IF EXISTS] db_name;
 9
10-- create a role
11CREATE ROLE role_name;
12
13-- create a role with username and password
14CREATE ROLE username NOINHERIT LOGIN PASSWORD password;
15
16-- set a password for an existing user
17ALTER USER postgres PASSWORD 'myPassword';
  • You need a linux user with the same name as the postgresql user and database
  • TablePlus will refuse to connect without a password
  • PostgreSQL is fussy about using single commas ' instead of doubles
  • For escape characters to work, you need to prepend the text with E. For example: E'Ocean\'s Eleven'

Sample commands

  • CREATE TABLE creates a new table.
  • INSERT INTO adds a new row to a table.
  • SELECT queries data from a table.
  • ALTER TABLE changes an existing table.
  • UPDATE edits a row in a table.
  • DELETE FROM deletes rows from a table.
1-- create a database
2CREATE TABLE celebs (
3   id INTEGER,
4   name TEXT,
5   age INTEGER
6 );

Auto-increment values with SEQUEL constraint. SEQUEL represents an auto-incrementing unique value. For example: id SERIAL PRIMARY KEY.

1-- insert data into table
2INSERT INTO celebs (id, name, age) 
3VALUES (1, 'Justin Bieber', 22);

1-- insert multiple rows
2INSERT INTO table_name (column_list)
3VALUES
4    (value_list_1),
5    (value_list_2),
6    ...
7    (value_list_n);

1-- add a new column
2ALTER TABLE celebs 
3ADD COLUMN twitter_handle TEXT;

1-- add multiple columns
2ALTER TABLE table_name
3ADD COLUMN column_name1 data_type constraint,
4ADD COLUMN column_name2 data_type constraint,
5...
6ADD COLUMN column_namen data_type constraint;

 1-- update data
 2UPDATE celebs 
 3SET twitter_handle = '@taylorswift13' 
 4WHERE id = 4; 
 5
 6UPDATE films
 7SET runtime = 92,
 8    category = 'animation',
 9    rating = 8,
10    box_office = 5774000000   
11WHERE name = 'Monsters, Inc.';

1-- delete records
2DELETE FROM celebs 
3WHERE twitter_handle IS NULL;

1-- escaping characters
2-- the text needed to be preceded with 'E'
3INSERT INTO films (name, release_year)
4VALUES(E'Ocean\'s Eleven',  2001);

1-- change data type of a column
2ALTER TABLE films
3ALTER COLUMN rating SET DATA TYPE float(1);

Related

Browse all topics