MacLochlainns Weblog

The most straightforward way to view the description of a PostgreSQL table is the \d command. For example, this lets you display an account_list table:

\d account_list

Unfortunately, this shows you the table, indexes, and foreign key constraints. Often, you only want to see the list of columns in positional order. So, I wrote a little function to let me display only the table and columns.

There are a few techniques in the script that might seem new to some developers. For example, the data types of the return parameter values of a function that returns values from the data dictionary are specific to types used by the data dictionary. These specialized types are required because the SQL cursor gathers the information from the data dictionary in the information_schema, and most of these types can’t be cast as variable length strings.

A simple assumption that the data dictionary strings would implicitly cast to variable length strings is incorrect. That’s because while you can query them like VARCHAR variables they don’t cast to variable length string. If you wrote a wrapper function that returned VARCHAR variables, you would probably get a result like this when you call your function:

ERROR:  structure of query does not match function result type
DETAIL:  Returned type information_schema.sql_identifier does not match expected type character varying in column 1.

The “character varying” is another name for a VARCHAR data type. Some notes will advise you to fix this type of error by using the column name and a %TYPE. The %TYPE anchors the data type in the function’s parameter list to the actual data type of the data dictionary’s table. You would implement that suggestion with code like:

RETURNS TABLE ( table_schema      information_schema.columns.table_schema%TYPE
              , table_name        information_schema.columns.table_name%TYPE
              , ordinal_position  information_schema.columns.ordinal_position%TYPE
              , column_name       information_schema.columns.column_name%TYPE
              , data_type         information_schema.columns.data_type%TYPE
              , is_nullable       information_schema.columns.is_nullable%TYPE ) AS

Unfortunately, your function would raise a NOTICE for every dynamically anchored column at runtime. The NOTICE messages would appear as follows for the describe_table function with anchored parameter values:

psql:describe_table.sql:34: NOTICE:  type reference information_schema.columns.table_schema%TYPE converted to information_schema.sql_identifier
psql:describe_table.sql:35: NOTICE:  type reference information_schema.columns.table_name%TYPE converted to information_schema.sql_identifier
psql:describe_table.sql:36: NOTICE:  type reference information_schema.columns.ordinal_position%TYPE converted to information_schema.cardinal_number
psql:describe_table.sql:37: NOTICE:  type reference information_schema.columns.column_name%TYPE converted to information_schema.sql_identifier
psql:describe_table.sql:38: NOTICE:  type reference information_schema.columns.data_type%TYPE converted to information_schema.character_data
psql:describe_table.sql:39: NOTICE:  type reference information_schema.columns.is_nullable%TYPE converted to information_schema.yes_or_no

As a rule, there’s a better solution when you know how to discover the underlying data types. You can discover the required data types with the following query of the pg_attribute table in the information_schema:

SELECT attname
,      atttypid::regtype
FROM   pg_attribute
WHERE  attrelid = 'information_schema.columns'::regclass
AND    attname IN ('table_schema','table_name','ordinal_position','column_name','data_type','is_nullable')
ORDER  BY attnum;

It returns:

     attname      |              atttypid
------------------+------------------------------------
 table_schema     | information_schema.sql_identifier
 table_name       | information_schema.sql_identifier
 ordinal_position | information_schema.cardinal_number
 column_name      | information_schema.sql_identifier
 is_nullable      | information_schema.yes_or_no
 data_type        | information_schema.character_data
(6 rows)

Only the character_data type can be replaced with a VARCHAR data type, the others should be typed as shown above. Here’s the modified describe_table function.

CREATE OR REPLACE
  FUNCTION describe_table (table_name_in  VARCHAR)
  RETURNS TABLE ( table_schema      information_schema.sql_identifier
                , table_name        information_schema.sql_identifier
                , ordinal_position  information_schema.cardinal_number
                , column_name       information_schema.sql_identifier
                , data_type         VARCHAR
                , is_nullable       information_schema.yes_or_no ) AS
