2

I am trying to perform the seemingly simple, even routine task of importing a TSV file into a Postgresql 9.4 database table.

2 Questions:

  1. Why can't it find this table, and

  2. Why is it telling me it can't find a column when I asked for a table?

I saw this question, but it doesn't have an answer. I have also since learned that mixED caps are not a good idea. You can see that I tried "double quotes". I can change the names later if I have to, but I'm hoping to get this resolved as is.

    > (aishah) malikarumi@Tetuoan2:~/Projects/aishah/jamf35$ python
    > importbyhand.py Database Error relation "codeat_code" does not exist
    > 
    > (aishah) malikarumi@Tetuoan2:~/Projects/aishah/jamf35$ psql
    > aishahjamf35 -U postgres psql (9.5.4, server 9.4.8) Type "help" for
    > help.

    aishahjamf35=# select table_schema, table_name
    aishahjamf35-# from information_schema.tables
    aishahjamf35-# where lower(table_name) = "codeat_code"
     ;
    ERROR:  column "codeat_code" does not exist
    LINE 3: where lower(table_name) = "codeat_code"
                                      ^
    aishahjamf35=# select table_schema, table_name
    from information_schema.tables
    where lower(table_name) = "codeat_code"
    ;
    ERROR:  column "codeat_code" does not exist
    LINE 3: where lower(table_name) = "codeat_code"
                                      ^
    aishahjamf35=# SELECT table_schema, table_name
    aishahjamf35-# FROM information_schema.tables
    aishahjamf35-# WHERE lower(table_name) = "codeat_code";
    ERROR:  column "codeat_code" does not exist
    LINE 3: WHERE lower(table_name) = "codeat_code";
                                      ^

aishahjamf35=# SELECT table_schema, table_name aishahjamf35-# FROM information_schema.tables;

table_schema | table_name
--------------------+---------------------------------------

...........a long list of tables............

pg_catalog | pg_statistic
pg_catalog | pg_type
public | codeAT_code_by_code
public | codeAT_code_by_case
public | codeAT_code

...........a long list of tables............

aishahjamf35=# SELECT table_schema, table_name
FROM information_schema.tables
aishahjamf35-# WHERE table_name = "codeAT_code"
;
ERROR:  column "codeAT_code" does not exist
LINE 3: WHERE table_name = "codeAT_code"
                           ^
aishahjamf35=# SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name = "CodeAT_code"
;
ERROR:  column "CodeAT_code" does not exist
LINE 3: WHERE table_name = "CodeAT_code"
                           ^
aishahjamf35=# SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name = "public.CodeAT_code"
;
ERROR:  column "public.CodeAT_code" does not exist
LINE 3: WHERE table_name = "public.CodeAT_code"
                           ^
aishahjamf35=# SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name = "public_CodeAT_code"
;
ERROR:  column "public_CodeAT_code" does not exist
LINE 3: WHERE table_name = "public_CodeAT_code"
                           ^
aishahjamf35=# SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name = \"codeAT_code\""
;
Invalid command \"codeAT_code. Try \? for help.
aishahjamf35-# \?

aishahjamf35=# SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name LIKE code 
;
ERROR:  column "code" does not exist
LINE 3: WHERE table_name LIKE code
                              ^
aishahjamf35=# SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name LIKE "code"
;
ERROR:  column "code" does not exist
LINE 3: WHERE table_name LIKE "code"
                              ^
aishahjamf35=# SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name LIKE %code%
;
ERROR:  syntax error at or near "%"
LINE 3: WHERE table_name LIKE %code%
                              ^
aishahjamf35=# SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name LIKE "%code%"
;
ERROR:  column "%code%" does not exist
LINE 3: WHERE table_name LIKE "%code%"
                              ^
aishahjamf35=# SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name LIKE %"code"%
;
ERROR:  syntax error at or near "%"
LINE 3: WHERE table_name LIKE %"code"%
                              ^

aishahjamf35=# \dt

                 List of relations
 Schema |                 Name                  | Type  |  Owner   
--------+---------------------------------------+-------+----------
 ...........a long list of tables............


 public | codeAT_code                           | table | postgres
 public | codeAT_code_by_case                   | table | postgres
 public | codeAT_code_by_code                   | table | postgres
 public | codeAT_code_by_commentary             | table | postgres

 ...........a long list of tables............
0

1 Answer 1

1

There are two separate issues. The first two lines:

> (aishah) malikarumi@Tetuoan2:~/Projects/aishah/jamf35$ python
> importbyhand.py Database Error relation "codeat_code" does not exist

mean that your Python code tries to access a table "codeat_code" or codeat_code. Either way, as the \d output reveals your table is named "codeaAT_code" and the capital AT make all the difference. As you found out in other blogs, it's easier to work with lower case named tables in Postgres.

If you want to query the table, use double quotes (with psql or the SQL produced by Python): "codeAT_code" or change the table name to have all lower case characters.


The second issue is why the table does not appear when you search the information_schema tables while it shows with \dt:

aishahjamf35=# select table_schema, table_name
aishahjamf35-# from information_schema.tables
aishahjamf35-# where lower(table_name) = "codeat_code"
 ;
ERROR:  column "codeat_code" does not exist
LINE 3: where lower(table_name) = "codeat_code"

The error about the missing column is because the double quotes say to Postgres to parse that as where lower(tables.table_name) = tables."codeat_code" and the system table (named tables) does not have such a column.

Use single quotes when querying the information schema:

where lower(table_name) = 'codeat_code' ; 

"codeAT_code" is a table name and should be double quoted when used in a query (example: when you want to insert into it) but in this case, the name is just a value in a column in a system table so it should be in single quotes like any other string literal.

0

Not the answer you're looking for? Browse other questions tagged or ask your own question.