Define a full-text search index using raw SQL concatenation
Hello, I have a
It works fine but I'd like the index definition to refer to more than one field, for example:
But I keep getting a 42601 postgres error:
posts schema that looks like this:
export const post_schema = pgTable(
'posts',
{
id: serial('id').primaryKey(),
slug: text('slug').notNull(),
heading: text('heading').notNull(),
subheading: text('subheading').notNull(),
image: text('image').notNull(),
caption: text('caption').notNull(),
author_id: integer('author_id'),
tags: text('tags').array().notNull(),
article_html: text('article_html').notNull(),
article_text: text('article_text').notNull(),
published: boolean('published').default(false).notNull(),
date_published: timestamp('date_published'),
updated_by: text('updated_by').notNull(),
created_at: timestamp('created_at').defaultNow().notNull(),
updated_at: timestamp('updated_at').$onUpdate(() => new Date())
},
(table) => [
index('posts_search_index').using(
'gin',
sql`to_tsvector('english', coalesce(${table.article_text}, ''))`
)
]
);
export const post_schema = pgTable(
'posts',
{
id: serial('id').primaryKey(),
slug: text('slug').notNull(),
heading: text('heading').notNull(),
subheading: text('subheading').notNull(),
image: text('image').notNull(),
caption: text('caption').notNull(),
author_id: integer('author_id'),
tags: text('tags').array().notNull(),
article_html: text('article_html').notNull(),
article_text: text('article_text').notNull(),
published: boolean('published').default(false).notNull(),
date_published: timestamp('date_published'),
updated_by: text('updated_by').notNull(),
created_at: timestamp('created_at').defaultNow().notNull(),
updated_at: timestamp('updated_at').$onUpdate(() => new Date())
},
(table) => [
index('posts_search_index').using(
'gin',
sql`to_tsvector('english', coalesce(${table.article_text}, ''))`
)
]
);
(table) => [
index('posts_search_index')
.using(
'gin',
sql`to_tsvector('english', coalesce(${table.heading}, '')) ||
to_tsvector('english', coalesce(${table.subheading}, '')) ||
to_tsvector('english', coalesce(${table.article_text}, '')) ||
to_tsvector('english', array_to_string(${table.tags}, ' '))`
),
]
(table) => [
index('posts_search_index')
.using(
'gin',
sql`to_tsvector('english', coalesce(${table.heading}, '')) ||
to_tsvector('english', coalesce(${table.subheading}, '')) ||
to_tsvector('english', coalesce(${table.article_text}, '')) ||
to_tsvector('english', array_to_string(${table.tags}, ' '))`
),
]
error: syntax error at or near "||". Any pointers as to how I can make the concatination work? Thanks!0 Replies