1

I have a table called Foo, which I'm trying to add a column "bar" to. Using psycopg2 in python, my function call is

cursor.execute('ALTER TABLE "Foo" ADD COLUMN bar text')

However, I get the following error:

relation "Foo" does not exist

I also tried putting the schema "public" before it to no avail. What am I doing wrong? Thanks.

Edit:

CraigRinger's command \dt *.?oo yielded No matching relations found

\dt *.foo and \dt *.Foo both yielded the following output:

     List of relations
Schema |Name|Type |Owner
-------+----+-----+--------
public |foo |table|postgres
Teboto
  • 1,005
  • 1
  • 12
  • 16
  • quoting an identifier makes it case sensitive, are you sure your table isn't actually named `foo`? check the last paragraph of [section 4.1.1](http://www.postgresql.org/docs/9.0/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) – mata Aug 11 '15 at 23:18
  • 1
    In `psql` what's the output of `\dt *.?oo` ? – Craig Ringer Aug 11 '15 at 23:41
  • No matching relations found. Did you mean `\dt *.foo` ? – Teboto Aug 11 '15 at 23:58
  • With this command I see the table under schema `public` – Teboto Aug 12 '15 at 00:08
  • Check the PostgreSQL log to see how *exactly* the query arrives at the backend. Some frameworks parse quotes in mysterious ways without your knowledge. – Patrick Aug 12 '15 at 01:23
  • 1
    Your answer to @CraigRinger seems confusing to me. His command *should* have returned all tables in any schema starting with any char and ending in 'oo'. Also if you gave command `\dt *.foo` and you did see a table, then it must be named `foo` not `Foo`. Could you update your question to include the \dt command you gave and the complete output you got? – harmic Aug 12 '15 at 04:10

1 Answers1

0

You table is named foo, not Foo. If you use the quoted name "Foo" then postgres treats it case sensitive and therfore doesn't find it.

Postgres treats all unquoted names as if they were lower case, that is Foo is the same as foo, as are FOO and "foo", however "Foo" is not the same.

This is explicitly mentioned in the documentation:

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)

mata
  • 67,110
  • 10
  • 163
  • 162