$$
BEGIN
  RETURN QUERY
  SELECT   c.table_schema
  ,        c.table_name
  ,        c.ordinal_position
  ,        c.column_name
  ,        CASE
             WHEN c.character_maximum_length IS NOT NULL
             THEN CONCAT(c.data_type, '(', c.character_maximum_length, ')')
             ELSE
               CASE
                 WHEN c.data_type NOT IN ('date','timestamp','timestamp with time zone')
                 THEN CONCAT(c.data_type, '(', numeric_precision::text, ')')
                 ELSE c.data_type
               END
           END AS modified_type
  ,        c.is_nullable
  FROM     information_schema.columns c
  WHERE    c.table_schema NOT IN ('information_schema', 'pg_catalog')
  AND      c.table_name = table_name_in
  ORDER BY c.table_schema
  ,        c.table_name
  ,        c.ordinal_position;
END;
$$ LANGUAGE plpgsql;

If you’re new to PL/pgSQL table functions, you can check my basic tutorial on table functions. You call the describe_table table function with the following syntax:

SELECT * FROM describe_table('account_list');

It returns:

 table_schema |  table_name  | ordinal_position |   column_name    |        data_type         | is_nullable
--------------+--------------+------------------+------------------+--------------------------+-------------
 public       | account_list |                1 | account_list_id  | integer(32)              | NO
 public       | account_list |                2 | account_number   | character varying(10)    | NO
 public       | account_list |                3 | consumed_date    | date                     | YES
 public       | account_list |                4 | consumed_by      | integer(32)              | YES
 public       | account_list |                5 | created_by       | integer(32)              | NO
 public       | account_list |                6 | creation_date    | timestamp with time zone | NO
 public       | account_list |                7 | last_updated_by  | integer(32)              | NO
 public       | account_list |                8 | last_update_date | timestamp with time zone | NO
(8 rows)

As always, I hope this helps those looking for a solution to functions that wrap the data dictionary and display table data from the PostgreSQL data dictionary.

How to write an overloaded set of hello_world functions in PostgreSQL PL/pgSQL. The following code lets you write and test overloaded functions and the concepts of null, zero-length string, and string values.

-- Drop the overloaded functions.
DROP FUNCTION IF EXISTS hello_world(), hello_world(whom VARCHAR);
 
-- Create the function.
CREATE FUNCTION hello_world()
RETURNS text AS
$$
DECLARE
  output  VARCHAR(20);
BEGIN
  /* Query the string into a local variable. */
  SELECT 'Hello World!' INTO output;
 
  /* Return the output text variable. */
  RETURN output;
END
$$ LANGUAGE plpgsql;
 
-- Create the function.
CREATE FUNCTION hello_world(whom VARCHAR)
RETURNS text AS
$$
DECLARE
  output  VARCHAR(20);
BEGIN
  /* Query the string into a local variable. */
  IF whom IS NULL OR LENGTH(whom) = 0 THEN
    SELECT 'Hello World!' INTO output;
  ELSE
    SELECT CONCAT('Hello ', whom, '!') INTO output;
  END IF;
 
 
  /* Return the output text variable. */
  RETURN output;
END
$$ LANGUAGE plpgsql;
 
-- Call the function.
SELECT hello_world();
SELECT hello_world(Null) AS output;
SELECT hello_world('') AS output;
SELECT hello_world('Harry') AS output;

It should print:

    output
--------------
 Hello World!
(1 row)
 
    output
--------------
 Hello World!
(1 row)
 
    output
--------------
 Hello World!
(1 row)
 
    output
--------------
 Hello Harry!
(1 row)

As always, I hope this helps those looking for the basics and how to solve problems.

This post is a step-by-step install guide to PostgreSQL 14 on Windows 10. It sometimes makes me curious that folks want a summary of screen shots from a Microsoftw Software Installer (MSI) because they always appear to me as straightforward.

This walks you through installing PostgreSQL 14, EDS’s version of Apache, supplemental connection libraries, and pgAdmin4. You can find the post-installation steps in my earlier Configure PostgreSQL 14 post.

PostgreSQL Database 14 Installation Steps

1. The first thing you need to do is download the PostgreSQL MSI file, which should be in your C:\Users\username\Downloads directory. You can double-click on the MSI file.

