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.

CodePudding user response：

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 ...

CodePudding user response：

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:

1. 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.
2. Define it's schema on connection. i.e. jdbc:postgresql://localhost:5432/mydatabase?currentSchema=myschema. More info here.
3. or, clearly state the schema in your query. Laurenz Albe's answer defines this clearly.