Case Insensitive

Hi PG-General and Pgsql-Admin Can we achieve CASE INSENSITIVE in PostgreSQL? I mean, need below way postgres=# select * from emp; eid | ename -+--- 1 | aaa 2 | AAA (2 rows) *postgres=# select * from emp where ename='aaa';* * eid | ename* *-+---* * 1 | aaa* * 2 | AAA

Re: Case Insensitive

On Thu, 28 Mar, 2019, 4:20 PM Sridhar N Bamandlapally, < sridhar@gmail.com> wrote: > Hi PG-General and Pgsql-Admin > > Can we achieve CASE INSENSITIVE in PostgreSQL? > You are perhaps migrating from another RDBMS where this kind of feature is considered a feature. > I mean, need below way

Re: Case Insensitive

Or you can just use `ilike`: SELECT * FROM emp WHERE ename ilike 'aaa'; https://www.postgresql.org/docs/10/sql-select.html#SQL-WHERE cheers Ben On Thu, 28 Mar 2019 at 16:24, Sameer Kumar wrote: > > > On Thu, 28 Mar, 2019, 4:20 PM Sridhar N Bamandlapally, < > sridhar@gmail.com> wrote: > >

Re: Case Insensitive

On Thu, Mar 28, 2019 at 1:50 PM Sridhar N Bamandlapally < sridhar@gmail.com> wrote: > Hi PG-General and Pgsql-Admin > > Can we achieve CASE INSENSITIVE in PostgreSQL? > You can try *select * from emp where ename *ILIKE *'aaa';* > > I mean, need below way > > postgres=# select * from emp; >

Re: Case Insensitive

Am 28.03.19 um 09:20 schrieb Sridhar N Bamandlapally: Hi PG-General and Pgsql-Admin Can we achieve CASE INSENSITIVE in PostgreSQL? test=# create extension citext; CREATE EXTENSION test=*# create table emp (eid int, ename citext); CREATE TABLE test=*# insert into emp values (1, 'aaa'); INSER

Re: Case Insensitive

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. -- Angular momentum makes the world go 'round.

Re: Case Insensitive

> 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). Cheers, Stev

Re: Case Insensitive

On Thu, 28 Mar, 2019, 4:28 PM Andreas Kretschmer, wrote: > > > Am 28.03.19 um 09:20 schrieb Sridhar N Bamandlapally: > > Hi PG-General and Pgsql-Admin > > > > Can we achieve CASE INSENSITIVE in PostgreSQL? > > test=# create extension citext; > CREATE EXTENSION > test=*# create table emp (eid int,

Re: Case Insensitive

On Thu, 28 Mar, 2019, 4:33 PM 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 t

Re: Case Insensitive

With ILIKE or extension CITEXT, does it have any impact on Indexes, like not picking index ? ILIKE works only for operator LIKE not for operator = CITEXT seems some possibilities, Thanks Sridhar On Thu, Mar 28, 2019 at 2:07 PM Sameer Kumar wrote: > > > On Thu, 28 Mar, 2019, 4:33 PM Steve Atk

Re: Case Insensitive

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(en

Re: Case Insensitive

On 2019-03-28 09:20, Sridhar N Bamandlapally wrote: > Can we achieve CASE INSENSITIVE in PostgreSQL? Use the citext extension. In PostgreSQL 12, there will be support for case-insensitive collations. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Suppo

Re: Case Insensitive

> On Mar 28, 2019, at 9:08 AM, Ron wrote: > > 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)=upp

Re: Case Insensitive

Would this also select characters with diacritical marks? For example, eid | ename -+--- 1 | aaa 2 | AAA 3 | áäâ 4 | āåȧ — Andy > On Mar 28, 2019, at 4:26 AM, Ben Madin wrote: > > Or you can just use `ilike`: > > SELECT * FROM emp WHERE ename ilike 'aaa'; > > https://www.

Re: Case Insensitive

Am 28.03.19 um 09:33 schrieb Sameer Kumar: test=*# select * from emp where ename = 'aaa';   eid | ename -+---     1 | aaa     2 | AAA (2 rows) Ummm... Will it use an index (a BTree index)? test=# explain select * from emp where ename = 'aaa';

