Cannot simply use PostgreSQL table name ("relation does not exist")

Question

I'm trying to run the following PHP script to do a simple database query:

$db_host = "localhost";
$db_name = "showfinder";
$username = "user";
$password = "password";
$dbconn = pg_connect("host=$db_host dbname=$db_name user=$username password=$password")
    or die('Could not connect: ' . pg_last_error());

$query = 'SELECT * FROM sf_bands LIMIT 10';
$result = pg_query($query) or die('Query failed: ' . pg_last_error());

This produces the following error:

Query failed: ERROR: relation "sf_bands" does not exist

In all the examples I can find where someone gets an error stating the relation does not exist, it's because they use uppercase letters in their table name. My table name does not have uppercase letters. Is there a way to query my table without including the database name, i.e. showfinder.sf_bands?

Are you sure that the sf_bands table exists? Does showfinder.sf_bands work? — brian-brazil, Mar 29 '09 at 20:26
Perhaps I should note that my database was migrated from MySQL — Keyslinger, Mar 29 '09 at 20:38
Can you try pg_query($dbconn, $query)? The implicit connection can cause hard-to-debug issues, may as well eliminate it as a possible problem. Can you also try pg_dbname($dbconn) to make sure it's indeed connected to showfinder? — brian-brazil, Mar 29 '09 at 20:53
+1 for mentioning that the uppercase letters are the problem. I spent an hour trying to figure out why I could not select from a single table in PostgreSQL. What a terrible program. — Brain2000, Oct 13 '15 at 03:29

Bill Karwin · Accepted Answer · 2009-03-29T20:59:22.390

478

From what I've read, this error means that you're not referencing the table name correctly. One common reason is that the table is defined with a mixed-case spelling, and you're trying to query it with all lower-case.

In other words, the following fails:

CREATE TABLE "SF_Bands" ( ... );

SELECT * FROM sf_bands;  -- ERROR!

Use double-quotes to delimit identifiers so you can use the specific mixed-case spelling as the table is defined.

SELECT * FROM "SF_Bands";

Re your comment, you can add a schema to the "search_path" so that when you reference a table name without qualifying its schema, the query will match that table name by checked each schema in order. Just like PATH in the shell or include_path in PHP, etc. You can check your current schema search path:

SHOW search_path
  "$user",public

You can change your schema search path:

SET search_path TO showfinder,public;

See also http://www.postgresql.org/docs/8.3/static/ddl-schemas.html

edited Mar 29 '09 at 20:59

answered Mar 29 '09 at 20:25

Bill Karwin

538,548
86
673
828

Oops, forgive me. I meant to say that my table name has no uppercase letters, not my database name. – Keyslinger Mar 29 '09 at 20:39
39

It appears that even if you type `SELECT * FROM SF_Bands` this will still fail, because Postgres decides to lowercase that table name for you. Weird... – Roman Starkov May 17 '11 at 15:09
6

@romkyns: Yes, this is actually pretty common across RDBMS brands, that undelimited identifiers are advertised as "case-insensitive." But they're not truly case insensitive because the way they've implemented that is to force lowercase. This matches the name of the table only if you had allowed the table name to be lowercased when you defined the table. If you use double-quote delimiters when you CREATE TABLE, you must use delimiters when you reference it in queries. – Bill Karwin May 17 '11 at 15:55
5

Postgres automatically lowercases table names if they aren't in quotes? That's pretty asinine... – Andy Jan 04 '18 at 21:30
4

@Andy, when you write your own SQL database, feel free to implement case-insensitive identifiers some other way. :) – Bill Karwin Jan 04 '18 at 21:39
1

@BillKarwin Really, Postgres should be courageous enough to release more sensible, modern case handling as a breaking change. – Andy Jan 05 '18 at 06:30
2

Before you heap ill-guided invective on PostgreSQL, listen to what the SQL standard has to say: *An is equivalent to an in which every letter that is a lower-case letter is replaced by the corresponding upper-case letter or letters. This treatment includes determination of equivalence, representation in the Information and Definition Schemas, representation in diagnostics areas, and similar uses.* So while PostgreSQL does not follow the standard in that it folds everything to lower case, case insensitivity as such is required by the standard. – Laurenz Albe Feb 06 '18 at 12:11
1

