Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9R1s-0005Gi-DH for pgsql-general@arkaria.postgresql.org; Thu, 28 Mar 2019 09:08:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h9R1r-0008DC-42 for pgsql-general@arkaria.postgresql.org; Thu, 28 Mar 2019 09:08:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9R1q-0008Ct-QC for pgsql-general@lists.postgresql.org; Thu, 28 Mar 2019 09:08:54 +0000 Received: from mail-ot1-x32a.google.com ([2607:f8b0:4864:20::32a]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9R1o-0000pd-LH for pgsql-general@lists.postgresql.org; Thu, 28 Mar 2019 09:08:54 +0000 Received: by mail-ot1-x32a.google.com with SMTP id j10so11152799otq.0 for ; Thu, 28 Mar 2019 02:08:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-transfer-encoding:content-language; bh=d9tpn7vF6ImfBSpPiKzq0nw7kyb9hS0sEM9VQ8IKtr4=; b=ZoQxv72z2IkB9YG6+rodXpzlOqrk38xN/RacF8ekTsjltNm+WGaehkjdJpmZ4vLupW Rk8WEaOaKyRa7YhSoTq7ya7JCvpltlU+1N39TYLANd6caJFWaCBNhdvN67RimuKX55O/ lTANUHIG2A7w1JPWaNWdlMxzNnKcY1xb3Y4OJczgXhT2YlwoJtwFjOMJ1I7FYsWsUu5a +vJ+Pss95ebqFWz+d2J9scnOT816AK00YtCsthNZAPXc6fsp/29YsVLnMIuHR4rImq2D KMf+Nt1y8cwGqjm4/oPwzV142gBjDQVjjXir8VLRluWJsI6TCYyXEnAtSlQBFuoL+CN9 ZZCw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-transfer-encoding :content-language; bh=d9tpn7vF6ImfBSpPiKzq0nw7kyb9hS0sEM9VQ8IKtr4=; b=aL9LkcDawn6+bKMS79he7sfoat8bYwtqt9tXSEd8CK+pYk8SWU9NU/ReiPDEI33Lhi OUMyvM5su68VKB22quXJEbX4g8xW4NVi3axNZVJFhKORNCBQbLDy6eEiIjjCafxe5FH8 I01MLVMqXzAr2JscPyzLqF3Ff6JmFQedCIapScGAN0FyhIFhp9VOyaKFPrg0/kg0jwdz 5W1J/z6WX+iPNynmGC9G8bIHqwDFvz8emgDHHCx/sDExvbfwYWOS3/dPhBDEoxBqS1GQ Ks++fuPGdozwJ9f8YB/+Tf5pxZi84Pj1+b0RYdInKPfrVWouQ5G4WR3lPTd0tssWhg2W Pfvg== X-Gm-Message-State: APjAAAV4+hyaCJTq14jE2WRhpExrFdUoHTsgzR9lmZFpYyb2ka3lFIrk /+kF9ErufZydFMS3XR6enEWRQl0oVpOSrQ== X-Google-Smtp-Source: APXvYqyfiYfqukm3ky4U4JYeGKZw//cqYWjapN/L9QXMGsLPSeXY8MVILlFi3pCluu9B8/yAXsZzJA== X-Received: by 2002:a9d:618d:: with SMTP id g13mr28790755otk.254.1553764130167; Thu, 28 Mar 2019 02:08:50 -0700 (PDT) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id c136sm3117626oih.14.2019.03.28.02.08.49 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 28 Mar 2019 02:08:49 -0700 (PDT) Subject: Re: Case Insensitive To: pgsql-general@lists.postgresql.org References: <150586A3-A8F4-4B80-9A4B-310DE265B4B3@blighty.com> From: Ron Message-ID: Date: Thu, 28 Mar 2019 04:08:48 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.4.0 MIME-Version: 1.0 In-Reply-To: <150586A3-A8F4-4B80-9A4B-310DE265B4B3@blighty.com> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 3/28/19 3:33 AM, Steve Atkins wrote: > >> On Mar 28, 2019, at 8:29 AM, Ron wrote: >> >> On 3/28/19 3:23 AM, Sameer Kumar wrote: >> [snip] >>> You can write a query with upper function: >>> >>> select * from emp where upper(ename)=upper('aaa'); >> That's a guaranteed table scan. > Unless you have an index on upper(ename). Are you sure?  I thought the lpart had to be immutable for the query optimizer to decide to use an index (and upper(ename) is mutable). -- Angular momentum makes the world go 'round.