Postgres / SQL Server / MariaDB – Table Setup to Store Persistent Default Values

In Postgres and a number of other databases you cannot directly set a columns’ default value to come from another table. Here I talk through how you can setup another table and use a trigger to pull the value from that table and insert it as a default value into a field of your choice if on Insert the value is null.

Postgres

-- Parent table
CREATE TABLE t002defaultvalues (
    gid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    warehouse text
);

-- Stock table
CREATE TABLE t001stock (
    gid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    warehouse text
);

-- Function to fetch default warehouse
CREATE OR REPLACE FUNCTION set_default_warehouse()
RETURNS trigger AS $$
BEGIN
    IF NEW.warehouse IS NULL THEN
        SELECT warehouse
        INTO NEW.warehouse
        FROM t002defaultvalues
        WHERE gid = 1;  -- adjust if you want another rule
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to apply the function
CREATE TRIGGER trg_t001stock_default_warehouse
BEFORE INSERT ON t001stock
FOR EACH ROW
EXECUTE FUNCTION set_default_warehouse();

SQL Server (T-SQL)
In SQL Server, defaults can’t reference other tables, so an AFTER INSERT trigger updates only the newly inserted rows (via the inserted pseudo-table).

-- Parent table
CREATE TABLE dbo.t002defaultvalues (
    gid BIGINT IDENTITY(1,1) PRIMARY KEY,
    warehouse NVARCHAR(MAX)  -- TEXT is deprecated in SQL Server
);

-- Stock table
CREATE TABLE dbo.t001stock (
    gid BIGINT IDENTITY(1,1) PRIMARY KEY,
    warehouse NVARCHAR(MAX) NULL
);
GO

-- Trigger: fill warehouse from t002defaultvalues when NULL
CREATE TRIGGER dbo.trg_t001stock_default_warehouse
ON dbo.t001stock
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE s
    SET s.warehouse = dv.warehouse
    FROM dbo.t001stock AS s
    INNER JOIN inserted AS i
        ON s.gid = i.gid
    CROSS APPLY (
        SELECT TOP (1) warehouse
        FROM dbo.t002defaultvalues
        WHERE gid = 1      -- adjust rule if needed
    ) AS dv
    WHERE s.warehouse IS NULL;
END;
GO

-- Example
INSERT INTO dbo.t002defaultvalues (warehouse) VALUES (N'Main Depot');
INSERT INTO dbo.t001stock (warehouse) VALUES (NULL); -- will be set by trigger

MariaDB
In MariaDB, a BEFORE INSERT trigger can assign NEW.warehouse from a scalar subquery.

-- Parent table
CREATE TABLE t002defaultvalues (
    gid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    warehouse TEXT
);

-- Stock table
CREATE TABLE t001stock (
    gid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    warehouse TEXT NULL
);

DELIMITER $$

-- Trigger: fill warehouse from t002defaultvalues when NULL
CREATE TRIGGER trg_t001stock_default_warehouse
BEFORE INSERT ON t001stock
FOR EACH ROW
BEGIN
    IF NEW.warehouse IS NULL THEN
        SET NEW.warehouse = (
            SELECT warehouse
            FROM t002defaultvalues
            WHERE gid = 1    -- adjust rule if needed
            LIMIT 1
        );
    END IF;
END$$

DELIMITER ;

-- Example
INSERT INTO t002defaultvalues (warehouse) VALUES ('Main Depot');
INSERT INTO t001stock () VALUES (); -- will be set by trigger

Adjust WHERE gid = 1 to whatever selection rule you need (e.g., ORDER BY created_at DESC LIMIT 1, or WHERE active = 1 LIMIT 1).

Vibe Coding – Experimenting with simple tool creation – Combine 2 PDF

I’ve been using chatGPT to pull together simple tools that I have been hosting on my cpanel shared hosting.

If you have been following along you might have noticed others such as the Google Route Planner and the PG Dump command generator. Really great stuff. I have also been using AI to generate thumbnails. I would simply not have had the time to create these tools without AI so this is a big win for me.

This is my latest tool – a simple combine 2 PDF site that I use to merge files into a combined pdf. I still have some ideas on improvements but it immediately started saving me time… I’ve noticed a lot of people creating applications like this now.

Combine2PDF

“Because talent doesn’t scale” – Why IT Projects fail

Over the years I’ve participated in several complex IT projects—many of which succeeded—yet I’ve struggled to understand why some thrived while others failed or stalled, even when I had a front‑row participation seat. I’ve documented my observations and listed factors I believed contributed to success (Link)(Link), but I’ve never distilled them into a cohesive theory.

Recently, I discovered a YouTube vlog by Fredrick Christenson in which he proposed what he believes is the fundamental driver of project success. His insights resonate strongly with me, and I’m inclined to agree.

“Because talent doesn’t scale”

Bookmarked Link below
Why do so many large IT projects fail to meet expectations?

Azure Postgres – Extensions

I was looking around for a list of extensions that can be used with Postgres – didn’t really find a consistent good list.

Just out of curiosity I thought I would look up Azure Postgres documentation which details which ones are available on their platform. Seems quite complete. Importantly it has the foreign data wrapper for Oracle.

