Psql Error For Restoring Pgsl Backup On Cmd

I'm having a hard time understanding what this error means. The command I used was: psql -U postgres -d app -1 -f postgres.sql and this is the error: psql:postgres.sql:1879: ERRO

Solution 1:

As joop explained, your SQL file is inconsistent.

There is a foreign key constraint from raffle.user_id to "user".id, which means that for every value in raffle.user_id there must be a row in "user" where id has the same value.

Now there is no row inserted in "user" with an id equal to 1, but the script attempts to insert a row in raffle with user_id equal to 1.

That violates the foreign key constraint an causes an error. Once there has been an error in a PostgreSQL transaction, all you can do is ROLLBACK. Until you do that, all statements in the transaction will fail with the error you observe.

The only solutions you have are either to fix the data so that they are consistent or to give up consistency by removing the foreign key constraint.

Remark: it is a bad idea to choose a reserved SQL keyword like USER as name.

Solution 2:

As @joop commented: your .dmp file is crippled because user_id=1 does not exist in the users table, but is referred in the raffle table. Example: run the next snippet, then uncomment the line with user#1 on it and run it again.

---

-- Drop the schema *after* usage-- DROP SCHEMA tmp CASCADE;CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATETABLE users (
        id INTEGERNOTNULL,
        email VARCHAR(120),
        PRIMARY KEY (id),
        UNIQUE (email)
);
    -- UNCOMMENT the next line to also add user#1-- INSERT INTO users VALUES(1,'Jim.Fake@nolive.co.uk');INSERTINTO users VALUES(2,'osman.narnia@live.co.uk');
INSERTINTO users VALUES(3,'KimFake1@outlook.com');
INSERTINTO users VALUES(4,'jaakume@gmail.com');
INSERTINTO users VALUES(5,'omarblack@protonmail.com');
INSERTINTO users VALUES(6,'osman.everton@hotmail.com');
INSERTINTO users VALUES(7,'radoslaw@ganczarek.in');
INSERTINTO users VALUES(8,'kane1001@live.co.uk');
INSERTINTO users VALUES(9,'osman.soloking009@outlook.com');
INSERTINTO users VALUES(10,'Shum1945@fleckens.hu');
CREATETABLE raffle (
        id INTEGERNOTNULL,
        user_id INTEGER,
        colour VARCHAR(120),
        up1 VARCHAR(4),
        up2 VARCHAR(4),
        PRIMARY KEY (id),
        CONSTRAINT _color_up1_up2_uc UNIQUE (colour, up1, up2),
        FOREIGN KEY(user_id) REFERENCES users (id)
);
INSERTINTO raffle VALUES(1,1,'Blue','7c4c','5c7e');
INSERTINTO raffle VALUES(2,1,'Pink','635d','853f');
INSERTINTO raffle VALUES(3,1,'Plum','5e80','7611');
INSERTINTO raffle VALUES(4,1,'Aqua','937c','1b75');
INSERTINTO raffle VALUES(5,2,'Navy','1d9a','8914');
INSERTINTO raffle VALUES(6,1,'Grey','d869','fc97');
INSERTINTO raffle VALUES(7,4,'Rose','5fee','b31f');
INSERTINTO raffle VALUES(8,1,'Ruby','d5b4','e749');
INSERTINTO raffle VALUES(9,2,'Teal','cf0b','3bf5');
INSERTINTO raffle VALUES(10,1,'Gold','98a7','3079');
INSERTINTO raffle VALUES(11,1,'Jade','5c69','66f8');
INSERTINTO raffle VALUES(12,1,'Lime','156f','6b34');
INSERTINTO raffle VALUES(13,1,'Blue','7da3','d95b');
INSERTINTO raffle VALUES(14,1,'Pink','a63e','b9b6');
INSERTINTO raffle VALUES(15,1,'Plum','d989','71a5');
INSERTINTO raffle VALUES(16,1,'Aqua','7372','0682');
/****
INSERT INTO raffle VALUES(17,9,'Navy','01b3','e444');
INSERT INTO raffle VALUES(18,1,'Grey','d679','0123');
INSERT INTO raffle VALUES(19,1,'Rose','5963','692d');
...
***/COMMIT;