Error relation does not exist line 1 - Исправление ошибок и поиск оптимальных решений проблем

I have a postgresql db with a number of tables. If I query:

SELECT column_name
FROM information_schema.columns
WHERE table_name="my_table";

I will get a list of the columns returned properly.

However, when I query:

SELECT *
FROM "my_table";

I get the error:

(ProgrammingError) relation "my_table" does not exist
'SELECT *n    FROM "my_table"n' {}

Any thoughts on why I can get the columns, but can’t query the table? Goal is to be able to query the table.

asked Apr 20, 2016 at 19:38

patkilpatkil

1,8793 gold badges15 silver badges17 bronze badges

You have to include the schema if isnt a public one

SELECT *
FROM <schema>."my_table"

Or you can change your default schema

SHOW search_path;
SET search_path TO my_schema;

Check your table schema here

SELECT *
FROM information_schema.columns

For example if a table is on the default schema public both this will works ok

SELECT * FROM parroquias_region
SELECT * FROM public.parroquias_region

But sectors need specify the schema

SELECT * FROM map_update.sectores_point

answered Apr 20, 2016 at 19:44

You can try:

SELECT * 
FROM public."my_table"

Don’t forget double quotes near my_table.

4b0

21.4k30 gold badges95 silver badges139 bronze badges

answered Sep 3, 2019 at 2:13

I had to include double quotes with the table name.

db=> d
                           List of relations
 Schema |                     Name                      | Type  | Owner 
--------+-----------------------------------------------+-------+-------
 public | COMMONDATA_NWCG_AGENCIES                      | table | dan
 ...

db=> d COMMONDATA_NWCG_AGENCIES
Did not find any relation named "COMMONDATA_NWCG_AGENCIES".

???

Double quotes:

db=> d "COMMONDATA_NWCG_AGENCIES"
                         Table "public.COMMONDATA_NWCG_AGENCIES"
          Column          |            Type             | Collation | Nullable | Default 
--------------------------+-----------------------------+-----------+----------+---------
 ID                       | integer                     |           | not null | 
 ...

Lots and lots of double quotes:

db=> select ID from COMMONDATA_NWCG_AGENCIES limit 1;
ERROR:  relation "commondata_nwcg_agencies" does not exist
LINE 1: select ID from COMMONDATA_NWCG_AGENCIES limit 1;
                       ^
db=> select ID from "COMMONDATA_NWCG_AGENCIES" limit 1;
ERROR:  column "id" does not exist
LINE 1: select ID from "COMMONDATA_NWCG_AGENCIES" limit 1;
               ^
db=> select "ID" from "COMMONDATA_NWCG_AGENCIES" limit 1;
 ID 
----
  1
(1 row)

This is postgres 11. The CREATE TABLE statements from this dump had double quotes as well:

DROP TABLE IF EXISTS "COMMONDATA_NWCG_AGENCIES";

