Postgres SELECT查询未正常工作

Question

我在pgAdmin 4中执行了2个查询。

select * from v_anken where gyomu_name like '%ひとり%' and nyuryoku_cd like '0187'

当使用gyomu_name和nyuryoku_cd选择时为=>。返回1行

with tempt as (select * from v_anken ) 
select * from tempt where gyomu_name like '%ひとり%' and nyuryoku_cd like '0187'

=>返回10行

查看：

CREATE OR REPLACE VIEW public.v_anken AS
 SELECT a.anken_seq,
    a.kokyaku_cd,
    b.kokyaku_name,
    b.kokyaku_ryaku,
    a.gyomu_seq,
    c.gyomu_cd,
    c.gyomu_name,
    fnc_getname('ANKEN_KBN'::character varying, c.anken_kbn::integer)::text  AS anken_kbn,
    fnc_getname('SEISAN_KBN'::character varying, c.seisan_kbn::integer)::text AS seisan_kbn,
    a.kotei_seq,
    d.kotei_kbn,
    a.shozoku_seq,
    a.pkg_yosan,
    COALESCE(g.jyuchu_yosan, a.pkg_yosan, NULL::numeric) AS yosan_kei,
    a.kaihatsu_kikan_from,
    a.kaihatsu_kikan_to,
    a.pkg_shokyaku_genka,
    a.pkg_shokyaku_yosan,
    a.nyuryoku_cd,
    concat_ws(' '::text, e.lastname, e.firstname) AS nyuryoku_name,
    a.nyuryoku_ymd AS nyuryoku_date,
    a.shonin_comment,
    a.shonin_cd,
    concat_ws(' '::text, f.lastname, f.firstname) AS shonin_name,
    a.shonin_ymd,
        CASE
            WHEN a.shonin_ymd IS NOT NULL THEN '済'::text
            ELSE ''::text
        END AS shonin_kbn,
    a.sagyo_kanryo_kbn,
    a.sagyo_kanryo_ym,
    a.biko,
    a.horyu_kbn,
    a.refix_time,
    a.inputtable_date,
    a.working_complete_report_checked_date,
    a.end_date
   FROM t_anken a
     JOIN v_kokyaku b ON a.kokyaku_cd = b.kokyaku_cd
     JOIN t_gyomu c ON a.gyomu_seq = c.gyomu_seq
     JOIN t_kotei d ON a.kotei_seq = d.kotei_seq
     JOIN employee e ON a.nyuryoku_cd::text = e.code::text
     LEFT JOIN employee f ON a.shonin_cd::text = f.code::text
     LEFT JOIN ( SELECT t_anken_jyuchu.anken_seq,
            sum(t_anken_jyuchu.gp_yosan) AS jyuchu_yosan
           FROM t_anken_jyuchu
          GROUP BY t_anken_jyuchu.anken_seq) g ON a.anken_seq = g.anken_seq;

ALTER TABLE public.v_anken
    OWNER TO postgres;

功能：

CREATE OR REPLACE FUNCTION public.fnc_getname(
    namekbn character varying,
    code integer)
    RETURNS character varying
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
BEGIN

     RETURN (SELECT name
               FROM t_name
              WHERE name_kbn = namekbn
                AND cd = code
            );

END;
$BODY$;

ALTER FUNCTION public.fnc_getname(character varying, integer)
    OWNER TO postgres;

当select from ..在哪里..。和。有时只返回1行，但返回超过1行。这会是什么问题呢？我甚至不知道该去哪里找。需要帮助。谢谢。

Answer 1

这看起来像是索引损坏。这个问题在REINDEX TABLE v_anken之后仍然存在吗？(如果它是一个视图，则对基础表重新编制索引。)

可能是硬件问题，也许您升级了操作系统上的C库而没有重新构建字符串上的索引，也可能是您在不同版本的操作系统上升级了备用系统。