Microsoft Azure Documentation – Postgres Extensions

Now I’m not saying you should go out and load too many. I would always be looking to load a minimum number of extensions as I feel you always want to keep complexity to a minimum and extensions are more likely to be prone to dropped support which could be a real long term serious issue.

Azure – Postgres – Restore to Azure Postgres Flexible Server

Have not tested this extensively but I have managed to take a pg_dump file from a version 16 Postgres Local Server and restore it to a Postgres version 16.3 Flexible Server on the Azure platform. I have also briefly tested taking a dump file from azure.

Requirements

  • The computer from which you will restore the database has been IP white listed.
  • Check that you have a higher version of restore than the version of Azure Postgres Database / Overview see version no.
  • Remember to create the database into which you will be placing the dump file
  • host will be endpoint and will be something like myserver.postgres.database.azure.com
  • username will be admin login defined at creation (probably not postgres)
  • Port default is usually 5432
  • These details can be found by going to Settings / Connect –

    • /

    Interestingly the connect screen contains a code generator similar to the one that I created that will assist users in creating their pg_dump and pg_restor commands.



    Postgres Database Dump and Restore

    Please find below link to detailed write up of my investigations of Pg_Dump and Pg_Restore command line tools for postgres.

    Detailed description of using PG_Dump and PG_Restore

    As ever performing database backups is absolutely vital on so many levels. Knowing where your data is, how to back it up and restore it with regular backups of the system of record is your best defence against data breach, data corruption, server failure or server room/building failure.

    Learn backup and learn them well.

    I also created a online tool to help create commands to be fed into pg_dump and pg_restore



    PG_RESTORE – Parameters

    These are the parameters the PG_RESTORE takes there is no strict required order for the parameters. However there are some best practices and dependencies to keep in mind I list these at the end of this post

    Quick Command Generator here

    General Options

    -d, --dbname=DBNAME
    Specifies the database to restore into. If omitted, pg_restore outputs SQL to stdout.

    -h, --host=HOSTNAME
    Specifies the database server hostname or IP. Defaults to a local connection.

    -p, --port=PORT
    Specifies the database server port. Defaults to 5432.

    -U, --username=USERNAME
    Specifies the username for authentication.

    -f, --file=FILENAME
    Specifies the ouput file or directory where the dump or restore should be saved or read from

    -w, --no-password
    Prevents password prompt. Used when authentication does not require a password.

    -W, --password
    Forces a password prompt. Useful when the password is not stored in .pgpass.

    Example Command

    pg_restore -h prod-server-01 -p 5432 -U username -d db003target "c:/dump/2025db003_backup.dump"

    Format Options

    -F, --format=c|d|t|p
    Specifies the archive format:

    c → Custom format (from pg_dump -Fc)

    d → Directory format (from pg_dump -Fd)

    t → Tar format (from pg_dump -Ft)

    p → Plain SQL format (not applicable to pg_restore)

    Restore Control Options

    -a, –data-only
    Restores only data (excludes schema). Useful when schema already exists.

    -c, –clean
    Drops database objects before recreating them. Prevents conflicts with existing objects.

    -C, –create
    Includes a CREATE DATABASE command. The database will be recreated before restoring.

    -I, –index=NAME
    Restores only a specific index. Useful for partial restores.

    -j, –jobs=NUM
    Uses multiple jobs (parallel restore). Speeds up restore for -Fd format dumps.

    -L, –use-list=FILE
    Restores only objects listed in FILE. The list can be generated using pg_restore -l.

    -n, --schema=NAME
    Restores only a specific schema. Useful for restoring part of a dump.

    -P, --function=NAME(args)
    Restores only a specific function.

    -s, --schema-only
    Restores only the schema (no data). Useful for setting up database structure.

    -S, --superuser=USERNAME
    Specifies a superuser for certain restore operations.

    -t, --table=NAME
    Restores only a specific table. Useful for partial restores.

    -T, --trigger=NAME
    Restores only a specific trigger.

    Output Control Options

    -e, --exit-on-error
    Stops on the first error. Useful for debugging issues.

    -v, --verbose
    Provides detailed output. Helps with troubleshooting.

    --no-owner
    Does not restore ownership metadata. Useful when restoring as a different user.

    --no-privileges, --no-acl
    Does not restore GRANT/REVOKE statements.

    --disable-triggers
    Disables triggers on tables during restore. Useful for bulk data loading.

    --if-exists
    Includes IF EXISTS in DROP statements. Prevents errors if objects don’t exist.

    --strict-names
    Requires exact match of table/schema names. Prevents unintended restores.

    Help & Version

    -?
    Displays help information.

    --version
    Shows the version of pg_restore.

    General Rules of IMPLEMENTATION

    These are the parameters the PG_DUMP takes there is no strict required order for the parameters. However there are some best practices and dependencies to keep in mind

    Options Can Be Placed in Any Order

    pg_dump -U postgres -h localhost -d mydb -F c -f backup.dump

    pg_dump -F c -f backup.dump -U postgres -d mydb -h localhost

    Both commands are valid.

    Options That Take Arguments Must Be Followed by Their Values

    ✅ pg_dump -U postgres -h localhost -d mydb -F c -f backup.dump

    ❌ pg_dump -U -h localhost postgres -d mydb -F c -f backup.dump (Invalid because -U needs a value immediately after)

    Multiple Parameters Can Be Combined (if they don’t need arguments)

    ✅ pg_dump -U postgres -v -F c -f backup.dump

    ✅ pg_dump -U postgres -F c -v -f backup.dump

    ❌ pg_dump -F -U postgres c -f backup.dump (Invalid because -F needs an argument immediately after)

    For Readability, It’s Best to Keep Related Options Together

    Authentication options (-U, -h, -p, -W) should be placed together.

    Dump/restore format options (-F, -j) should be grouped logically.

    Filtering options (-n, -t, -T) should be near each other.

    Specific Cases to Watch Out For

    1. pg_restore with -d (Database Restore)

    If using pg_restore -d mydb, you must not use -C (create database) because the restore is running inside an existing database.

    If using -C, omit -d and restore into the default postgres database first: pg_restore -C -d postgres backup.dump

    2. pg_dump Parallel Jobs (-j)

    -j (parallel jobs) only works with directory format (-F d).

    Example: pg_dump -F d -j 4 -f my_backup_dir -U postgres -d mydb

    If -j is used with a non-directory format, pg_dump will fail.

    3. pg_restore and -j (Parallel Restore)

    -j (parallel restore) is only valid for directory format dumps (-F d).

    Example:pg_restore -j 4 -d mydb my_backup_dir

    Using -j with -F c (custom format) does not work unless restoring only selected tables.

    Best Practices

    ✅ Authentication options first (-U, -h, -p)
    ✅ Output control next (-F, -f)
    ✅ Content filtering later (-s, -a, -t, -n)
    ✅ Parallelization at the end (-j)

    PG_DUMP – Parameters

    These are the parameters the PG_DUMP takes there is no strict required order for the parameters. However there are some best practices and dependencies to keep in mind which is at the end.

    Quick Command Generator here

    General Options

    -d, --dbname=DBNAME
    Specifies the database to restore into. If omitted, pg_restore outputs SQL to stdout.

    -h, --host=HOSTNAME
    Specifies the database server hostname or IP. Defaults to a local connection.

    -p, --port=PORT
    Specifies the database server port. Defaults to 5432.

    -U, --username=USERNAME
    Specifies the username for authentication.

    -f, --file=FILENAME
    Specifies the ouput file or directory where the dump or restore should be saved or read from

    -w, --no-password
    Prevents password prompt. Used when authentication does not require a password.

    -W, --password
    Forces a password prompt. Useful when the password is not stored in .pgpass.

    Example below

    pg_dump -h prod-server-01 -p 5432 -U username -d db003 -F c -f "c:/dump/2025db003_backup.dump"

    Format Options

    -F, --format=c|d|t|p
    Specifies the archive format:

    c → Custom format (from pg_dump -Fc)

    d → Directory format (from pg_dump -Fd)

    t → Tar format (from pg_dump -Ft)

    p → Plain SQL format (not applicable to pg_restore)

    Restore Control Options

    -a, --data-only
    Restores only data (excludes schema). Useful when schema already exists.

    -c, --clean
    Drops database objects before recreating them. Prevents conflicts with existing objects.

    -C, --create
    Includes a CREATE DATABASE command. The database will be recreated before restoring.

    -I, --index=NAME
    Restores only a specific index. Useful for partial restores.

    -j, --jobs=NUM
    Uses multiple jobs (parallel restore). Speeds up restore for -Fd format dumps.

    -L, --use-list=FILE
    Restores only objects listed in FILE. The list can be generated using pg_restore -l.

    -n, --schema=NAME
    Restores only a specific schema. Useful for restoring part of a dump.

    -P, --function=NAME(args)
    Restores only a specific function.

    -s, --schema-only
    Restores only the schema (no data). Useful for setting up database structure.

    -S, --superuser=USERNAME
    Specifies a superuser for certain restore operations.

    -t, --table=NAME
    Restores only a specific table. Useful for partial restores.

    -T, --trigger=NAME
    Restores only a specific trigger.

    Output Control Options

    -e, --exit-on-error
    Stops on the first error. Useful for debugging issues.

    -v, --verbose
    Provides detailed output. Helps with troubleshooting.

    --no-owner
    Does not restore ownership metadata. Useful when restoring as a different user.

    --no-privileges, --no-acl
    Does not restore GRANT/REVOKE statements.

    --disable-triggers
    Disables triggers on tables during restore. Useful for bulk data loading.

    --if-exists
    Includes IF EXISTS in DROP statements. Prevents errors if objects don’t exist.

    --strict-names
    Requires exact match of table/schema names. Prevents unintended restores.

    Help & Version

    -?
    Displays help information.

    --version
    Shows the version of pg_restore.

    General Rules of IMPLEMENTATION

    These are the parameters the PG_DUMP takes there is no strict required order for the parameters. However there are some best practices and dependencies to keep in mind

    Options Can Be Placed in Any Order

    pg_dump -U postgres -h localhost -d mydb -F c -f backup.dump

    pg_dump -F c -f backup.dump -U postgres -d mydb -h localhost

    Both commands are valid.

    Options That Take Arguments Must Be Followed by Their Values

    ✅ pg_dump -U postgres -h localhost -d mydb -F c -f backup.dump

    ❌ pg_dump -U -h localhost postgres -d mydb -F c -f backup.dump (Invalid because -U needs a value immediately after)

    Multiple Parameters Can Be Combined (if they don’t need arguments)

    ✅ pg_dump -U postgres -v -F c -f backup.dump

    ✅ pg_dump -U postgres -F c -v -f backup.dump

    ❌ pg_dump -F -U postgres c -f backup.dump (Invalid because -F needs an argument immediately after)

    For Readability, It’s Best to Keep Related Options Together

    Authentication options (-U, -h, -p, -W) should be placed together.

    Dump/restore format options (-F, -j) should be grouped logically.

    Filtering options (-n, -t, -T) should be near each other.

    Specific Cases to Watch Out For

    1. pg_restore with -d (Database Restore)

    If using pg_restore -d mydb, you must not use -C (create database) because the restore is running inside an existing database.

    If using -C, omit -d and restore into the default postgres database first: pg_restore -C -d postgres backup.dump

    2. pg_dump Parallel Jobs (-j)

    -j (parallel jobs) only works with directory format (-F d).

    Example: pg_dump -F d -j 4 -f my_backup_dir -U postgres -d mydb

    If -j is used with a non-directory format, pg_dump will fail.

    3. pg_restore and -j (Parallel Restore)

    -j (parallel restore) is only valid for directory format dumps (-F d).

    Example: pg_restore -j 4 -d mydb my_backup_dir

    Using -j with -F c (custom format) does not work unless restoring only selected tables.

    Best Practices

    ✅ Authentication options first (-U, -h, -p)
    ✅ Output control next (-F, -f)
    ✅ Content filtering later (-s, -a, -t, -n)
    ✅ Parallelization at the end (-j)

    Postgres – Summary Check of Size No of Tables Views and Rows

    It is a good idea to do a general reconcilliation before and after a database dump and restore to peform some sense check as to whether restoration has completed successfully.

    SELECT datname AS database_name, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

    And here is a further breakdown of numbers in a database

    DO $$
