Sql error 42p01 ошибка отношение не существует - Решение и исправление самых разных ошибок на TopOshibok.ru

I’m having this strange problem using PostgreSQL 9.3 with tables that are created using qoutes. For instance, if I create a table using qoutes:

create table "TEST" ("Col1" bigint);

the table is properly created and I can see that the quotes are preserved when view it in the SQL pane of pgAdminIII. But when I query the DB to find the list of all available tables (using the below query), I see that the result does not contain quotes around the table name.

select table_schema, table_name from information_schema.tables where not table_schema='pg_catalog' and not table_schema='information_schema';

Since the table was created with quotes, I can’t use the table name returned from the above query directly since it is unquoted and throws the error in posted in the title.

I could try surrounding the table names with quotes in all queries but I’m not sure if it’ll work all the time. I’m looking for a way to get the list of table names that are quoted with quotes in the result.

I’m having the same issue with column names as well but I’m hoping that if I can find a solution to the table names issue, a similar solution will work for column names as well.

asked Oct 29, 2014 at 13:08

you have two choices:
— no quotes: then everything will automatically be lowercase and non-case-sensitive
— with quotes: from now on everything is case sensitive.

i would highly recommend to NOT use quotes and make PostgreSQL behave non case sensitive. it makes life so much easier. once you get into quoting you got to use it EVERYWHERE as PostgreSQL will start to be very precise.

some example:

   TEST = test       <-- non case sensitive
   "Test" <> Test    <-- first is precise, second one is turned to lower case
   "Test" = "Test"   <-- will work
   "test" = TEST     <-- should work; but you are just lucky.

really try to avoid this kind of trickery at any cost. stay with 7 bit ascii for object names.

answered Oct 29, 2014 at 13:18

While using npg package as your data store ORM you are expecting the ORM framework (Entity Framework in our case) to generate the sql statement you might face a PostgreSQL exception the relation ‘Table Name’ does not exist

Either the table is not created or the generated SQL statement is missing something. Try to debug using visual studio you will see that the schema name is not leading the table name

SELECT "ID", "Name", "CreatedBy", "CreatedDate" 
FROM "TestTable";

while PostgreSQL is expecting the schema name. Resolution is in the DBContext class override the OnModelCreating method and add modelBuilder.HasDefaultSchema("SchemaName"); and execute the base constructor which should look like the following

protected override void OnModelCreating(ModelBuilder modelBuilder)   {             
  modelBuilder.HasDefaultSchema("PartyDataManager");                  
  base.OnModelCreating(modelBuilder);         
}

answered Feb 16, 2019 at 18:36

in my case. table in database has to be lowercase…change name dataTable do datatable help

answered Feb 23, 2021 at 9:29

Marcel NMarcel N

811 silver badge2 bronze badges

Keep the table name to lower case and it will solve the issue.

answered Mar 30, 2022 at 7:58

A string function used to suitably quote identifiers in an SQL statement string is quote_ident(), which references a good example (used in conjunction with related quote_literal()).

To use your example, and mix in other results:

select
   quote_ident(table_schema) as table_schema,
   quote_ident(table_name) as table_name
...

 table_schema |    table_name
--------------+------------------
 ...
 public       | good_name
 public       | "table"
 public       | some_table
 public       | "something else"
 public       | "Tom's work"
 public       | "TEST"
 ...

answered Oct 30, 2014 at 22:14

Mike TMike T

41.2k18 gold badges152 silver badges203 bronze badges

Источник

PostgreSQL error 42P01 actually makes users dumbfounded, especially the newbies.

Usually, this error occurs due to an undefined table in newly created databases.

That’s why at Bobcares, we often get requests to fix PostgreSQL errors, as a part of our Server Management Services.

Today, let’s have a look into the PostgreSQL error 42P01 and see how our Support Engineers fix it.

What is PostgreSQL error 42P01?

PostgreSQL has a well-defined error code description. This helps in identifying the reason for the error.

Today, let’s discuss in detail about PostgreSQL error 42P01. The typical error code in PostgreSQL appears as:

ERROR: relation "[Table name]" does not exist

SQL state:42P01

Here the 42P01 denotes an undefined table.

So, the code description clearly specifies the basic reason for the error.

But what does an undefined table means?

Let’s discuss it in detail.

Causes and fixes for the PostgreSQL error 42P01

Customer query on undefined tables of a database often shows up the 42P01 error.

