为什么 Postgres 在为列设置默认函数时双引号第一个函数标识符?

Question

>我有一个遗留表，在 Rails 项目中有一个默认表达式。此表达式选取一个随机的四位数字并将其转换为字符串。在我的测试过程中，我一直在 psql 中键入这个确切的语句来设置默认表达式：

alter table owners alter column signing_code set default 
substring(to_char(((random() * ((10000 - 1))::double precision) + 
(1)::double precision), '0000'::text), '....$'::text);

然后当我这样做d owners这就是Postgres决定的我的实际默认表达式：

default "substring"(to_char(((random() * ((10000 - 1))::double 
precision) + (1)::double precision), '0000'::text), '....$'::text)

请注意第一个函数标识符substring两边的双引号。这会导致 Rails 模式转储/加载出现两个问题：

1. 将模式转储到 db/schema.rb 时，将生成无效的 Ruby，因为双引号未转义
2. 即使您正确地手动转义引号，在将模式加载回数据库时，Rails 也会错误地将整个表达式设置为默认字符串值，而不是表达式(即它用单引号将表达式括起来)

在我的情况下，有没有办法让 Postgres 不对嵌套函数调用中的第一个函数进行双引号？这将是一个很好的解决方法，否则我将在 Rails 项目中提交一个错误。

Answer 1

为什么substring函数被双引号并不重要，这两个表达式在功能上是等效的。PostgreSQL将解析默认表达式，然后保存解析后的版本，在此过程中，substring标识符被双引号。如果添加如下所示的 CHECK 约束，您可以看到类似的事情发生：

check (c in (1, 2, 3))

对具有该约束的表执行d将提供：

CHECK (c = ANY (ARRAY[1, 2, 3]))

PostgreSQL 将in表达式转换为等效的= ANY表达式，因为这是它想要在内部使用的。这两个表达式在功能上是等效的。

真正的问题是 ActiveRecord 不知道如何处理比单个文本值更复杂的默认表达式。AR对默认值是什么样子做出了无效的假设，然后盲目地把事情搞得一团糟。

当您处理最简单的DDL SQL之外的任何内容时，最好的选择是使用db/structure.sql而不是db/schema.rb。structure.sql使用数据库的本机转储/还原工具，因此它将理解并保留数据库所知道的所有内容。

切换到structure.sql非常简单：

1. 更新config/application.rb以使用正确的格式：

   config.active_record.schema_format = :sql
   

2. 使用db:structure:dump耙任务转储structure.sql。

3. 从源代码树和修订控制中删除db/schema.rb。

4. 将db/structure.sql添加到修订控制。

5. 重新训练您的手指以使用不同的耙子任务来转储和恢复架构：

   • db:structure:dump而不是db:schema:dump.
   • db:structure:load而不是db:schema:load.

我总是从structure.sql开始，因为schema.rb对于我想要如何使用我的数据库来说太有限了。