CREATE TABLE "COMMONDATA_NWCG_AGENCIES" (
...

answered Sep 26, 2019 at 21:57

dfrankowdfrankow

19.5k40 gold badges145 silver badges203 bronze badges

I hit this error and it turned out my connection string was pointing to another database, obviously the table didn’t exist there.

I spent a few hours on this and no one else has mentioned to double check your connection string.

answered Nov 13, 2020 at 2:29

Jeremy ThompsonJeremy Thompson

59.8k32 gold badges184 silver badges308 bronze badges

I had the same problem that occurred after I restored data from a postgres dumped db.

My dump file had the command below from where things started going south.

    SELECT pg_catalog.set_config('search_path', '', false);

Solutions:

Probably remove it or change that false to be true.
Create a private schema that will be used to access all the tables.

The command above simply deactivates all the publicly accessible schemas.

Check more on the documentation here: https://www.postgresql.org/docs/9.3/ecpg-connect.html

answered Sep 17, 2019 at 16:51

dmigwidmigwi

611 silver badge5 bronze badges

The error can be caused by access restrictions. Solution:

GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

answered Oct 1, 2020 at 0:47

MarcelMarcel

2,7102 gold badges25 silver badges43 bronze badges

I was using pgAdmin to create my tables and while I was not using reserved words, the generated table had a quote in the name and a couple of columns had quotes in them. Here is an example of the generated SQL.

CREATE TABLE public."Test"
(
    id serial NOT NULL,
    data text NOT NULL,
    updater character varying(50) NOT NULL,
    "updateDt" time with time zone NOT NULL,
    CONSTRAINT test_pk PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE public."Test"
    OWNER to svc_newnews_app;

All of these quotes were inserted at «random». I just needed to drop and re-create the table again without the quotes.

Tested on pgAdmin 4.26

answered Oct 9, 2020 at 14:05

ChewyChewy

6526 silver badges21 bronze badges

Please ensure that:

Your password is non-empty
In case it is empty, do not pass the password param in the connection string

This is one of the most common errors when starting out with the tutorial.

answered Mar 6, 2022 at 8:21

In my case, the dump file I restored had these commands.

CREATE SCHEMA employees;
SET search_path = employees, pg_catalog;

I’ve commented those and restored again. The issue got resolved

answered Oct 30, 2020 at 12:03

samsrisamsri

1,07412 silver badges23 bronze badges

Keep all your table names in lower case because when you rollback and then go to latest, it’s looking for lowercase apparently.

answered Oct 25, 2021 at 8:00

ErickErick

211 silver badge3 bronze badges

Lets say we have database name as students and schema name as studentinformation then to use all the table of this schema we need to set the path first which we can do in postgresql like:

client.connect()
.then(()=>console.log("connected succesfully"))
.then(()=>client.query("set search_path to students"))
.then(()=>client.query("show search_path"))
.then(()=>client.query("set search_path to studentinformation"))
.then(()=>client.query("show search_path"))
.then(results => console.table(results.rows)) //setting the search path

Toni

1,5254 gold badges15 silver badges23 bronze badges

answered Jul 1, 2021 at 17:36

I was using psql from PostgreSQL, and somehow I created the table in the «postgres=#» directory instead of first connecting to the database and creating it there.

So make sure that you connected to the database you want before creating tables

answered Feb 5 at 18:11

Источник

Posted on Dec 24, 2021

When you’re running Sequelize code to fetch or manipulate data from a PostgreSQL database, you might encounter an error saying relation <table name> does not exist.

For example, suppose you have a database named User in your PostgreSQL database as shown below:

cakeDB=# dt

          List of relations
 Schema | Name | Type  |    Owner
--------+------+-------+-------------
 public | User | table | nsebhastian
(1 row)

In the above output from psql, the cakeDB database has one table named User that you need to retrieve the data using Sequelize findAll() method.

Next, you create a new connection to the database using Sequelize and create a model for the User table:

const { Sequelize } = require("sequelize");

const sequelize = new Sequelize("cakeDB", "nsebhastian", "", {
  host: "localhost",
  dialect: "postgres",
});

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
});

After that, you write the code to query the User table as follows:

const users = await User.findAll();
console.log(users);

Although the code above is valid, Node will throw an error as follows:

Error
    at Query.run
    ...
  name: 'SequelizeDatabaseError',
  parent: error: relation "Users" does not exist

In PostgreSQL, a relation does not exist error happens when you reference a table name that can’t be found in the database you currently connect to.

In the case above, the error happens because Sequelize is trying to find Users table with an s, while the existing table is named User without an s.

But why does Sequelize refer to Users while we clearly define User in our model above? You can see it in the code below:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
});

This is because Sequelize automatically pluralizes the model name User as Users to find the table name in your database (reference here)

To prevent Sequelize from pluralizing the table name for the model, you can add the freezeTableName option and set it to true to the model as shown below:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
},
{
  freezeTableName: true,
});

The freezeTableName option will cause Sequelize to infer the table name as equal to the model name without any modification.

Alternatively, you can also add the tableName option to tell Sequelize directly the table name for the model:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
},
{
  tableName: "User",
});

Once you add one of the two options above, this error should be resolved.

Please note that the model and table names in Sequelize and PostgreSQL are also case-sensitive, so if you’re table name is User, you will trigger the error when you refer to it as user from Sequelize:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
},
{
  tableName: "user", // relation "user" does not exist
});

The relation does not exist error in Sequelize always happens when you refer to a PostgreSQL database table that doesn’t exist.

When you encounter this error, the first thing to check is to make sure that the Sequelize code points to the right table name.

This error can also occur in your migration code because you might have migration files that create a relationship between two tables.

Always make sure that you’re referencing the right table, and that you’re using the right letter casing.

Источник

acidflash: Сообщения: 102; Зарегистрирован: 2015.09.03, 19:37

Yii2 + postgresql, проблемы (relation «…» does not exist)

Добрый день, пытаюсь прикрутить PostgreSQL к Yii2, при заходе на страницу возникает ошибка

Database Exception – yiidbException

