Меню

Relation does not exist postgresql ошибка

What are you doing?

edit2: Remember folks, when you change your env variables, you have to restart your server/pm2 instance =) This fixed it, although I would expect a more helpful error message when host, port etc. are undefined.

Hey guys,

I am switching my node/express app from mysql to postgresql. Everything was pretty seamless except I had to swap some data types. When I try to run the following command I get an error.

edit: Looks like something else is up. Sequelize throws the same error for all other queries, including relation "users" does not exist. I know this was marked as support, but mysql was working perfectly before changing to postgres, so I imagine it should also work now.

const [ serviceUser, created ] = await ServiceUserAccountModel.findOrCreate({ 
          where: { service_user_id: '123456' },
        });

relation "serviceUserAccounts" does not exist. or with users relation "users" does not exist

const userModel = Sequelize.define('user', {
    // has many ServiceUserAccounts

    id: { 
      type: DataTypes.INTEGER, 
      primaryKey: true, 
      autoIncrement: true 
    },

    email: {
      type: DataTypes.STRING,
      allowNull: false
    },

    age: {
      type: DataTypes.SMALLINT,
      allowNull: false
    },

    gender: {
      type: DataTypes.STRING,
      allowNull: false
    },

    first_name: {
      type: DataTypes.STRING,
      allowNull: true
    },

    last_name: {
      type: DataTypes.STRING,
      allowNull: true
    }
       
  });

    const serviceUserAccountsModel = Sequelize.define('serviceUserAccount', {
      // belongs to User
      
      id: { 
        type: DataTypes.INTEGER, 
        primaryKey: true, 
        autoIncrement: true 
      },

      display_name: {
        type: DataTypes.STRING,
        allowNull: true
      },

      email_address: {
        type: DataTypes.STRING,
        allowNull: true
      },
  
      service_id: { 
        type: DataTypes.SMALLINT,
        allowNull: true,
      },

      service_user_id: { 
        type: DataTypes.STRING,
        allowNull: true,
      },
  
      refresh_token: {
        type: DataTypes.STRING,
        allowNull: true
      },
  
      access_token: {
        type: DataTypes.STRING,
        allowNull: true
      },
  
      token_type: {
        type: DataTypes.STRING,
        allowNull: true
      },
  
      expiration_date: {
        type: DataTypes.INTEGER,
        allowNull: true
      },
  
      storage_limit: {
        type: DataTypes.INTEGER,
        allowNull: true
      },

      storage_usage: {
        type: DataTypes.INTEGER,
        allowNull: true
      },

      trashed_storage_usage: {
        type: DataTypes.INTEGER,
        allowNull: true
      },
      
    });

// Relations

module.exports = function( database ){
  const User = database.models.user.user;
  const ServiceUserAccounts = database.models.user.serviceUserAccounts;
  
  User.hasMany(ServiceUserAccounts);
  ServiceUserAccounts.belongsTo(User);
};

What do you expect to happen?

As it was working perfectly before with mysql dialect, I expect it to also work with Postgresql.

What is actually happening?

relation "serviceUserAccounts" does not exist. I’m able to run the query just fine in pgAdmin, so it must be something with sequelize. What am I missing?

Here’s the gist with the stacktrace
https://gist.github.com/Mk-Etlinger/569093387a0cb97699acfcba3994f59d

Any ideas? I checked my permissions and it came back public, $user.

also looked here but no luck:
https://stackoverflow.com/questions/28844617/sequelize-with-postgres-database-not-working-after-migration-from-mysql

https://stackoverflow.com/questions/946804/find-out-if-user-got-permission-to-select-update-a-table-function-in-pos

Dialect: postgres
Dialect version: XXX
Database version: 9.6.2
Sequelize version: ^4.38.1
Tested with latest release: Yes, 4.39.0

Note : Your issue may be ignored OR closed by maintainers if it’s not tested against latest version OR does not follow issue template.

Источник

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.

Источник

Содержание

  1. Debugging “relation does not exist” error in postgres
  2. PostgreSQL relation «mytable» does not exist #1044
  3. Comments
  4. sergioszy commented Dec 16, 2017
  5. Environment
  6. brettwooldridge commented Dec 16, 2017
  7. sergioszy commented Dec 16, 2017
  8. org.postgresql.util.PSQLException:ERROR: relation «contacts» does not exist
  9. Comments

Debugging “relation does not exist” error in postgres

So, i was getting this nasty error even when the table clearly existed in t11 database.