DECLARE
    rec         RECORD;
    rec_count   bigint;
    total_rows  bigint := 0;
    total_items int := 0;
BEGIN
    -- Create a temporary table to store individual counts
    CREATE TEMP TABLE temp_table_counts (
        table_name text,
        row_count  bigint
    ) ON COMMIT DROP;

    -- Loop through each user table and view (exclude system schemas)
    FOR rec IN
        SELECT table_schema, table_name, table_type
        FROM information_schema.tables
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
          AND table_type IN ('BASE TABLE', 'VIEW')
        ORDER BY table_name
    LOOP
        BEGIN
            -- Dynamically count the rows for the current table/view
            EXECUTE format('SELECT count(*) FROM %I.%I', rec.table_schema, rec.table_name)
            INTO rec_count;
        EXCEPTION WHEN OTHERS THEN
            -- In case of error (for example, if the view is not countable), set to NULL
            rec_count := NULL;
        END;

        -- Insert the result into our temporary table.
        INSERT INTO temp_table_counts(table_name, row_count)
        VALUES (rec.table_schema || '.' || rec.table_name, rec_count);

        -- Accumulate totals (if count succeeded)
        IF rec_count IS NOT NULL THEN
            total_rows := total_rows + rec_count;
        END IF;
        total_items := total_items + 1;
    END LOOP;

    -- Return the per-table results
    RAISE NOTICE 'Table Name | Row Count';
    FOR rec IN 
        SELECT table_name, row_count 
        FROM temp_table_counts 
        ORDER BY table_name
    LOOP
        RAISE NOTICE '% | %', rec.table_name, rec.row_count;
    END LOOP;

    -- Return the summary totals
    RAISE NOTICE '--------------------------------------';
    RAISE NOTICE 'Total Rows: %, Total Tables/Views: %', total_rows, total_items;