Now let’s see a few situations when our customers get the 42P01 error. We will also see how our Support Engineers fix this error.

1. Improper database setup

Newbies to Postgres often make mistakes while creating a new database. Mostly, this improper setup ends up in a 42P01 error.

In such situations, our Support Team guides them for easy database setup.

Firstly, we create a new database. Next, we create a new schema and role. We give proper privileges to tables.

Postgres also allows users to ALTER DEFAULT PRIVILEGES.

2. Unquoted identifiers

Some customers create tables with mixed-case letters.

Usually, the unquoted identifiers are folded into lowercase. So, when the customer queries the table name with the mixed case it shows 42P01 error.

The happens as the PostgreSQL has saved the table name in lower case.

To resolve this error, our Support Engineers give mixed case table name in quotes. Also, we highly recommend to NOT use quotes in database names. Thus it would make PostgreSQL behave non-case sensitive.

3. Database query on a non-public schema

Similarly, the PostgreSQL 42P01 error occurs when a user queries a non-public schema.

Usually, this error occurs if the user is unaware of the proper Postgres database query.

For instance, the customer query on table name ‘pgtable‘ was:

SELECT * FROM  pgtable

This query is totally correct in case of a public schema. But, for a non-public schema ‘xx’ the query must be:

SELECT * FROM  "xx"."pgtable"

Hence, our Support Engineers ensure that the query uses the correct schema name.

[Still having trouble in fixing PostgreSQL errors? – We’ll fix it for you.]

Conclusion

In short, PostgreSQL error 42P01 denotes the database query is on an undefined table. This error occurs due to improper database setup, unidentified table name, and so on. Today, we saw how our Support Engineers fix the undefined table error in Postgres.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

What you had originally was a correct syntax — for tables, not for schemas. As you did not have a table (dubbed ‘relation’ in the error message), it threw the not-found error.

I see you’ve already noticed this — I believe there is no better way of learning than to fix our own mistakes

But there is something more. What you are doing above is too much on one hand, and not enough on the other.

Running the script, you

create a schema
create a role
grant SELECT on all tables in the schema created in (1.) to this new role_
and, finally, grant all privileges (CREATE and USAGE) on the new schema to the new role

The problem lies within point (3.) You granted privileges on tables in replays — but there are no tables in there! There might be some in the future, but at this point the schema is completely empty. This way, the GRANT in (3.) does nothing — this way you are doing too much.

But what about the future tables?

There is a command for covering them: ALTER DEFAULT PRIVILEGES. It applies not only to tables, but:

Currently [as of 9.4], only the privileges for tables (including views and foreign tables), sequences, functions, and types (including domains) can be altered.

There is one important limitation, too:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

This means that a table created by alice, who is neither you nor a role than you are a member of (can be checked, for example, by using \du in psql), will not take the prescribed access rights. The optional FOR ROLE clause is used for specifying the ‘table creator’ role you are a member of. In many cases, this implies it is a good idea to create all database objects using the same role — like mydatabase_owner.

A small example to show this at work:

CREATE ROLE test_owner; -- cannot log in
CREATE SCHEMA replays AUTHORIZATION test_owner;
GRANT ALL ON SCHEMA replays TO test_owner;

SET ROLE TO test_owner; -- here we change the context, 
                        -- so that the next statement is issued as the owner role

ALTER DEFAULT PRIVILEGES IN SCHEMA replays GRANT SELECT ON TABLES TO alice;

CREATE TABLE replays.replayer (r_id serial PRIMARY KEY);

RESET ROLE; -- changing the context back to the original role

CREATE TABLE replays.replay_event (re_id serial PRIMARY KEY);

-- and now compare the two

\dp replays.replayer
                                   Access privileges
 Schema  │   Name   │ Type  │       Access privileges       │ Column access privileges 
─────────┼──────────┼───────┼───────────────────────────────┼──────────────────────────
 replays │ replayer │ table │ alice=r/test_owner           ↵│ 
         │          │       │ test_owner=arwdDxt/test_owner │ 

\dp replays.replay_event
                               Access privileges
 Schema  │     Name     │ Type  │ Access privileges │ Column access privileges 
─────────┼──────────────┼───────┼───────────────────┼──────────────────────────
 replays │ replay_event │ table │                   │

As you can see, alice has no explicit rights on the latter table. (In this case, she can still SELECT from the table, being a member of the public pseudorole, but I didn’t want to clutter the example by revoking the rights from public.)

