20.8 PostgreSQL

20200819

The PostgreSQL database is simple to install and test on a local machine. Below we install the software, create a database user (role) having the same username as the Linux user on the host system and grant the user superuser privileges. We also create a default database for that user.

$ wajig install postgresql
$ sudo -u postgres createuser --interactive
Enter name of role to add: kayon
Shall the new role be a superuser? (y/n) y
$ createdb kayon

We can then start the database client with the psql command. Here we illustrated the creation of a new table and populate it from a csv file.

$ psql
sql (12.2 (Ubuntu 12.2-4))
Type "help" for help.

kayon=# CREATE TABLE iris(
  sepal_length numeric,
  sepal_width numeric,
  petal_length numeric,
  petal_width numeric,
  species text);
CREATE TABLE
kayon-# \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | iris | table | kayon
(1 row)

kayon=# COPY iris FROM '/home/kayon/iris.csv' WITH (FORMAT csv, HEADER true);
COPY 150

We can then query the database to check it has been properly populated.

kayon=# SELECT * FROM iris LIMIT 5;
 sepal_length | sepal_width | petal_length | petal_width | species 
--------------+-------------+--------------+-------------+---------
          5.1 |         3.5 |          1.4 |         0.2 | setosa
          4.9 |         3.0 |          1.4 |         0.2 | setosa
          4.7 |         3.2 |          1.3 |         0.2 | setosa
          4.6 |         3.1 |          1.5 |         0.2 | setosa
          5.0 |         3.6 |          1.4 |         0.2 | setosa
(5 rows)

kayon=# \q

---

Your donation will support ongoing availability and give you access to the PDF version of this book. Desktop Survival Guides include Data Science, GNU/Linux, and MLHub. Books available on Amazon include Data Mining with Rattle and Essentials of Data Science. Popular open source software includes rattle, wajig, and mlhub. Hosted by Togaware, a pioneer of free and open source software since 1984. Copyright © 1995-2022 Graham.Williams@togaware.com Creative Commons Attribution-ShareAlike 4.0