END $$;

    And on running this is the kind of result it should return

    SQL Server / Postgres – Pattern for Distinct List / Combo Box source

    This primarily refers to variable text fields but in all of the CRUD systems I’ve ever designed every single page has contained fields that use lookup dialogs. Their sheer ubiquity makes their design and operation particularly important significantly impacting an applications feel to users.

    In principle sourcing the values for a lookup appears to be a very simple choice.

  • Hard coded through the user interface (e.g Y/N)
  • Referenced to a separate “lookup” table


    • As you design more systems you start to see further important options

  • Referenced to distinct values sourced from a self referencing query of the table
  • Whether to store values as foreign keys
  • Whether to enforce validation at the database level.


    • Some general rules I follow;

    1)If the application allows users to bulk import data into a table consider using referencing to a distinct values list sourced from a self referencing query of the table.
    2)Almost NEVER alias values / use foreign keys in tables that will receive imported data but often better not to use it even if there is no import facility.
    3)The one exception to rule 2 can be customer account codes – account codes for clients can be incredibly important and often link to a table that specifies say a company name and address – things that may change in time. Users really need to be trained to understand the derivation of foreign keys to use systems or the codes will generally break import routines or break referencing and require correction. Account codes tend to be a weird case of a derived surrogate where the value is obviously derived from the lengthened value – e.g AMZN for Amazon or MSFT for Microsoft in NASDAQ listings. This works for NASDAQ because of its ubiquity and because NASDAQ has educated its users be prepared that you will need to educate your users if you use this pattern. For this reason my general rule is only use foreign key aliases for Account codes.
    4)If you have a field or table that will receive values which may be the subject of a lookup – don’t be too harsh with the field validation in the database data definition and be more permissive with the field variable type and length than you might expect.

    And here is suggested SQL that can be used to identify distinct values in a field.

    SELECT DISTINCT account_code FROM table_name;

    PHP Runner : Javascript Snow Effect at Login

    Something probably better to be implemented prior to Xmas but always good to vary things up and you will have to have a darker background screen at login prior to starting. I have tested this in Edge and Chrome. Minor thing – in google chrome it can be affected by your profile settings. I concluded that one of my google profiles had stricter security settings that prevented the execution of certain scripts or animation effects. Compare the settings in both profiles (e.g., JavaScript enabled/disabled, cookie settings, site data restrictions) to identify any discrepancies and if you have your application installed as an application ensure that you installed it using the working profile. Happy 2025 everyone.

    Code below

    function createSnowflake() {
    const snowflake = document.createElement('div');

    // Randomize position within the middle 95% of the viewport width
    snowflake.style.left = 2.5 + Math.random() * 95 + 'vw'; // Offset for 95% width

    snowflake.style.position = 'absolute';
    snowflake.style.top = '-10%';

    // Base size and variation
    const baseSize = 8; // Base size in pixels
    const sizeVariation = baseSize * 0.2; // 20% of the base size
    const size = baseSize + (Math.random() * 2 * sizeVariation - sizeVariation); // Random size adjustment

    snowflake.style.width = `${size}px`;
    snowflake.style.height = `${size}px`;

    snowflake.style.background = 'white';
    snowflake.style.borderRadius = '50%';
    snowflake.style.opacity = Math.random() * 0.5 + 0.5; // Random initial opacity
    snowflake.style.pointerEvents = 'none';
    snowflake.style.animation = `fall ${Math.random() * 10 + 10}s linear infinite`; // Slower speed

    document.body.appendChild(snowflake);

    // Remove the snowflake after its animation ends
    snowflake.addEventListener('animationend', () => {
        snowflake.remove();
    });
}

