GVKun编程网logo

不能简单地使用PostgreSQL表名（“关系不存在”）

14

本文的目的是介绍不能简单地使用PostgreSQL表名的详细情况，特别关注“关系不存在”的相关信息。我们将通过专业的研究、有关数据的分析等多种方式，为您呈现一个全面的了解不能简单地使用PostgreS

本文的目的是介绍不能简单地使用PostgreSQL表名的详细情况，特别关注“关系不存在”的相关信息。我们将通过专业的研究、有关数据的分析等多种方式，为您呈现一个全面的了解不能简单地使用PostgreSQL表名的机会，同时也不会遗漏关于centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教、Hibernate + PostgreSQL：关系不存在-SQL错误：0，SQLState：42P01、Jooq 和 postgres：纯 sql 中的 pg_trgm 运算符产生错误“运算符不存在”、php – 简单Postgresql语句 – 列名称不存在的知识。

本文目录一览：

不能简单地使用PostgreSQL表名（“关系不存在”）

不能简单地使用PostgreSQL表名（“关系不存在”）

我正在尝试运行以下PHP脚本来执行简单的数据库查询：

$db_host = "localhost";$db_name = "showfinder";$username = "user";$password = "password";$dbconn = pg_connect("host=$db_host dbname=$db_name user=$username password=$password")    or die(''Could not connect: '' . pg_last_error());$query = ''SELECT * FROM sf_bands LIMIT 10'';$result = pg_query($query) or die(''Query failed: '' . pg_last_error());

这将产生以下错误：

查询失败：错误：关系“ sf_bands”不存在

在所有示例中，我都能找到有人指出该关系不存在的错误，这是因为他们在表名中使用大写字母。我的表格名称没有大写字母。有没有一种方法可以查询我的表而不包含数据库名称，即showfinder.sf_bands？

答案1

小编典典

从我所读的内容来看，此错误表示您未正确引用表名。一个常见的原因是该表是使用大小写混合的拼写定义的，而您正在尝试使用所有小写字母对其进行查询。

换句话说，以下操作失败：

CREATE TABLE "SF_Bands" ( ... );SELECT * FROM sf_bands;  -- ERROR!

使用双引号分隔标识符，以便在定义表时使用特定的大小写混合拼写。

SELECT * FROM "SF_Bands";

作为您的注释，您可以将架构添加到“
search_path”，以便在引用表名而不限定其架构时，查询将通过按顺序检查每个架构来匹配该表名。就像PATH在Shell或include_pathPHP中一样。您可以检查当前的架构搜索路径：

SHOW search_path  "$user",public

您可以更改架构搜索路径：

SET search_path TO showfinder,public;

centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教

centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教

1. 下载源码

$ mkdir /usr/downloads
$ wget -c http://cn2.php.net/distributions/php-5.6.20.tar.gz
$ tar -xvf php-5.6.20.tar.gz
$ mv php-5.6.20 /usr/local/src
$ cd !$ & cd php-5.6.20
登录后复制

2. 阅读安装指导

$ ls -also
$ less README
$ less INSTALL
登录后复制

3. 安装依赖包

$ yum install apr apr-util apr-devel apr-util-devel prce lynx
登录后复制

4. 安装httpd

$ wget -c http://apache.fayea.com//httpd/httpd-2.4.20.tar.gz
$ tar -xvf httpd-2.4.20.tar.gz
$ cd httpd-2.4.20
$ ./configure \
--prefix=/usr/local/programs/apache2 \
--enable-rewrite \
--enable-so \
--enable-headers \
--enable-expires \
--with-mpm=worker \
--enable-modules=most \
--enable-deflate \
--enable-module=shared
$ make
$ make install
$ cd /usr/local/programs/apache2
$ cp bin/apachectl /etc/init.d/httpd ## 复制启动脚本
$ /etc/init.d/httpd start ## 启动apache服务器,访问http://localhost/
$ egrep -v ''^[ ]*#|^$'' /usr/local/apache2/conf/httpd.conf | nl ## 查看apache服务器的配置
## 将apache加入系统服务
vi /etc/rc.d/rc.local
```
/usr/local/programs/apache2/bin/apachectl start
```
$ cat /etc/rc.local
登录后复制

4. 安装postgresql

立即学习“PHP免费学习笔记（深入）”；

