gmane.comp.db.postgresql.sql

Re: Index Usage and Running Times by FullTextSearch with prefix matching

rawi — 2013-06-18T12:49:12

rawi wrote

Just tested with 9.2: pretty much the same




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Index-Usage-and-Running-Times-by-FullTextSearch-with-prefix-matching-tp5759021p5759645.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Re: apt.postgresql.org vs. Pitti PPA - install error

rawi — 2013-06-18T11:31:01

/etc/apt/sources.list.d/apt_postgresql_org.list:
deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main 9.3

(for 9.2 also tryed only 'deb http://apt.postgresql.org/pub/repos/apt/
precise-pgdg main')

lsb_release -c
Codename:       raring

but there is no repository for raring... so I took then the precise one...

Thank you!

Regards, Rawi



--
View this message in context: http://postgresql.1045698.n5.nabble.com/apt-postgresql-org-vs-Pitti-PPA-install-error-tp5759622p5759636.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Re: apt.postgresql.org vs. Pitti PPA - install error

basti — 2013-06-18T10:43:58

Please post your /etc/apt/source.list entry and "lsb_release -c"

Am 18.06.2013 12:12, schrieb rawi:

apt.postgresql.org vs. Pitti PPA - install error

rawi — 2013-06-18T10:12:04

Working on Ubuntu 13.04 64bit (raring)

I red, that Pitti-PPA will be discontinued in the future, so we should make
the switch to apt.postgresql.org.

But trying to "aptitude install postgresql-9.3" (and with 9.2 - the same) I
get an install error:

<error>
The following packages have unmet dependencies:
 postgresql-common : Breaks: logrotate (>= 3.8) but 3.8.3-3ubuntu2 is
installed.
</error>

Going back to the Pitti PPA I could install pg-9.2 without problems (there
will be no 9.3 in there)

Regards
Rawi



--
View this message in context: http://postgresql.1045698.n5.nabble.com/apt-postgresql-org-vs-Pitti-PPA-install-error-tp5759622.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Re: Select clause in JOIN statement

Andreas Joseph Krogh — 2013-06-14T07:14:43

Re: Select clause in JOIN statement

Luca Vernini — 2013-06-13T23:10:51

It works.
Also consider views.

Just used this on a my db:

SELECT * FROM tblcus_customer
INNER JOIN
( SELECT * FROM tblcus_customer_status WHERE status_id > 0) AS b
ON tblcus_customer.status = b.status_id


You can even join with a function result.

Regards,

Luca.

2013/6/14 JORGE MALDONADO <jorgemal1960< at >gmail.com>:

Select clause in JOIN statement

JORGE MALDONADO — 2013-06-13T22:40:36

Is it valid to specify a SELECT statement as part of a JOIN clause?

For example:

SELECT table1.f1, table1.f2 FROM table1
INNER JOIN
(SELECT table2.f1, table2.f2 FROM table2) table_aux ON table1.f1 =
table_aux.f1

Respectfully,
Jorge Maldonado

Re: Index Usage and Running Times by FullTextSearch with prefix matching

rawi — 2013-06-13T15:39:26

Tom Lane-2 wrote

Short or long prefixes seem to be equaly unfavorable. Even with the full
length of the words, but queried as prefix I get a runtime of 25342ms
compared to 353ms without prefixes:

SELECT count(a)
FROM t1
WHERE a_tsvector < at >< at > to_tsquery('aaaaa:* & bbbbb:* & ccccc:* & ddddd:*')

Total query runtime: 25342 ms

"Aggregate  (cost=804.02..804.03 rows=1 width=36)"
"  ->  Bitmap Heap Scan on t1  (cost=800.00..804.02 rows=1 width=36)"
"        Recheck Cond: (a_tsvector < at >< at > to_tsquery('aaaaa:* & bbbbb:* &
ccccc:* & ddddd:*'::text))"
"        ->  Bitmap Index Scan on a_tsvector_idx  (cost=0.00..800.00 rows=1
width=0)"
"              Index Cond: (a_tsvector < at >< at > to_tsquery('aaaaa:* & bbbbb:* &
ccccc:* & ddddd:*'::text))"