Источник

PostgreSQL error 42P01 actually makes users dumbfounded, especially the newbies.

Usually, this error occurs due to an undefined table in newly created databases.

That’s why at Bobcares, we often get requests to fix PostgreSQL errors, as a part of our Server Management Services.

Today, let’s have a look into the PostgreSQL error 42P01 and see how our Support Engineers fix it.

What is PostgreSQL error 42P01?

PostgreSQL has a well-defined error code description. This helps in identifying the reason for the error.

Today, let’s discuss in detail about PostgreSQL error 42P01. The typical error code in PostgreSQL appears as:

ERROR: relation "[Table name]" does not exist

SQL state:42P01

Here the 42P01 denotes an undefined table.

So, the code description clearly specifies the basic reason for the error.

But what does an undefined table means?

Let’s discuss it in detail.

Causes and fixes for the PostgreSQL error 42P01

Customer query on undefined tables of a database often shows up the 42P01 error.

Now let’s see a few situations when our customers get the 42P01 error. We will also see how our Support Engineers fix this error.

1. Improper database setup

Newbies to Postgres often make mistakes while creating a new database. Mostly, this improper setup ends up in a 42P01 error.

In such situations, our Support Team guides them for easy database setup.

Firstly, we create a new database. Next, we create a new schema and role. We give proper privileges to tables.

Postgres also allows users to ALTER DEFAULT PRIVILEGES.

2. Unquoted identifiers

Some customers create tables with mixed-case letters.

Usually, the unquoted identifiers are folded into lowercase. So, when the customer queries the table name with the mixed case it shows 42P01 error.

The happens as the PostgreSQL has saved the table name in lower case.

3. Database query on a non-public schema

Similarly, the PostgreSQL 42P01 error occurs when a user queries a non-public schema.

Usually, this error occurs if the user is unaware of the proper Postgres database query.

For instance, the customer query on table name ‘pgtable‘ was:

SELECT * FROM  pgtable

This query is totally correct in case of a public schema. But, for a non-public schema ‘xx’ the query must be:

SELECT * FROM  "xx"."pgtable"

Hence, our Support Engineers ensure that the query uses the correct schema name.

[Still having trouble in fixing PostgreSQL errors? – We’ll fix it for you.]

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

What is causing go-pg to always attempt finding the pluralized name of my table?

PostgreSQL Table:

public.employeesalary (
    employeeid varchar(50),
    badgeid varchar(50),
    salary bigint
)

GO Model:

type EmployeeSalary struct {
        tableName struct{} `sql:",employeesalary"`
        EmployeeId string `sql:",pk"`
        BadgeId string
        Salary int
}

Query:

var salary int
db.Model(&EmployeeSalary{}).Column("salary").Where("badgeid = ?", emp.BadgeId[0]).Limit(1).Select(&salary)

Error:
ERROR #42P01 relation "employee_salaries" does not exist

What have I missed?

Remove From My Forums

Question

public bool girisKontrol(string user, string pass)
        {string sunucu, port, kullaniciadi, sifre, veritabani;
            sunucu = "localhost";
            port = "5432";
            kullaniciadi = "postgres";
            sifre = "tellioglu";
            veritabani = "postgres";
            string baglantimetni = string.Format("Server ={0};Port ={1};User ID = {2};Password = {3};Database={4};", sunucu, port, kullaniciadi, sifre, veritabani);
            var baglanti = new NpgsqlConnection();
            baglanti.ConnectionString = baglantimetni;
            var cmd = new NpgsqlCommand();
            cmd.Connection = baglanti;
            cmd.CommandText = "select * from kullanicigiris where Kullaniciadi = @Kullanici and sifre = @sifre";//kullanicigiris tablonun adi , Kullaniciadi sütünun adı,sifre sütunun adi
            cmd.Parameters.AddWithValue("@Kullanici", user);
            cmd.Parameters.AddWithValue("@sifre", pass);
            cmd.Connection.Open();
            var reader = cmd.ExecuteReader();
            var sonuc = reader.HasRows;
            reader.Close();
            reader.Dispose();
            cmd.Connection.Close();
            cmd.Connection.Dispose();
            cmd.Dispose();
            return sonuc;

        }

i am using postgreSQL database . executereader(); giving ‘ERROR: 42P01: relation does not exist’ problem. is sql line wrong i dont know please help me

