Queries in Postgres fail with ERROR: relation "<tablename>" does not exist
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
When manually running queries against Jira database in Postgres, user is informed that the table does not exist.
Environment
Jira Server or DC
Postgres database
Diagnosis
When manually running queries against Jira database in Postgres, user is informed that the table does not exist.
For example:
1
2
3
jiradb=# select * from cwd_user;
ERROR: relation "cwd_user" does not exist
LINE 1: select * from cwd_user;We can confirm that the table is present by running the following query:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
jiradb=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------------------------+-------+------------
jira | cwd_synchronisation_status | table | jiradbuser
jira | cwd_synchronisation_token | table | jiradbuser
jira | cwd_user | table | jiradbuser
jira | cwd_user_attributes | table | jiradbuserCause
In this case, the query is failing because the database was created with a custom schema name called "jira".
The default schema in postgres is "public", so, when you leave the schema name out of your sql, it will try to search for the table as public.cwd_user.
Solution
You must change your sql, so it includes the schema name in your query, <schema>.<table_name>:
Example:
1
jiradb=# select * from jira.cwd_user;Was this helpful?