0

I am using strapi and postgres. I created two collections in strapi called "solutions" and "references".

When I run \dt I get the following output:

 ...more tables
 public | references                                                 | table | postgres
 public | references_components                                      | table | postgres
 public | references_pages                                           | table | postgres
 public | references_pages_components                                | table | postgres
 public | solutions                                                  | table | postgres
 public | solutions_components                                       | table | postgres
 public | solutions_pages                                            | table | postgres
 public | solutions_pages_components

Now I want to run a SELECT on table "solutions", which works: SELECT * FROM solutions;

But when I run SELECT * FROM references; I get:

ERROR:  syntax error at or near "references"
LINE 1: SELECT * FROM references;

I already checked if there is a lock, but nothing. Running \z references gives the following output:

                                   Access privileges
 Schema |    Name    | Type  |     Access privileges     | Column privileges | Policies
--------+------------+-------+---------------------------+-------------------+----------
 public | references | table | postgres=arwdDxt/postgres |                   |
(1 row)

What could be wrong? Is there maybe an issue with the table being named "references"? This issue happens on my local machine as well on our staging server, so I do not assume that it is an issue with my local postgres setup.

I also checked priviliges with

SELECT table_catalog, table_schema, table_name, privilege_type                                                                                                                                                                   
 FROM   information_schema.table_privileges WHERE  grantee = 'postgres'

Which gives this output (scroll to the right):

 mytable     | public             | references                                                 | INSERT
 mytable     | public             | references                                                 | SELECT
 mytable     | public             | references                                                 | UPDATE
 mytable     | public             | references                                                 | DELETE
 mytable     | public             | references                                                 | TRUNCATE
 mytable     | public             | references                                                 | REFERENCES
 mytable     | public             | references                                                 | TRIGGER
Felix Hagspiel
  • 101
  • 2

1 Answers1

0

The issue is that the table is named "references", which Postgres apparently does not like. Calling the table "my_references" or "referencess" works.

template1=# CREATE TABLE references (id bigint NOT NULL);
ERROR:  syntax error at or near "references"
LINE 1: CREATE TABLE references (id bigint NOT NULL);

This works:

template1=# CREATE TABLE referencesss (id bigint NOT NULL);
CREATE TABLE
Felix Hagspiel
  • 101
  • 2
  • "references" is a reserved word, used in the creation of Foreign Keys. You should *never* use reserved words to name tables, columns or other objects in your databases. It /will/ come back to bite you. – Phill W. Mar 01 '21 at 11:25
  • Yes, thanks for the info. I will have to create an issue at strapi that their cms should throw an error when you try to create a collection name "references" (or they should just prefix it). – Felix Hagspiel Mar 01 '21 at 13:45