Postgres SQL - Key is not present in table, but it is-CodePudding

I'm trying to insert values into a table called dummy from a csv file, in the table dummy I have a value ProductID which references ProductID from another table called product. An error happens though when it tries to copy off the first line of the csv file, it says the Key (productid)=(31) is not present in the table product.

This is false as 31 is present in that table under the productid column:

The csv file:

Both data types for productID were declared as integers.

Here is the code:

     CREATE TABLE Product (
 VolumePerTon real,
 Name varchar(30) ,
 ValuePerTon real,
 ProductID integer ,
 PRIMARY KEY (ProductID) 
)





     CREATE TABLE Dummy (
   BatchID integer ,
      ProductID integer REFERENCES Product(ProductID), 
   ExtractionOrManufacturingDate varchar(30) ,
   OriginalFrom integer REFERENCES Planet(PlanetID)
   );
  \copy Dummy from './data/Batches.csv' with csv header;

here are my list of tables:

When I do \dn, i get 1 row which says: public | username

When I do show search_path i get: "$user", public

CodePudding user response：

Unless you are making a trivial error, like looking at the wrong product table, that would mean that 31 is in the table, but not in the index, which in turn would mean that you are suffering from index corruption.

Run

REINDEX TABLE product;

and try again.

CodePudding user response：

Run

\dt

to check the size of the table. If "product" table is a "parent" table, then check out this for more info about sizing.