35

In PostgreSQL 9.6 there will be a new version of pg_trgm, 1.2, which will be much better about this. With a little effort, you can also get this new version to work under PostgreSQL 9.4 (you have to apply the patch, and compile the extension module yourself and install it). What the oldest version does is search for each trigram in the query and take the ...


27

Do all queries have to be be in dictionary? No. Because only word stems (according to the used text search configuration) are in the index to begin with. But more importantly: No. Because, on top of that Full Text Search is also capable of prefix matching: This would work: SELECT id, subject FROM mailboxes WHERE tsv @@ to_tsquery('simple', 'avail:*') ...


22

The documentation often gives you an answer to such questions. Like in this case, too: The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar, and char respectively. The difference from the default operator classes is that the values are compared strictly character by ...


21

I am assuming data type text for the relevant columns. CREATE TABLE prefix (code text, name text, price int); CREATE TABLE num (number text, time int); "Simple" Solution SELECT DISTINCT ON (1) n.number, p.code FROM num n JOIN prefix p ON right(n.number, -1) LIKE (p.code || '%') ORDER BY n.number, p.code DESC; Key elements: DISTINCT ON is a ...


19

Worth mentioning that you installed the additional module pg_trgm, which provides the similarity() function. Similarity operator % Whatever else you do, use the similarity operator % instead of the expression (similarity(job_title, 'sales executive') > 0.6). Index support is bound to operators in Postgres, not to functions. To get the desired minimum ...


15

You can use unnest() like @dezso already mentioned, for instance with a LATERAL join (Postgres 9.3+) SELECT l.* FROM list l, unnest(array_field) a -- implicit lateral WHERE lower(a) LIKE '1234%'; You don't need any of this for the presented case. No materialized view at all. This query on the underlying tables is faster, because it can use an index: ...


12

Two factors are important for performance: Reduce the number of string operations. You may find it is possible to implement what you need using e.g. CHARINDEX and PATINDEX to find the start and end of groups, rather than performing very many REPLACE operations on the whole string each time. Use the cheapest collation that provides correct results. Binary ...


10

Yes. Function REPLICATE(): SELECT * FROM table WHERE account LIKE REPLICATE('[0-9]', 5) ;


10

I suggest this expression for query and index: SELECT * FROM tbl WHERE to_tsvector('simple', f_concat_ws(' ', country, city, street, house_nr, postcode)) @@ plainto_tsquery('simple', '22 Kärntner Wien'); Note the custom function f_concat_ws() above. That's because concat_ws() is only STABLE not IMMUTABLE. You need to create it first: CREATE OR REPLACE ...


9

Assessment In your last query, the bitmap index scan looking for 'hat' produces 307 hits. Postgres then runs a bitmap heap scan to filter merchants similar enough ( similarity(...) > 0.2), producing 12 rows. Your test is with 30K rows, so your real life query will produce around 300 times as many hits, 90k / 3.5k for the test case at hand. An additional ...


9

I suggest you provide explicitly the characters you want to be considered as "white space" and excluded to a regex: where name !~ '[ \t\v\b\r\n\u00a0]' Characters: \s white space (space, \r, \n, \t, \v, \f) ' ' space \t (horizontal) tab \v vertical tab \b backspace \r carriage return \n newline \f ...


9

The key word here is phrase search, introduced with Postgres 9.6. Use the tsquery FOLLOWED BY operator <-> or one of the related <N> operators. Or better yet, use the function phraseto_tsquery() to generate your tsquery. Quoting the manual, it ... produces tsquery that searches for a phrase, ignoring punctuation And: phraseto_tsquery behaves ...


8

This query on the system catalog creates the necessary DDL script: SELECT string_agg(format('DROP SCHEMA %I CASCADE;', nspname), E'\n') FROM pg_namespace WHERE nspname LIKE 'ceu_shard_test_merge_%'; Note the use of format() to escape identifiers if necessary. format() requires PostgreSQL 9.1+. Replace with quote_ident() in older versions. string_agg() ...


7

mysql> SHOW GLOBAL VARIABLES like 'ft_min_word_len'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | ft_min_word_len | 1 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> SELECT event.id AS eid, event.name AS ename FROM event WHERE MATCH(event.name) AGAINST('+W*' ...


7

The best way to handle your issue (and avoid SQL injection) is to pass in your user input as a variable. Since you are using a LIKE you can do something like this: CREATE TABLE #person (person_name nvarchar(50)) INSERT INTO #person VALUES (N'Bob'),(N'Bo''b'),(N'Bo‘b'),(N'Bo’b'),(N'Bo#b'),(N'Bo^b') DECLARE @user_input nvarchar(50) = 'Bo’b' SET @user_input ...


7

The key here is that Column_1, represents three possible values for the JOIN. So what you want to use is string_to_array() (so long as those values are comma-separated and can not themselves include a comma). Run this query, SELECT id_number, string_to_array(column_1, ',') AS column_1 FROM table_a; id_number | column_1 -----------+-----------...


7

My own solution, which is more of a workaround, consisted in specifying a character range that included the ] and using that range along with the other characters in the [ ] wildcard. I used a range based on the ASCII table. According to that table, the ] character is located in the following neighbourhood: Hex Dec Char --- --- ---- … 5A 90 Z 5B ...