Even after lot of googling, debugging and trying Stackoverflow solutions, there was no resolution. Then i got a hunch, that i should check what database and schema were actually being used when done programmatically (even though dbname was clearly provided in connection string).

If you use database t11 by running c t11 and then run:

select * from information_schema.tables where table_schema NOT IN (‘pg_catalog’, ‘information_schema’)

It will tell you that userinfo5 does exist in t11 database. But what happens when we try to access it programmatically?

So, i ran above query in a golang function, the function which was earlier running query select * from userinfo5 where >

Output showed that database name which was actually being used was postgres and not t11 Why?

Because, my postgres user was configured to not use password. But my connection string had password= This was somehow confusing the DB driver and postgres database was being used and not t11 .

remove password= from connection string so that it looks like: “host=localhost port=5432 user=postgres dbname=t11 sslmode=disable”

  1. Alter user postgres so that it uses password: alter user postgres with password ‘pwd123’;
  2. Change connection string: “host=localhost port=5432 user=postgres password=pwd123 dbname=t11 sslmode=disable”

Источник

PostgreSQL relation «mytable» does not exist #1044

Environment

PreparedStatement prepared = connection.preparedStatement(
«select mytable.column1, mytable.column2 from mytable where mytable.column1 > 0» ); //OK

When execute resultSet = prepared.executeQuery();

throws this exception

org.postgresql.util.PSQLException: ERROR: relation mytable does not exist
Position: 176
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:117)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)

The text was updated successfully, but these errors were encountered:

This is not a HikariCP question, this is a PostgreSQL question.

PostgreSQL will not throw an error on prepare, even if the relation does not exist, because the driver will not actually prepare the statement until it has been executed several times. Creating a Statement via Statement stmt = connection.createStatement() , and then executing the same query via resultSet = stmt.executeQuery(«select . «) will fail with the same error.

You may be connecting to PostgreSQL server, but you are not connecting to the database which contains that table. Either specifiy the database, or check that the user has permission to see that table.

But, if I use directly the PGSimpleDatasource everthing is OK. Then the problem is in how HikariCP

Источник

org.postgresql.util.PSQLException:ERROR: relation «contacts» does not exist

Hello, I am pretty new to java and NetBeans. I am getting an error that appears when NetBeans tries to insert a record into a PostgreSQL database. I am using the files that are from a Sun Java tutorial.
————————————-
insert into contacts(id, first_name, last_name)

org.postgresql.util.PSQLException: ERROR: relation «contacts» does not exist
————————————-
I think PostgreSQL needs a SQL statement to formatted like the following: «schema».»tablename»
To include parenthesis around the schema name followed by a dot, followed by the table name. But in the insert statement that NetBeans is creating, just includes the the table name. I have tried to modify the code in different ways, but I can’t get it to format the SQL statement correctly.

I have included the entire statement below. Thanks again for any help.

/**
* Updates the selected contact or inserts a new one (if we are
* in the insert mode).
*
* @param firstName first name of the contact.
* @param lastName last name of the contact.
* @param title title of the contact.
* @param nickname nickname of the contact.
* @param displayFormat display format for the contact.
* @param mailFormat mail format for the contact.
* @param emails email addresses of the contact.
*/
public void updateContact(String firstName, String lastName, String title, String nickname,
int displayFormat, int mailFormat, Object[] emails) <
int selection = getContactSelection().getMinSelectionIndex();
Statement stmt = null;
try <
if (!insertMode) <
rowSet.absolute(selection+1);
>
Connection con = rowSet.getConnection();
stmt = con.createStatement();
String sql;
if (insertMode) <
sql = «insert into public.» + CONTACTS_TABLE + «(» + CONTACTS_KEY + «, » + CONTACTS_FIRST_NAME + «, »
+ CONTACTS_LAST_NAME + «, » + CONTACTS_TITLE + «, » + CONTACTS_NICKNAME + «, »
+ CONTACTS_DISPLAY_FORMAT + «, » + CONTACTS_MAIL_FORMAT + «, » + CONTACTS_EMAIL_ADDRESSES
+ «) values ((case when (select max(» + CONTACTS_KEY + «) from » + CONTACTS_TABLE + «)»
+ «IS NULL then 1 else (select max(» + CONTACTS_KEY + «) from » + CONTACTS_TABLE + «)+1 end), »
+ encodeSQL(firstName) + «, » + encodeSQL(lastName) + «, » + encodeSQL(title) + «, »
+ encodeSQL(nickname) + «, » + displayFormat + «, » + mailFormat + «, »
+ encodeSQL(encodeEmails(emails)) + «)»;
> else <
sql = «update public.» + CONTACTS_TABLE + » set «;
sql += CONTACTS_FIRST_NAME + ‘=’ + encodeSQL(firstName) + «, «;
sql += CONTACTS_LAST_NAME + ‘=’ + encodeSQL(lastName) + «, «;
sql += CONTACTS_TITLE + ‘=’ + encodeSQL(title) + «, «;
sql += CONTACTS_NICKNAME + ‘=’ + encodeSQL(nickname) + «, «;
sql += CONTACTS_DISPLAY_FORMAT + ‘=’ + displayFormat + «, «;
sql += CONTACTS_MAIL_FORMAT + ‘=’ + mailFormat + «, «;
sql += CONTACTS_EMAIL_ADDRESSES + ‘=’ + encodeSQL(encodeEmails(emails));
sql += » where » + CONTACTS_KEY + ‘=’ + rowSet.getObject(CONTACTS_KEY);
>
System.out.println(sql);
stmt.executeUpdate(sql);
rowSet.execute();
> catch (SQLException sqlex) <
sqlex.printStackTrace();
> finally <
setInsertMode(false);
if (stmt != null) <
try <
stmt.close();
> catch (SQLException sqlex) <
sqlex.printStackTrace();
>
>
>
>