Also make sure you are connected to the right db. Keyslinger was doing that right, but there are other workflows that might confuse people or make them oversee the database part. – Tashows Dec 06 '18 at 14:45
On the off chance somebody else runs into this, I used `camelCase` accidentally in a Django `ManyToManyField`, which caused the migration to create a table of mysterious casing, which shows as lowercase in both pgadmin and psql, but `SELECT * from mytable` only worked as `SELECT * from "mytable"`. I'm not really sure how to figure out what casing it actually is. – jrh May 05 '21 at 01:13
In my case , I had written the table name in uppercase but it was in lowercase, thanks a lot . It saved a lot of my time. – Irfan Khan Feb 28 '22 at 20:30
Rather than add name of database on the 'search path', why not first connect to the database before starting the search? – Gathide Jul 13 '22 at 07:35
@Gathide, You would connect to the database, then within that database, you may have multiple schemas. In PostgreSQL, a database and a schema are two different things. A schema is like a subdirectory within a top-level directory corresponding to the database. – Bill Karwin Jul 13 '22 at 12:48
It should be noted that this behavior applies to any database object names in postgres, including schema and column names. – not2savvy Jan 18 '23 at 14:33

score 102 · Answer 2 · edited Apr 23 '13 at 06:41

102

I had problems with this and this is the story (sad but true) :

If your table name is all lower case like : accounts you can use: select * from AcCounTs and it will work fine
If your table name is all lower case like : accounts The following will fail: select * from "AcCounTs"
If your table name is mixed case like : Accounts The following will fail: select * from accounts
If your table name is mixed case like : Accounts The following will work OK: select * from "Accounts"

I dont like remembering useless stuff like this but you have to ;)

edited Apr 23 '13 at 06:41

Apurv

3,723
3
30
51

answered Sep 03 '12 at 15:35

Mitzi

2,652
1
20
15

1

Same for column names in where-clauses – Roland May 12 '14 at 15:46
9

5. Mixed case, like `Accounts`, will fail with `select * from Accounts;` I find the weirdest part: same-case is NOT identical. – Roland May 12 '14 at 16:00
13

All there is to it: all names in postgres query are lowercase, unless you use quotes. – Erndob Oct 03 '17 at 15:20
1

The fourth option worked for me, though I'm not using PHP – andy Mar 01 '18 at 20:20
2

Thanks for laying out all the interactions! :) – GetHacked Aug 26 '19 at 16:07

score 31 · Answer 3 · edited Apr 23 '13 at 06:52

31

Postgres process query different from other RDMS. Put schema name in double quote before your table name like this, "SCHEMA_NAME"."SF_Bands"

edited Apr 23 '13 at 06:52

answered Jul 10 '12 at 20:10

Ugur Artun

1,744
2
23
41

9

What does your answer adds to the previously accepted answer, upvoted 22 times and with lot of details? – Yaroslav Oct 10 '12 at 18:47

score 28 · Answer 4 · edited Dec 06 '16 at 19:43

28

Put the dbname parameter in your connection string. It works for me while everything else failed.

Also when doing the select, specify the your_schema.your_table like this:

select * from my_schema.your_table

edited Dec 06 '16 at 19:43

Eric Leschinski

146,994
96
417
335

answered Mar 03 '11 at 18:38

JarosPL

281
3
2

1

Putting the schema name in, e.g. my_schema.my_relation into the query helped. – JoeTidee Feb 27 '15 at 11:57
3

Thank you very much! It reall helps me solve the problem! But is there a way that i can omit the scheme name? – Charlotte Nov 26 '17 at 12:27

score 20 · Answer 5 · answered Sep 19 '20 at 03:27

20

If a table name contains underscores or upper case, you need to surround it in double-quotes.

SELECT * from "Table_Name";

answered Sep 19 '20 at 03:27

Steve Shipway

3,754
3
22
39

Underscores do not create a problem, only uppercase characters do. – not2savvy Jan 18 '23 at 14:31

score 14 · Answer 6 · edited Jun 05 '18 at 19:49

14

I had a similar problem on OSX but tried to play around with double and single quotes. For your case, you could try something like this

$query = 'SELECT * FROM "sf_bands"'; // NOTE: double quotes on "sf_Bands"

edited Jun 05 '18 at 19:49

e382df99a7950919789725ceeec126

5,751
4
31
56

answered Feb 05 '15 at 14:13

Muzoora Savior

529
5
16

score 12 · Answer 7 · answered Sep 06 '19 at 07:38

