Relation 'table-name' does not exist issue


#1

Hello crdb friends,

I encountered a strange basic issue with my crdb and am wondering if anyone has encountered a similar or has insight as to what’s going on. Here’s the situation: I created a table in crdb; can see the table in the result list with show tables command; can see the table in the result list with select * from information_schema.tables where...; yet a SQL SELECT statement select * from table yielded an error.

Any idea what I’m doing wrong?
Thanks in advance.

The following illustrates this issue:

# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.
#
# Server version: CockroachDB CCL v2.0.6 (x86_64-unknown-linux-gnu, built 2018/10/01 13:59:40, go1.10) (same version as client)
# Cluster ID: 212d25c7-fd68-4ce7-b947-94e2753cb369
#
# Enter \? for a brief introduction.
#
warning: no current database set. Use SET database = <dbname> to change, CREATE DATABASE to make a new database.
root@:26257/> use test_crdb;
SET

Time: 485.617µs

root@:26257/test_crdb> show tables;
+-----------------+
|      Table      |
+-----------------+
| ProcessLogs     |
| knex_migrations |
+-----------------+
(2 rows)

Time: 1.125883ms

root@:26257/test_crdb> select * from ProcessLogs;
pq: relation "processlogs" does not exist
root@:26257/test_crdb> select * from information_schema.tables where table_name = 'ProcessLogs';
+---------------+--------------+-------------+------------+--------------------+---------+
| table_catalog | table_schema | table_name  | table_type | is_insertable_into | version |
+---------------+--------------+-------------+------------+--------------------+---------+
| test_crdb     | public       | ProcessLogs | BASE TABLE | YES                |       1 |
+---------------+--------------+-------------+------------+--------------------+---------+
(1 row)

Time: 1.890136ms

root@:26257/test_crdb> select * from processlogs;
pq: relation "processlogs" does not exist
root@:26257/test_crdb> select * from knex_migrations;
+--------------------+----------------------------+-------+-------------------------------+
|         id         |            name            | batch |        migration_time         |
+--------------------+----------------------------+-------+-------------------------------+
| 393817635494658049 | 20181019013336_baseline.js |     1 | 2018-10-23 00:20:04.579+00:00 |
+--------------------+----------------------------+-------+-------------------------------+
(1 row)

Time: 13.578564ms

root@:26257/> select table_name, column_name, data_type from information_schema.columns where table_name = 'ProcessLogs';
+-------------+----------------+--------------------------+
| table_name  |  column_name   |        data_type         |
+-------------+----------------+--------------------------+
| ProcessLogs | id             | INT                      |
| ProcessLogs | message        | STRING                   |
| ProcessLogs | iso_timestamp  | TIMESTAMP WITH TIME ZONE |
| ProcessLogs | unix_timestamp | INTEGER                  |
+-------------+----------------+--------------------------+

root@:26257/test_crdb>

NodeJs modules in context:

  • knex
  • pg
  • knex-migrate

Edit: added selection from information_schema.columns


(Ben Darnell) #2

The rules for case-sensitivity of table names are the same as in PostgreSQL, which are sometimes confusing. An unquoted table name is folded to lowercase, while a quoted table name is preserved as-is. This means that if a table with a mixed-case name is created with double quotes, quotes must always be used to access it:

SELECT * FROM "ProcessLogs";

It looks like knex used quotes when creating this table, so you must do the same when accessing it.

In general, you must do one of three things:

  • Only use lower-case table (and column) names
  • Always quote your table and column names
  • Never quote your table and column names

It’s only safe to mix quoted and unquoted forms if your table names are lower case.


#3

Aah… I hadn’t paid close attention to the pull down menu in knexjs doc-site.
Much thanks for your keen observation!

It did show a generated creation script with quotes: https://knexjs.org/#Schema-withSchema

knex.schema.withSchema('public').createTable('users', function (table) {
  table.increments();
})

Outputs:

create table "public"."users" ("id" serial primary key)