M. Guillemette M. Guillemette - 1 year ago 56
SQL Question

Accessing associative tables postgresql

I'm working on an existing project that uses a postgresql database. This is my first time working with postgresql.

I have one major issue that is completely blocking me.

In the database creation script, I have the following lines:

CREATE TABLE "TA_cat_group" (
cat character varying NOT NULL,
group character varying NOT NULL

When I use a terminal and connect myself to psql, I can describe my database, by doing

List of relations
Schema | Name | Type | Owner
public | TA_cat_group | table | vit
public | cat | table | vit

I can then do
\d cat
in order to access the description of the cat table.

However, if I do
\d TA_cat_group
the following line appears:

Did not find any relation named "TA_cat_group".

Because of this issue I'm uncapable of doing requests on this table... What could be the reason for this?

PS: I did a
\c vit
before, so I am connected under the right database, that doesn't seem to be the cause of my problem. Moreover, this schema is supposed to be public...

Answer Source

Due to the double quotes your table name is now case sensitive. "TA_cat_group" is a different name then TA_cat_group.

You need to use

\d "TA_cat_group"

I would however recommend to never use double quotes in your SQL statements to avoid having to cope with case-sensitive names.

More details in the manual:

Unrelated, but: you can't use group as a column name without enclosing it in double quotes because it is a reserved keyword. Your example create table in your question will result in:

ERROR: syntax error at or near "group"

The only way to avoid that error would be to use "group" instead of group - but that however would use those dreaded double quotes.