I am using Postgresql 9.3 on an Ubuntu 12.4 machine.
I've only recently started studying database and database design, and I am to create a database that includes text that will later be searched through. I've read up on tsvectors and stuff better than WHERE column LIKE 'query'
, but I don't have much experience with it. I am thinking of building a table where one column is of type text[]
, and another one will be the tsvector build from the text. I will use and array of text because some of the text will be displayed to the user with different formatting etc. I have never worked with array columns before. My questions are:
- Is using a text array a good idea at all in this case?
- Will the text array work well with the tsvectors?
- Will using
text[]
compromise my performance? - Are there any pitfalls I should stay alert for?
Thanks for the attention, and this is my first post ever here, so if I messed up on this question do tell me. P.S.: I've searched this topic here beforehand, I hope I am not creating a redundant question. P.P.S.: Not a native speaker, sorry for the weird language.
EDIT: Thanks for the answers! The texts in the array won't be of fixed size. They will range from 6 ~ 20 lines, but they need to be editable, and some of the texts inside an array will be formatted in a different manner when displayed to the user. I'm not using multiple text
columns because some of the entries in the table will have a single, two, or more elements inside the array. I know how each element will be formatted because every row will also have a "type" column, which will define it's formatting.
tsv = to_tsvector(array_agg(txt_array, ''))
. If you would use diffent text columns (let's say title, headline, body), you'd just computetsv = to_tsvector(coalesce(title, '') || ' ' || coalesce(headline,'') || ' ' || coalesce(body, ''))
.