6

Why? Because Postgres replace() is a standard SQL function that works the same as in other RDBMS. Example: replace() in SQL Server: Replaces all occurrences of a specified string value with another string value. While regexp_replace() is used to ... Replace substring(s) matching a POSIX regular expression. The handling of regular expressions is ...


5

The disadvantage is that you need to escape all characters of special significance in the pattern syntax. Following your previous question it seems the way you do the escaping affects the cardinality estimates. Testing on SQL Server 2008/2012 SELECT name /*Copying into a temp table as on 2012 this table is now in the readonly resource database*/ INTO #T ...


5

An easy approach would be to consider the array of letters corresponding to each word, and search inside that with the @> (contains) array operator. This works independantly of the letters positions as shown in the example from the manual, i.e. ARRAY[1,4,3] @> ARRAY[3,1] is true. This array can be easily obtained with regexp_split_to_array(name, ''). [...


5

Index usage with text_pattern_ops (as well as with the default operator class when using the C locale) depends on the binary representation of character data. citext stores original values with the case preserved, so there must be a problem with that ... Like you commented, the actual reason is burried in collation support. Either way, citext or text, you ...


5

One way of doing it would be to use LEAD. WITH T AS ( SELECT StudentID, GradeIssued AS g1, LEAD(GradeIssued, 1) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g2, LEAD(GradeIssued, 2) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g3, LEAD(GradeIssued, 3) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g4 ...


5

See if doing a binary collate fits what you need. Here is a quick test. USE Tempdb GO IF OBJECT_ID('PattMatch') IS NOT NULL BEGIN DROP TABLE PattMatch END GO CREATE TABLE PattMatch (COL1 NVARCHAR(50)) GO INSERT INTO PattMatch VALUES (nchar(46797)),(nchar(14843)),('ddddddd*'),('lettersand9999') GO DECLARE @Pattern nvarchar(50) = ...


5

Use substring() with a regular expression instead: substring(ls.attribute_actions_text FROM 'name="(.*?)"/>') The dot (.) matches any character, *? is the non-greedy quantifier for a sequence of 0 or more matches and the parentheses (()) mark the substring to be returned. Like your code, this selects the first string matching the pattern and does not ...


5

I think this is my favorite. create table t (id int,str varchar(100)); insert into t (id,str) values (1,'a.b.c.d.e'),(2,'xxx.yyy.zzz'); ROWS select id ,array_to_string((string_to_array(str,'.'))[i:],'.') from t,unnest(string_to_array(str,'.')) with ordinality u(token,i) ; +----+-----------------+ | id | array_to_string | +----+----...


5

Try this: update table_b set id_number = (select id_number from table_a where table_a.Column_1 like '%' || table_b.Column_1 || '%' OR table_a.Column_1 like '%' || table_b.Column_2 || '%' limit 1) ; It can be another solutions by converting Column_1 into an array, but this is so clear....


5

There exist a multitude of space characters as per the following Unicode listing: Unicode Characters in the 'Separator, Space' Category I would extend the where clause in ypercubeᵀᴹ's answer to: (edit: added \ at the beginning of the string] where name !~ '[\u0020\u00A0\u1680\u2000\u2001\u2002\u2003\u2004\u2005\u2006\u2007\u2008\u2009\u200A\u202f\u205f\...


5

Without knowing anything about what your actual scenario looks like, I'd perhaps do something like: USE tempdb; IF OBJECT_ID(N'dbo.Strings', N'U') IS NOT NULL DROP TABLE dbo.Strings; CREATE TABLE dbo.Strings ( TargetString varchar(46) NOT NULL ); IF OBJECT_ID(N'dbo.Matches', N'U') IS NOT NULL DROP TABLE dbo.Matches; CREATE TABLE dbo.Matches ( ...


5

Perhaps you are over-complicating this by focusing too much on wanting a number. Take a step back for a moment. What you actually want is a substring without any digits on either side of it. The only way a number could be part of a larger number is to have at least 1 digit on either side of it, right? So as long as you only pass in numbers, then this ...


4

Wrap you expression in a CASE statement: SELECT * FROM tbl WHERE CASE $1 WHEN '*' THEN TRUE ELSE some_tsvector_column @@ $1::tsquery END; This way you can pass '*' to disable the filter or any other valid tsquery string to actually filter.


Only top voted, non community-wiki answers of a minimum length are eligible