SQLSTATE[42P01]: Undefined table: 7 ERROR: relation «tbl_products» does not exist
LINE 1: SELECT COUNT(*) FROM «tbl_products» INNER JOIN «tbl_products…
^
The SQL being executed was: SELECT COUNT(*) FROM «tbl_products» INNER JOIN «tbl_products_to_category» ON tbl_products.id = tbl_products_to_category.id WHERE («status»=1) AND («category»=8)
Error Info: Array
(
[0] => 42P01
[1] => 7
[2] => ERROR: relation «tbl_products» does not exist
LINE 1: SELECT COUNT(*) FROM «tbl_products» INNER JOIN «tbl_products…
^
)
↵
Caused by:PDOException

SQLSTATE[42P01]: Undefined table: 7 ERROR: relation «tbl_products» does not exist
LINE 1: SELECT COUNT(*) FROM «tbl_products» INNER JOIN «tbl_products…
^

Настройки конфига:

Код: Выделить всё

            'dsn' => 'pgsql:host=localhost;port=5432;dbname=db1',
            'username' => '..',
            'password' => '..',
            'charset' => 'utf8',
            'schemaMap' => [
                'pgsql'=> [
                    'class'=>'yiidbpgsqlSchema',
                    'defaultSchema' => 'db1' //specify your schema here
                ]
            ],

В чем может быть проблема?

ElisDN: Сообщения: 5825; Зарегистрирован: 2012.10.07, 10:24; Контактная информация:

acidflash: Сообщения: 102; Зарегистрирован: 2015.09.03, 19:37

Re: Yii2 + postgresql, проблемы (relation «…» does not exist)

Сообщение

acidflash » 2016.05.01, 17:12

Кажется дело в schema
create table «TEST» («Col1» bigint);
вызвает ошибку SQLSTATE[3F000]: Invalid schema name: 7 ERROR: no schema has been selected to create in at character 14
create table «y2oc».»TEST» («Col1» bigint);
Таки создает таблицу

Не могу понять, я ведь выбрал базу данных, выбрал схему, что еще нужно прописать и где чтобы запросы к бд отрабатывались?

‘pgsql’=> [
‘class’=>’yiidbpgsqlSchema’,
‘defaultSchema’ => ‘y2oc’ //specify your schema here
]

Источник

Откуда ноги

Причина данной ошибки в том, что таблицы, либо их отдельные поля, описанные в конфигурации 1с, не соответствуют таблицам в базе данных SQL. Например в новой, обновленной конфигурации 1с существует регистр, а среди таблиц SQL его нет.

Что делать?

Нужно привести таблицы(поля) SQL в соответствие с описанием конфигурации.

Т.е. все таблицы(поля), описанные в конфигурации, должны присутствовать в SQL.

!!! Внимание Если у вас появляется ошибка «schemastorage does not exist» попробуйте сначала провести ТИИ (тестирование и исправление информационной базы), а именно только «реструктуризация БД«. В большинстве случаев она помогает, возможно поможет и при отсутствии других таблиц.

Лечение

Необходимо, воспользовавшись утилитами, сравнить таблицы SQL с 1с. Описание ошибки сразу выводит на ту таблицу, которую нужно искать.

Далее нужно добавить(исправить) таблицы SQL с тем, чтобы они соответствовали конфигурации 1с.

В приложенном файле показаны примеры исправления.

Размышления

1.Поиск в интернете показал, что наиболее страдают этой ошибкой базы, размещенные на Postgre.

Здесь описано, что эта проблема существует и решена в версиях начиная с 8.3.

Сталкивался трижды с этой проблемой. Во всех случаях это был Postgre 8.4.

2.Есть мнение, что одним из поводов для появления ошибки, является динамическое обновление конфигурации.

3. Данная ошибка не возникает, если в новой конфигурации, относительно старой, не изменяли реквизиты, таблицы. Т.е. при изменении только программного кода, форм конфигурации, такая ошибка не должна проявляться, т.к. не изменяется структура таблиц SQL.

На дорожку

При исправлении ошибки, сами работы с таблицами SQL, хотя и не являются сложными, но все же требуют определенной подготовки.

Поэтому — пару рекомендаций, чтобы не пришлось решать описанную проблему:

— Не хочу обижать Postgre, но если база данных небольшая, может использовать MSSQL? Бесплатная версия Express позволяет обслуживать базу размером до 10Гб.

— По возможности избегайте делать динамическое обновление. Хотя фирма 1с периодически сообщает, что ей удалось «победить» эту проблему, но «Пуганая ворона…».

Ну и конечно, прежде чем начать работать с базой данных «по живому», сделайте ее бэкап.

Благодарности:

За статью спасибо aspirator23
Для анализа конфигурации использовалась обработка Структура хранения таблиц базы данных

Источник