Installing PostgreSQL

As per my previous blog on PostgreSQL, I'm going to start a journey to study this new area of interest. As the first step I'm going to install it on my local development/testing environment. I'm gong to follow articles from PostgreSQL Tutorial site and PostgreSQL course on W3 Schools site.

I have downloaded installation package for Window from Enterprise DB site -> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads. As of this article date, I'm using version 16.3 and it is about 375 MB.

I have followed the easy installation wizard.

Select the installation path

Select components. I have selected them all as I'm studying.

• PostgreSQL server -> the core database engine which you must install
• pgAdmin4 -> GUI to manage PostgreSQL server (like SSMS)
• Stack Builder -> Drivers and stuff for development (I guess)
• Command Line Tools

Select you data directory -> I choose default which is the "Data" sub folder in installation directory

Choose password for super user (administration/sa password)

Choose the port number for the server. I left it default, which is 5432.

Choose locale.

Check the installation summary and make sure all settings selected are looks ok.

Install.

Took about 5 minutes in my not so high end test machine and at the end it has asked me whether I want to launch the stack builder to install additional drivers and all. I choose yes.

At this point main installation is done and you can check the installation by launchign pgAdmin GUI from start menu.

Optionally you can continue with stack builder setup.

Stack Builder Setup

Once main PostgreSQL finishes Stack Builder setup starts with following screen:

You need to choose your PostgreSQL server from the drop down list (in case there are more than one installation).

Then you need to select categories for components to install.

•   Add-ons, tools and utilities
• EDB Language Pack v4.3-1 (programming language pack including python)
• pgAgent (64 bit) for PostgreSQL 16 v4.2.2-1 (Job agent)
• pgBouncer v1.22.1-1 (connection pooler)

•   Database Drivers
• Npgsql v3.2.6-3 (Microsoft .NET data provider)
• pgJDBC v42.7.2-1 (JDBC driver)
• psqlODBC (32 bit) v13.02.0000-1 (ODBC driver 32bit)
• psqlODBC (64 bit) v13.02.0000-1 (ODBC driver 64bit)

•   Database Server (additional version of Postgre SQL server, which I will not install)
• PostgreSQL (64 bit) v12.19-2
• PostgreSQL (64 bit) v13.15-2
• PostgreSQL (64 bit) v14.12-2
• PostgreSQL (64 bit) v15.7-2
• PostgreSQL (64 bit) v16.3-2 (installed)

• Registration-required and trial products
• • EnterpriseDB Tools -> No idea what these are, hopefully we learn about them in future)
  • Migration Toolkit v57.0-1
  • PEM SQL Profiler Plugin for PostgreSQL 16 (64 bit) v41.2
  • Postgres Enterprise Manager Agent v8.7.1-2
  • Postgres Enterprise Manager Agent v8.7.0-1
  • Postgres Enterprise Manager Server v8.7.0-1
  • Postgres Enterprise Manager Server v9.6.0-2
  • Replication Server v6.2.19 - 1
  • SQL/Protect for PostgreSQL (64bit) v16.0 - 2
  
  
• Spatial Extensions
• PostGIS 3.4 Bundle for PostgreSQL 16

•   Web Development
• PEM-HTTPD v2.4.59-1

Once you select your components, start the installation, which will download components.

This will download various components about 2Gb (depends on what you selected from above options).

Once all components are downloaded, you have options to setup them.

Please note that if you start this installation it will install all the components you choose one by one (each component have separate installation wizard). So if you choose 5 you will have to complete all 5 installation wizards.

APPLY Operator in T-SQL

APPLY operator was introduced with SQL Server 2005 edition and was great help to join records set with table value function or table value expression.

There are two APPLY operators.

1. CROSS APPLY

2. OUTER APPLY

In simplest terms, CROSS APPLY behave like INNER JOIN and OUTER APPLY behave like LEFT OUTER JOIN.

Let's consider a following example table structure

-- Employee Table

CREATE TABLE Employee (
    EmpId INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    Age INT,
    BranchId INT
);

-- Branch Table

CREATE TABLE Branch (
    BranchId INT PRIMARY KEY,
    BranchName VARCHAR(100)
);

Here is my test data:

Branch Table

 

Employee Table

I have a table value function which brings branch id and average age of employees in that branch.

CREATE FUNCTION GetAverageEmployeeAgeByBranch
(
    @BranchId INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT BranchId, AVG(Age) AS AverageAge
    FROM Employee
    WHERE BranchId = @BranchId
    GROUP BY BranchId
);

