天道酬勤,学无止境

postgresql

如何将记录传递给 PL/pgSQL 函数?(How to pass a record to a PL/pgSQL function?)

问题 我有 8 个类似的 PL/pgSQL 函数; 它们用作视图上的INSTEAD OF INSERT/UPDATE/DELETE触发器以使其可写。 每个视图都组合了一个通用表(在下面的示例中称为“事物”)和一个特殊表(下面的“shape_things”和“flavored_things”)的列。 顺便说一下,在我们的例子中不能使用 PostgreSQL 的继承特性。 触发器必须在通用表中插入/更新行; 这些部分在所有 8 个功能中都是相同的。 由于通用表有大约 30 列,我试图在那里使用辅助函数,但是我无法将视图的NEW记录传递给需要things记录作为输入的函数。 (这里和这里已经提出了类似的问题,但我认为我不能在我的情况下应用建议的解决方案。) 简化模式 CREATE TABLE things ( id SERIAL PRIMARY KEY, name TEXT NOT NULL -- (plus 30 more columns) ); CREATE TABLE flavored_things ( thing_id INT PRIMARY KEY REFERENCES things (id) ON DELETE CASCADE, flavor TEXT NOT NULL ); CREATE TABLE shaped_things ( thing_id INT PRIMARY

2021-06-21 12:07:33    分类:技术分享    postgresql   function   parameters   triggers   plpgsql

how to collect multiple values as a single string in postgres?

I have tables : Project table id name ------- 1 A 2 B Assignment table id name project_id ------------------- 1 A1 1 2 A2 1 3 A3 2 I wish to write a query that returns each project with the name of the assignments created from it, like : project_id assignments ----------------------- 1 A1,A2 2 A3 Is there any way to achieve that ?

2021-06-21 12:03:26    分类:问答    sql   postgresql   postgresql-9.1

尝试创建新触发器时出现 SQL 错误(SQL error when trying to create new trigger)

问题 当我尝试创建触发器时: CREATE TRIGGER ctg_el_del AFTER DELETE ON ctg FOR EACH ROW BEGIN DELETE FROM ctg where ctg.id_ctg = old.lft WHERE cat.id_ctg = old.rgt; END; 显示此错误: ERROR: syntax error at or near "BEGIN" LINE 4: BEGIN ^ 回答1 第 1 步:您需要创建在该函数中返回类型触发器的函数,您应该提及 SQL 操作,如下所示: CREATE FUNCTION trigger_function() RETURNS TRIGGER AS $$ BEGIN DELETE FROM ctg WHERE ctg.id_ctg = old.lft AND ctg.id_ctg = old.rgt; END $$ LANGUAGE plpgsql; 第 2 步:然后在表上创建触发器以在删除后调用,如下所示: CREATE TRIGGER ctg_el_del AFTER DELETE ON ctg FOR EACH ROW EXECUTE PROCEDURE trigger_function();

2021-06-21 11:55:03    分类:技术分享    postgresql   triggers

如何在 Windows7 中备份和恢复 PostgreSQL 数据库?(How to backup & Restore PostgreSQL database in Windows7?)

问题 我是 Postgres 数据库的新手。 我必须从生产服务器获取备份(pgAdmin 版本是 9.2.4)并在我的本地机器上恢复它(我有 pgAdmin 版本 9.4)。 我试图通过右键单击数据库-> 备份来获取备份以获取 .backup 文件。 如下图所示: 但是当我尝试恢复备份文件时,出现了很多错误。 我还想知道在两个系统上使用不同的端口是否也会在恢复备份时产生问题。 因为当我尝试恢复相同系统的备份时没有问题。 回答1 要备份数据库,您可以使用pg_dump.exe : 打开 Powershell 转到 Postgres bin 文件夹。 例如: cd "C:\Program Files\PostgreSQL\9.6\bin" 输入命令以转储数据库。 例如: ./pg_dump.exe -U postgres -d my_database_name -f D:\Backup\<backup-file-name>.sql 为您的 postgres 用户键入密码 要恢复数据库,您可以使用psql.exe 。 (请注意,以下内容摘自 Alexandr Omelchenko 的有用答案,由于我不清楚原因,该答案已被删除。) 打开 Powershell 转到 Postgres bin 文件夹。 例如: cd "C:\ProgramFiles\PostgreSQL\9.6\bin"

2021-06-21 11:51:34    分类:技术分享    windows   postgresql   pgadmin

postgres query with IN is very slow

I have a table which has an index on (column A, column B). And I'm running a query that looks like this: SELECT * FROM table WHERE (A, B) IN ((a_1, b_1), (a_2, b_2), ..., (a_5000, b_5000)) This query is very slow ! The plan looks like: Bitmap Heap Scan on table Recheck Cond: (((A = a_1) AND (B = b_1)) OR ((A = a_2) AND (B = b_2)) OR ... -> BitmapOr -> Bitmap Index Scan on idx Index Cond: ((A = a_1) AND (B = b_1)) -> Bitmap Index Scan on idx Index Cond: ((A = a_2) AND (B = b_2)) ...(5000 other Bitmax Index Scan) Instead of doing one index scan with 5000 values, postgres seems to be doing 5000

2021-06-21 11:47:40    分类:问答    sql   postgresql   postgresql-performance

PostgreSQL 中的簇索引和非簇索引(Cluster and Non cluster index in PostgreSQL)

问题 我正在使用 PostgreSQL 9.3 版本来创建数据库。 我有一些列列表的下表测试。 create table test ( cola varchar(10), colb varchar(10), colc varchar(10), cold varchar(10) ); 现在我想在某些列上创建索引。 例如: 我想为cola和colb列创建聚集索引。 我想为colc和cold列创建非聚集索引。 正如我提到的this和this,我开始知道PostgreSQL中没有聚集和非聚集索引。 我的问题:我可以使用什么类型的索引来代替 PostgreSQL 中的聚集索引和非聚集索引,哪个与聚集索引和非聚集索引的工作相同? 回答1 我的问题:我可以使用什么类型的索引来代替 PostgreSQL 中的聚集索引和非聚集索引,哪个与聚集索引和非聚集索引的作用相同? PostgreSQL 根本没有聚集索引的概念。 相反,所有表都是堆表,所有索引都是非聚集索引。 当您通常创建聚集索引时,只需创建一个非聚集索引。 更多细节: http://use-the-index-luke.com/sql/clustering/index-organized-clustered-index 您可能需要阅读整章以正确理解这些概念。

2021-06-21 11:32:18    分类:技术分享    postgresql   indexing   postgresql-9.3

如何从另一个脚本运行 postgres sql 脚本?(How to run postgres sql script from another script?)

问题 我有一堆在数据库中创建表的 SQL 脚本。 每个表都位于一个单独的文件中,因此编辑它们要容易得多。 我想准备一个单独的 SQL 脚本,它将创建完整的模式、创建表、插入测试数据并为表生成序列。 我能够为 oracle 数据库做这样的事情,但是我在使用 postgres 时遇到了问题。 问题是 - 我不知道如何从另一个脚本运行表创建脚本。 在 oracle 中,我使用以下语法进行操作: @@'path of the script related to the path of the currently running sql file' 一切都像魅力一样。 在 postgres 中,我试图搜索类似的东西并找到了这个: \ir 'relative path to the file' 不幸的是,当我运行主脚本时,我收到以下消息: No such file or directory. 示例调用在这里: \ir './tables/map_user_groups.sql' 我使用 Postgres 9.3。 我尝试使用 psql 运行脚本: psql -U postgres -h localhost -d postgres < "path to my main sql file" 除了调用其他脚本外,该文件执行良好。 有人知道如何解决问题吗? 如果问题中的某些内容不清楚-请告诉我:)

