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.
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=(.*) .*$')
SUBSTRING (column_name , '(?:dbname=)(.*?)(?:user)' )
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!
UNIQUE?