I need to show branch id, branch name and average employee age for that branch in my query. First I have used CROSS apply in my query.

SELECT b.BranchId, BranchName, e.AverageAge

From Branch b CROSS APPLY dbo.GetAverageEmployeeAgeByBranch(BranchId) as e

ORDER BY BranchId

Result as follows:

Then I have used OUTER APPLY:

SELECT b.BranchId, BranchName, e.AverageAge

From Branch b OUTER APPLY dbo.GetAverageEmployeeAgeByBranch(BranchId) as e

ORDER BY BranchId

Result as follows:

As you can see in the first result set, SQL server only brings branches where there are employees on them (i.e. inner join), but on second result set it has bring all branches, where it cannot find employee age, it has put null on them (like LEFT OUTER join).

You can also use APPLY operator with table value expressions (i.e. sub queries).

SELECT b.BranchId, b.BranchName, e.EmployeeName

FROM Branch b CROSS APPLY 

(SELECT e.BranchId, 

STRING_AGG(EmployeeName, ';') WITHIN GROUP (ORDER BY e.EmployeeName) As EmployeeName 

FROM Employee e WHERE e.BranchId = b.BranchId GROUP BY e.BranchId) as e

Above query brings concatenated employee names for a given branch:

Shut It Down Correctly

My new (newish but used, provided by company) Windows 11 laptop keeps drains it's battery even when I shut it down from the Windows Shut Down menu (I turn the wall plug off so battery doesn't keep (trickle) charging over night).

I tried several power settings, but wasn't able to fix it. I wanted it to shut down properly and save battery. I didn't want it to startup quickly, I'm not in a hurry to start it up.

So I start searching for solutions.

That's when I found out Windows 11 hibernate mode and Fast Startup.

When hibernate mode is enabled, even when you shut down the computer from Shut Down menu, it goes to hibernate state, i.e. it will consume power to keep settings on the memory.

Fast Startup, which is another feature in Windows 11, will also make more battery to consume, even though you shut down your computer.

You need to turn off both these options to make it correctly shut down (which I wanted to do).

Turning of Fast Startup

1. Window Search bar (on start menu) type "Control Panel"

2. Control panel, choose "Hardware and Sound"

3. Under "Power Options", choose "What the power button does"

4. Click on "Change settings that are currently unavailable" link.

5. You will see a option called "Turn on Fast startup (recommended)" option. If it is ticked, un-tick it.

6. Save your settings.

Turn off Hibernate mode

1. Open command line (or terminal) under "Administrator"

2. Type following and press enter

         powercfg -h off

Turning off these two will make laptop to shut down properly.

While you are worry about battery, if you need to see very detail report about battery, you can do this by running following command under administrator command line (terminal):

        powercfg /batteryreport /output "C:\battery_report.html"

Note you can change the output path if you wish.

Rebuilding system databases in SQL Server

SQL Server has few system databases, which help it to manage the system. They are:

• master
• msdb
• model
• tempdb
• resource

tempdb is rebuilt every time your restart your SQL server, so there is not issues there.

In case of a corruption, how do you re-create all those system databases? One approach is restoring backup for those system databases. But if you don't backup system databases or backups also contains the corruption, you have no other option, but recreate them.

However, note that if you re-create them, you will lost any custom database object you created in those databases (i.e. it is like doing a factory reset) hence you will lost things like customized settings and agent jobs.

Prep

If you want current settings to be applied after restore/rebuild, you need to do following pre-requisite tasks:

        1. Note down current server configuration. You can fetch current server configuration by running following SQL (you probably want to extract this to a excel sheet or to a user database):

            SELECT * FROM sys.configurations;

        2.   If you hoping to restore to latest hotfixes, note down current hotfixes and current collation:

            SELECT

                    SERVERPROPERTY('ProductVersion ') AS ProductVersion,

                    SERVERPROPERTY('ProductLevel') AS ProductLevel,

                    SERVERPROPERTY('ResourceVersion') AS ResourceVersion,

                    SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,

                    SERVERPROPERTY('Collation') AS Collation;

        3. Following query shows current database file locations. Note this down if you are not using the default locations.

        SELECT name, physical_name AS current_file_location

        FROM sys.master_files

        WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

Rebuild

In order to rebuild, you need SQL Server installation media. There fore please mount or insert the installation media into the server you are rebuilding.

Then run following command on command line (make sure you are on the correct folder):

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName ]

There are many other parameters you can specify. For more detail please refer to Microsoft documentation - https://learn.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-ver16

