Getting Started with PostgreSQL

PostgreSQL RDBMS

• Postgres is an open source object-relational database management system with:
  • reliability and flexibility
  • support for both relational and non-relational data types
• Postgres is a popular database choice for:
  • OLTP
  • data analytics
  • geographic information systems.
• Tools:
  • psql command line
  • pgAdmin: provides an open source graphical interface to the database server which is available as a desktop application or as a web application that you can install on your web servers.
  • Navicat, DBeaver: commercial graphical interface options that you can use to access Postgres, MySQL, and other types of database
  • Cloud vendor tools and APIs: For example, Amazon RDS for PostgreSQL provides a web-based management console and RDS APIs

Using psql

• an interactive command line tool
• You can run interactive queries and display information about the objects in your database

Using pgAdmin

• You can use pgAdmin to complete all your development and administrative tasks, including creating databases and tables, loading data, querying data, writing stored procedures and functions, managing database objects, managing security, and monitoring usage.
• Includes:
  • Query Tool: you can use to run SQL commands and view or interact with their results
  • ERD Tool: you can use to create an ERD for an existing database or to create a new ERD and generate the SQL statements for creating the underlying database objects

Creating Databases and Loading Data in PostgreSQL

Using psql to create a database

Example:

CREATE DATABASE employees;
\connect employees;

CREATE TABLE employee_details (firstname VARCHAR(20), lastname VARCHAR(20),
                              startdate DATE, salary DECIMAL);

• Use \d employee_details command to show the structure of the created table

Using psql to restore data

• Restore a previously backed up database

psql restored_employees < employeesbackup.sql

• restored_employees: the name of the destination database

• employeesbackup.sql: the name of the dump file

• Recreates:

  • Tables
  • Other database objects
  • Data

Creating a database in pgAdmin

To create the database, in the treeview in the left-hand pane, right-click Databases, click Create, and then click Database.

Restoring a database in pgAdmin

Select the database that you want to restore to in the treeview, click Restore, and then in the Restore box, enter the location of your dump file

Creating a table in pgAdmin

In the treeview, right-click Tables, click Create, and then click Table.

On the General page, enter the name for your table.

Then on the Columns tab, enter the details for your columns, and then click Save.

Loading data

In the Import/Export data box, select Import, and then enter the location and filename for your data file

If you’re loading data from a CSV file, you don’t need to specify the delimiter because it’s the default option for CSV files.

Then use the View/Edit Data option to review the loaded data.

Exporting data

Use the Import/Export function to export existing data in your database to a CSV file for use elsewhere.

CSV is the default format, so you can just specify the filename and then click OK.

Using pg_dump

• Back up a database

Example:

pg_dump employees > employeesbackup.sqp

• employees: the database name

• employeesbackup.sqp: the filename for the dump file

• Backs up:

  • Schema
  • Data

Hands-on Lab: Getting started with PostgreSQL command line

Hands-on Lab: Create Tables and Load Data in PostgreSQL using pgAdmin

Views

What is a view?

• A view is an alternative way of representing data from one or more tables or other views
• Can interact with views in the same way as you interact with tables
• Use to:
  • Limit access to sensitive data
  • Simplify data retrieval
  • Reduce access to underlying tables

Materialized views

• Behave differently to regular views
• Result set is materialized, or saved, for future use
• Cannot insert, update, or delete rows
• Can improve performance

Hands-on Lab: Views in PostgreSQL

Summary & Highlights

Congratulations! You have completed this lesson. At this point in the course, you know:

PostgreSQL is an open-source object-relational database management system that you can download and install on your own systems or access on the Cloud.

You can either self-manage a Cloud instance of PostgreSQL or use a managed services provider, including IBM Cloud Databases for PostgreSQL, Amazon RDS, Google Cloud SQL for PostgreSQL, EnterpriseDB cloud, or Microsoft Azure for PostgreSQL.

PostgreSQL includes several options for creating databases and tables, loading and querying data, and importing and exporting data relational databases:

• The psql command line interface. You use this CLI to run SQL statements.
• pgAdmin. A graphical interface to the database server, which is available as a desktop application or as a web application that you can install on your web servers.
• Navicat and Dbeaver. Commercial graphical interface options that you can use to access PostgresSQL, MySQL, and other types of databases.
• Cloud vendor tools and APIs.

Using pgAdmin, you can:

• Use pg_dump to back up databases and psql to restore them.
• Use the Import/Export tool to load data into and export data from tables.

Using views:

• You can use views to limit access to sensitive data and simplify data retrieval.
• Views can be materialized, which means that the view store the result set for quicker subsequent access.
• Materialized views enhance performance because the view is saved and often stored in memory. However, you cannot insert, update, or delete rows in a materialized view, and they must be refreshed before you can see updated data.

Practice Quiz: PostgreSQL

TOTAL POINTS 5

Question 1

True or False: When you run SQL queries in the pgAdmin Query Tool, you can always edit the results.

• True
• False

Correct. You can edit the results if they are editable. Otherwise, the results are read only.

Question 2

When loading data into a PostgreSQL table, what is the default delimiter for data files?

• Comma
• Quotation marks
• Tab
• Hyphens

Correct. CSV files are the default option for data files, so comma is the delimiter.

Question 3

Where do you create views in a PostgreSQL database?

• In a query
• In a table
• In a schema
• In two or more tables

Correct. Views are created in a schema.

Question 4

When you first connect to a PostgreSQL database server, which database do you connect to?

• The template1 database.
• No database. You must create a new database before you can connect one.
• The template0 database.
• The default database.

Correct. Every PostgreSQL database server has a default database to which you connect when it first starts. From here, you can either edit the default database or create new ones.

Question 5

Which PostgreSQL commands can you use to load a new database with data from a backup? Select two answers

• Load the dump file using the pgAdmin Restore command.
• Load the dump file using the pgAdmin Import/Export data command.
• Load the dump file using the CREATE DATABASE command.
• Load the dump file using the psql command.

Correct. The psql command is one way to load a new database with data from a backup.

Graded Quiz: PostgreSQL

LATEST SUBMISSION GRADE 100%

Question 1

Which tool can you use to both design and create a Postgres database?

• psql
• pgAdmin
• Query Tool
• ERD Tool

Correct. You can use the ERD Tool in pgAdmin to create an entity-relationship diagram (ERD) and the generate the SQL statements for creating the underlying database objects from it.

Question 2

When using the pgAdmin Restore command, which of the following items are recreated in the database?

• Tables
• Passwords
• Data
• Data types

Correct. Tables, data, and other database objects, like data types, are recreated in the database.

Question 3

What is a main difference between regular views and materialized views?

• Regular views can improve performance.
• You can insert, update, or delete data in regular views.
• Regular views are saved for future use.
• Regular views are often stored in memory.

Correct. You can interact with regular views in the same way you interact with tables, meaning you can insert, update, or delete data as required. You cannot do this with materialized views.

Question 4

When creating a new table in a database using pgAdmin, on which tab of the Create Table dialog box do you specify data types?

• Constraints
• General
• Parameters
• Columns

Correct. On the Columns tab, you can enter the details for each column in the table, including the data type or length.

Question 5

What SQL statement do you use to define a view in PostgreSQL?

• VIEW
• JOIN
• CREATE VIEW
• SELECT

Correct. To define a view, you add the SQL SELECT statement to the Code page.