What’s that «encodeSQL» method doing? You’re much better off with a PreparedStatement, IMO.

No, you don’t need the «public.». Connect to the database and use the table name just as it appears in the Postgres client. That’s what I do. It works fine.

That’s the worst way you can possible build up that SQL statement. Why create all those extra Strings? Better to use a StringBuffer and PreparedStatement.

No transactional logic that I can see. Wouldn’t you want the INSERT to rollback if an exception is caught?

The writeable row set must be a data member of this class. You’ve done nothing to synchronize this method, so it’s not thread safe. Don’t use it with more than one client.

Thanks for your reply.

Knowing that I don’t need the «public» schema keyword helps, but I think I still need the » » double quotes around the table name?

Below is the entire code. Does this look thread safe or like it has any transaction logic?

import java.beans.PropertyChangeSupport;
import java.beans.PropertyChangeListener;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import javax.swing.event.*;

/**
* Model of Contacts application.
*
* @author Jan Stola
*/
public class ContactsModel implements ListSelectionListener <
// Constants for database objects
private static final String CONTACTS_TABLE = «contacts»;
private static final String CONTACTS_KEY = «id»;
private static final String CONTACTS_ID = «id»;
private static final String CONTACTS_FIRST_NAME = «first_name»;
private static final String CONTACTS_LAST_NAME = «last_name»;
private static final String CONTACTS_TITLE = «title»;
private static final String CONTACTS_NICKNAME = «nickname»;
private static final String CONTACTS_DISPLAY_FORMAT = «display_format»;
private static final String CONTACTS_MAIL_FORMAT = «mail_format»;
private static final String CONTACTS_EMAIL_ADDRESSES = «email_addresses»;

// Constants for property names
public static final String PROP_REMOVAL_ENABLED = «removalEnabled»;
public static final String PROP_EDITING_ENABLED = «editingEnabled»;

// RowSet with contacts
private JDBCRowSet rowSet;
// Contacts selection model
private ListSelectionModel contactSelection;
// Insert mode (e.g. are we about to insert a new contact)
private boolean insertMode;

/**
* Getter for rowSet property.
*
* @return rowSet with contacts.
*/
public JDBCRowSet getRowSet() <
return rowSet;
>

/**
* Setter for rowSet property.
*
* @param rowSet rowSet with contacts.
*/
public void setRowSet(JDBCRowSet rowSet) <
this.rowSet = rowSet;
>

/**
* Getter for contactSelection property.
*
* @return contacts selection model.
*/
public ListSelectionModel getContactSelection() <
if (contactSelection == null) <
contactSelection = new DefaultListSelectionModel();
contactSelection.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
setContactSelection(contactSelection);
>
return contactSelection;
>

/**
* Setter for contactSelection property.
*
* @param contactSelection contacts selection model.
*/
public void setContactSelection(ListSelectionModel contactSelection) <
if (this.contactSelection != null) <
this.contactSelection.removeListSelectionListener(this);
>
this.contactSelection = contactSelection;
contactSelection.addListSelectionListener(this);
>

/**
* Setter for insertMode property.
*
* @param insertMode insert mode.
*/
void setInsertMode(boolean insertMode) <
this.insertMode = insertMode;
>

