These notes were made whilst creating a postgreSQL database and importing records using an R script. They include setting up administration and read access roles, transferring database to a different server, linking the postgreSQL database to a Microsoft Access database and useful SQL.
CREATE ROLE DATABASE_NAME_admin LOGIN CREATEDB CREATEROLE PASSWORD 'string';
SELECT * FROM pg_roles;
-- Create - SERVER
-- General - Name = DATABASE_NAME
-- Connection - HOST NAME (eg. localhost)
-- Connection - Username = DATABASE_NAME_admin
-- Connection - Password = 'string'CREATE DATABASE DATABASE_NAME OWNER DATABASE_NAME_admin;-- Create user group role
CREATE ROLE DATABASE_NAME_read_access;
-- Allow connection to database
GRANT CONNECT ON DATABASE DATABASE_NAME TO DATABASE_NAME_read_access;
-- Create user which inherits read access user group role
CREATE ROLE DATABASE_NAME_read_user LOGIN PASSWORD 'string';
GRANT DATABASE_NAME_read_access TO DATABASE_NAME_read_user;-- Included in procedure is setting read access to schema database
-- Allow access to schema
GRANT USAGE ON SCHEMA SCHEMA_NAME TO DATABASE_NAME_read_access;
-- Allow select access to all tables
GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA_NAME TO DATABASE_NAME_read_access;
-- Allow select access to all future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA SCHEMA_NAME
GRANT SELECT ON TABLES TO DATABASE_NAME_read_access;Create key on database keyring for database admin user on local machine
Run DATABASE_NAME.Rmd script to populate database
Run VACUUM maintenance
VACUUM;Backup SCHEMA_NAME schema SCHEMA_NAME_schema_backup_YYYYMMDD.sql by Right click schema - Backup
Create new server connection for read access user in pgadmin4
-- Create - SERVER
-- General - Name = DATABASE_NAME
-- Connection - HOST NAME (eg. localhost)
-- Connection - Username = DATABASE_NAME_read_access
-- Connection - Password = 'string'Create key on database keyring for read access user on local machine
Login as superuser and back up DATABASE_NAME roles as sql by Right click server name - Backup Globals DATABASE_NAME_database_roles_YYYYMMDD.sql, selecting SQL from this file
CREATE ROLE DATABASE_NAME_admin;
ALTER ROLE DATABASE_NAME_admin WITH NOSUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5_string';
CREATE ROLE DATABASE_NAME_read_access;
ALTER ROLE DATABASE_NAME_read_access WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE DATABASE_NAME_read_user;
ALTER ROLE DATABASE_NAME_read_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5_string';CREATE DATABASE DATABASE_NAME OWNER DATABASE_NAME_admin;CREATE SCHEMA SCHEMA_NAME;Restore schema by right click schema - Restore
Grant access to user roles
-- Allow connection to database
GRANT CONNECT ON DATABASE DATABASE_NAME TO DATABASE_NAME_read_access;
-- Allow access to schema
GRANT USAGE ON SCHEMA SCHEMA_NAME TO DATABASE_NAME_read_access;
-- Allow select access to all tables
GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA_NAME TO DATABASE_NAME_read_access;
-- Allow select access to all future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA SCHEMA_NAME
GRANT SELECT ON TABLES TO DATABASE_NAME_read_access;This SQL truncates all the tables in a schema following instructions in stack overflow Truncating all tables in a Postgres database
DO $$ DECLARE
r RECORD;
BEGIN
FOR r IN (SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'SCHEMA_NAME')
LOOP
EXECUTE 'TRUNCATE TABLE '
|| quote_ident(r.schemaname)
|| '.'
|| quote_ident(r.tablename)
|| ' RESTART IDENTITY CASCADE';
END LOOP;
END $$;CREATE OR REPLACE FUNCTION getPrecision (gridReference VARCHAR(14))
RETURNS SMALLINT AS $$
DECLARE
p INTEGER;
BEGIN
CASE
WHEN gridReference ~ '^\D{1,2}\d{2}$' THEN p = 10000;
WHEN gridReference ~ '^\D{1,2}\d{2}\D{2}$' THEN p = 5000;
WHEN gridReference ~ '^\D{1,2}\d{2}\D{1}$' THEN p = 2000;
WHEN gridReference ~ '^\D{1,2}\d{4}$' THEN p = 1000;
WHEN gridReference ~ '^\D{1,2}\d{6}$' THEN p = 100;
WHEN gridReference ~ '^\D{1,2}\d{8}$' THEN p = 10;
WHEN gridReference ~ '^\D{1,2}\d{10}$' THEN p = 1;
ELSE
RAISE INFO 'UNABLE TO FIND PRECISION FOR %', gridReference;
p = NULL;
END CASE;
RETURN p;
END; $$
LANGUAGE 'plpgsql';
-- CONVERTS GRIDREFERENCE TO 10KM
CREATE OR REPLACE FUNCTION convertGRto10KM (
INOUT gridReference varchar(4))
AS $$
BEGIN
CASE
WHEN gridReference ~ '^\D{1,2}\d{2}$' THEN gridReference = gridReference; -- 10000
WHEN gridReference ~ '^\D{1,2}\d{2}\D{1,2}$' THEN gridReference = SUBSTRING(gridReference, '^\D{1,2}\d{2}'); -- 5000 | 2000
WHEN gridReference ~ '^\D{1,2}\d{4}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{1}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{2}', ''), 1)); -- 1000
WHEN gridReference ~ '^\D{1,2}\d{6}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{1}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{3}', ''), 1)); -- 100
WHEN gridReference ~ '^\D{1,2}\d{8}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{1}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{4}', ''), 1)); -- 10
WHEN gridReference ~ '^\D{1,2}\d{10}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{1}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{5}', ''), 1)); -- 1
ELSE
RAISE INFO 'UNABLE TO CONVERT % TO 10KM GRIDREFERENCE', gridReference;
gridReference = NULL;
END CASE;
END; $$
LANGUAGE 'plpgsql';CREATE OR REPLACE FUNCTION convertGRto1KM (
INOUT gridReference varchar(4))
AS $$
BEGIN
CASE
WHEN gridReference ~ '^\D{1,2}\d{4}$' THEN gridReference = gridReference; -- 1000
WHEN gridReference ~ '^\D{1,2}\d{6}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{2}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{3}', ''), 2)); -- 100
WHEN gridReference ~ '^\D{1,2}\d{8}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{2}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{4}', ''), 2)); -- 10
WHEN gridReference ~ '^\D{1,2}\d{10}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{2}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{5}', ''), 2)); -- 1
ELSE
RAISE INFO 'UNABLE TO CONVERT % TO 1KM GRIDREFERENCE', gridReference;
gridReference = NULL;
END CASE;
END; $$
LANGUAGE 'plpgsql';
-- CONVERTS GRIDREFERENCE TO 2KM
CREATE OR REPLACE FUNCTION convertGRto2KM (
INOUT gridReference varchar(4))
AS $$
DECLARE
tenKM VARCHAR(4);
oneKM VARCHAR(6);
twoKMNumber VARCHAR(2);
twoKMLetter CHAR(1);
BEGIN
IF gridreference ~ '^\D{1,2}\d{2}\D{1}$' THEN -- 2000
ELSIF gridreference ~ '^\D{1,2}\d{4,10}$' THEN -- 1000 | 100 | 10 | 1
tenKM = convertGRto10KM(gridReference);
oneKM = convertGRto1KM(gridReference);
twoKMNumber = CONCAT(LEFT(regexp_replace(oneKM , '^\D{1,2}\d{1}', ''), 1), RIGHT (regexp_replace(oneKM , '^\D{1,2}\d{1}', ''), 1));
CASE
WHEN twoKMNumber IN ('00', '01', '10', '11') THEN twoKMLetter = 'A';
WHEN twoKMNumber IN ('02', '03', '12', '13') THEN twoKMLetter = 'B';
WHEN twoKMNumber IN ('04', '05', '14', '15') THEN twoKMLetter = 'C';
WHEN twoKMNumber IN ('06', '07', '16', '17') THEN twoKMLetter = 'D';
WHEN twoKMNumber IN ('08', '09', '18', '19') THEN twoKMLetter = 'E';
WHEN twoKMNumber IN ('20', '21', '30', '31') THEN twoKMLetter = 'F';
WHEN twoKMNumber IN ('22', '23', '32', '33') THEN twoKMLetter = 'G';
WHEN twoKMNumber IN ('24', '25', '34', '35') THEN twoKMLetter = 'H';
WHEN twoKMNumber IN ('26', '27', '36', '37') THEN twoKMLetter = 'I';
WHEN twoKMNumber IN ('28', '29', '38', '39') THEN twoKMLetter = 'J';
WHEN twoKMNumber IN ('40', '41', '50', '51') THEN twoKMLetter = 'K';
WHEN twoKMNumber IN ('42', '43', '52', '53') THEN twoKMLetter = 'L';
WHEN twoKMNumber IN ('44', '45', '54', '55') THEN twoKMLetter = 'M';
WHEN twoKMNumber IN ('46', '47', '56', '57') THEN twoKMLetter = 'N';
WHEN twoKMNumber IN ('48', '49', '58', '59') THEN twoKMLetter = 'P';
WHEN twoKMNumber IN ('60', '61', '70', '71') THEN twoKMLetter = 'Q';
WHEN twoKMNumber IN ('62', '63', '72', '73') THEN twoKMLetter = 'R';
WHEN twoKMNumber IN ('64', '65', '74', '75') THEN twoKMLetter = 'S';
WHEN twoKMNumber IN ('66', '67', '76', '77') THEN twoKMLetter = 'T';
WHEN twoKMNumber IN ('68', '69', '78', '79') THEN twoKMLetter = 'U';
WHEN twoKMNumber IN ('80', '81', '90', '91') THEN twoKMLetter = 'V';
WHEN twoKMNumber IN ('82', '83', '92', '93') THEN twoKMLetter = 'W';
WHEN twoKMNumber IN ('84', '85', '94', '95') THEN twoKMLetter = 'X';
WHEN twoKMNumber IN ('86', '87', '96', '97') THEN twoKMLetter = 'Y';
WHEN twoKMNumber IN ('88', '89', '98', '99') THEN twoKMLetter = 'Z';
ELSE twoKMLetter = NULL;
END CASE;
IF tenKM IS NOT NULL OR twoKMLetter IS NOT NULL THEN
gridreference = CONCAT(tenKM, twoKMLetter);
ELSE
RAISE INFO 'UNABLE TO CONVERT % TO 2KM GRIDREFERENCE', gridReference;
gridReference = NULL;
END IF;
ELSE
gridReference = NULL; -- 10000 | 5000
END IF;
END; $$
LANGUAGE 'plpgsql';Install PostgreSQL 32 bit ODBC driver on machine using Postgres’s Application Stack Builder or via website. Need corresponding version ie for PostgReSQL 11 need psqlodbs_11_01_0000.zip
Create File Datasource using PostgreSQL Unicode driver and file DNS
Link PostgreSQL tables into Microsoft Access
Create pass-through queries storing connection string in properties
If have odbc error when opening table then try refreshing link in Linked Table Manager