Re: plctl extension issue postgresql 11.2

Hi Adrian, I tried which command you gave but its not working.please check, [root@brurhenaaa001 postgres]# yum install postgresql11-tcl Loaded plugins: enabled_repos_upload, langpacks, package_upload, product-id, search-disabled-repos, subscription-manager rhel-7-server-eus-optional-rpms

Re: Case Insensitive

On 28/03/2019 11:10, Andy Anderson wrote: Would this also select characters with diacritical marks? For example, eid | ename -+--- 1 | aaa 2 | AAA 3 | áäâ 4 | āåȧ — Andy For that you need https://www.postgresql.org/docs/10/unaccent.html Tim Clarke Main: +44 (0)1376 503

Re: Case Insensitive

Andreas Kretschmer wrote on 03/28/2019 07:28:53 AM: > > > > Ummm... Will it use an index (a BTree index)? > > > > test=# explain select * from emp where ename = 'aaa'; > QUERY PLAN > - >  Seq Scan on emp  (cost=0.00..25.

Re: stale WAL files?

On Tue, Mar 26, 2019 at 09:50:37AM -0600, Rob Sargent wrote: > No, sorry I should have said that up front. We’re simple folk. What is the WAL position (LSN) where Postgres is writing to and what is the set of WAL segments in pg_wal (or pg_xlog if that's a server older than 10)? Please double-chec

Re: plctl extension issue postgresql 11.2

On 3/28/19 3:20 AM, Prakash Ramakrishnan wrote: Hi Adrian, I tried which command you gave but its not working.please check, First I posted the wrong package name previously. For the Tcl PL it should be: postgresql11-pltcl Still postgresql11-tcl is a valid package in the PGDG repos(it is fo

Re: Key encryption and relational integrity

Il 27/03/2019 07:42, Tony Shelver ha scritto: Not in Europe, but have worked a bit with medical records systems in the USA, including sharing across providers. The primary key of the u

Re: Data entry / data editing tools (more end-user focus).

On 3/27/19 11:49 PM, Tony Shelver wrote: Please reply to list also, more eyes on the the problem. Ccing list My take on below is since you are feeding a Website why not use Web technologies for your data entry. My language of choice is Python. I have done something similar to this(on small sca

Re: Key encryption and relational integrity

On 3/28/19 7:29 AM, Moreno Andreo wrote: Il 27/03/2019 07:42, Tony Shelver ha scritto: Not in Europe, but have worked a bit with medical records systems in the USA, including sharing across providers. The only other way to do it would be to store the encrypted key value in both user.id

Re: plctl extension issue postgresql 11.2

Adrian Klaver writes: > Still postgresql11-tcl is a valid package in the PGDG repos(it is for a > Tcl client library). It is trying to find postgresql11-tcl in the RHEL > repo not the PGDG repo. Yet packages have been installed from the PGDG > repo(from previous post) in the past: > postgresq

Re: Case Insensitive

Hi Sridhar, There are a few workarounds available, hope this will help you. 1) Use the citext extension 2) Use ILIKE instead of LIKE 3) Use Postgres lower() function 4) Add an index on lower(ename) Thanks & Regards, *Shreeyansh DBA Team* www.shreeyansh.com On Thu, Mar 28, 2019 at 1:50 PM Sridh

Re: stale WAL files?

On 3/28/19 7:30 AM, Michael Paquier wrote: On Tue, Mar 26, 2019 at 09:50:37AM -0600, Rob Sargent wrote: No, sorry I should have said that up front. We’re simple folk. What is the WAL position (LSN) postgres=# select * from pg_current_wal_flush_lsn();  pg_current_wal_flush_lsn ---

Re: Key encryption and relational integrity

Il 26/03/2019 18:08, Adrian Klaver ha scritto: On 3/26/19 9:08 AM, Moreno Andreo wrote: Il 26/03/2019 15:24, Adrian Klaver ha scritto: On 3/26/19 7:19 AM, Moreno Andreo wrote: Hello folks :-) Is there any workaround to implement key encryption without breaking relational integrity? This is

Re: Key encryption and relational integrity