Answers

From the code sinppet, I don’t think it’s the SQL line error. In C#, we should use the SQL parameters like yours.

But for postgreSQL, I would suggest you to try the following code to see if it works.
```
cmd.CommandText = "select * from kullanicigiris where Kullaniciadi = :Kullanici and sifre = :sifre"     
       cmd.Parameters.AddWithValue(":Kullanici", user);
            cmd.Parameters.AddWithValue(":sifre", pass);
```
And there is a category for postgreSQL support query for your reference:

http://www.postgresql.org/support/

Hope it hleps.

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us
- Marked as answer by
  
  Wednesday, May 2, 2012 2:51 AM

8 / 5 / 3

Регистрация: 13.02.2013

Сообщений: 294

07.11.2017, 19:08

[ТС]

Доброго времени суток, при обращении к таблице в БД выскакивает исключение со след. «{«ОШИБКА: 42P01: отношение «dbo.name_auto» не существует»}». Дело в том, что таблица существует, но по какой-то причине найти не может, нашел след. информацию: в постгрес синтаксис иной от MS SQL и он не поддерживает формат dbo.name_auto, необходимо удалить префиксы имени владельца ( dbo в вашем случае ). Как это сделать не знаю, начал еще гуглить и опять же нашел, но никакой конкретики Wiki. Прошу помощи