2021-06-21 11:19:47    分类:技术分享    postgresql

How to avoid race conditions when using the find_or_create method of DBIx::Class::ResultSet?

From the documentation for find_or_create: Note: Because find_or_create() reads from the database and then possibly inserts based on the result, this method is subject to a race condition. Another process could create a record in the table after the find has completed and before the create has started. To avoid this problem, use find_or_create() inside a transaction. Is it enough to just use find_or_create() inside a transaction in PostgreSQL?

2021-06-21 11:11:39    分类:问答    perl   postgresql   database   dbix-class

UPDATE .. LIMIT 1 with SqlAlchemy and PostgreSQL

With SqlAlchemy, is it possible to build a query which will update only the first matching row? In my case, I need to update the most recent log entry: class Log(Base): __tablename__ = 'logs' id = Column(Integer, primary_key=True) #... analyzed = Column(Boolean) session.query(Log) \ .order_by(Log.id.desc()) \ .limit(1) \ .update({ 'analyzed': True }) Which results into: InvalidRequestError: Can't call Query.update() when limit() has been called It makes sense, since UPDATE ... LIMIT 1 is a MySQL-only feature (with the solution given here) But how would I do the same with PostgreSQL? Possibly

2021-06-21 11:04:00    分类:问答    python   postgresql   sqlalchemy   sql-update   sql-limit

使用 sqlalchemy 从 PostgreSQL 查询返回 Pandas 数据帧(Return Pandas dataframe from PostgreSQL query with sqlalchemy)

问题 我想查询 PostgreSQL 数据库并将输出作为 Pandas 数据框返回。 我使用“SqlAlchemy”创建了到数据库的连接: from sqlalchemy import create_engine engine = create_engine('postgresql://user@localhost:5432/mydb') 我将 Pandas 数据框写入数据库表: i=pd.read_csv(path) i.to_sql('Stat_Table',engine,if_exists='replace') 根据文档,看起来 pd.read_sql_query() 应该接受 SQLAlchemy 引擎: a=pd.read_sql_query('select * from Stat_Table',con=engine) 但它抛出一个错误: ProgrammingError: (ProgrammingError) relation "stat_table" does not exist 我正在使用 Pandas 0.14.1 版。 这样做的正确方法是什么? 回答1 您被 PostgreSQL 的大小写(不)敏感性问题所困扰。 如果您在查询中引用表名,它将起作用: df = pd.read_sql_query('select * from "Stat_Table"',con

2021-06-21 11:02:41    分类:技术分享    python   postgresql   pandas   sqlalchemy