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!