added a space which gave better
results:
to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));
On Fri, Apr 17, 2009 at 1:04 AM, Tom Lane wrote:
> Rick Schumeyer writes:
> > I want to be able to search a list of articles
I want to be able to search a list of articles for title words as well as
author names. I understand how to do the title words with the full text
searching. But I'm not sure the best strategy for the names. The full text
parser "parses" the names giving undesirable results.
For example,
select
Steve Crawford wrote:
BTW, why not upgrade to the latest version?
Cheers,
Steve
Mostly because its not my server :-) I've suggested that, we'll see.
I appreciate the comments regarding type casting. I'll be sure to look
out for that.
---(end of broadcast)
Tom Lane wrote:
Rick Schumeyer <[EMAIL PROTECTED]> writes:
I'm working with someone who is about to upgrade from (I believe) pg 7.4
to pg 8.1.
What would be the best resource to read to look for "gotchas"? Release
notes? At the moment, we don't care
I'm working with someone who is about to upgrade from (I believe) pg 7.4
to pg 8.1.
What would be the best resource to read to look for "gotchas"? Release
notes? At the moment, we don't care about performance problems, only
things that might break.
---(end of broa
I've read the earlier threads on whether to store images in the database
or filesystem. I think I understand the pros and cons of each method,
but I have a question on a specific use case.
Let's say I have a web app, and I want to display the images in a web page.
a) if the images are in the
I'm developing an application that will be used by several independent
customers. Conceptually, the data from one customer has no relation at
all to another customer. In fact, each customer's data is private, and
you would never combine data from different customers. I'm trying to
decide whe
When you say "reconfigure db" does this mean "recreate all your tsearch
indexes"?
Joshua D. Drake wrote:
Rick Schumeyer wrote:
I have a database running under pg 8.1.4 that uses tsearch2. I am
upgrading to pg 8.2.4. I dumped the pg 8.1.x database and tried to
install i
I have a database running under pg 8.1.4 that uses tsearch2. I am
upgrading to pg 8.2.4. I dumped the pg 8.1.x database and tried to
install it in pg 8.2.4. This does not seem to work.
Is there a procedure for this that someone can point me to?
---(end of broadcast)-
Actually, the situation is slightly more complicated. It's more like I
have tables A1, A2, and A3 each of which must have a corresponding row
in B. So each of A1, A2 and A3 has a BEFORE INSERT trigger that creates
a row in B and sets a FK in A1 (or A2 or A3). So I can't just use the
same PK
trigger, since it does not receive a result from the
database...until reload is called.
At least I assume that is correct.
Michael Glaesemann wrote:
On May 22, 2007, at 21:21 , Rick Schumeyer wrote:
The problem is that the new A object that rails gets does not see the
effect of the trigger
I'm using Ruby on Rails and have two tables, A and B. Every row in A
needs a corresponding row in B. A also contains a FK pointing to B.
I created a before insert trigger on A that inserts a new row in B, and
sets the FK in A. This seems to be running fine.
The problem is that the new A ob
Has anyone here used a postgres array with Rails? If so, how?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
I'm writing an article based on my experience setting up a ruby-on-rails
server for a web apps class I'm teaching. My target audience is people
who have a clue,
but are not gurus at the wide collection of software necessary to get a
server running.
The in-work article is at http://www.schumey
I'm developing a system using Ruby on Rails (with ActiveRecord) and
postgres. (Although I think my question is still relevant for, say,
java with hibernate.)
I have two classes (tables): users and employees. A user is an account
that can logon to the system, while an employee is...umm...an
This may be bad design on my part, but...
I have three tables of interest...Account, Employee, and
AccountEmployeeRelation. There is a many-to-many relationship between
accounts and employees. The join table also contains a column
indicating what role the employee plays on this account.
My
load the result of
running pgdump on the 8.1 database, and ignore the errors?
Oleg Bartunov wrote:
Rick,
did you load tsearch2 itself into your database ?
Oleg
On Thu, 7 Dec 2006, Rick Schumeyer wrote:
I am trying to copy a database that uses tsearch2 from pg8.1 to pg8.2.
All I did to
I am trying to copy a database that uses tsearch2 from pg8.1 to pg8.2.
All I did to install tsearch2 was cd to the contrib/tsearch2 directory,
then make, make install.
I then dumped the database from pg8.1 and used psql -f filename db to
load in into pg8.2.
Attempting a query gives an error
Thanks for the suggestion...it needed only one small change:
update tsubset set k = t.k from t where t.f=tsubset.f;
Thanks!
Alban Hertroys wrote:
Rick Schumeyer wrote:
foreach f in tsubset
update tsubset set k=(select k from t, tsubset where t.f=f);
end
Can this be done with one SQL
I think this can be done with one SQL statement, but I'm not sure.
I have two tables: table t contains key k, another field f, and a bunch
of other stuff.
In a poor design decision, table tsubset contains a small number of
"pointers" to t. I should have used the k column; instead I used the
Albe Laurenz wrote:
My database locale is en_US, and by default my databases are UTF8.
My application code allows the user to paste text into a box and
submit
it to the database. Sometimes the pasted text contains non UTF8
characters, typically the "fancy" forms of quotes and apostr
My database locale is en_US, and by default my databases are UTF8.
My application code allows the user to paste text into a box and submit
it to the database. Sometimes the pasted text contains non UTF8
characters, typically the "fancy" forms of quotes and apostrophes. The
database does not
I am transferring a database from a system where I am a pg superuser to
one where I am not.
The database uses tsearch2. I am unable to install any of the
functions. For example:
CREATE FUNCTION gtsvector_in(cstring) RETURNS gtsvector
AS '$libdir/tsearch2', 'gtsvector_in'
LANGUAGE c ST
To date I have always used pg on a system where I had pg superuser status.
I'm trying to move a database from such a system to one where I am just
a user, and I'm having a couple of problems.
The first is, the output of pg_dump has a lot of lines like:
ALTER FUNCTION some_function OWNER TO ric
I confess to having once written code that prints something like
"Items # 1 to 10 of 45"
by using select count(*) from t where condition; and select * from t
where condition limit 10 offset x;
I now know this is "bad", I guess because of the count() and the offset.
So what is the preferred way
mandprompt...my initial guess is they do not support
my*** ?
-Original Message-
From: Joshua D. Drake [mailto:[EMAIL PROTECTED]
Sent: Friday, September 29, 2006 5:05 PM
To: Rick Schumeyer
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg web hosting with tsearch2?
Rick Schumeyer
I hope pg-general is the correct forum for this question…if
not please let me know the correct location.
I have a pg application that uses tsearch2. I would
like to move this application off my local machine and onto a web host
somewhere. I have some questions regarding this:
1)
ong about this?
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Andrew J. Kopciuch
> Sent: Wednesday, November 23, 2005 12:08 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] tsearch2: more than one ind
Is there something in tsearch2 that prevents more than one
index per table?
I would like an index on field A, and a separate index on
field B.
The index builds fine for A, but gives an error for B.
The error text is
ERROR: could not find tsearch config by locale
The code below
I have a database in pg 8.0.4 that is encoded as UNICODE. Somehow,
some “strange” characters made it in there. Mostly, several
instances of ‘ (apostrophe) are really some extended character. They
display in a text editor as \222 (or something similar). I’m not
sure how that happened, bu
I’m trying to install the version of tsearch2
distributed with pg 8.0.1. “make” and “make install”
runs with no apparent problems, but “make installcheck” fails.
Looking at the diffs, I would guess that the differences are
inconsequential. But I would like to be sure before proceeding.
At the suggestion of several
people, I have increased the
default settings in postgresql.conf
before continuing my
postgresql vs mysql
performance tests.
To date, I have only been
loading a (roughly) million-row
file, creating indexes
during the load, running a vacuum analyze,
and a
That site produces some sort of php error.
I don't suppose this information is available elsewhere?
> Stop now. I've not looked at your test results, and frankly there is no
> point. As it ships, PG should run fine on a small corner of an old
> laptop. It will not perform well with any sort of se
Below are some PRELIMINARY results in comparing the performance of pgsql and
mysql.
These results are for a single process populating a table with 934k rows,
and then performing some selects. I also compared the effect of creating
indexes on some of the columns.
I have not yet done any testing
I’m interested in comparing the performance of postgresql
and mysql
on various combinations of user loads and database sizes.
I have seen
a few statements to the effect of “mysql is faster for
small, low use
applications, but postgresql is better for bigger stuff”.
I would like
to run
I tried that, but I get a "...violates foreign-key constraint" error.
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Bruce Momjian
> Sent: Friday, February 25, 2005 6:23 PM
> To: Rick Schumeyer
> Cc: &
I suspected that might be part of the answer.
Would some combination of triggers work instead? I've played
with those too, but without success.
>
> This is an ancient gotcha: as soon as you delete the book row, there is
> no longer any such entry in the bookview view ... and "old.id" is
> effe
I have two related tables, “item”
and “book”. I have defined
a view, “bookview”
that contains fields from item and book.
My goal was to have all
inserts, updates, and deletes performed
on bookview rather than on
the tables directly. I was able
to do this with ON INSERT
and ON UPDAT
I know how to create a rule to delete from a view. But
I can’t
figure this one out.
Let’s say I have two tables, t1 and t2, and a view tview
that
uses columns from both t1 and t2. I don’t want users
to
have access to t1 and t2, only to tview. I have a rule
that
handles inserts an
I have a question about whether or not I need to do locking to a pg
table being accessed from a php application.
Let's say two users select rows from the table and display them in
their browser. User A clicks on row 1 to edit it. Since this is php,
so far it just selects the current values from
I think this is a common task, but I’m not sure how to
do it.
I want to run a query that can return many records, display
them
10 at a time, and be able to go forward/backward in the
list. I’m
not concerned about the list changing after the initial
query.
I’m accessing this via a
I think I’ve found a bug in PG 8.0 that occurs while
copying from CSV files.
I checked the bugs list but didn’t see anything
similar.
This occurs when reading a CSV file where one of the ‘text’
fields has
a blank line. I included an example that shows the
problem below.
I found that
Is there a way to import mdb files from Access into pg?
I found a web page for mdbtools but I can’t get it to
compile.
I hope this is a valid question for this list.
I want to install PG 8.0 on a RHEL system.
I have been told our security policy says
that only packages provided by red hat can be installed.
Does anyone know how long until PG8.0 would
be available as a RHEL rpm?
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Mike Rylander
> Sent: Wednesday, January 19, 2005 6:10 PM
> To: Rick Schumeyer; PgSql General
> Subject: Re: [GENERAL] sorting library of congress numbers
>
> O
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Scott Marlowe
> Sent: Wednesday, January 19, 2005 5:54 PM
> To: Rick Schumeyer
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] sorting library of congres
I have a table where one of the columns is a library of
congress number.
I would like to be able to ORDER BY this column.
Currently, the column is a varchar. Unfortunately, a
normal string comparison
will not work.
First, has anyone done this already?
If not, I’m thinking I w
A question about using transactions from php:
Does the entire transaction have to be sent all at once,
or can I begin the transaction, issue commands one
at a time, and then end the transaction?
As a new user, I recently had the same problem. I then realized what the
problem is.
You want to select from sharp_p1 instead of selecting from sharp. That
means you never actually select from sharp. Since nothing was selected,
postgres can't do anything useful with the WHERE clause. To put it
I’m new both to databases and postgres, so forgive me
if this is a stupid question.
Where do people usually enforce business rules? In the
client application or in the database?
For example, I might have a rule “don’t allow
customers to enter an order if their account
is delinquen
50 matches
Mail list logo