Once rebuild completed, you can put your old settings back if necessary.

Creating Windows Task to Call URL

There are many ways you can call a URL from a windows schedule task. Old time we used vbscript to call a URL. We can create a small console app on .Net (Visual Studio) to call a URL and then schedule the console app to run periodically.

But what is the best and most easiest way these days. I wanted to find out recently as I wanted to call a URL from a website periodically to keep the website live (not going to sleep).

Answer I got from several GPTs were to use Powershell scritp.

This is the powershell command to use:

-ExecutionPolicy unrestricted 

    -Command "(New-Object Net.WebClient).DownloadString('YOUR-URL')"

To call this using a Windows Task scheduler do the following steps:

1. Launch Task Scheduler and create a task (don't select basic task)

2. Give an appropriate name and fill out the properties as suited for you

3. Create a trigger to run the task. You can schedule it to run periodically

4. In the "Actions" tab, click new and type "powershell" on "Program/script" box

5. In the "Arguments" box, type the above text (replace the url with your URL).

6. Click ok and save the task.

Make sure to run the script with a user who have appropriate permission to run powershell and commands.

Getting into PostgreSQL

PostgreSQL is open source database management system which can compete with more established DBMSs like SQL server and MySQL. In recent years PostgreSQL has grown popularity exponentially. Specially, we can see lot of SQL server and MySQL database experts are turning their attention to it. This is why novice like me also should have a look at what's the fuzz.

There are many theories why PostgreSQL has suddenly become very popular. But I agree that one of the main factors to it is that MySQL community is beginning to distance them self from MySQL, because it is now owned by Oracle (vendor lock). 

In addition to that, I see SQL server new features are becoming less and less enthusiastic people who use it. For example SQL Server 2022, release was disaster to Microsoft, because few of the feature didn't work the way they expected. Plus we are now in middle of the 2024, but there is no sign of the next version of SQL server yet (bit odd comparing Microsoft previous release cycles). So SQL server experts are getting little agitated and they are looking for alternative ways to keep the future bright. When expert do that rest of the community also follows.

From PostgreSQL side also it has become more and more robust over the years to compete with main players in the field. Features like non relational query support and object relational support gives the main advantages.

Considering above, I have also started looking into PostgreSQL basics. In future blogs I will be blogging about my journey with PostgreSQL.

Check() Constraint in SQL Server

CHECK() Constraint in SQL server is a data validation technique. Using CHECK() constraints, we can minimise data issues that could cause by bad application code or by incorrect queries by user at the database level.

Consider a table like below:

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL,

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year) )

Let's say we need users (or applications) to insert year as 4 digit year and year should be value between 2000 and 3000. Then we can define a check constraint like below.

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL,

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year),

    CHECK(course_year >= 2000 and course_year <= 3000))

You can give a name to the constraint. This is really helpful when debugging issue. So you know which table and which column it happened.

E.g.

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL,

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year),

    CONSTRAINT my_constraint1 CHECK(course_year >= 2000 and course_year <= 3000))

Check constraint can be declared in the line of the column, if it only applied to that column

E.g.

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL CHECK(course_year >= 2000 AND course_year <= 3000), 

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year))

But if it reference multiple columns, it has to be on its own line.

E.g.

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL,

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year),

    CONSTRAINT my_constraint2 CHECK(course_start_date <= course_end_date))

If you want to drop a check constraint:

ALTER TABLE CourseSchedule 

DROP CONSTRAINT my_constraint2

Temporary Stored Procedures

I have worked with stored procedures in SQL Server for more than decade now, but only now I got to know the existence of temporary stored procedures. Weird!

Temporary stored procedures works exactly the same way as temporary tables in SQL server.

So their name starts with # for local temporary stored procedures and ## for global temporary stored procedures.

E.g. #thisIsMyLocalTempSp

        ##thisIsMyGlobalTempSp

Same as temporary tables, local temporary stored procedures only available in current session and global temporary stored procedures is available to all sessions, but dropped when session that created it closes.

That make me think, will SQL server allow temporary functions and views too? Unfortunately (or fortunately) my research shows - No.

Note that temporary stored procedures are created on tempdb, so all the issues with tempdb will arise with them (just like temp tables).

So what is the use of temporary stored procedures? Main advantage is I think code re-use. For example if you have a very long stored proc, that uses piece of code over and over again (but you really don't want to create it as a permanent stored proc), you can create a temp stored proc and use it within permanent stored procedure.