$ yum install readline-devel  ## 安装readline依赖
$ cd /usr/downloads
$ wget -c https://ftp.postgresql.org/pub/source/v9.5.0/postgresql-9.5.0.tar.bz2
$ tar -xvf postgresql-9.5.0.tar.bz2
$ cd postgresql-9.5.0
$ ./configure --prefix=/usr/local/programs/postgresql
$ make
$ su
$ make install
$ /sbin/ldconfig /usr/local/programs/postgresql/lib ## 刷新下共享动态库
$ cd /usr/local/programs/postgresql
$ bin/psql --version  ## 检查运行情况
## 开始对postgresql的配置
$ vi /etc/profile.d/postgresql.sh ## 增加环境变量,不推荐直接在/etc/profile中添加,系统更新升级时会需要merge
``` 
PATH=/usr/local/programs/postgresql:$PATH
export PATH
```
$ source /etc/profile ## 更新环境变量

## 增加用户和其他文件夹
$ adduser postgres
$ passwd postgres 
$ mkdir /usr/local/programs/postgresql/logs
$ mkdir /usr/local/programs/postgresql/data
$ chown postgres /usr/local/programs/postgresql/data
$ su - postgres

## 初始化数据库
$ ./bin/initdb -D ./data
$ ./bin/createdb test
$ ./bin/psql test
## 已有数据库，可导入data文件夹后尝试root访问，假如带密码，可能需要进一步研究下
$ ./bin/postgres -D ./data >./logs/start-log-1.log 2>&1 &
$ ./bin/psql --list  ##列出数据库
## ok，安装完成

## 自定义设置，权限控制等，可以跳过，等熟悉使用后再做
## 编辑数据库配置及权限文件：
$ vi /usr/local/programs/postgresql/data/postgresql.conf   ## 数据库配置文件
$ chown postgres postgresql.conf
$ chmod 644 postgresql.conf
$ vi /usr/local/programs/postgresql/data/pg_hba.conf   ## 权限文件
$ vi /usr/local/programs/postgresql/data/pg_ident.conf

## 设置开机自启动：
$ vi /etc/rc.d/rc.local    ## 添加如下内容
```
/usr/local/programs/postgresql/bin/postgresql start
```
登录后复制

5. 安装php

## 源码已经在第一步中下载，现在开始安装:
$ yum install libxml2 libxml2-devel libpng libpng-devel libjpeg libjpeg-devel freetype freetype-devel
$ ./configure \
--prefix=/usr/local/programs/php \
--with-apxs2=/usr/local/programs/apache2/bin/apxs \
--with-zlib \
--with-gd \
--with-jpeg-dir \
--with-png-dir \
--with-freetype-dir \
--with-zlib-dir \
--enable-mbstring \
--with-pgsql=/usr/local/programs/postgresql \
--with-pdo-pgsql=/usr/local/programs/postgresql 
$ make
$ make test
> Bug #42718 (unsafe_raw filter not applied when configured as default filter) [ext/filter/tests/bug42718.phpt]  XFAIL REASON: FILTER_UNSAFE_RAW not applied when configured as default filter, even with flags
> Bug #67296 (filter_input doesn''t validate variables) [ext/filter/tests/bug49184.phpt]  XFAIL REASON: See Bug #49184
> Bug #53640 (XBM images require width to be multiple of 8) [ext/gd/tests/bug53640.phpt]  XFAIL REASON: Padding is not implemented yet
> zend multibyte (7) [ext/mbstring/tests/zend_multibyte-07.phpt]  XFAIL REASON: https://bugs.php.net/bug.php?id=66582
> zend multibyte (9) [ext/mbstring/tests/zend_multibyte-09.phpt]  XFAIL REASON: https://bugs.php.net/bug.php?id=66582
>Bug #70470 (Built-in server truncates headers spanning over TCP packets) [sapi/cli/tests/bug70470.phpt]  XFAIL REASON: bug is not fixed yet

## 查阅官方的bug，发现：
> id=66582: status : Closed. Fixed in master (PHP7)
> id=42718: status : Assigned
> id=42718: reference to id=49184, unsolved for many years
## 那就不关心了，直接装吧
$ make install
> You may want to add: /usr/local/programs/php/lib/php to your php.ini include_path

## 那就按它说的设置吧
$ cp php.ini-development /usr/local/programs/php/lib/php.ini
```
include_path = ".;/usr/local/programs/php/lib/php"

## 然后，编辑httpd的设置，确保其能正确解析php文件
```
...
LoadModule php5_module modules/libphp5.so
...
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .php5

...
<ifmodule dir_module>
    DirectoryIndex index.html index.php