Kind Regards
Rawi



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Index-Usage-and-Running-Times-by-FullTextSearch-with-prefix-matching-tp5759021p5759046.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Re: Index Usage and Running Times by FullTextSearch with prefix matching

rawi — 2013-06-13T15:20:56

Tom Lane-2 wrote

Thank you Tom,

I'm testing on PG 9.1 on UbuntuServer 12.10, 64bit

I'll update to 9.2 the next days and try again.

Kind Regards
Rawi




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Index-Usage-and-Running-Times-by-FullTextSearch-with-prefix-matching-tp5759021p5759045.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Re: Index Usage and Running Times by FullTextSearch with prefix matching

Tom Lane — 2013-06-13T14:56:38



FWIW, I get fairly decent performance for cases like this in HEAD
(at least with a GIN index; GIST seems much less able to do well with
short prefixes).  What PG version are you testing?

regards, tom lane

Index Usage and Running Times by FullTextSearch with prefix matching

rawi — 2013-06-13T11:39:16

Hi

I tested the following:

CREATE TABLE t1
(
  id serial NOT NULL,
  a character varying(125),
  a_tsvector tsvector,
  CONSTRAINT t1_pkey PRIMARY KEY (id)
);

INSERT INTO t1 (a, a_tsvector) 
VALUES ('ooooo,ppppp,fffff,jjjjj,zzzzz,jjjjj',
to_tsvector('ooooo,ppppp,fffff,jjjjj,zzzzz,jjjjj');

CREATE INDEX a_tsvector_idx  ON t1 USING gin (a_tsvector);

(I have generated 900000 records with random words like this)

Now querying: normal full text search

SELECT count(a)
FROM t1
WHERE a_tsvector < at >< at > to_tsquery('aaaaa & bbbbb & ccccc & ddddd')

(RESULT: count: 619)
Total query runtime: 353 ms.
Query Plan:
"Aggregate  (cost=6315.22..6315.23 rows=1 width=36)"
"  ->  Bitmap Heap Scan on t1  (cost=811.66..6311.46 rows=1504 width=36)"
"        Recheck Cond: (a_tsvector < at >< at > to_tsquery('aaaaa & bbbbb & ccccc &
ddddd'::text))"
"        ->  Bitmap Index Scan on a_tsvector_idx  (cost=0.00..811.28
rows=1504 width=0)"
"              Index Cond: (a_tsvector < at >< at > to_tsquery('aaaaa & bbbbb & ccccc
& ddddd'::text))"

And querying: F

Re: Advice with an insert query

Thomas Kellerer — 2013-06-07T14:26:49

JORGE MALDONADO, 07.06.2013 15:58:


INSERT INTO table1 (fld1, fld2, fl3)
SELECT value1, fldx, fldy 
FROM table2

Re: Advice with an insert query

Oliver d'Azevedo Cristina — 2013-06-07T14:06:58

Just pass the fixed value for inside the select subquery. 
Do this


HTH

Best,
Oliver 



Enviado via iPhone

Em 07/06/2013, às 02:58 PM, JORGE MALDONADO <jorgemal1960< at >gmail.com> escreveu:

Re: Advice with an insert query

Andreas Gaab — 2013-06-07T14:01:19

INSERT INTO table1 (fld1, fld2, fl3)
VALUES (SELECT value1, fldx, fldy FROM table2);

should work,

Andreas


Von: pgsql-sql-owner< at >postgresql.org [mailto:pgsql-sql-owner< at >postgresql.org] Im Auftrag von JORGE MALDONADO
Gesendet: Freitag, 7. Juni 2013 15:59
An: pgsql-sql< at >postgresql.org
Betreff: [SQL] Advice with an insert query

I need to insert records into a table where one value is fixed and 2 values come from a SELECT query, something like the following example:

INSERT INTO table1 fld1, fld2, fl3
VALUES value1, (SELECT fldx, fldy FROM table2)

Is this valid?

Respectfully,
Jorge Maldonado

Advice with an insert query

JORGE MALDONADO — 2013-06-07T13:58:51