2. After double-clicking on the MSI file, you are prompted by User Account Control to allow the PostgreSQL MSI to make changes to your device. Clicking the Yes button is the only way forward.

3. The Setup – PostgreSQL dialog requires you click the Next button to proceed.

4. The Installation Directory dialog prompts you for an installation directory. The default directory is C:\Program Files\PostgreSQL\14 and you should use it. Click the Next button to continue.

5. The Select Components dialog prompts you to choose the products to install. You should choose all four – PostgreSQL Server, pgAdmin 4, Stack Builder, and Command Line Tools. Click the Next button to continue.

6. The Password dialog prompts you for the PostgreSQL superuser password. In a development system for your local computer, you may want to use something straightforward like cangetin. Click the Next button to continue.

7. The Setup dialog lets you select the port number for the PostgreSQL listener. Port 5432 is the standard port for a PostgreSQL database, and ports 5433 and 5434 are used sometimes. Click the Next button to continue.

8. The Advanced Options dialog lets you select the Locale for the database. As a rule for a development instance you should chose the Default locale. Click the Next button to continue.

9. The Pre Installation Summary dialog tells you what you’ve chosen to install. It gives you a chance to verify what you are installing. Click the Next button to continue.

10. The Ready to Install dialog lets you pause before you install the software. Click the Next button to continue.

11. The Installing dialog is a progress bar that will take several minutes to complete. When the progress bar completes, click the Next button to continue.

12. The Completing the PostgreSQL Setup Wizard dialog tells you that the installation is complete. Click the Finish button to complete the PostgreSQL installation.

13. The Welcome to Stack Builder! dialog lets you choose an installation from those on your computer to build a software stack. Click the drop down box to chose an installation.

14. The second copy of the Welcome to Stack Builder! dialog shows the choice of the PostgreSQL installation you just completed. Click on the Next button to continue.

15. The Stack Builder dialog prompts you to choose the products to install. You should choose all four database drivers – Npgsql, pgJDBC, psqlODBC, psqlODBC; and the PostGIS 3.1 and PostGIS 3.2 Bundles for PostgreSQL. Then, click the Next button to continue.

16. The Stack Builder dialog shows you the products you will install. You should choose all four database drivers – Npgsql, pgJDBC, psqlODBC, psqlODBC; and the PostGIS 3.1 and PostGIS 3.2 Bundles for PostgreSQL. Click the Next button to continue.

17. The Stack Builder dialog shows a download progress bar subdialog, which may take some time to complete. The Stack Builder dialog’s Progress Bar automatically advances to the next dialog box.

18. The Stack Builder dialog tells you the products you downloaded. Click the Next button to continue the developer stack.

19. The Setup dialog advises that you are installing the PEM-HTTPD Setup Wizard. Click the Next button to continue.

20. The Installation Directory dialog prompts you for an installation directory. The default directory is C:\Program Files (x86)\edb\pem\httpd and you should use it because that’s where Windows 10 puts 64-bit libraries. Click the Next button to continue.

21. The Setup dialog lets you select the port number for the HTTP listener. Port 8080 is the standard port for an HTTP listener, and ports 8081, 8082, and so forth are used when you have more than one HTTP listener on a single server or workstation. Click the Next button to continue.

22. The Ready to Install dialog lets you pause before you install the software. Click the Next button to continue.

23. The Installing dialog is a progress bar that will take several minutes to complete. When the progress bar completes, click the Next button when it becomes available to continue.

24. The Windows Security Alert dialog asks you to allow the Apache HTTP Server to use port 8080. Click the Private networks, such as my home or work network checkbox and then the Allow access button to continue.

25. The Setup dialog advises that you have completed the installation of the PEM-HTTPD Setup Wizard. Click the Finish button to continue.

26. The Stack Builder dialog advises you that all four database drivers – Npgsql, pgJDBC, psqlODBC, psqlODBC are downloaded and ready to install. Click the Next button to continue.

