Обсуждение: [GENERAL] Get tables ending with numbers
Hi All,
Im trying to get tables ending with numbers (last 8 characters should be numbers).
For example: I have the tables "test_20160215" and "test_20160131" and "test_1". When i run the below sql im not getting any output.
Select relname from pg_class where relname like '%[0-9]'
Can someone please give some idea on this.
Thanks,
Sathesh
Hello Sathesh > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sathesh S > Sent: Mittwoch, 15. Februar 2017 07:17 > To: pgsql-general <pgsql-general@postgresql.org> > Subject: [GENERAL] Get tables ending with numbers > > Hi All, > > Im trying to get tables ending with numbers (last 8 characters should be numbers). > > For example: I have the tables "test_20160215" and "test_20160131" and "test_1". When i run the below sql im not > getting any output. > > Select relname from pg_class where relname like '%[0-9]' You should use an operator for regexp: CREATE TABLE test_20160215 (id integer); CREATE TABLE SELECT relname FROM pg_class WHERE relname ~ '[0-9]'; relname --------------- [snip] test_20160215 [snip] Or SELECT relname FROM pg_class WHERE relname ~ 'test_[0-9]+$'; relname --------------- test_20160215 (1 row) Regards Charles > Can someone please give some idea on this. > > Thanks, > Sathesh >
Sathesh S <sathesh.sundaram@hotmail.com> writes: > Im trying to get tables ending with numbers (last 8 characters should be numbers). > For example: I have the tables "test_20160215" and "test_20160131" and "test_1". When i run the below sql im not gettingany output. > Select relname from pg_class where relname like '%[0-9]' > Can someone please give some idea on this. You're confusing SQL LIKE patterns with POSIX regexp patterns. "%" is a wildcard only in the former; "[...]" is special only in the latter. (The great thing about standards is there are so many to choose from :-() A correct implementation of your stated requirement is where relname ~ '[0-9]$' or if you want to be picky about "last 8 characters" you could do where relname ~ '[0-9]{8}$' See https://www.postgresql.org/docs/current/static/functions-matching.html regards, tom lane
Thanks Tom & Charles.
-Sathesh
From: Charles Clavadetscher
Sent: Wednesday, February 15, 2017 12:05 PM
To: 'Sathesh S'; 'pgsql-general'
Subject: RE: [GENERAL] Get tables ending with numbers
Hello Sathesh
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sathesh S
> Sent: Mittwoch, 15. Februar 2017 07:17
> To: pgsql-general <pgsql-general@postgresql.org>
> Subject: [GENERAL] Get tables ending with numbers
>
> Hi All,
>
> Im trying to get tables ending with numbers (last 8 characters should be numbers).
>
> For example: I have the tables "test_20160215" and "test_20160131" and "test_1". When i run the below sql im not
> getting any output.
>
> Select relname from pg_class where relname like '%[0-9]'
You should use an operator for regexp:
CREATE TABLE test_20160215 (id integer);
CREATE TABLE
SELECT relname FROM pg_class WHERE relname ~ '[0-9]';
relname
---------------
[snip]
test_20160215
[snip]
Or
SELECT relname FROM pg_class WHERE relname ~ 'test_[0-9]+$';
relname
---------------
test_20160215
(1 row)
Regards
Charles
> Can someone please give some idea on this.
>
> Thanks,
> Sathesh
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sathesh S
> Sent: Mittwoch, 15. Februar 2017 07:17
> To: pgsql-general <pgsql-general@postgresql.org>
> Subject: [GENERAL] Get tables ending with numbers
>
> Hi All,
>
> Im trying to get tables ending with numbers (last 8 characters should be numbers).
>
> For example: I have the tables "test_20160215" and "test_20160131" and "test_1". When i run the below sql im not
> getting any output.
>
> Select relname from pg_class where relname like '%[0-9]'
You should use an operator for regexp:
CREATE TABLE test_20160215 (id integer);
CREATE TABLE
SELECT relname FROM pg_class WHERE relname ~ '[0-9]';
relname
---------------
[snip]
test_20160215
[snip]
Or
SELECT relname FROM pg_class WHERE relname ~ 'test_[0-9]+$';
relname
---------------
test_20160215
(1 row)
Regards
Charles
> Can someone please give some idea on this.
>
> Thanks,
> Sathesh
>