I need to insert records into a table where one value is fixed and 2 values
come from a SELECT query, something like the following example:

INSERT INTO table1 fld1, fld2, fl3
VALUES value1, (SELECT fldx, fldy FROM table2)

Is this valid?

Respectfully,
Jorge Maldonado

Re: crosstab help

Jayadevan — 2013-06-05T04:24:01

Hello all ,
I have a similar situation - an EAV table.
entity_attr_id  integer          
 entity_id       bigint           
 ent_attr_value  character varying
I am trying 
select * from crosstab('select entity_id::text as row_name,
entity_attr_id::bigint as entity_attr_id , ent_attr_value::text  as
ent_attr_value from org_entity_attr_value order by 1') as ct(                
row_name text,entity_attr_id bigint,ent_attr_value text )
and get an error - 
ERROR:  return and sql tuple descriptions are incompatible





--
View this message in context: http://postgresql.1045698.n5.nabble.com/crosstab-help-tp5511994p5757977.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Correlated Update Query

Robert DiFalco — 2013-06-02T20:39:20

I have an update query that I'm not sure if I am taking a good approach or
a naive one. It works but seems ugly.

I have a table named "contacts".  Contacts have a user_id and an owner_id.
There is also a one to many relationship between contacts and email
addresses. Users also have an email address but only one.

When I get a new set of contacts (from an external source) I insert all
those contacts. Then afterwards I want to search users whose email
addresses match those of the new contacts. If there is a match, I want to
update contact so that it points to the user it's email address correlates
to. If the user is already set (i.e. NOT NULL) I don't need to update it
again.

Here is the query:

    UPDATE
        contact
    SET
        user_id = u.id
    FROM
        app_user u
    JOIN
        email_addresses e
            ON u.email = e.email
    WHERE
        contact.owner_id = 24
        AND contact.user_id IS NULL
        AND contact.id = e.owner_id

Is there a better way to do this? I'm not great wit

Re: [GENERAL] foreign key to multiple tables depending on another column's value

Rodrigo Rosenfeld Rosas — 2013-05-29T16:14:00

Em 29-05-2013 12:51, Vick Khera escreveu:

Ok, thanks. I just wanted to be sure there wasn't some hidden feature of 
PostgreSQL I wasn't aware of yet...

You know, I'm always learning something new on PG, so it worths trying 
to ask first ;)

Cheers,
Rodrigo.

Re: [GENERAL] foreign key to multiple tables depending on another column's value

Vick Khera — 2013-05-29T15:51:03

On Wed, May 29, 2013 at 9:58 AM, Rodrigo Rosenfeld Rosas <rr.rosas< at >gmail.com


The FK tests are basically triggers, but highly optimized.

That said, the way they enforce the integrity is by having a trigger on
both tables. So for your custom need here, you would want to put a trigger
on the referenced table to disallow deleting a value that is still
referenced, or do whatever appropriate action upon delete/update your
application needs.

Re: reduce many loosely related rows down to one

Marc Mamin — 2013-05-29T15:07:07


for such cases, I have created an new aggregate function:

SELECT firstnotnull(rspid) AS rspid,

this avoid to collect first all rspid values to then keep only the first one...


  CREATE OR REPLACE FUNCTION public.first_agg_nn ( anyelement, anyelement )
  RETURNS anyelement AS $$
        SELECT $1;
  $$ LANGUAGE SQL IMMUTABLE STRICT
  COST 1;

  CREATE AGGREGATE public.firstnotnull (
           sfunc    = public.first_agg_nn,
           basetype = anyelement,
           stype    = anyelement
  );


regards,

Marc Mamin

Re: reduce many loosely related rows down to one

Bill MacArthur — 2013-05-28T20:58:53

Slick! Interesting usage scenarios for those aggregate functions array_agg and bool_or, one new to me and the other rarely used, and even for min and max which I never thought of using in this sense.

I tried not be be overbearing with descriptive details hoping that somebody would look at the simplistic case and offer what might be considered an obscure way of implementing some of Postgres's handy features for an unusual problem. With a little tweaking for the exact nature of the environment, I am good to go.

Thank you, Torsten!
Bill MacArthur