top of page

PostgreSQL

Wha is it?

  • PostgreSQL is a general purpose and object-relational database management system.

  • Most advanced open source database system. 

  • it could run on various platforms such as Mac OS X, Solaris, and Windows.

  • Developed based on POSTGRES 4.2 at Berkeley Computer Science Department, University of California.

  • PostgreSQL features:

  • Table inheritance

  • Native Microsoft Windows Server version

  • Tablespaces

  • User-defined types

  • more

Installation Process

  • Google the software name; postgresql.

  • Click on the Download page of the official site.

  • Select the Operating system.

  • Choose the software version and OS bit version from drop-down menu.

  • Select the download now button to start download.

  • Double click on the downloaded software.

  • Follow the instruction, set password, and finish the installation process.

Opening PostgreSQL 

  • Search pgAdmin in windows search bar.

  • Open that application for manipulating with the database.

  • This is the window looks like.

  • Here we need to connect to the server.

  • Click on Server on left > Select PostgreSQL 10 > provide passoword > OK.

  • To create a database, right click on Databases > Create > Database > give a name of Database > set owner > Save.

  • To create a table, click on Tools on menu > Query Tool.

  • A query window will appear on right as below.

  • Write the query and execute by pressing F5, or execute button.

PostgreSQL Data Type

  • The kind of data you want to store in the table fields.

  • PostgreSQL supports a wide set of Data Types.

  • Besides, users can create their own custom data type using CREATE TYPE SQL command.

  • The followings are the datatypes:

  • Numeric Types

  • Monetary Types

  • Character Types

  • Binary Data Types

  • Data/Time Type

  • Boolean Type

  • Enumerated Type

  • Geometric Type

  • Network Address Type

  • XML Types

  • JSON Type

  • Array Type

  • Composite Types

  • Object Identifier Types​

  • Pseudo Types

PostgreSQL Queries

SELECT Data

  •  want to know the first name, last name and email of customers, you can specify the column names in the SELECT statement as follows:

  • SELECT first_name, last_name, email FROM customer;

INSERT Data

  • When you create a new table, it does not have any data. The first thing you often do is to insert new rows into the table. 

  • Syntax: INSERT INTO table(column1, column2, …) VALUES (value1, value2, …);

UPDATE Data

  • To change the values of the columns in a table, you use the UPDATE statement.

  • Syntax: UPDATE table SET column1 = value1, column2 = value2 ,... WHERE condition;

FILTER Data

  • WHERE clause to filter rows returned from the SELECT statement.

  • Syntax: â€‹SELECT column_1, column_2 … column_n FROM table_name WHERE conditions;

  • Example: â€‹SELECT last_name, first_name FROM customer WHERE first_name = 'Jamie' AND last_name = 'Rice';

DELETE Data

  • To delete data from a table, you use PostgreSQL DELETE statement as shown below:

  • DELETE FROM table WHERE condition;

bottom of page