Substring a multiline with PostgreSQL

Question

I have one text column that have this type of value

---
cart_id: 23332251
id: 1824
push: 
key: 
last_checked_in_at: 2019-03-04 10:39:53.946659000 Z
other_field: hello

I want to have a column with only last_checked_in_at and extract the value as 2019-03-04 10:39:53.946659000

I tried

SELECT substring(object FROM '.*last_checked_in_at: (.*) Z') AS last_checkin_at, *
FROM versions;

But I get the begging of the field but it never stop and take the rest of the lines below.

I tried substring(object FROM '.*last_checked_in_at: (.*) Z$') but I get NULL. I am on PostgreSQL 10.

I succeed by having a dedicated regex substring(object FROM '(\d*-\d*-\d* \d*:\d*:\d*\.\d*)') but I would to understand why it is not working with the one bellow. — Mio
– Mio, Commented Mar 5, 2019 at 16:01

unutbu · Accepted Answer · 2019-03-06 11:43:27Z

1

Given

CREATE TABLE test (
    object text
);
INSERT INTO test VALUES
($$---
cart_id: 23332251
id: 1824
push: 
key: 
last_checked_in_at: 2019-03-04 10:39:53.946659000 Z
other_field: hello$$);

the SQL

SELECT substring(object FROM '.*last_checked_in_at: (.*) Z') AS last_checkin_at, * FROM test;

yields

+-------------------------------+-----------------------------------------------------+
|        last_checkin_at        |                       object                        |
+-------------------------------+-----------------------------------------------------+
| 2019-03-04 10:39:53.946659000 | ---                                                +|
|                               | cart_id: 23332251                                  +|
|                               | id: 1824                                           +|
|                               | push:                                              +|
|                               | key:                                               +|
|                               | last_checked_in_at: 2019-03-04 10:39:53.946659000 Z+|
|                               | other_field: hello                                  |
+-------------------------------+-----------------------------------------------------+

So substring(object FROM '.*last_checked_in_at: (.*) Z') works as desired. If you don't want the second column, use

SELECT substring(object FROM '.*last_checked_in_at: (.*) Z') AS last_checkin_at FROM test;

edited Mar 6, 2019 at 11:43

answered Mar 5, 2019 at 18:07

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mio Over a year ago

But it is the case?

unutbu Over a year ago

My mistake. I tested .*last_checked_in_at: (.*) Z and I think it should work as desired. I've edited the post above accordingly.

unutbu Over a year ago

If this is not the behavior you are seeing with your actual data, then please post SELECT object::bytea FROM versions LIMIT 3. Given the values as bytes we might be able to reproduce the behavior you are seeing. We would be able to see if there is a non-visible character that is not accounted for in the regex, for example.

Collectives™ on Stack Overflow

Substring a multiline with PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related