Dimitrios Desyllas's questions

在 mysql（或 mariadb）表中：

mytable:
---
id PK Biginteger Autoincrement
name varchar(255)

我搜索这样的东西：

select * from mytable where name like "%someval%"; 

搜索词也可以是希腊语：

select * from mytable where name like "%οτιδήπ%"; 

如何根据与我搜索的值的相关性对返回的结果进行排序？我的意思是，一旦我搜索，我希望首先返回最接近的οτιδήπ结果。nameοτιδήπ

我怎样才能做到这一点？

我有下表：

-- laravel.spies definition

CREATE TABLE `spies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `surname` varchar(255) NOT NULL,
  `agency` varchar(255) NOT NULL DEFAULT 'NO-AGENCY',
  `country_of_operation` varchar(255) DEFAULT NULL,
  `birth_date` date NOT NULL,
  `death_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_spy` (`name`,`surname`,`agency`,`birth_date`),
) ENGINE=InnoDB AUTO_INCREMENT=2
       DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

我还插入了以下约束：

ALTER TABLE spies add constraint
         death_date_valid check ( spies.death_date = NULL
           || spies.death_date > spies.birth_date );

并且以下插入语句插入一条记录。就我而言，它不应该能够这样做：

INSERT INTO spies (name,surname,birth_date,death_date)
      VALUES ('Namae','Myoji','1980-12-01','1970-12-03');

但它却插入了值。你知道为什么吗？

我想要实现的死亡日期将有：

• 空值
• 任何大于birth_date的值

我有下表：

create workers (
  id SERIAL PRIMARY KEY,
  name STRING
);

create works_zones (
  id SERIAL PRIMARY KEY,
  area_name String,
  work_from TIME WITHOUT TIMEZONE,
  work_until TIME WITHOUT TIMEZONE
) 

create table shifts (
  id SERIAL PRIMARY KEY,
  worker_id INT,
  day_of_week INT,
  work_zone INT,
  CONSTRAINT fk_worker FOREIGN KEY(worker_id) REFERENCES workers(id) ON DELETE CASCADE,
  CONSTRAINT fk_work_zone FOREIGN KEY(work_zone) REFERENCES works_zones(id) ON DELETE CASCADE
)

在我的班次中，我有以下记录：

worker_id在我的例子中，我想在 columns , day_of_week, work_zoneat table上应用一个唯一索引shifts：

CREATE UNIQUE INDEX CONCURRENTLY unique_day_zone_worker ON shifts (worker_id,work_zone,"day_of_week");

但是根据我的记录会失败。因此，我想检测重复的记录，以便我可以手动修复它们。任何想法我该怎么做？

在其他 wqords 中，我想检测shift.id在列worker_id、day_of_week、处有重复记录的work_zone。

我有下表：

create table my table (id SERIAL PRIMARY KEY,value TEXT);

我试图找到 serial 创建的序列：

SELECT sequence_schema, sequence_name 
FROM information_schema.sequences 
ORDER BY sequence_name ;

如何将列出的序列号与table.id主键相关联？

在我的例子中，我想阻止来自具有 2 个模式的数据库的表和视图列表：

• 民众
• 双

我想防止任何表和视图列表向特定用户公开，因此我查阅了这个答案：https ://dba.stackexchange.com/a/177272/118215

但是当我阅读答案时，我注意到 ROLE 与 USER 不同，并且登录是与上面的不同的“实体”。

尽管相关，但我无法理解以下两者之间的区别：

• 登录
• 角色
• 用户

因此，有人可以解释差异吗？

在我正在制作的应用程序中，我有下表

records
------
id PK
pattern TEXT

在这个数据库中我有这样的记录

我想将字符串与模式匹配。在模式列中，* 用作通配符，类似于在文件路径中使用的通配符。

就我而言，我想在 for 中找到最佳匹配https://google.com/lorem_ipsum。我该怎么做？

在我的例子中，我希望 * 被视为通配符。

在我的例子中，我有一个名为 clients 的表，它遵循以下信息：

store_listing:
#######
id SERIAL PK
title VARCHAR(100) not null
features JSONB not null
comments TEXT
vat_num VARCHAR(100) NOT null

我用来生成的这张表：

1. 客户端列表（例如各种第 3 方服务的 csv 生成）
2. 将横幅更改为网站以进行各种促销
3. 启用和禁用客户端有权访问的各种功能。

一个典型的值可以是：

{
   banner_cats:{
      active: true,
      types: ['persian','aegean']
   },
   promo_petfest_2023:{
     active: true,
     meta: ['dogs','cats','giraqffes']
   },
   1bank_catzoo: {
     active: true
   },
   feature_virtual_pet: {
     active: true
   }
}

每次我需要标记元数据并将其放置到特定的 Store_listing 时，我都需要更新该features列。此外，在我的案例中，促销可以根据例如一个大型组织（例如 WWE）想要制作一个将在我的网站上放置特殊横幅的促销，因此我必须像这样更新集成列：

{
   banner_cats:{
      active: true,
      types: ['persian','aegean']
   },
   promo_petfest_2023:{
     active: true,
     meta: ['dogs','cats','giraqffes']
   },
   1bank_catzoo: {
     active: true
   },
   feature_virtual_pet: {
     active: true
   },
   wwe_wild_beast_payperview:{
      active: true
   }
}

这对我来说似乎是一个错误的决定，因此我认为这种方法：

features_and_promos_settings
####
listing_id FK store_listing
identifier: String (lowercase, does not contain spaces)
active: boolean
meta: jsonb
type: ENUM(PROMO,FEATURE,BANNER) NULL
#####
PK (listing_id, identifier)

所以在我的例子中，该integrations列将包含这个值 (listing_id = 1) ：

{
   banner_cats:{
      active: true,
      types: ['persian','aegean']
   },
   promo_petfest_2023:{
     active: true,
     meta: ['dogs','cats','giraqffes']
   },
   1bank_catzoo: {
     active: true
   },
   feature_virtual_pet: {
     active: true
   },
   wwe_wild_beast_payperview:{
      active: true
   }
}

会变成记录features_and_promos_settings

但是这种方法在选择过程中的搜索和资源使用等情况下对我有好处吗？我将使用的 RDMS 是 postgresql-11。

我预计可能会为 KPIS 生成报告（例如回答问题有多少列表参与了特定的促销活动）

我制作了下表：

CREATE table IF NOT EXISTS redirect (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            url_from TEXT not null,
            url_to TEXT not null,
            method TEXT not null,
            http_status_code INTEGER not null CHECK( http_status_code IN (300,301,302,304,304,308,307) ) DEFAULT 301,
            use_in_https INTEGER not null CHECK(use_in_https IN (0,1)) DEFAULT 0,
            use_in_http INTEGER not null CHECK(use_in_http IN (0,1)) DEFAULT 1,
            exact_match INTEGER not null CHECK(exact_match IN (0,1)) DEFAULT 1
        );

我做了以下触发器：

        CREATE TRIGGER remove_http_https BEFORE INSERT ON redirect BEGIN 
           SELECT CASE WHEN NEW.url_from THEN REPLACE(REPLACE(NEW.url_from,"http:\/\/",""),"https:\/\/","")   END;
        END;

但是我希望触发器在我更新值或插入值时同时运行。有没有办法将插入和更新定义为 sqlite3 的单个触发器？

我有一个jsondocs包含以下列的表：

id SERIAL
json TEXT

我想检查列是否json包含一些值但不完全匹配。

例如我可以做类似的事情：

select * from jsondocs where json like '%id:123%'

但是对于包含 100 个值的数组。我的意思是（伪代码）：

select * from jsondocs where json like in ('%id:123%','%id:123234324423243%',...)

我想根据数组中提供的值进行“喜欢”搜索。

我有 2 个数据库，其中一个名为logs：

http_log:
id: serial
method: Varchar(10)
url: varchar(150)

我还有一个名为的数据库archiving，它也有一个名为的表http_log：

http_log:
id: unsinged integer
method: Varchar(10)
url: varchar(150)

如何创建外部表archived_http_log，以便将数据http_log从archived_http_log. 我的 postgresql 中不能有同名的表，因此我不能有 2 个名为http_log.

我最想要实现的是通过单个 SQL 脚本将数据logs.http_log从archiving.http_log. 所以在一个服务器上运行：

INSERT INTO archived_http_log VALUES (SELECT * from http_log);

在我的应用程序中，postgresql 数据库中的一些查询返回：

SQLSTATE[08006] [7] timeout expired (SQL: ^some_sql^ )

在运行查询的同时，我还运行了一个pg_dump通过 cron 使用的备份脚本。

可以并行pg_dump可能会导致查询超时，在什么情况下会出现？

我有下表命名jobs：

id PK Serial
model String
model_id UNSIGNED Integer
status String
created_at timestamp
prosessed_at timestamp

对于每个模型 ID，我将每个状态记录在一个数组中：

select
 model_id,
 model,
 array_agg(id) as jobs,
 array_agg(statuses) as statuses
from jobs
group by model,model_id

我将其用作子查询，以检测已处理作业中的任何故障：

select
 *
from (
  select
   model_id,
   model
   array_agg(id) as jobs,
   array_agg(statuses) as statuses
  from jobs
  group by model,model_id
) as jobs
where
    'aborted' in statuses
and
    'pending' not in statuses
and
    'failed' not in statuses
and
    'processed' not in statuses;

但在以下行中：

    'aborted' in statuses

有某种语法错误：

SQL Error [42601]: ERROR: syntax error at or near "statuses"
  Position: 312

你知道为什么吗？

Ι 有下表命名discounts：

id Serialize
discount INT
offers JSON

有以下记录

我如何在表中搜索所有具有非零折扣但在 JSON 中具有 JSON 类型的记录discount。

到目前为止，我已经尝试过这个查询：

select * 
from discounts r 
where discount <> 0 
  and offers @> '[{"type":"discount"}]'::json;

但我收到以下错误：

错误：运算符不存在：json @> json

提示：没有运算符匹配给定的名称和参数类型。您可能需要添加显式类型转换。

职位：68

我有下表，命名为商店：

store_id INT
emails VARCHAR

包含值：

我想生成以下集合：

如您所见，我想将电子邮件字段拆分为仅包含一个电子邮件地址的单独记录。你知道我该怎么做吗？

到目前为止，我设法创建了以下查询：

select store_id,string_to_array(emails,',') from stores

但我不知道如何将其拆分string_to_array为自己的行。

Ι 有以下看法：

CREATE OR REPLACE VIEW public.my_view as 
SELECT
  col1,
  col11
from
  table_a JOIN table_b on table_a.id=table_b.a_id
  join
   (
     select id,col56 from table_c where col56 > 3
   ) as filtered_c on table_a.id=filtered_c.a_id;

table_a 是：

id: SERIAL PK
col1: INTEGER

table_b 是：

id: SERIAL PK
col11: INTEGER
a_id FK table_a

table_c 是：

id: SERIAL PK
col56 INTEGER
a_id FK table_a

正如我在 postgresql wiki上看到的那样，我可以将值插入到与相应表对应的视图中。但就我而言，如果我插入：

INSERT INTO my_view VALUES (7,88);

将在哪个表中插入值？据我所知，视图是只读表。

我将现有的 posttgresql 数据库从一台服务器复制到另一台服务器。我的数据源是 postgresql 9.6，而较新的服务器是 postgresql 11。

所以我想知道如何以快速简便的方式检查两个数据库是否具有相同的架构和数据？

我有下表：

mytable
---------
id Serial PK
action Integer
model VARCHAR
model_id integer
active BOOL

model,model_id,action但是由于我的逻辑，当 active 为 true 时，不能有具有相同值的重复值。

因此，我放置了一个独特的约束：

ALTER TABLE mytable ADD CONSTRAINT constraint_name UNIQUE (model,model_id,action,active);

但这并不能避免我只有在 active=true 时才具有唯一约束。此外，我的表预先填充了重复值，因此我想在重复值上设置 active=false 并只保留一条记录，value=true除了在应用约束之前编写更新语句之外，您知道如何做到这一点吗？ ?

我有以下 SQL 脚本：

CREATE temporary table if not EXISTS the_values (
 key SERIAL,
 value INTEGER NULL 
);

insert into the_values(value) values (null),(1),(null),(2),(3),(4),(5),(6),(10),(null),(null);

select * 
from the_values 
where value not in (1,2,3,4,5,6,10); 

我注意到查询：

select * 
from the_values 
where value not in (1,2,3,4,5,6,10); 

不返回具有valueNULL 的行，这引起了我的注意。因此，我想知道为什么会这样。我对这种现象的技术方面更感兴趣，而不是明显的解决方案：

select * 
from the_values 
where value not in (1,2,3,4,5,6,10) 
   or value IS NULL; 

我正在将视图使用映射到一个未知的数据库方案。我收到了一份包含一些观点的清单：

view_a
view_b
view_c

我想搜索我的数据库哪些视图依赖于另一个视图，例如，如果 view_c 是通过以下 sql 查询生成的：

CREATE VIEW view_c AS SELECT * from view_a JOIN view_b WHERE view_a.column=344

我想以某种方式列出该view_c用途view_a和view_bExcel 表。现在是通过查看 Dbeaver 上的视图 dll sql 并从中找出来手动完成的。但这是一项非常费力的工作，并且可能会错过一些视图依赖项，所以你知道如果我可以通过 information_schema.* 的查询生成 Excel 表tables吗？

在我的数据库中，我有下表：

Mytable
id SERIAL,
category VARCHAR,
value VARCHAR

我执行以下查询：

select category, COUNT(*) from mytable group by category

我想要的是生成一个包含以下内容的单行值：

category1 | category2 |  category3
1234      | 3456      |  12345

该表返回以下结果：

 category   | Value
`category1` | 1234
`category2` | 3456
`category3` | 12345

你知道怎么做吗？我查看了交叉表函数，但需要一个名为row_name在我的情况下不存在的额外列。同样使用第二个查询对我来说似乎是一种浪费。

这