How do I make a simple day dimension table for data warehousing star schema with postgresql?

Question

How would I go about creating and populating a simple DAY dimension table for a star schema in postgreSQL ? It is for an intro course to data warehousing and so it only has a few fields but most of the examples online are very involved and seem very complicated for a beginner. This isn't for an assignment - it is for studying because I am trying to make my own simple Star Schema with a fact table so I can start getting comfortable with it.

Can anyone give me a simple example of how I'd create the table with just a few fields (day_key as the surrogate key, a string describing the day, and some integer values representing the days or months for example) so I can at least get started on understanding?

Answer 1

A very simple DAY dimension table that should work for most versions of PostgreSQL (I am using 10.5). This is just something that should help someone newer to Data Warehousing make a basic day dimension for use when just getting started.

Create a Day Table

CREATE TABLE day (
    day_key SERIAL PRIMARY KEY, -- SERIAL is an integer that will auto-increment as new rows added
    description VARCHAR(40), -- a 'string' for a description
    full_date DATE, -- an actual date type
    month_number INTEGER,
    month_name VARCHAR(40),
    year INTEGER
);

Inserting Rows into the Day dimension

INSERT INTO day(description, full_date, month_number, month_name, year)
SELECT 
to_char(days.d, 'FMMonth DD, YYYY'), 
days.d::DATE, 
to_char(days.d, 'MM')::integer, 
to_char(days.d, 'FMMonth'), 
to_char(days.d, 'YYYY')::integer 
from (
    SELECT generate_series(
        ('2019-01-01')::date, -- 'start' date
        ('2019-12-31')::date, -- 'end' date
        interval '1 day'  -- one for each day between the start and day
        )) as days(d);

Result

Notes:

• Basically you are just using the rows generated by the nested SELECT generate_series(... to insert into the Day table.
• I used the FM above twice to remove some of the white space padding automatically generated in some of these date formatting.
• I'd recommend removing the INSERT INTO day(...) line the first time you do this just to make sure the format of each column is what you're after before inserting it into your table.

This is just what I've seen commonly used - check the PostgreSQL documentation has some more thorough and good examples of more ways to format date types and get all kinds of useful dimensions.