87238

How to index a varchar array in Postgresql using array_to_string?

Question:

I'm looking to set an index in a varchar array column in postgresql 9.3. I was told to set it using array_to_string(col) but I don't really understand how this works. I came up with the following statement:

CREATE INDEX CONCURRENTLY rtb_id_search ON sites USING GIN(array_to_string(rtb_id, ''));

However, postgresql complains with:

ERROR: functions in index expression must be marked IMMUTABLE

Answer1:

What operations do you would to accelerate? GIN indes supports array directly:

create table foo(a text[]); create index on foo using gin (a); set enable_seqscan to off;

There can be some issues because not all array operators are supported by index. But almost it is.

postgres=# explain select * from foo where a @> ARRAY['a']; ┌────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞════════════════════════════════════════════════════════════════════════╡ │ Bitmap Heap Scan on foo (cost=8.05..18.20 rows=7 width=32) │ │ Recheck Cond: (a @> '{a}'::text[]) │ │ -> Bitmap Index Scan on foo_a_idx (cost=0.00..8.05 rows=7 width=0) │ │ Index Cond: (a @> '{a}'::text[]) │ └────────────────────────────────────────────────────────────────────────┘ (4 rows)

Answer2:

create function string_array_to_string(text[], text, text) returns text as $$ select array_to_string($1, $2, $3) $$ language sql cost 1 immutable; create index concurrently sites_rtb_ids on sites using gin (string_array_to_string(rtb_ids, ' ', ' ') gin_trgm_ops);

This is the way to create the index. The function used needs to be marked immutable.

Recommend

  • choosing nodes color according to their name
  • How to call a Java method while deploying a WAR [duplicate]
  • Gin - Go lang How to use Context.Request.Body and retain it?
  • Creating multicolumn index in PostgreSQL, containing both scalar and array columns
  • Could not find the gcm.jar error when creating App Engine backend => suggests installing deprecat
  • Anonymous functions and Maps in Scala
  • Is there ever a reason to use is versus as? [duplicate]
  • Observable.forkJoin() TS2322 error after updating to TypeScript 2.4.1 and Rxjs 5.4.2
  • Insert Path of a file with \\\\ in mysql using java
  • Disable check for override in gcc
  • Azure Diagnostic is not saving logs in azure tables
  • Subversion reverting resolved file
  • Linux command line : edit hacked index files
  • pygame.init() shows as undefined variable after installing Pygame
  • @tailrec why does this method not compile with 'contains a recursive call not in tail position&
  • Javascript Array, Object, Date not defined
  • Why are YouTube videos using 'youtube.com/v' not loading
  • NUnit 3.0 TestCase const custom object arguments
  • Plotting line graph with factors in R
  • Web.config system.webserver errors
  • Unable to get column index with table.getColumn method using custom table Model
  • How can I extract results of aggregate queries in slick?
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Word Open XML Mail Merge
  • Replace value with Factor in r data.table
  • Database structure design with variable amounts of fields
  • FFmpeg Conversion Error
  • Submit form in a displaytag pagination
  • output of program is not same as passed argument
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Statically linking a C++ library to a C# process using CLI or any other way
  • Delete MySQLi record without showing the id in the URL
  • Why winpcap requires both .lib and .dll to run?
  • Unanticipated behavior
  • Comma separated Values
  • Hits per day in Google Big Query
  • Trying to get generic when generic is not available
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass