2

I have data in a table column like below:

host=0.0.0.0 port=5432 dbname=database_name user=pglogicaluser
host=0.0.0.0 port=5432 dbname=database_name2 user=pglogicaluser

I want to write a query to get the database names only like below:

database_name
database_name2

I could come up with something like:

select substring(column_name, '.+dbname=(.*)$') from table_name;

However, I couldn't figure how to stop the extraction before the user keyword starts.

2
  • Yes all the data is from one column. Mar 21, 2022 at 18:42
  • Is the database name UNIQUE?
    – Vérace
    Sep 3, 2023 at 11:48

2 Answers 2

3

you need to include a space after the regex group that acts as a delimiter to separate the user keyword from the text you want to match. To make the regex match in that case, an additional "any other character" is needed.

substring(column_name, '.+dbname=(.*) .*$') 
2
  • Thank you that worked. I run the below regex and it worked too. SUBSTRING (column_name , '(?:dbname=)(.*?)(?:user)' ) Mar 21, 2022 at 19:38
  • @SayadXiarkakh - performance will be terrible!
    – Vérace
    Sep 3, 2023 at 14:48
0

If this string always has the form

host=x port=y dbname=name user=z

then there's a far better (i.e. faster) way of doing what you want. I did the following (all of the code below is available here):

CREATE TABLE str
(
  pg_str TEXT
);

Populate it:

INSERT INTO str VALUES
('host=0.0.0.0 port=5432 dbname=database_name user=pglogicaluser'),
('host=0.0.0.0 port=5432 dbname=database_name2 user=pglogicaluser');

First we use the SPLIT_PART() function. Its signature is SPLIT_PART(string, delimiter, position) (manual)

So, we run:

SELECT
  SPLIT_PART(pg_str, ' ', 3)
FROM str;

Result:

split_part
dbname=database_name
dbname=database_name2

Next, we apply the REPLACE() (same manual page) function to remove the dbname= part above:

SELECT
  REPLACE(SPLIT_PART(pg_str, ' ', 3), 'dbname=', '') AS dbname
FROM str;

Result:

dbname
database_name
database_name2

Now, what you could do is something like this:

ALTER TABLE str ADD COLUMN db TEXT GENERATED ALWAYS AS 
(REPLACE(SPLIT_PART(pg_str, ' ', 3), 'dbname=', '')) STORED;

so, now you don't have to perform complex queries on the fly - every time you INSERT or UPDATE your pg_connection_str, the db field will be modified accordingly. There is a space penalty to be paid, but it's relatively small.

As a general rule of thumb, if it can be done without regexes, then do it without regexes!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.