27. The Installation Directory dialog prompts you for an installation directory. The default directory is C:\Program Files (x86)\PostgreSQL\Npgsql and you should use it because that’s where Windows 10 puts 64-bit libraries and the subdirectory meets the standard installation convention for Microsoft .Net libraries. Click the Next button to continue.

28. The Ready to Install dialog lets you pause before you install the Npgsql software for Microsoft .Net. Click the Next button to continue.

29. The Setup dialog advises that you have completed the installation of npgsql driver for Microsoft .Net. Click the Finish button to continue.

30. The Setup dialog advises that you are installing the pgJDBC diver Setup Wizard. Click the Next button to continue.

31. The Installation Directory dialog prompts you for an installation directory. The default directory is C:\Program Files (x86)\PostgreSQL\pgJDBC and you should use it because that’s where Windows 10 puts 64-bit libraries and the subdirectory meets the standard installation convention for libraries. Click the Next button to continue.

32. The Ready to Install dialog lets you pause before you install the pgJDBC software. Click the Next button to continue.

33. The Setup dialog advises that you have completed the installation of pgJDBC driver. Click the Finish button to continue.

34. The Advisory Message pgAdmin is Starting dialog is really telling you to be patient. It can take a couple minutes to launch pgAdmin.

35. The Password dialog prompts you for the pgAdmin superuser password. In a development system for your local computer, you may want to use something straightforward like cangetin. Click the Next button to continue.

36. Enter your password from the earlier step and click the OK button.

37. This is the pgAdmin console. You should see one database and tweleve login/group roles.

The foregoing walked you through the installation of PostgreSQL, the connector libraries and pgAdmin utility. I hope it helps those who would like to see the installation steps.

After you install PostgreSQL 14 on Windows, there are a few steps to create a sandbox database. This post shows you those steps, including a couple Windows OS tasks you’ll need to complete. You should note that these instructions are for the PostgreSQL psql Command Line Interface (CLI).

Open a Command Prompt with Administrator privileges. It should give you a command prompt like the following:

Microsoft Windows [Version 10.0.19042.1466]
(c) Microsoft Corporation. All rights reserved.
 
C:\Users\username>

Type psql to launch the PostgreSQL CLI and then the return or enter key:

C:\Users\username>psql

Most likely, you’ll get the following error message. It means that your System Path environment variable doesn’t include the directory where the psql executable is found, and that’s why the command is not recognized.

'psql' is not recognized as an internal or external command, operable program or batch file.

You can set it temporarily in your current Windows CLI with the following command:

set PATH=%PATH%;C:\Program Files\PostgreSQL\14\bin;

For those familiar with Windows CLI navigation in prior releases, the convention is to append a semicolon at the end of the item added to the %PATH% environment variable. If you were to put the semicolon between the %PATH% and new directory path there would be two semicolons together. While it won’t do any harm, it’s best to follow the new convention or style.

CRITICAL NOTE: The rest of the post assumes you have set the correct %PATH% environment variable or added it to your System’s Path environment variable and restarted the Windows CLI after adding it through the GUI tool. The reason you need to restart the Windows CLI is that the %PATH% environment variable is inherited at startup and doesn’t change in an open Windows CLI shell.

Another common mistake some users make, at least those who have used an older version of the psql utility on a Linux distribution (or “distro”), is to type psql without any arguments to become the superuser. This error doesn’t occur in Linux because you most likely connected as the postgres user before trying to connect to the PostgreSQL database. A quick demonstration should illustrate the error and support explaining why it occurs on the Windows OS.

Attempting to connect to the PostgreSQL database as a non-postgres user:

C:\Users\username>psql

You should get the following error:

psql: error: connection to server at "localhost" (::1), port 5432 failed: fe_sendauth: no password supplied

This error occurs because you’re not the postgres user, and all other users must designate that they’re connecting to the superuser account. The correct syntax is:

C:\Users\username>psql -U postgres

Then, you’ll be prompted for the password that you set when you installed PostreSQL database. Enter that password from the installation at the prompt.

Password for user postgres:
psql (14.1)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.
 
postgres=#