// Create snowflakes at regular intervals with fewer instances
setInterval(createSnowflake, 3600); // Further reduced frequency

// CSS for falling animation
const style = document.createElement('style');
style.textContent = `
@keyframes fall {
    0% {
        top: -10%; /* Start slightly above the viewport */
        opacity: 0; /* Fully transparent at the top */
    }
    10% {
        opacity: 1; /* Fade in after falling slightly */
    }
    100% {
        top: 110%; /* Move slightly below the viewport */
        opacity: 0; /* Fully fade out at the bottom */
    }
}
`;
document.head.appendChild(style);

    Gmail : Create an Email List

    Its 2025 and I’ve never created an email list in Gmail despite using it for over a decade. Whats more it just took me half an hour to figure it out. So I thought I would just pull it together because it will probably be a while before I need to use it.

    Synopsis – I’m used to email lists – Gmail doesn’t have this concept and calls them Labels instead.
    Additionally Labels in Gmail are not the same as Labels in Google Contacts – We will be creating and editing a label in Google Contacts which is then referenced in Gmail.

    First things first within your Google Menu go to Contacts – the icon looks as below (Note don’t go into Groups which has a similar icon)

    Here we create the email list we wish to Create…

    Click on the plus symbol next to the Labels item

    You should be presented with the following dialog

    Where it says New lable type in the name of the email list you wish to create..

    Hit save and the label should appear on the left hand side under Labels..

    Now you go to the Contacts on the left hand side and using the search facility look up the email you wish to include in the list here I have Royal Bank of Scotland for anonimity purposes.

    You want to hit the Label button beneath the contact…

    Next hit Apply

    You should see a Working dialog that appears on the screen for a period after which the count next to the newly created label should increase by 1

    e.g.

    You can now go back to Googlemail and this “Label” will be available for selection in the To / CC and Bcc as here.

    Postgres : Adjust default Database to inherit PostGIS and pgRouting on CREATE

    In Postgres when an administrator creates a database in the background Postgres is copying a template database.
    As default the new database is a copy of a database that is created at install called template1. This database is usually hidden and even if you are logged in as the Postgres superuser this database is not listed when you search on the server for all available database.

    Normally an administrator that is limited to dbcreator rights does NOT have rights to install extensions. This is particularly an issue when dealing with geography administrators that need to create databases with PostGIS and pgrouting extensions but you don’t want to elevate these administrators to superusers.

    Superuser administrators can however install these extensions on the template database which means that when an administrator with dbcreator rights logins in and runs the create database command the new database created will inherit the extensions postgis and pgrouting (and any other extensions you add to template1).

    Steps to Alter template1:
    Connect to the PostgreSQL Instance as a Superuser:

    Use a superuser account (e.g., postgres) to connect to the PostgreSQL instance.

    Allow template1 to be modified:

    By default, the template databases are marked as “databases not allowed for connection.” You need to change this setting temporarily.

    ALTER DATABASE template1 IS_TEMPLATE = false;

    Connect to template1:

    Now, connect to the template1 database.

    \c template1

    Install the PostGIS and pgRouting Extensions in template1:

    Once connected to template1, install the extensions:

    CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;

    Restore template1 to be a template database:

    After installing the extensions, you need to restore template1 as a template:

    ALTER DATABASE template1 IS_TEMPLATE = true;

    Prevent Modifications to template1 (Optional):

    To prevent accidental changes to template1 in the future, you can revoke connection permissions:

    REVOKE CONNECT ON DATABASE template1 FROM public;

    Future Databases Will Inherit PostGIS and pgRouting:
    Now, whenever a new database is created using the template1 template, it will automatically inherit the PostGIS and pgRouting extensions:

    
CREATE DATABASE new_database_name;

    — This new database will have PostGIS and pgRouting installed.

    Caution:
    Modifying the default template1 means all databases created in the future will have the extensions, which might not be ideal if you don’t want PostGIS or pgRouting in every database. You could alternatively create a custom template and specify it when creating spatial databases.

    Example of creating a custom template:

    CREATE DATABASE template_postgis TEMPLATE template1;
\c template_postgis
CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
ALTER DATABASE template_postgis IS_TEMPLATE = true;

    This way, you can create new spatial databases by specifying this custom template:

    CREATE DATABASE spatial_db TEMPLATE template_postgis;

    This approach provides more control over which databases include the extensions.

    Geoserver – CREATE View

    Geoserver has this really great feature whereby admins are able to create views that are held with the configuration of the Geoserver but separate from the database. This allows admins the ability to create views WITHOUT the need to have database admin rights on the server. This is particularly useful where you have an onsite vendor database that you would not otherwise be allowed to write views in but nonetheless you require some way to store spatially enabled views .

    Quick setup guide how to do this.

    Firstly go to layers and hit

    Select the Store that you are interested in

    Next you should see text and just hit it.

    Then there will be a place where you can put the SQL Statement in.

    Postgres : Create Table SQL Syntax Best Practice

    For primary keys it is now the recommendation to use identity during table creation.

    Apparently it explicitly conveys the purpose of the column and aligns with SQL standards thus it is important to use when possible rather than the former serial command. In theory this should be more portable as well.

    Depending on what source you read you may or may not need the following extension to create UUIDs – I needed it but I am told it should be in the main library.

    CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;

    So here is standard code for creating a table with three columns

    CREATE TABLE t001example 