Il 28/03/2019 15:45, Adrian Klaver ha scritto: On 3/28/19 7:29 AM, Moreno Andreo wrote: Il 27/03/2019 07:42, Tony Shelver ha scritto: Not in Europe, but have worked a bit with medical records systems in the USA, including sharing across providers. The only other way to do it would be to store

Aw: Re: Key encryption and relational integrity

> Yes, but to be GDPR compliant I _have_ to remove that link. As you > reported in an earlier email, they say that you can't link patient and > medication if not with an external resource. In this case we are linking > them without an external resource I REALLY doubt that. I believe you when

Aw: Re: Key encryption and relational integrity

> I believe you when you say "they say that ..." but I truly doubt that > GDPR intended to make data processing fully impractical. > > (I work in the medical field) In Germany, that is, which usually takes things to the extreme, for better or worse. Karsten

Timescale database is going towards emergency autovacuum

We are running timescale pg_prometheus with separating each services in schema. So in one database we have: > >> schema_a >view: metrics > tables: metrics_copy, metrics_labels, metrics_values > >> schema_b >view: metrics > tables: metrics_copy, metrics_labels, metrics_values > and

Re: software or hardware RAID?

Hi All Just would like to know conclusion here , What is best RAID method (Software Or Hardware) for Postgres DB and what level ? Thanks, Raj On Sat, Mar 23, 2019 at 3:12 PM Kenneth Marshall wrote: > On Sat, Mar 23, 2019 at 12:09:11PM +, Rory Campbell-Lange wrote: > > On 17/03/19, Rory

Re: software or hardware RAID?

On 28/03/19, Perumal Raj (peruci...@gmail.com) wrote: > On Sat, Mar 23, 2019 at 3:12 PM Kenneth Marshall wrote: > > On Sat, Mar 23, 2019 at 12:09:11PM +, Rory Campbell-Lange wrote: > > > On 17/03/19, Rory Campbell-Lange (r...@campbell-lange.net) wrote: > > > > We aren't sure whether to use so

Re: Key encryption and relational integrity

On 3/28/19 10:36 AM, Moreno Andreo wrote: Il 26/03/2019 18:08, Adrian Klaver ha scritto: On 3/26/19 9:08 AM, Moreno Andreo wrote: Il 26/03/2019 15:24, Adrian Klaver ha scritto: On 3/26/19 7:19 AM, Moreno Andreo wrote: Hello folks :-) Is there any workaround to implement key encryption withou

Re: plctl extension issue postgresql 11.2

On 3/28/19 9:07 AM, Prakash Ramakrishnan wrote: Hi Tom, Thanks for your big help i did yum install plctl and extension below path but still am getting error please find the below details, ==> psql psql (11.2) Type "help" for help. postgres=# \c "AIBE01PR" You are now connected to database "A

Re: plctl extension issue postgresql 11.2

Thanks, I'll check it out. On Thu, Mar 28, 2019, 20:26 Tom Lane wrote: > Adrian Klaver writes: > > Still postgresql11-tcl is a valid package in the PGDG repos(it is for a > > Tcl client library). It is trying to find postgresql11-tcl in the RHEL > > repo not the PGDG repo. Yet packages have bee

Re: plctl extension issue postgresql 11.2

Hi Tom, Thanks for your big help i did yum install plctl and extension below path but still am getting error please find the below details, ==> psql psql (11.2) Type "help" for help. postgres=# \c "AIBE01PR" You are now connected to database "AIBE01PR" as user "postgres". *AIBE01PR=# create exte

Re: plctl extension issue postgresql 11.2

On Thu, 28 Mar 2019 at 17:11, Prakash Ramakrishnan < prakash.ramakrishnan...@nielsen.com> wrote: > Hi Tom, > > Thanks for your big help i did yum install plctl and extension below path > but still am getting error please find the below details, > > ==> psql > psql (11.2) > Type "help" for help. >

Re: Key encryption and relational integrity

On 2019-03-28 18:36:40 +0100, Moreno Andreo wrote: > Il 26/03/2019 18:08, Adrian Klaver ha scritto: > > To me it would seem something like: > > > > Table medications > > id    user_id    med > > 1    sgkighs98    Medication > > 2    sghighs98    Ear check > > > > > > > > Table users > > id 

