What causes error "relation "table_name" does not exist" and how can I fix it?

Question

I know this question was asked before, but none of the suggested solutions worked for me.

I have the following simple query:

UPDATE scm_repos repos SET token_id=(SELECT token_id FROM scm_orgs orgs WHERE repos.org_id=orgs.id)

When running this query locally on my PostgreSQL, it works as expected, but when it runs as a script on our 'deploy' phase on a remote PostgreSQL, it fails with the following error:

UPDATE scm_repos repos SET token_id=(SELECT token_id FROM scm_orgs orgs WHERE repos.org_id=orgs.id); nested exception is org.********ql.util.PSQLException: ERROR: relation "scm_repos" does not exist

I tried to surround the table name with double quotes and I also made sure that the table relates to a public schema (I saw some solutions suggests to explicitly write the schema name if not public).

What am I missing here? This is a very simple task but nothing I tried makes it run.

score 1 · Answer 1 · answered Oct 01 '21 at 13:47

1

Either you are connected to the wrong database, or the parameter search_path is set differently.

Do the obvious thing and name the schema explicitly:

UPDATE public.scm_repos SET ...

answered Oct 01 '21 at 13:47

Laurenz Albe

209,280
17
206
263

Thanks for the suggestion. I tried to add the 'public' schema explicitly as you wrote and also with double quotes ("public"."scm_repos"), but still getting the error: `ERROR: relation "public.scm_repos" does not exist`. The table does exists in the remote database. any other solution? – Nimrod Oct 01 '21 at 16:20
1

Then the table really doesn't exist. You probably connected to the wrong database, like I said. – Laurenz Albe Oct 01 '21 at 16:30
You were right. After further investigation it turns out the table actually doesn't exists. I searched for a proper ways to run the UPDATE query only if the table exists, but none of them actually worked... What is the correct way running this script? – Nimrod Oct 03 '21 at 11:17
Simply run it, and catch and handle the error. That is usually the best, since it is safe from race conditions. – Laurenz Albe Oct 04 '21 at 05:51

score 1 · Answer 2 · edited Oct 01 '21 at 16:02

Postgres is a bit finicky, meaning that sometimes you have to specify the schema and database clearly in connection. As such, it can behave differently on the local and remote machines.

What you could do is:

Firstly check the current state of the remote database. You can easily do that by using tools such as DataGrip. Specify the remote connection as you normally would do.
Define it's schema on connection. i.e. jdbc:postgresql://localhost:5432/mydatabase?currentSchema=myschema. More info here.
or, clearly state the schema in your query. Laurenz Albe's answer defines this clearly.

What causes error "relation "table_name" does not exist" and how can I fix it?

2 Answers2