(
pkid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
genuuid uuid DEFAULT gen_random_uuid(),
sitename varchar(500)
);

    And these are example values

    cPanel – Just gets better and better

    Good self service Web Hosting Management software is just a game changer for Dev Ops work.

    Every year I learn a bit more and every year they probably improve it a bit more. I am really very far off the cutting edge so I can’t speak to cutting edge improvements of the whole platform but I can talk to the options that I am continually learning.

  • Being able to quickly create, manage and backup additonal wordpress sites for zero cost
  • Being able to quickly create, manage and backup additional mysql databases for zero cost
  • Being able to host additional CRUD applications for zero cost (with thanks to PHPRunner)
  • Being able to create and manage email addresses easily

    • .

    AND NOW……

    Being able to quickly manage the update PHP versions is just amazing…

    cPanel:

    Log into cPanel

  • Under Software click MultiPHP Manager
  • Make a note of of your current version on the domain
  • Select an updated version of PHP and click Apply
  • Visit your site to confirm that the change has not broken your site.
  • If the site is broken, you can switch back to the previous PHP version

    • .

    Yes its that easy

    Things I want next
    Front end cPanel at InMotion backend SQL Server in Azure
    Front end cPanel at InMotion backend Postgres Server in Azure
    Postgres backends in cPanel

    cPanel rocks
    and so does PHP and PHPRunner
    and AI when it can learn to spell and count!
    (yes the picture annoys me as well)

    Device Name – Quickest ways to Identify on Windows x64

    Computing tries hard to obfuscate the device that is actually running the software that you are using. That is particularly the case with cloud computing and its particularly the case with web enabled software. Are you logging into the software that manages the database? or are you logging into the database itself?

    Irrespective software gets installed on an individual machine and it is often important to know the name of that machine as that software might have its own username and password requirement and you need to know the username/password combination for the particular installation you are trying to access.

    This is often confusing for database servers – increasingly I am trying to remember a broad map of what computers are where and what servers are where so I can at least reference them accurately.

    Two ways to find the DEVICE NAME on a windows machine (there are others)

    In the Windows Search bar type

    ABOUT

    or in the Windows Search bar type

    DEVICE MANAGER

    About will give you this

    Device Manager will give you this

    Note you can get to that About screen through file explorer by right clicking on this PC and selecting Properties – but it is slower than just typing about in the Windows Search box.

    GeoServer and ORACLE – WFS & WMS Config Tips

    This post is a brief description of my findings on setting up Geoserver from scratch and some particular points on setting it up for an Oracle connection. I hope it will prove useful for others.

    INSTALLATION
    GeoServer with ORACLE database connection support
    Geoserver Postgres (PostGIS) connection support comes with the default installation. This is NOT the case for Oracle or SQL Server therefore for enterprise purposes it is a requirement to install Geoserver WITH Oracle and SQL Server extensions if you wish to support these database connections.

    I’ve done a post on Geoserver on windows installation link here that talks specifically about SQL Server. I will not go into a lot of detail about installation here. See below for links to the geoserver docker image I didn’t install the geoserver I describe here but I can confirm that we have been successful in including Oracle as a plugin to our Geoserver installation and we are able to connect.

    Official Geoserver Docker Image GITHUB

    Below is a recent (Feb 2024) list of Docker parameters available at install including Oracle and SQL Server please seek advice on the use of Docker for installation if you are not familiar with it already.

    GENERAL SETUP

    Contact Information – General Setup
    On the Welcome screen there is a line that reads For more information visit {organization} to change this from the initial meaningless variable setting go to Contact Information and fill out your organisation.

    Difference between Save and Apply – General point

    In geoserver admin there are a lot of pages where there is a Save and an Apply button. Most users initially at least find this confusing.

    In GeoServer, the terms “Save” and “Apply” serve different purposes:

    Save:
    When you click “Save”, it permanently stores the changes you’ve made to the configuration. These changes are saved in the GeoServer configuration files. , “Save” ensures that your modifications persist even after restarting GeoServer or rebooting the system. It’s like committing your changes to a version control system—it makes them permanent.
    Apply:
    “Apply”, on the other hand, is more immediate. When you click “Apply”, it takes the current configuration (including any unsaved changes) and applies it dynamically. The applied changes take effect immediately without requiring a restart or permanent storage. Think of it as a temporary adjustment that affects the running instance of GeoServer but doesn’t alter the saved configuration. In summary, “Save” makes changes permanent, while “Apply” applies them immediately without saving them to the configuration files

    The first thing you will want to do is determine if you can actually connect to your ORACLE database.

    ORACLE required parameters for database connection. Before you continue make sure you have the following

    ORACLE host name
    Port ORACLE database (default 1521)
    database name
    user – case sensitive
    password – case sensitive
    Geoserver installed with Oracle extension

    It is good idea to get your DBA to setup a read only user if you are connecting to a database that is part of a vendor supplied product as you really should not be editing the database through anything except the vendor’s UI and Geoserver can be setup to allow edit of data. Geoserver has powerful security configuration included but conflicting settings can arise that may alter the intended security levels making it safer to enforce read only least privilege at the database level.

    ORACLE and Postgres – some SQL Differences
    Please be aware Oracle databases are by default case sensitive but may convert SQL Strings to upper case when passed to Oracle. I believe this complies strictly with the SQL Standard.

    Postgres classifies tables, columns views and other database objects as Identifiers. SQL Identifiers must begin with a letter. If possible avoid including special characters in the naming of identifiers because although postgres may support them special characters are not allowed in the SQL Standard so their use might render applications less portable. Additional identifiers and keyword must begin with a letter. Keywords are defined as items such as SELECT / UPDATE / CREATE etc.. The SQL Standard says that databases should not define a key word that contains a digit or starts or ends with an underscore so identifiers could be something like t001users should be safe against possible conflict with future extensions of the standard. In postgres the maximum identifier length is 63 bytes. Additionally Postgres also folds unquoted names to lower case but some postgres management UIs allow for the naming of identifiers with varied case.

    UPSHOT for Geoserver (and anything else for that matter)

    If you are having difficulty referencing tables or views in any enterprise database – experiment with the case sensitivity in the query. There might be some folding of object names when passed to the server.
    This characteristic should be born in mind when using SQL Views in Geoserver.

    ORACLE Store setup to get the most out of Geoserver
    Login to Geoserver admin panel using your admin account

    On the left hand side under Data hit Stores

    Assuming you have successfully installed Geoserver with the Oracle extension you should be presented with the following dialog and in particular the items highlighted below.

    Oracle NG uses a standard Oracle driver (ignore the other options this article will not explain them)

    Select Oracle NG

    Use the default settings for most things but IMPORTANTLY ensure Expose Primary Keys is ticked

    NOTE from our brief testing we found that exposing primary keys seemed to be one factor that improved stability in our Oracle connection when dealing with QGIS.

    QGIS WFS set up to improve Oracle Connection Stability
    It is recommended that you have a copy of QGIS to test setup to things like WFS

    When we first started displaying specifically the Oracle spatially enabled table in QGIS via WFS service we found that the layer would initially display but if we were to leave the project and go back in for a period of time the layer would not be visible. This seemed to be temporary and after an unpredictable amount of time the layer would reappear. The attribute table continued to be viewable but polygons were not visible.

    We fixed this by ensuring Expose Primary Key (see Geoserver layer configuration) was ticked and within QGIS within the WFS Connection Configuration ensure within the WFS options section that the version is set to match the linked WFS Service. This might not be necessary in all QGIS versions.

    LAYER Config
    Useful background information (partly written by co-pilot)

    Enabled and Advertised parameters – Layer Config
    In GeoServer, the Enabled and Advertised checkboxes play distinct roles in layer configuration:

    Enabled:
    When a layer is enabled, it means that the layer is available for requests (such as WMS GetMap or WMS GetFeature). If a layer is not enabled, it won’t be accessible for any kind of request. However, it will still appear in the configuration (and in the REST config). Essentially, enabling a layer makes it operational and ready to serve data.
    Advertised:
    By default, a layer is advertised. An advertised layer is included in the GetCapabilities request and appears in the layer preview. However, if you uncheck the Advertised checkbox, the layer will still be available for data access requests (like WMS), but it won’t appear in any capabilities documents or previews. In other words, non-advertised layers remain functional but are not explicitly listed in service metadata. Remember, enabling a layer makes it operational, while advertising it determines whether it appears in service capabilities and previews

    Name and Title parameters – Layer Config
    Name:
    The Name corresponds to the identifier used to reference the layer in WMS (Web Map Service) requests. It is primarily used for computer interaction and serves as an internal reference default name is usually the name of the table or the name of the referenced view. When creating a new layer for an already-published resource, the Name must be unique to avoid conflicts. Essentially, it’s the technical name associated with the layer.
    Title:
    The Title provides a human-readable description that briefly identifies the layer to clients. Unlike the Name, which is for computers, the Title is meant for humans to read. It helps users understand the purpose or content of the layer. For example, if you have a layer representing a map of the USA, you might set the Title to “This is a map of USA.” Remember, while the Name is essential for system functionality, the Title enhances user experience by providing meaningful context about the layer1

    Security – the Name does not need to be the table or view name you are referencing. This string appears in the URL which may be viewable to the user – you may wish to obfuscate this by changing the name

    i18n – Internalization – Layer Config
    Internationalization (i18n):
    GeoServer supports returning a GetCapabilities document (used for describing available services and layers) in various languages. The i18n functionality is available for the following services:
    WMS 1.1 and 1.3
    WFS 2.0
    WCS 2.0
    The i18n editor allows you to provide translations for the title and abstract of various components:
    Layers configuration page
    Layergroups configuration page
    WMS, WFS, and WCS service configuration pages
    Additionally, for Styles, there’s a separate i18n configuration (see i18N in SLD).By default, the i18n editor is disabled and can be enabled via the i18n checkbox.

    GetCapabilities Document Language:
    The language of the GetCapabilities document can be selected using the AcceptLanguages request parameter.GeoServer’s response varies based on the following rules:
    Internationalized elements include titles, abstracts, and keywords.If a single language code is specified (e.g., AcceptLanguages=en), GeoServer tries to return content in that language.If multiple language codes are specified (e.g., AcceptLanguages=en,fr), GeoServer attempts to return content in one of the specified languages.

    Coordinate Reference Systems – Declared SRS and SRS handling – Layer Config

    If you are in the UK ensure that Declared SRS is set to 27700

    SRS Handling
    In GeoServer, the SRS handling setting plays a crucial role in how coordinate reference systems (CRS) are managed. Let’s explore its significance:

    Declared SRS:
    The Declared SRS specifies the coordinate system that GeoServer publishes to clients. It represents the CRS that clients should use when interacting with the layer. Essentially, it’s the officially declared CRS associated with the layer.
    SRS Handling:
    The SRS Handling option determines how GeoServer should handle projection transformations when the declared CRS and the native CRS of the data differ. Here are the possible values for SRS Handling:
    Force declared (default): In this mode, GeoServer forces the declared SRS upon the data, overwriting the native CRS if necessary.
    Other options (not mentioned in the snippet):
    Reproject native to declared: GeoServer performs a reprojection from the native CRS to the declared CRS.
    Reproject declared to native: GeoServer reprojects from the declared CRS to the native CRS.
    None: No reprojection is performed; the data remains in its native CRS.
    In summary, the SRS handling setting ensures that data is presented consistently to clients, regardless of the underlying native CRS. It’s a critical aspect of maintaining accurate spatial information in GeoServer1.

    Bounding Box recommendations – Layer Config
    Scotland

    Min X = 010000
    Min Y = 533000

    Max X = 464000
    Max Y = 1215000

    West Lothian

    Min X = 287000
    Min Y = 655000

    Max X = 314000
    Max Y = 678000

    UK

    Workspace setup and visibility in WFS
    Workspaces can allow for editing and not editing of information although to date I seem to be struggling to isolate layers and workspaces to allow them to be edited or not edited. One thing that is important in our present system is to Enable the settings in the Basic info – without this the layers within the workspace were not showing up in the WFS

    Restrictions on Layers : Important Note

    Important – Workspace Security settings override Layer Security Settings

    If you need to have different layer security settings within one Workspace you will need to turn off all Workspace security settings e.g see below.