Re: Key encryption and relational integrity

On 2019-03-28 15:29:50 +0100, Moreno Andreo wrote: > here I'm trying to find a way so nobody can, without the use of the > application, match a patient with their clinical records (i.e. someone > breaking into the server -- data breach) I think it is very optimistic to assume that an intruder woul

Re: Subquery to select max(date) value

On Fri, 15 Feb 2019, Andrew Gierth wrote: select p.person_id, p.lname, p.fname, p.direct_phone, o.org_name, sq.* from people as p join organizations as o on p.organization_id=o.id -- OR WHATEVER cross join lateral (select a.next_contact from activities a

Re: Subquery to select max(date) value

On Thu, Mar 28, 2019 at 3:59 PM Rich Shepard wrote: > > After working with this query I modified it slightly to return only the > next_contact date: > > select p.person_id, p.lname, p.fname, p.direct_phone, p.active, > o.org_name, sq.* > from people as p > join organizations as o on p.org_i

Re: Subquery to select max(date) value

On Thu, Mar 28, 2019 at 3:59 PM Rich Shepard wrote: > select p.person_id, p.lname, p.fname, p.direct_phone, p.active, > o.org_name, sq.* > from people as p > join organizations as o on p.org_id = o.org_id > cross join > lateral > (select a.next_contact >

Re: Subquery to select max(date) value

On Thu, Mar 28, 2019 at 4:05 PM Ken Tanzer wrote: > > (And I don't think the one inside the lateral join is doing you any >> good). Try: >> > > ... > a.next_contact is not null > limit 1) sq > order by sq.next_contact DESC; > > The fact that the subquery h

Re: Subquery to select max(date) value

On Thu, Mar 28, 2019 at 4:07 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Mar 28, 2019 at 4:05 PM Ken Tanzer wrote: > >> >> (And I don't think the one inside the lateral join is doing you any >>> good). Try: >>> >> >> ... >> a.next_contact is not null >>

Re: Subquery to select max(date) value

On Thu, 28 Mar 2019, Ken Tanzer wrote: You need the ORDER BY in the outer join. Ken, I thought so. But, ... (And I don't think the one inside the lateral join is doing you any good). Try: ... a.next_contact is not null limit 1) sq order by sq.next_contact

Re: Subquery to select max(date) value

On Thu, 28 Mar 2019, David G. Johnston wrote: Yes, if you join the result on an ordered subquery to anything you no longer have a guaranteed order for the combined relation. David, This makes sense to me. select ... from ... join ... cross join lateral ... -- now add an order by for the top

Re: Subquery to select max(date) value

On Thu, Mar 28, 2019 at 4:14 PM Rich Shepard wrote: > On Thu, 28 Mar 2019, Ken Tanzer wrote: > > > You need the ORDER BY in the outer join. > > Ken, > > I thought so. But, ... > > > (And I don't think the one inside the lateral join is doing you any > good). > > Try: > > ... > > a.ne

Re: Subquery to select max(date) value

On Thu, Mar 28, 2019 at 4:21 PM Rich Shepard wrote: > This does not return the > desired order: > It returns something at least. If you put the output you get into a spreadsheet are you able to manually sort it the way you desire? David J.

Re: Subquery to select max(date) value

> "Rich" == Rich Shepard writes: Rich> Tried this and did not do it correctly. Should there be two Rich> 'order by', one in the sub-query, the other in the top-level Rich> query? Yes. Rich> This does not return the desired order: Rich> select p.person_id, p.lname, p.fname, p.direct_ph

Archival process of partition tables with filtering few rows from tables.

Hello Team, We are using PostgreSQL Version 9.6 and planning to archive our partition tables containing about 300 - 500 million rows . We have around ~ 50 partition tables to be archived to a new cold path PostgreSQL database , version 10.6. We have a requirement to filter few rows before exportin

Re: Archival process of partition tables with filtering few rows from tables.

On 29 March 2019 05:13:31 CET, github kran wrote: >Hello Team, > >We are using PostgreSQL Version 9.6 and planning to archive our >partition >tables containing about 300 - 500 million rows . We have around ~ 50 >partition tables to be archived to a new >cold path PostgreSQL database , version 10.6