</ifmodule>     
```

## 重启httpd，测试
$ cd /usr/local/programs/apache2
$ bin/httpd -h
$ bin/httpd -k stop
$ bin/httpd -f conf/httpd.conf
## 默认设置的www页面在./htdocs/下，那就先去里面建一个测试页面吧
$ vi htdocs/index.php
```
<?php phpinfo(); ?>
```
$ curl http://localhost/index.php |grep postgresql
#ok
登录后复制

后续应该做的事

* 1. 启动时，不需要要手动指定配置文件
* 2. php初始化www目录设置
* 3. php 用户、权限管理等

'').addClass(''pre-numbering'').hide(); $(this).addClass(''has-numbering'').parent().append($numbering); for (i = 1; i '').text(i)); }; $numbering.fadeIn(1700); }); });

以上就介绍了centos 7下源码编译安装php支持PostgreSQL，包括了postgresql,centos 7方面的内容，希望对PHP教程有兴趣的朋友有所帮助。

Hibernate + PostgreSQL：关系不存在-SQL错误：0，SQLState：42P01

Hibernate + PostgreSQL：关系不存在-SQL错误：0，SQLState：42P01

我在尝试使用PostgreSQL和Hibernate时遇到一些问题，更具体地说，是标题中提到的问题。我已经在网上搜索了几个小时，但没有找到适合我的解决方案。

我正在为Web开发人员使用Eclipse Java EE IDE。在Ubuntu 9.10上使用HibernateTools，Hibernate
3，PostgreSQL 8.4.3构建ID：20090920-1017

以下是相关文件：

消息类

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/>
   </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>

主要

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();
   }

  }
 }
}

表结构：

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

运行时的Eclipse控制台输出

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
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,'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,'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日志文件

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

如果我将查询复制/粘贴到postgre命令行中，然后将值放在and中；之后，它可以工作。

一切都是小写，所以我不认为这是问题。

如果我切换到MySQL，则使用相同的代码执行相同的项目（我仅更改驱动程序，URL，身份验证），即可正常工作。

在Eclipse Datasource
Explorer中，我可以对数据库执行ping操作，并且成功。奇怪的是，我也从那里看不到桌子。它扩展了公共模式，但不扩展表。可能是一些权限问题？

谢谢！

Jooq 和 postgres：纯 sql 中的 pg_trgm 运算符产生错误“运算符不存在”

Jooq 和 postgres：纯 sql 中的 pg_trgm 运算符产生错误“运算符不存在”

php小编苹果为您介绍一则java相关的问题：在使用jooq和postgresql时，可能会出现“运算符不存在”错误，原因是在纯sql中使用pg_trgm运算符不被识别。本文将详细解释这一问题的背景和解决方法。

问题内容

我正在使用的技术：java、spring boot、jooq、带有 pg_trgm 扩展的 postgres、r2dbc。

我尝试使用 pg_trgm 运算符在 postgres 上进行简单搜索，但 jooq 抛出错误。

代码示例：

string searchkeyword = "something";
dsl.select(tables.example.id)
          .from(tables.example)
          .where(dsl.condition("{0} <<% {1}", dsl.val(searchkeyword), tables.example.text_field))
登录后复制

或者更简单：

dsl.resultquery("select ''a'' <<% ''a'';")
登录后复制

产生错误 operator 不存在：字符变化 <<% text。

堆栈跟踪：

org.jooq.exception.dataaccessexception: sql [select ''a'' <<% ''a'';]; operator does not exist: unknown <<% unknown

original stack trace:
        at org.jooq.impl.tools.translate(tools.java:3470)
        at org.jooq.impl.tools.translate(tools.java:3448)
        at org.jooq.impl.tools.translate(tools.java:3432)
        at org.jooq.impl.r2dbc$forwarding.lambda$onerror$0(r2dbc.java:252)      at org.jooq.impl.internal$1.oncomplete(internal.java:497)
        at reactor.core.publisher.strictsubscriber.oncomplete(strictsubscriber.java:123)
        at org.springframework.security.test.context.support.reactorcontexttestexecutionlistener$delegatetestexecutionlistener$securitysubcontext.oncomplete(reactorcontexttestexecutionlistener.java:130)
        at reactor.core.publisher.fluxpeekfuseable$peekfuseablesubscriber.oncomplete(fluxpeekfuseable.java:277)
        at reactor.core.publisher.operators$multisubscriptionsubscriber.oncomplete(operators.java:2060)
        at reactor.core.publisher.monoignorethen$thenignoremain.oncomplete(monoignorethen.java:209)
        at reactor.core.publisher.monoignorethen$thenignoremain.oncomplete(monoignorethen.java:209)
        at reactor.pool.simpledequepool.mayberecycleanddrain(simpledequepool.java:533)
        at reactor.pool.simpledequepool$queuepoolrecyclerinner.oncomplete(simpledequepool.java:765)
        at org.springframework.security.test.context.support.reactorcontexttestexecutionlistener$delegatetestexecutionlistener$securitysubcontext.oncomplete(reactorcontexttestexecutionlistener.java:130)
        at reactor.core.publisher.operators.complete(operators.java:137)        at reactor.core.publisher.monoempty.subscribe(monoempty.java:46)        at reactor.core.publisher.mono.subscribe(mono.java:4490)
        at reactor.pool.simpledequepool$queuepoolrecyclermono.subscribe(simpledequepool.java:877)       at reactor.core.publisher.monodefer.subscribe(monodefer.java:53)        at reactor.core.publisher.internalmonooperator.subscribe(internalmonooperator.java:64)      at reactor.core.publisher.monoignorethen$thenignoremain.subscribenext(monoignorethen.java:240)      at reactor.core.publisher.monoignorethen$thenignoremain.oncomplete(monoignorethen.java:203)         at org.springframework.security.test.context.support.reactorcontexttestexecutionlistener$delegatetestexecutionlistener$securitysubcontext.oncomplete(reactorcontexttestexecutionlistener.java:130)      at reactor.core.publisher.fluxpeek$peeksubscriber.oncomplete(fluxpeek.java:260)         at reactor.core.publisher.operators.complete(operators.java:137)        at reactor.core.publisher.monoempty.subscribe(monoempty.java:46)        at reactor.core.publisher.mono.subscribe(mono.java:4490)        at reactor.core.publisher.monoignorethen$thenignoremain.subscribenext(monoignorethen.java:263)      at reactor.core.publisher.monoignorethen.subscribe(monoignorethen.java:51)      at reactor.core.publisher.internalmonooperator.subscribe(internalmonooperator.java:64)      at reactor.core.publisher.monodefer.subscribe(monodefer.java:53)        at reactor.core.publisher.internalmonooperator.subscribe(internalmonooperator.java:64)      at reactor.core.publisher.monoignorethen$thenignoremain.subscribenext(monoignorethen.java:240)      at reactor.core.publisher.monoignorethen$thenignoremain.oncomplete(monoignorethen.java:203)         at org.springframework.security.test.context.support.reactorcontexttestexecutionlistener$delegatetestexecutionlistener$securitysubcontext.oncomplete(reactorcontexttestexecutionlistener.java:130)      at reactor.core.publisher.monoignoreelements$ignoreelementssubscriber.oncomplete(monoignoreelements.java:89)        at reactor.core.publisher.fluxhandlefuseable$handlefuseablesubscriber.oncomplete(fluxhandlefuseable.java:236)       at reactor.core.publisher.operators$monosubscriber.complete(operators.java:1840)        at reactor.core.publisher.monosupplier.subscribe(monosupplier.java:62)      at reactor.core.publisher.mono.subscribe(mono.java:4490)        at reactor.core.publisher.monoignorethen$thenignoremain.subscribenext(monoignorethen.java:263)      at reactor.core.publisher.monoignorethen.subscribe(monoignorethen.java:51)      at reactor.core.publisher.internalmonooperator.subscribe(internalmonooperator.java:64)      at reactor.core.publisher.monodefer.subscribe(monodefer.java:53)        at reactor.core.publisher.mono.subscribe(mono.java:4490)        at org.jooq.impl.r2dbc$abstractnonblockingsubscription.lambda$cancel0$4(r2dbc.java:663)         at java.base/java.util.concurrent.atomic.atomicreference.updateandget(atomicreference.java:210)         at org.jooq.impl.r2dbc$abstractnonblockingsubscription.cancel0(r2dbc.java:647)      at org.jooq.impl.r2dbc$abstractsubscription.complete(r2dbc.java:213)        at org.jooq.impl.r2dbc$abstractresultsubscriber.complete(r2dbc.java:303)        at org.jooq.impl.r2dbc$forwarding.complete(r2dbc.java:265)      at org.jooq.impl.r2dbc$forwarding.onerror(r2dbc.java:252)       at reactor.core.publisher.strictsubscriber.onerror(strictsubscriber.java:106)       at org.springframework.security.test.context.support.reactorcontexttestexecutionlistener$delegatetestexecutionlistener$securitysubcontext.onerror caused by: io.r2dbc.postgresql.exceptionfactory$postgresqlbadgrammarexception: [42883] operator does not exist: unknown <<% unknown   at io.r2dbc.postgresql.exceptionfactory.createexception(exceptionfactory.java:96)   suppressed: the stacktrace has been enhanced by reactor, refer to additional information below:  assembly trace from producer [reactor.core.publisher.fluxhandlefuseable] :     reactor.core.publisher.flux.handle(flux.java:5913)  io.r2dbc.postgresql.postgresqlresult.map(postgresqlresult.java:107) error has been observed at the following site(s):   *__flux.handle ⇢ at io.r2dbc.postgresql.postgresqlresult.map(postgresqlresult.java:107) original stack trace:       at io.r2dbc.postgresql.exceptionfactory.createexception(exceptionfactory.java:96)       at io.r2dbc.postgresql.exceptionfactory.createexception(exceptionfactory.java:65)       at io.r2dbc.postgresql.exceptionfactory.handleerrorresponse(exceptionfactory.java:132)      at io.r2dbc.postgresql.postgresqlresult.lambda$map$2(postgresqlresult.java:111)         at reactor.core.publisher.fluxhandlefuseable$handlefuseablesubscriber.onnext(fluxhandlefuseable.java:176)       at reactor.core.publisher.fluxwindowpredicate$windowflux.drainregular(fluxwindowpredicate.java:668)         at reactor.core.publisher.fluxwindowpredicate$windowflux.drain(fluxwindowpredicate.java:746)        at reactor.core.publisher.fluxwindowpredicate$windowflux.onnext(fluxwindowpredicate.java:788)       at reactor.core.publisher.fluxwindowpredicate$windowpredicatemain.onnext(fluxwindowpredicate.java:239)      at io.r2dbc.postgresql.util.fluxdiscardoncancel$fluxdiscardoncancelsubscriber.onnext(fluxdiscardoncancel.java:91)
登录后复制

请注意，如果直接在数据库上运行，以下纯 sql 查询将有效：

select id from example where ''something'' <<% text_field;
登录后复制

另请注意：这不是类型转换问题。如果我将 searchkeyword 转换为文本，或者将其内联，则会产生类似的错误

另一个注意事项：如果我更改代码以使用 strict_word_similarity 函数而不是运算符，它就可以工作。问题仅出在运营商

解决方法

您可以将绑定变量显式转换为文本：

dsl.condition("{0}::text <<% {1}", ...)
登录后复制

或者内联它而不是绑定它：

DSL.condition("{0} <<% {1}", DSL.inline(searchKeyword), Tables.EXAMPLE.TEXT_FIELD)
登录后复制

以上就是Jooq 和 postgres：纯 sql 中的 pg_trgm 运算符产生错误“运算符不存在”的详细内容，更多请关注php中文网其它相关文章！

php – 简单Postgresql语句 – 列名称不存在

php – 简单Postgresql语句 – 列名称不存在

我一直把我的头发拉出来我有一个非常简单的postgre数据库,一个特定的表有一个名为lName(大写N)的列.现在我知道postgre我必须引用lName,因为它包含一个大写N.

我正在尝试使用以下语句查询数据库：

SELECT * FROM employee WHERE
“lName” LIKE “Smith”

但是我收到这个错误：

Warning: pg_query()
[function.pg-query]: Query Failed:
ERROR: column “Smith” does not exist
in …..

这里有什么问题？为什么说专栏是“史密斯”？

我猜： 
SELECT * FROM employee WHERE "lName" LIKE 'Smith'

(注意不同的引号;“foo”是引用的标识符;“foo”是字符串文字)

此外,在大多数sql方言中,没有通配符的LIKE等效于=;你是否意味着包含一个通配符？

我们今天的关于不能简单地使用PostgreSQL表名和“关系不存在”的分享已经告一段落，感谢您的关注，如果您想了解更多关于centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教、Hibernate + PostgreSQL：关系不存在-SQL错误：0，SQLState：42P01、Jooq 和 postgres：纯 sql 中的 pg_trgm 运算符产生错误“运算符不存在”、php – 简单Postgresql语句 – 列名称不存在的相关信息，请在本站查询。

本文标签：

上一篇使用PHP创建，编辑和删除crontab作业？（php实现删除功能）

下一篇在PHP中检测请求类型（GET，POST，PUT或DELETE）（php判断请求方式）

相关文章