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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 getting any 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks Tom & Charles.

-Sathesh

From: Charles Clavadetscher<mailto:clavadetscher@swisspug.org>
Sent: Wednesday, February 15, 2017 12:05 PM
To: 'Sathesh S'<mailto:sathesh.sundaram@hotmail.com>; 'pgsql-general'<mailto:pgsql-general@postgresql.org>
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