Menu
If yes, pls provide an example for lookbehind or an alternative.
I'm trying to extract the sequence name without '
select table_name,
column_name,
regexp_replace(substring(column_default from '''.*(?='')'),'''','','g') as sequence
FROM information_schema.columns
864
PostgreSQL employs Regular Expressions to get around pattern matching. In this article, we will learn about PostgreSQL Regex. PostgreSQL uses POSIX or “Portable Operating System Interface for Unix” regular expressions, which are better than LIKE and SIMILAR TO operators used for pattern matching.
The good news is that you can use lookbehind anywhere in the regex, not only at the start.
The lookbehind asserts that what immediately precedes the current position is a lowercase letter. And the lookahead asserts that what immediately follows the current position is an uppercase letter.
The % operator lets you compare against elements of an array, so you can match against any part of the name.
As of 9.6, it does accept positive lookbehinds using (?<=re), documentation here.
173
It didn't support it at the sql level last I checked, but you can use plperl to work around the limitation if absolutely necessary. (Heavy regexing generally doesn't belong at the DB level, though...)
In your particular example, consider using a negative class instead: [^'] (escape it as needed), or a non-greedy wildcard: .*?.
Adding to this re your specific question, unless you actually create your sequence manually its name will always be:
tablename_colname_seq
Also FWIW, the following two defaults have a different behavior if you use multiple schemas and search paths in your app:
nextval('foo'::regclass) -- find foo once
nextval('foo'::text) -- find foo each time
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