The warning message is telling you that the character set collation differs between your Windows OS and the PostgreSQL database. We’ll discuss this more later but for the sake of almost all your work, it won’t matter. If the warning message bothers you, you can run the chcp command before launching PostgreSQL when you open your Windows CLI:

chcp 1252

The chcp command changes your terminal character set to align with the Latin 1 character set, which enables you to use things like non-English accent characters (the umlaut over an o, or ö). After running the You will see this when you connect after running that command:

psql (14.2)
Type "help" for help.
 
postgres#

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Command Processor

Now that you’re connected as the superuser, let’s examine the steps to configure your playground or videodb database. There are five steps to configure your database and one step to connect and use the videodb database as the student user

1. Create Physical Directory

postgres=# show data_directory;

           data_directory
-------------------------------------
 C:/Program Files/PostgreSQL/14/data
(1 row)

md video_db

2. Create Tablespace

CREATE TABLESPACE video_db
  OWNER postgres
  LOCATION 'C:\Users\username\video_db';

CREATE TABLESPACE

SELECT * FROM pg_tablespace;

  oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 16395 | video_db   |       10 |        |
(3 rows)

3. Create a Database

postgres=# SELECT datname, datcollate FROM pg_database WHERE datname = 'postgres';

  datname  |         datcollate
-----------+----------------------------
 postgres  | English_United States.1252
(1 row)

CREATE DATABASE videodb
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = video_db
       LC_COLLATE = 'English_United States.1252'
       LC_CTYPE = 'English_United States.1252'
       CONNECTION LIMIT = -1;

postgres# \l

                                                 List of databases
   Name    |  Owner   | Encoding |          Collate           |           Ctype            |   Access privileges
-----------+----------+----------+----------------------------+----------------------------+-----------------------
 postgres  | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
 template0 | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres          +
           |          |          |                            |                            | postgres=CTc/postgres
 template1 | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres          +
           |          |          |                            |                            | postgres=CTc/postgres
 videodb   | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
(4 rows)

COMMENT ON DATABASE videodb IS 'Video Store Database';

4. Create a Role, Grant, and User

5. Create a Schema

psql -U postgres -W

GRANT CREATE ON DATABASE videodb TO student;

QUIT;

psql -U postgres -W

CREATE SCHEMA app;

SELECT   * 
FROM     pg_catalog.pg_namespace
ORDER BY nspname;

  oid  |      nspname       | nspowner |               nspacl
-------+--------------------+----------+-------------------------------------
 16399 | app                |    16398 |
 13388 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
    11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
    99 | pg_toast           |       10 |
  2200 | public             |       10 | {postgres=UC/postgres,=UC/postgres}
(5 rows)

-- Set client messages to warning or error, which means any
-- notice associated with the if exists clause is suppressed.
SET client_min_messages TO warning;
 
-- Conditionally drop an existing revision_history table in
-- the app schema.
DROP TABLE IF EXISTS revision_history;
 
-- Create a revision_history table.
CREATE TABLE app.revision_history
( revision_history_id serial
, session_id          VARCHAR
, table_name          VARCHAR
, revision_id         INTEGER );

\I revision_history.sql

\d revision_history

Did not find any relation named "revision_history".

show search_path;

   search_path   
-----------------
 "$user", public
(1 row)

SET search_path TO app, "$user", public;

\d revision_history

                                                  Table "app.revision_history"
       Column        |       Type        | Collation | Nullable |                            Default                            
---------------------+-------------------+-----------+----------+---------------------------------------------------------------
 revision_history_id | integer           |           | not null | nextval('revision_history_revision_history_id_seq'::regclass)
 session_id          | character varying |           |          | 
 table_name          | character varying |           |          | 
 revision_id         | integer           |           |          |

6. Connect as student to videodb:

psql -d videodb -U student -W

Password:
psql (14.1)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.
 
videodb=>

SELECT current_database();

 current_database 
------------------
 videodb
(1 row)

This has shown you how to create a videodb tablespace, a videodb database, a dba role, a student user, an app schema, and connect to your new videodb database as the student user. As always, I hope it lets you get a lot down with little effort and avoiding pages and pages of documentation.