11441 / 7768 / 1190 Регистрация: 21.01.2016 Сообщений: 29,135
08.11.2017, 07:34	2
Kurtis, в PostgeSQL имена идентификаторов чувствительны к регистру. Для него `name_auto` и `Name_Auto` — два разных идентификатора. Причём интересно, что PostgeSQL явно переводит в нижний регистр имена идентификаторов из запросов. Поэтому, если вы создали таблицу `Name_Auto` и будете искать её по имени `Name_Auto`, то не найдёте, ибо PostgeSQL переведёт её имя в `name_auto`, что уже отличается. Либо соблюдайте регистр ВЕЗДЕ и ВСЮДУ, либо заключайте имя в символы апострофов (`), тогда имя будет восприниматься буквально. 1

Kurtis

8 / 5 / 3

Регистрация: 13.02.2013

Сообщений: 294

08.11.2017, 21:14

[ТС]

Usaga, Добрый вечер, Name_auto — идентификатор в context, в model. отрывок из кода:

    public class AutoContext : DbContext
    {
         public DbSet<automobile> Automobiles { get; set; }
        //public IEnumerable Automobiles { get; set; }
        public DbSet<class_auto> Class_auto { get; set; }
        public DbSet<type_auto> Type_auto { get; set; }
        [B]public DbSet<name_auto> Name_auto { get; set; }[/B]
        //public SelectList Name_auto { get; set; }
        public DbSet<color> Colors { get; set; }
        public DbSet<copmplectation> Complectations { get; set; }
    }

В БД же таблица создана с нижнем регистром. (см. вложение)

Миниатюры

Usaga

11441 / 7768 / 1190

Регистрация: 21.01.2016

Сообщений: 29,135

09.11.2017, 06:54

Kurtis, вот EF и ищет таблицу «Name_auto». Укажите ему явно, в какую таблицу лукаться:

    public class AutoContext : DbContext
    {
        public DbSet<name_auto> Name_auto { get; set; }
 
        protected override void OnModelCreating(DbModelBuilder builder)
        {
            builder.Entity<name_auto>()
                .ToTable("name_auto");
        }
    }

8 / 5 / 3

Регистрация: 13.02.2013

Сообщений: 294

09.11.2017, 20:18

[ТС]

Usaga, Если он ищет «Name_auto», почему пишет ошибку связанную с «name_auto». Попробовал сделать как Вы показали, не сработало…Можно ли искать таблицу не «dbo.name_auto», а просто «name_auto», т.к. первый, как я понимаю — префикс для MS SQL. И подумать не мог о том, что такая проблема с подключением БД будет…
Эта ошибка может быть связана с тем, что нет подключения к БД??

11441 / 7768 / 1190

Регистрация: 21.01.2016

Сообщений: 29,135

10.11.2017, 04:40

Сообщение было отмечено Kurtis как решение

Решение

Kurtis, если бы были проблемы с подклёчением, то вы соответствующую ошибку бы и получили. Убрать префикс схемы нельзя, так как таблица всегда принадлежит какой-то схеме. На вашем скриншоте фигурирует схема «public». Попробуйте указать её вторым параметром в методе ToTable.

2719 / 2029 / 375 Регистрация: 22.07.2011 Сообщений: 7,687
10.11.2017, 14:31	7
А почему проблема только с Name_Auto , там же есть и другие таблицы с аналогичным именованием , и в контексте данное свойство обрабатывается далеко не первым. 1.Проверьте , все ли символы в одной кодировке (может там русская буковка затесалась) 2.Посмотрите под профайлером , какие запросы формирует EF к БД. 1

Kurtis

8 / 5 / 3

Регистрация: 13.02.2013

Сообщений: 294

11.11.2017, 17:21

[ТС]

Usaga, sau, таким способом решил свою проблему. Всем спасибо!

        protected override void OnModelCreating(DbModelBuilder builder)
        {
            builder.HasDefaultSchema("public");
        }

942 / 617 / 153 Регистрация: 09.09.2011 Сообщений: 1,905 Записей в блоге: 2
12.05.2021, 13:52	9
Тема так то не совсем чтобы про MVC, уж скорее про базы данных. Сообщение от Kurtis Доброго времени суток, при обращении к таблице в БД выскакивает исключение со след. «{«ОШИБКА: 42P01: отношение «dbo.name_auto» не существует»}». Дело в том, что таблица существует, но по какой-то причине найти не может Ещё один вариант: — PgAdmin — свойства базы данных — Parameters — добавить параметр «search_path» со списком префиксов схем в которых будет искаться по умолчанию (в вашем случае просто написать «dbo») Документашка: PostgreSQL: Documentation: 9.6: Schemas 0

Sql error 42p01 error relation

Я новичок в создании баз данных, и эта ошибка меня ошеломила, так как я новичок в администрировании БД (в основном я выполняю запросы типа отчетов). Я создал новую базу данных через графический интерфейс pgAdmin3, и я пытаюсь создать объекты БД там, используя SQL, но получаю:

ERROR: relation “replays” does not exist

SQL state: 42P01

Я просмотрел руководство, но не нашел ничего очень полезного, хотя я подозреваю, что это может быть как-то связано с search_path. Вот скриншот. Есть идеи, что я делаю не так, пожалуйста?

Ответ

Я поговорил с кем-то, кто помог мне найти ответ. Правильный синтаксис, для любого в будущем. В документации упоминается об этом, хотя это может быть легко пропустить.

START TRANSACTION;

DROP SCHEMA IF EXISTS replays CASCADE;

CREATE SCHEMA replays;

CREATE ROLE admins WITH

PASSWORD ‘changeme’;

GRANT SELECT ON ALL TABLES IN SCHEMA replays TO PUBLIC;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA replays TO admins;

COMMIT;

PostgreSQL ERROR: 42P01: relation “[Table]” does not exist

У меня возникла эта странная проблема с использованием PostgreSQL 9.3 с таблицами, созданными с использованием qoutes. Например, если я создаю таблицу с использованием qoutes:

create table “TEST” (“Col1” bigint);

таблица создана правильно, и я вижу, что кавычки сохраняются при просмотре ее на панели SQL в pgAdminIII. Но когда я запрашиваю базу данных, чтобы найти список всех доступных таблиц (используя приведенный ниже запрос), я вижу, что результат не содержит кавычек вокруг имени таблицы.

select table_schema, table_name from information_schema.tables where not table_schema=’pg_catalog’ and not table_schema=’information_schema’;

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

Я мог бы попробовать заключить имена таблиц в кавычки во всех запросах, но я не уверен, что это будет работать постоянно. Я ищу способ получить список имен таблиц, которые заключены в кавычки в результате.

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

Ответ

у вас есть два варианта: – без кавычек: тогда все автоматически будет в нижнем регистре и без учета регистра – с кавычками: с этого момента все чувствительно к регистру.

я бы настоятельно рекомендовал НЕ использовать кавычки и заставить PostgreSQL вести себя без учета регистра. это делает жизнь намного проще. как только вы начнете цитировать, вы должны использовать его ВЕЗДЕ, поскольку PostgreSQL начнет быть очень точным.

некоторый пример:

TEST = test <– non case sensitive

“Test” <> Test <– first is precise, second one is turned to lower case

“Test” = “Test” <– will work

“test” = TEST <– should work; but you are just lucky.

действительно старайтесь избегать такого рода уловок любой ценой. для имен объектов используйте 7-битный ascii.

При использовании пакета npg в качестве хранилища данных ORM вы ожидаете, что платформа ORM (в нашем случае Entity Framework) сгенерирует инструкцию sql, вы можете столкнуться с исключением PostgreSQL, отношение “Имя таблицы” не существует

Либо таблица не создана, либо в сгенерированном операторе SQL чего-то не хватает. Попробуйте выполнить отладку с помощью Visual Studio, вы увидите, что имя схемы не является ведущим именем таблицы

SELECT “ID”, “Name”, “CreatedBy”, “CreatedDate”

FROM “TestTable”;

в то время как PostgreSQL ожидает имя схемы. Разрешение находится в классе DbContext переопределите метод OnModelCreating и добавьте modelBuilder.HasDefaultSchema(“SchemaName”); и запустите базовый конструктор, который должен выглядеть следующим образом

protected override void OnModelCreating(ModelBuilder modelBuilder) {

modelBuilder.HasDefaultSchema(“PartyDataManager”);

base.OnModelCreating(modelBuilder);

}

Источник

I am having some problems trying to work with PostgreSQL and Hibernate, more specifically, the issue mentioned in the title. I’ve been searching the net for a few hours now but none of the found solutions worked for me.

I am using Eclipse Java EE IDE for Web Developers. Build id: 20090920-1017 with HibernateTools, Hibernate 3, PostgreSQL 8.4.3 on Ubuntu 9.10.

Here are the relevant files:

Message.class

package hello;

        public class Message {
         private Long id;
         private String text;

         public Message() {
         }

         public Long getId() {
          return id;
         }

         public void setId(Long id) {
          this.id = id;
         }

         public String getText() {
          return text;
         }

         public void setText(String text) {
          this.text = text;
         }
        }

Message.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
 "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
 "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="hello">
  <class 
   name="Message"
   table="public.messages">
   <id  name="id" column="id">
    <generator class="assigned"/>
   </id>
   <property name="text" column="messagetext"/>
   </class>
</hibernate-mapping>

hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
  "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
        <property name="hibernate.connection.password">bar</property>
        <property name="hibernate.connection.url">jdbc:postgresql:postgres/tommy</property>
        <property name="hibernate.connection.username">foo</property>
        <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
        <property name="show_sql">true</property>
        <property name="log4j.logger.org.hibernate.type">DEBUG</property>
        <mapping resource="hello/Message.hbm.xml"/> 
    </session-factory>
</hibernate-configuration>

Main

package hello;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class App {

 public static void main(String[] args) {
  SessionFactory sessionFactory = new Configuration().configure()
    .buildSessionFactory();

  Message message = new Message();
  message.setText("Hello Cruel World");
  message.setId(2L);

  Session session = null;
  Transaction transaction = null;
  try {
   session = sessionFactory.openSession();
   transaction = session.beginTransaction();
   session.save(message);
  } catch (Exception e) {
   System.out.println("Exception attemtping to Add message: "
     + e.getMessage());

  } finally {
   if (session != null && session.isOpen()) {
    if (transaction != null)
     transaction.commit();
    session.flush();
    session.close();
   }

  }
 }
}

Table structure:

foo=# \d messages
 Table "public.messages"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 id          | integer | 
 messagetext | text    |

Eclipse console output when I run it

Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment <clinit>
INFO: Hibernate 3.5.1-Final
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment <clinit>
INFO: hibernate.properties not found
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment buildBytecodeProvider
INFO: Bytecode provider name : javassist
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment <clinit>
INFO: using JDK 1.4 java.sql.Timestamp handling
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Configuration configure
INFO: configuring from resource: /hibernate.cfg.xml
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Configuration getConfigurationInputStream
INFO: Configuration resource: /hibernate.cfg.xml
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Configuration addResource
INFO: Reading mappings from resource : hello/Message.hbm.xml
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
INFO: Mapping class: hello.Message -> public.messages
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.Configuration doConfigure
INFO: Configured SessionFactory: null
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Using Hibernate built-in connection pool (not for production use!)
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Hibernate connection pool size: 20
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: autocommit mode: false
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: using driver: org.postgresql.Driver at URL: jdbc:postgresql:postgres/tommy
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: connection properties: {user=foo, password=****}
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: RDBMS: PostgreSQL, version: 8.4.3
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC driver: PostgreSQL Native Driver, version: PostgreSQL 8.4 JDBC4 (build 701)
Apr 28, 2010 11:13:54 PM org.hibernate.dialect.Dialect <init>
INFO: Using dialect: org.hibernate.dialect.PostgreSQLDialect
Apr 28, 2010 11:13:54 PM org.hibernate.engine.jdbc.JdbcSupportLoader useContextualLobCreation
INFO: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
Apr 28, 2010 11:13:54 PM org.hibernate.transaction.TransactionFactoryFactory buildTransactionFactory
INFO: Using default transaction strategy (direct JDBC transactions)
Apr 28, 2010 11:13:54 PM org.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup
INFO: No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic flush during beforeCompletion(): disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic session close at end of transaction: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch size: 15
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch updates for versioned data: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Scrollable result sets: enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC3 getGeneratedKeys(): enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Connection release mode: auto
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default batch fetch size: 1
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Generate SQL with comments: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL updates by primary key: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL inserts for batching: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory createQueryTranslatorFactory
INFO: Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
Apr 28, 2010 11:13:54 PM org.hibernate.hql.ast.ASTQueryTranslatorFactory <init>
INFO: Using ASTQueryTranslatorFactory
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query language substitutions: {}
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JPA-QL strict compliance: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Second-level cache: enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query cache: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory createRegionFactory
INFO: Cache region factory : org.hibernate.cache.impl.NoCachingRegionFactory
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Optimize cache for minimal puts: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Structured second-level cache entries: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Echoing all SQL to stdout
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Statistics: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Deleted entity synthetic identifier rollback: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default entity-mode: pojo
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Named query checking : enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Check Nullability in Core (should be disabled when Bean Validation is on): enabled
Apr 28, 2010 11:13:54 PM org.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory
Apr 28, 2010 11:13:55 PM org.hibernate.impl.SessionFactoryObjectFactory addInstance
INFO: Not binding factory to JNDI, no JNDI name configured
Hibernate: insert into public.messages (messagetext, id) values (?, ?)
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: 42P01
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Batch entry 0 insert into public.messages (messagetext, id) values ('Hello Cruel World', '2') was aborted.  Call getNextException to see the cause.
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: 42P01
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ERROR: relation "public.messages" does not exist
  Position: 13
Apr 28, 2010 11:13:55 PM org.hibernate.event.def.AbstractFlushingEventListener performExecutions
SEVERE: Could not synchronize database state with session
org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:179)
 at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
 at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
 at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206)
 at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:375)
 at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
 at hello.App.main(App.java:31)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into public.messages (messagetext, id) values ('Hello Cruel World', '2') was aborted.  Call getNextException to see the cause.
 at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569)
 at org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:459)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2708)
 at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
 ... 8 more
Exception in thread "main" org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:179)
 at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
 at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
 at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206)
 at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:375)
 at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
 at hello.App.main(App.java:31)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into public.messages (messagetext, id) values ('Hello Cruel World', '2') was aborted.  Call getNextException to see the cause.
 at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569)
 at org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:459)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2708)
 at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
 ... 8 more

PostgreSQL log file

2010-04-28 23:13:55 EEST LOG:  execute S_1: BEGIN
2010-04-28 23:13:55 EEST ERROR:  relation "public.messages" does not exist at character 13
2010-04-28 23:13:55 EEST STATEMENT:  insert into public.messages (messagetext, id) values ($1, $2)
2010-04-28 23:13:55 EEST LOG:  unexpected EOF on client connection

If I copy/paste the query into the postgre command line and put the values in and ; after it, it works.

Everything is lowercase, so I don’t think that it’s that issue.

If I switch to MySQL, the same code same project (I only change driver,URL, authentication), it works.

In Eclipse Datasource Explorer, I can ping the DB and it succeeds. Weird thing is that I can’t see the tables from there either. It expands the public schema but it doesn’t expand the tables. Could it be some permission issue?

Thanks!

Источник

What is PostgreSQL error 42P01?

Causes and fixes for the PostgreSQL error 42P01

1. Improper database setup

2. Unquoted identifiers

3. Database query on a non-public schema

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

What is PostgreSQL error 42P01?

Causes and fixes for the PostgreSQL error 42P01

1. Improper database setup

2. Unquoted identifiers

3. Database query on a non-public schema

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Question

Answers

Решение