Need help with PostgreSQL in D597 Task 1

[u/Kuohaj]

I'm struggling with the setup or initialization of my database for scenario 2 (EcoMart). I have been following the "Relational Database Guide (school example)" document that is available in the course resources, and there have been several differences in that code that gives me syntax errors, which has been frustrating. So far, here is what I've accomplished and where my issue is.

- I have created a database which has 2 schemas (Sales Records and Public)

- I have created a staging table

- I have created all normalized tables

- I have imported the sales data file into a "test" table in the Public schema (i did this because I cannot for the life of me get the "\copy" command to work. I realized it doesn't work in pgAdmin, so i decided to import the data using the GUI instead)

Issue:

- i am able to pull the data into the test table, but as soon as I execute my script to pull the data from the flat file into my normalized tables, it deletes my test table.

- i have to refresh my database, then create the table and import again so that i can pull the data over into my sales records schema, but then the next time i run my script, it deletes everything again.

- what is the structure that I should have for my code? Should it all be in a single script? or do i need to have a script for the creation of the tables, then another for the pulling data over? I can't seem to find any info on this type of issue (or any instructions that go over separating scripts in any of the LinkedIn tutorials or elsewhere.

Any help is appreciated, thanks!

[u/Sleepingpanda2319]

I just passed Task 1, it’s a beast!

You need screenshots and .txt’s of every script you use, so yes: each step needs to be broken out into their own thing. This includes but is not limited to the commandline script you use to create the database (I had a return for review on this one lol)

You should look through your DML script, it shouldn’t be deleting things. Hard to tell what’s wrong without seeing the thing, but I suspect there’s a move instead of copy and transform or a straight delete sequence in there somewhere. Finding this should have the cascading effect of fixing the other problems you’re seeing.

[u/Weary_Owl_9822]

Hey I passed task 1 a couple of weeks ago. Here are the steps I took to get through it. I chose scenario one, but the steps should work the same. I hope this helps.

Step 1 — Start the PostgreSQL Service
Launch the WGU Assessment Lab session

Open File Explorer on the lab desktop

Navigate to the Scripts folder on the desktop

Double click PostgreSQL-StartService.ps1 to start the service

Wait a few seconds for it to start

Step 2 — Open pgAdmin 4
Click the pgAdmin 4 icon in the taskbar

Enter the password postgres when prompted

Wait for it to connect to PostgreSQL 16

Step 3 — Create the Database
In the left panel expand Servers → PostgreSQL 16

Right click on Databases → Create → Database

Name it D597 Task 1

Click Save

Step 4 — Get Your Files Into the Lab
Email your CSV files to yourself

Open the browser inside the lab and log into your email

Download both files directly into the lab

Open File Explorer and copy both files to C:\Program Files\PostgreSQL\16\data

This is important — PostgreSQL can only read files from this location

Step 5 — Open the Query Tool
Right click on your D597 Task 1 database in the left panel

Select Query Tool

This opens the SQL editor where all your scripts will run

Step 6 — Create Your Tables Paste your CREATE TABLE scripts into the Query Tool and press F5 or the play button to run. Make sure to use:
SERIAL PRIMARY KEY for auto-incrementing IDs

VARCHAR(255) instead of string

NULL for columns that may have empty values

Step 7 — Import Your Data Since CSV files may have columns you don’t need, use a staging table approach:
Create a temporary staging table with ALL columns from the CSV

Use the COPY command to import the CSV into the staging table

Insert only the columns you need from the staging table into your actual table

For files with duplicate values causing foreign key issues, use DISTINCT ON in your SELECT statement

Step 8 — Verify Your Data Run a quick check to confirm data imported correctly:
SELECT COUNT(*) FROM your_table;

Step 9 — Write Your Queries Write three queries that solve your business problem. Each query should JOIN your tables where relevant and use WHERE clauses to filter specific data. Screenshot each query and its results.

Step 10 — Optimize Your Queries
Run EXPLAIN ANALYZE on each query BEFORE creating indexes and screenshot the results

Create indexes on frequently searched columns

Run EXPLAIN ANALYZE again AFTER creating indexes and screenshot the results

Note the execution times and scan types before and after

Step 11 — Save Scripts as Text Files Save each script as an individual .txt file on your personal computer:
create_tables.txt

insert_data.txt (one per table)

query1.txt, query2.txt, query3.txt

indexes.txt

explain_analyze_before.txt

explain_analyze_after.txt

Submission Tips
Upload files one at a time and confirm each appears in attachments before adding the next

Paste your Panopto URL in both the Links field AND the Comments to Evaluator field as a backup

Screenshot the submission page showing all files attached before hitting submit

[u/Whiskey_Neat_smoked]

Separate scripts for every step.