19  PostgreSQL

PostgreSQL, often pronounced “Post-gress-Q-L”, is an advanced, open-source relational database management system (RDBMS) known for its robustness and capabilities in managing complex data operations.

19.1 Installation

1. Download and install PostgreSQL from here.
2. Select the default installation directory.
3. Select these components:
   • PostgreSQL Server
   • pgAdmin4
   • Command Line Tools
4. Select the default data installation directory.
5. Password: Set a memorable password; it is critical as it cannot be changed easily later.
6. Port: Accept the default suggested port (usually 5432)
7. Locale: Set to Default Locale.

19.2 PgAdmin4

pgAdmin4 is the official graphical management tool for PostgreSQL, providing a user-friendly interface for database administration. It is installed as part of the PostgreSQL installation.

19.2.1 Add New Server

To manage databases, first connect to a PostgreSQL instance by setting up a new server connection in pgAdmin4:

1. Open pgAdmin4.
2. Click Add New Server found under “Quick Links”.
3. For the name, enter local-connection.
4. Switch to the Connection tab:
   • Host name/address: Enter localhost.
   • Make sure the port is the same as set in the installation process.
   • Password: Input the password set during installation and select “Save password”.
5. Confirm the setup by clicking Save.
6. This will display the local-connection in the left sidebar.

19.2.2 Create a Database

Next, create a new database to store data:

Default PostgreSQL Database

When you install PostgreSQL, it automatically creates a default database named postgres. This database is typically used for administrative purposes and testing. It is always advisable to create new databases for application-specific data to avoid cluttering the default database with application data.

1. In pgAdmin4, right-click on the local-connection server in the left sidebar.
2. Navigate to: Create > Database.
3. In the “General” tab, enter db_data as the database name.
4. Click Save to create the database.

19.2.3 Create a Table

Create a table to store data about marketing campaigns.

Table definition

Columns in the Marketing Campaigns Table: - id: A unique identifier for each campaign, auto-incremented. - name: The name of the marketing campaign. - start_date & end_date: The time frame of the campaign. - budget: The budget allocated to the campaign. - channel: The channel used for the campaign (e.g., “Email”, “Social Media”, “TV”).

1. Navigate to the db_data database.
2. Open the Query Tool from Tools > Query Tool.
3. Enter the following SQL to create the campaigns table:

CREATE TABLE campaigns (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    start_date DATE,
    end_date DATE,
    budget DECIMAL(10, 2),
    channel VARCHAR(255)
);

Table Definition Explanation

The SQL command above creates a new table named campaigns. Here’s a breakdown of each column definition:

• id: A column named id that uses the BIGSERIAL data type, which is an auto-incrementing integer suitable for a primary key.
  
• name: A column for storing campaign names as text, defined to hold a string of up to 255 characters. The NOT NULL constraint ensures that every row must have a name.
• start_date and end_date: These columns store the start and end dates of the campaigns, respectively, with the DATE data type.
  
• budget: A column for the budget associated with each campaign, using DECIMAL(10, 2) to allow for up to 10 digits with 2 decimal places.
  
• channel: A column to specify the medium used for the campaign, capable of storing strings up to 255 characters.

4. Execute the script by clicking the ▶️ button (or press F5).
5. To verify, expand “Schemas” > “Tables” > “campaigns” > “Columns” in the database db_data.

19.2.4 Insert Values into the Table

Populate the table with sample data:

1. Again, use the Query Tool to execute the following insert statement:

INSERT INTO campaigns (name, start_date, end_date, budget, channel) VALUES
('Winter Sale', '2023-12-01', '2023-12-31', 5000.00, 'Email'),
('Summer Festival', '2023-06-01', '2023-06-15', 10000.00, 'Social Media'),
('Back to School', '2023-08-01', '2023-08-20', 7500.00, 'TV');

Insert Statement Explanation

This SQL command inserts data into the campaigns table. Each row of values corresponds to a different marketing campaign:

• First Row: Inserts a campaign called “Winter Sale” running from December 1 to 31, 2023, with a budget of $5000, using the “Email” channel.
  
• Second Row: Inserts “Summer Festival”, a shorter campaign from June 1 to 15, 2023, with a higher budget of $10000, and utilizing “Social Media”.
  
• Third Row: Adds the “Back to School” campaign from August 1 to 20, 2023, with a budget of $7500, marketed through “TV”.
  Each set of values is enclosed within parentheses and separated by commas to delineate different campaigns.

2. Run the script by clicking the ▶️ button.

19.2.5 Retrieve values

Finally, view the data stored in the campaigns table:

• Open a new Query Tool session and execute a SELECT query:

SELECT * 
FROM campaigns;

SELECT Query Explanation

This SQL command retrieves all columns from all rows in the campaigns table. It is useful for viewing all the data that has been entered into the table, ensuring that entries are correct and to facilitate further data analysis or reporting.

This should output:

id	name	start_date	end_date	budget	channel
1	Winter Sale	2023-12-01	2023-12-31	5000.00	Email
2	Summer Festival	2023-06-01	2023-06-15	10000.00	Social Media
3	Back to School	2023-08-01	2023-08-20	7500.00	TV