gpt4 book ai didi

sql - 按 Postgres hstore 属性排序未按预期工作

转载 作者:行者123 更新时间:2023-11-29 12:35:10 24 4
gpt4 key购买 nike

我使用 Postgres 10.4,我在一个简单的查询中得到了一个奇怪的排序行为。

我的表是这样定义的:

CREATE TABLE country (
id varchar(2) NOT NULL PRIMARY KEY,
order_by smallint,
app_locale boolean DEFAULT false NOT NULL,
country boolean DEFAULT false NOT NULL,
lang boolean DEFAULT false NOT NULL,
nationality boolean DEFAULT false NOT NULL,
c_name hstore,
l_name hstore,
n_name hstore,
active boolean DEFAULT false NOT NULL,
language varchar(6)
);

这个简单的查询应该返回按英文国籍名称排序的国家:

select id, c_name
from country c
where active = true
and c.nationality = true
and id in ('de','fr','be','lu','kz','af','ad') -- limit the result a bit
order by c.n_name -> 'en'

意想不到的结果是:

id  c_name
be "de"=>"Belgien", "lb"=>"Belsch", "en"=>"Belgium", "fr"=>"Belgique"
fr "de"=>"Frankreich", "lb"=>"Frankräich", "en"=>"France", "fr"=>"France"
de "de"=>"Deutschland", "lb"=>"Däitschland", "en"=>"Germany", "fr"=>"Allemagne"
lu "de"=>"Luxemburg", "lb"=>"Lëtzebuerg", "en"=>"Luxembourg", "fr"=>"Luxembourg"
ad "en"=>"Andorra"
kz "en"=>"Kazakhstan"
af "de"=>"Afghanistan", "en"=>"Afghanistan", "fr"=>"Afghanistan"

这里有什么问题?

最佳答案

您在 order by 中使用了错误的列:

select id, c_name
from country c
where active = true
and c.nationality = true
and id in ('de','fr','be','lu','kz','af','ad')
order by c_name -> 'en'

关于sql - 按 Postgres hstore 属性排序未按预期工作,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51580188/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com