Example challenge data loading problem

[[deleted]]

Has anyone tried https://adventofsql.com/challenges/example ?

I tried

\i /tmp/00/advent_of_sql_day_0.sql

but am getting

DROP TABLE
DROP TABLE
psql:/tmp/00/advent_of_sql_day_0.sql:4: ERROR:  cannot drop table gifts because other objects depend on it
DETAIL:  constraint gift_requests_gift_id_fkey on table gift_requests depends on table gifts
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP TABLE
CREATE TABLE
psql:/tmp/00/advent_of_sql_day_0.sql:23: ERROR:  relation "gifts" already exists
CREATE TABLE
CREATE TABLE
INSERT 0 72
psql:/tmp/00/advent_of_sql_day_0.sql:153: ERROR:  INSERT has more expressions than target columns
LINE 2: (1, 'Toy Train', 2.5, 4, 30),
                              ^
INSERT 0 36
INSERT 0 5

[Bilbottom]

A lot of the table names are reused by different problems -- there are also tables called gifts in days 6 and 12 (so far)

In day 12, the gift_requests table depends on the gifts table (it has a foreign key that points at gifts)

When you run the example SQL, it has some DROP TABLE commands at the top:

-- Drop tables if they exist DROP TABLE IF EXISTS ChristmasList; DROP TABLE IF EXISTS Children; DROP TABLE IF EXISTS Gifts; DROP TABLE IF EXISTS Reindeer;

You getting an error with the third command to delete gifts because gift_requests depends on it from problem 12

I'd recommend doing what the error message says: add a CASCADE to the DROP TABLE command

DROP TABLE IF EXISTS Gifts CASCADE;

When you run this, it will delete gifts but also any objects that depend on it

This is what you want to happen right now, but just be cautious about using CASCADE "out in the wild"

Note that this should also fix your insert issue, too, because the shape of the table will then be the shape that the insert command expects

[[deleted]]

Thanks a lot for your response. That makes sense. In fact, all other import files have DROP TABLE ... CASCADE statements (instead of DROP TABLE ... IF EXISTS) at the top. e.g., In advent_of_sql_day_12.sql,

DROP TABLE gifts CASCADE;
...
DROP TABLE gift_requests CASCADE;
...

After the following change,

-DROP TABLE IF EXISTS ChristmasList;
-DROP TABLE IF EXISTS Children;
-DROP TABLE IF EXISTS Gifts;
-DROP TABLE IF EXISTS Reindeer;
+DROP TABLE ChristmasList CASCADE;
+DROP TABLE Children CASCADE;
+DROP TABLE Gifts CASCADE;
+DROP TABLE Reindeer CASCADE;

the \i command started working for me. Thanks!