12

You must write schema name and table name in qutotation mark. As below:

select * from "schemaName"."tableName";

answered Sep 06 '19 at 07:38

Geshe

679
1
9
23

score 11 · Answer 8 · answered Jun 16 '18 at 07:29

This is realy helpfull

SET search_path TO schema,public;

I digged this issues more, and found out about how to set this "search_path" by defoult for a new user in current database.

Open DataBase Properties then open Sheet "Variables" and simply add this variable for your user with actual value.

So now your user will get this schema_name by defoult and you could use tableName without schemaName.

score 3 · Answer 9 · answered Jan 26 '21 at 15:14

I had the same issue as above and I am using PostgreSQL 10.5. I tried everything as above but nothing seems to be working.

Then I closed the pgadmin and opened a session for the PSQL terminal. Logged into the PSQL and connected to the database and schema respectively :

\c <DATABASE_NAME>;
set search_path to <SCHEMA_NAME>;

Then, restarted the pgadmin console and then I was able to work without issue in the query-tool of the pagadmin.

score 3 · Answer 10 · answered Jun 09 '21 at 22:04

In addition to Bill Karwin's answer =>

Yes, you should surround the table name with double quotes. However, be aware that most probably php will not allow you to just write simply:

$query = "SELECT * FROM "SF_Bands"";

Instead, you should use single quotes while surrounding the query as sav said.

$query = 'SELECT * FROM "SF_Bands"';

score 2 · Answer 11 · answered Apr 05 '16 at 15:30

For me the problem was, that I had used a query to that particular table while Django was initialized. Of course it will then throw an error, because those tables did not exist. In my case, it was a get_or_create method within a admin.py file, that was executed whenever the software ran any kind of operation (in this case the migration). Hope that helps someone.

score 2 · Answer 12 · answered May 19 '21 at 11:41

It might be silly for a few, but in my case - once I created the table I could able to query the table on the same session, but if I relogin with new session table does not exits.

Then I used commit just after creating the table and now I could able to find and query the table in the new session as well. Like this:

select * from my_schema.my_tbl;

Hope this would help a few.

score 1 · Answer 13 · answered Apr 26 '20 at 16:34

1

You have to add the schema first e.g.

SELECT * FROM place.user_place;

If you don't want to add that in all queries then try this:

SET search_path TO place;

Now it will works:

SELECT * FROM user_place;

answered Apr 26 '20 at 16:34

Alexis Gamarra

4,362
1
33
23

score 1 · Answer 14 · edited May 07 '20 at 12:09

1

Easiest workaround is Just change the table name and all column names to lowercase and your issue will be resolved.

For example:

Change Table_Name to table_name and
Change ColumnName to columnname

edited May 07 '20 at 12:09

marc_s

732,580
175
1,330
1,459

answered May 07 '20 at 05:59

meMadhav

233
2
12

Mohamad Alnatsha · Answer 15 · 2021-10-03T22:57:11.660

0

Make sure that Table name doesn't contain any trailing whitespaces

edited Oct 03 '21 at 22:57

answered Oct 03 '21 at 22:33

Mohamad Alnatsha

23
4

score 0 · Answer 16 · edited Jan 16 '23 at 19:53

0

I tried every good answer ( upvote > 10) but not works.

I met this problem in pgAdmin4.

so my solution is quite simple:

find the target table / scheme.
mouse right click, and click: query-tool
in this new query tool window, you can run your SQL without specifying set search_path to <SCHEMA_NAME>;
you can see the result:

edited Jan 16 '23 at 19:53

naXa stands with Ukraine

35,493
19
190
259

answered Apr 27 '22 at 12:06

Siwei

19,858
7
75
95

score 0 · Answer 17 · answered Nov 24 '22 at 16:09

0

I'd suggest checking if you run the migrations or if the table exists in the database.

answered Nov 24 '22 at 16:09

dOkaKuri

1
1
2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 27 '22 at 23:23

score -1 · Answer 18 · answered Jul 26 '22 at 09:15

-1

Try this: SCHEMA_NAME.TABLE_NAME

answered Jul 26 '22 at 09:15

Islam

1
1

Welcome to SO. Please look at the other answers to this question - you will see, that this way has been suggested by many of them. – ahuemmer Jul 30 '22 at 15:44

Cannot simply use PostgreSQL table name ("relation does not exist")

18 Answers18

Linked

Related