DATABASE: PostgreSQL Commands

Originally written on 2020-03-04. DEV's displayed publication date reflects this archive migration.

PostgreSQL Basic Commands

You can always check the manual and its useful Appendixes section.

• versioning: article

  # in Ubuntu: check currently installed versions:
  $ dpkg --get-selections | grep postgres
  

• PostgreSQL packages for Debian and Ubuntu

  Quickstart and News, how to install the latest packages of your distribution

• prompt

  # on mac
  $ psql db_name
  db_name=>
  

• In Ubuntu, commands are a little bit different, check here: PostgreSQL manual in Ubuntu

  $ sudo -u postgres psql db_name
  db_name=#
  # 'sudo -u postgres' <-- means command on behalf of superuser 'postgres'
  
  # you can also switch to superuser like this:
  $ sudo su - postgres
  $ psql
  postgres=#
  

• change postgres password

  $ sudo -u postgres psql
  postgres=> \password
  

• restart psql server

  $ sudo systemctl restart postgresql
  

• quick command in Ubuntu

  $ sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"
  $ sudo -u postgres psql -c "CREATE DATABASE testdb;"
  

• start and stop postgresql service

  $ sudo service postgresql start
  
  $ sudo service postgresql stop
  

• createdb and dropdb

  $ createdb db_name
  # if db_name is not specified, system user name will be used as the db_name
  
  $ dropdb mydb
  # a db_name has to be specified
  

• psql - PostgreSQL interactive terminal commands:

  mydb=> SELECT version();
  mydb=> SELECT current_date;
  mydb=> \h
  mydb=> \q
  

• create table columns (header)

  mydb=>
  CREATE TABLE weather (
      city            varchar(80),
      temp_lo         int,           -- low temperature
      temp_hi         int,           -- high temperature
      prcp            real,          -- precipitation
      date            date
  );
  ## -- comment, code after this won't be executed
  
  mydb=>
  CREATE TABLE cities (
      name            varchar(80),  -- this is a column
      location        point         -- another column
  );
  

• delete a table

  mydb=> DROP TABLE tablename;
  

• insert rows

  after the columns are defined you can INSERT data in rows:
  

  mydb=>
  INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
  
  INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)')
  

  If you do not remember the order of the column:
  

  mydb=>
  INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
  VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
  
  INSERT INTO weather (date, city, temp_hi, temp_lo)
  VALUES ('1994-11-29', 'Hayward', 54, 37);
  

• COPY

  mydb=>
  COPY weather FROM '/home/user/weather.txt';
  

• Querying a Table

  • ### to retrieve all the rows of table weather

  # option 1
  SELECT * FROM weather;
  # option 2
  SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
  # exactly the same output
  

  city	temp_lo	temp_hi	prcp	date
  San Francisco	46	50	0.25	1994-11-27
  San Francisco	43	57	0	1994-11-29
  Hayward	37	54		1994-11-29

  • ### flexible and editable

  SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
     city        | temp_avg |    date
  ---------------+----------+------------
  San Francisco  |       48 | 1994-11-27
  San Francisco  |       50 | 1994-11-29
  Hayward        |       45 | 1994-11-29
  

  • ### be selective / filtering Retrieve the weather of San Francisco on rainy days

  SELECT * FROM weather
      WHERE city = 'San Francisco' AND prcp > 0.0;
      # AND, OR, NOT can be used as Boolean operators
  

  • ### ordering

  SELECT * FROM weather
      ORDER BY city;
  
          city      | temp_lo | temp_hi | prcp |    date
      ---------------+---------+---------+------+------------
      Hayward       |      37 |      54 |      | 1994-11-29
      San Francisco |      43 |      57 |    0 | 1994-11-29
      San Francisco |      46 |      50 | 0.25 | 1994-11-27
  

  • ### be DISTINCT

  SELECT DISTINCT city
      FROM weather;
  
      city
  ---------------
  Hayward
  San Francisco
  (2 rows)
  

  • ### be DISTINCE in ORDER

  SELECT DISTINCT city
      FROM weather
      ORDER BY city;
  

• UPDATE

  Suppose you discover the temperature readings are all off by 2 degrees after November 28. You can correct the data as follows:
  

  UPDATE weather
      SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
      WHERE date > '1994-11-28';
  

• DELETE

  Delete rows whoes data matches value preceded by WHERE:
  

  DELETE FROM weather WHERE city = 'Hayward';
  

  Note: The system will not request confirmation before command like this:

  DELETE FROM tablename;
  

  All rows from the tabel will be removed, leaving an empty table.