/**
* Returns ID of the selected contact.
*
* @return ID of the selected contact.
*/
public String getID() <
return insertMode ? null : stringValue(CONTACTS_ID);
>

/**
* Returns the first name of the selected contact.
*
* @return the first name of the selected contact.
*/
public String getFirstName() <
return insertMode ? «» : stringValue(CONTACTS_FIRST_NAME);
>

/**
* Returns the last name of the selected contact.
*
* @return the last name of the selected contact.
*/
public String getLastName() <
return insertMode ? «» : stringValue(CONTACTS_LAST_NAME);
>

/**
* Returns title of the selected contact.
*
* @return title of the selected contact.
*/
public String getTitle() <
return insertMode ? «» : stringValue(CONTACTS_TITLE);
>

/**
* Returns nickname of the selected contact.
*
* @return nickname of the selected contact.
*/
public String getNickname() <
return insertMode ? «» : stringValue(CONTACTS_NICKNAME);
>

/**
* Returns display format of the selected contact.
*
* @return display format of the selected contact.
*/
public int getDisplayFormat() <
return insertMode ? 0 : intValue(CONTACTS_DISPLAY_FORMAT);
>

/**
* Returns mail format of the selected contact.
*
* @return mail format of the selected contact.
*/
public int getMailFormat() <
return insertMode ? 0 : intValue(CONTACTS_MAIL_FORMAT);
>

/**
* Returns email addresses of the selected contact.
*
* @return email addresses of the selected contact.
*/
public Object[] getEmails() <
String encodedEmails = insertMode ? null : stringValue(CONTACTS_EMAIL_ADDRESSES);
return decodeEmails(encodedEmails);
>

/**
* Determines whether editing of the selected contact is enabled.
*
* @return true if the selected contact can be edited,
* returns false otherwise.
*/
public boolean isEditingEnabled() <
// Additional business logic can go here
return (getContactSelection().getMinSelectionIndex() != -1);
>

/**
* Determines whether removal of the selected contact is enabled.
*
* @return true if the selected contact can be removed,
* returns false otherwise.
*/
public boolean isRemovalEnabled() <
// Additional business logic can go here
return (getContactSelection().getMinSelectionIndex() != -1);
>

// Helper method that returns value of a selected contact’s attribute
private String stringValue(String columnName) <
String value = null;
try <
if ((rowSet != null) && selectContactInRowSet()) <
value = rowSet.getString(columnName);
>
> catch (SQLException sqlex) <
sqlex.printStackTrace();
>
return value;
>

// Helper method that returns value of a selected contact’s attribute
private int intValue(String columnName) <
int value = 0;
try <
if ((rowSet != null) && selectContactInRowSet()) <
value = rowSet.getInt(columnName);
>
> catch (SQLException sqlex) <
sqlex.printStackTrace();
>
return value;
>

// Helper method that synchronizes rowSet position with selected contact
private boolean selectContactInRowSet() <
int index = getContactSelection().getMinSelectionIndex();
if (index != -1) <
try <
rowSet.absolute(index+1);
> catch (SQLException sqlex) <
sqlex.printStackTrace();
>
>
return (index != -1);
>

// Helper method that decodes email addresses from the encoded string
private Object[] decodeEmails(String encodedEmails) <
if ((encodedEmails == null) || (encodedEmails.equals(«»))) <
return new String[0];
>
char sep = encodedEmails.charAt(0);
List emails = new LinkedList();
StringTokenizer st = new StringTokenizer(encodedEmails, String.valueOf(sep));
while (st.hasMoreTokens()) <
emails.add(st.nextToken());
>
return emails.toArray(new Object[emails.size()]);
>

// Helper method that encodes email addresses into one string
private String encodeEmails(Object[] emails) <
StringBuffer sb = new StringBuffer();
for (int i=0; i 0 · Share on Twitter Share on Facebook

Источник

Источник

Откуда ноги

Причина данной ошибки в том, что таблицы, либо их отдельные поля, описанные в конфигурации 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
  • Для анализа конфигурации использовалась обработка Структура хранения таблиц базы данных
Источник

0 0 голоса
Рейтинг статьи
Подписаться
Уведомить о
guest

0 комментариев
Старые
Новые Популярные
Межтекстовые Отзывы
Посмотреть все комментарии

А вот еще интересные материалы:

  • Яшка сломя голову остановился исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного где ошибка
  • Rekam f150 ошибка памяти
  • Request mespilock failed ошибка

    • ➤