[SQL] weird join producing too many rows

[Gary Stainburn]

I have a pieces table with p_id as primary key.
I have a requests table with r_id as primary key.
I have a pieces_requests table with (p_id, r_id) as primary key, and an 
indicator pr_ind reflecting the state of that relationship

A single select of details from the pieces table based on an entry in the 
pieces_requests table returns what I expect.

users=# select * from pieces_requests where r_id=5695;
 p_id | r_id | pr_ind 
--+--+
 5102 | 5695 |   
 5020 | 5695 |   
 5065 | 5695 |   
 5147 | 5695 |   
 4917 | 5695 |   
 5165 | 5695 |   
 4884 | 5695 |   
 5021 | 5695 |   
 5121 | 5695 |   
 5130 | 5695 |   
 5088 | 5695 |   
 4900 | 5695 |   
 4197 | 5695 |   
 2731 | 5695 |   
(14 rows)

users=# select p_id, p_name from pieces where p_id in (select p_id from 
pieces_requests where r_id=5695);
 p_id | p_name  
--+-
 4884 | LSERVB
 4900 | ESALES4
 5102 | LSALES6
 2731 | LSALESE
 5147 | ESALES5
 5020 | LSALES5
 5130 | LSALES3
 5021 | WSERV7
 4917 | LSALESA
 5165 | LSERV8
 5088 | LADMIN1
 5121 | LSALESL
 4197 | WSERV1
 5065 | LSALESG
(14 rows)

users=# 


However, when I try to include the pr_ind in the result set I get multiple 
records (at the moment pr_ind is NULL for every record)

I've tried both

select p.p_id, r.pr_ind 
from pieces p
join pieces_requests r on p.p_id = r.p_id
where p.p_id in (select p_id from pieces_requests where r_id=5695)

and 

select p.p_id, r.pr_ind 
from pieces p, pieces_requests r 
where p.p_id = r.p_id and 
p.p_id in (select p_id from pieces_requests where r_id=5695)

Both result in the following. Can anyone see why. I think I'm going blind on 
this one

users=# select p.p_id, p_name, r.pr_ind 
users-# from pieces p, pieces_requests r 
users-# where p.p_id = r.p_id and 
users-# p.p_id in (select p_id from pieces_requests where r_id=5695);
 p_id | p_name  | pr_ind 
--+-+
 2731 | LSALESE |   
 2731 | LSALESE |   
 2731 | LSALESE |   
 2731 | LSALESE |   
 4884 | LSERVB  |   
 4900 | ESALES4 |   
 4900 | ESALES4 |   
 4917 | LSALESA |   
 4197 | WSERV1  |   
 4197 | WSERV1  |   
 4884 | LSERVB  |   
 5021 | WSERV7  |   
 5065 | LSALESG |   
 5065 | LSALESG |   
 4884 | LSERVB  |   
 5121 | LSALESL |   
 5088 | LADMIN1 |   
 5130 | LSALES3 |   
 5147 | ESALES5 |   
 5102 | LSALES6 |   
 5020 | LSALES5 |   
 5065 | LSALESG |   
 5147 | ESALES5 |   
 4917 | LSALESA |   
 5165 | LSERV8  |   
 4884 | LSERVB  |   
 5021 | WSERV7  |   
 5121 | LSALESL |   
 5130 | LSALES3 |   
 5088 | LADMIN1 |   
 4900 | ESALES4 |   
 4197 | WSERV1  |   
 2731 | LSALESE |   
(33 rows)

users=# 

-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk 


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] weird join producing too many rows

[Samuel Gendler]

I'll admit I don't see any reason why you should get duplicate rows based
on the data you've provided, but I am wondering why you are using the
subquery instead of just 'where r.r_id = 5695'

select p.p_id, r.pr_ind
from pieces p
join pieces_requests r on p.p_id = r.p_id
where r.r_id = 5695

Though I'll be the first to admit that that seems to me like it ought to
return the exact same rows as both your queries.  Are you sure you don't
have multiple rows in pieces_requests with the same p_id, r_id pairing?
 Your join must be resulting in multiple rows for each p_id somehow.

On Tue, Sep 11, 2012 at 7:42 AM, Gary Stainburn <
gary.stainb...@ringways.co.uk> wrote:

> I have a pieces table with p_id as primary key.
> I have a requests table with r_id as primary key.
> I have a pieces_requests table with (p_id, r_id) as primary key, and an
> indicator pr_ind reflecting the state of that relationship
>
> A single select of details from the pieces table based on an entry in the
> pieces_requests table returns what I expect.
>
> users=# select * from pieces_requests where r_id=5695;
>  p_id | r_id | pr_ind
> --+--+
>  5102 | 5695 |
>  5020 | 5695 |
>  5065 | 5695 |
>  5147 | 5695 |
>  4917 | 5695 |
>  5165 | 5695 |
>  4884 | 5695 |
>  5021 | 5695 |
>  5121 | 5695 |
>  5130 | 5695 |
>  5088 | 5695 |
>  4900 | 5695 |
>  4197 | 5695 |
>  2731 | 5695 |
> (14 rows)
>
> users=# select p_id, p_name from pieces where p_id in (select p_id from
> pieces_requests where r_id=5695);
>  p_id | p_name
> --+-
>  4884 | LSERVB
>  4900 | ESALES4
>  5102 | LSALES6
>  2731 | LSALESE
>  5147 | ESALES5
>  5020 | LSALES5
>  5130 | LSALES3
>  5021 | WSERV7
>  4917 | LSALESA
>  5165 | LSERV8
>  5088 | LADMIN1
>  5121 | LSALESL
>  4197 | WSERV1
>  5065 | LSALESG
> (14 rows)
>
> users=#
>
>
> However, when I try to include the pr_ind in the result set I get multiple
> records (at the moment pr_ind is NULL for every record)
>
> I've tried both
>
> select p.p_id, r.pr_ind
> from pieces p
> join pieces_requests r on p.p_id = r.p_id
> where p.p_id in (select p_id from pieces_requests where r_id=5695)
>
> and
>
> select p.p_id, r.pr_ind
> from pieces p, pieces_requests r
> where p.p_id = r.p_id and
> p.p_id in (select p_id from pieces_requests where r_id=5695)
>
> Both result in the following. Can anyone see why. I think I'm going blind
> on
> this one
>
> users=# select p.p_id, p_name, r.pr_ind
> users-# from pieces p, pieces_requests r
> users-# where p.p_id = r.p_id and
> users-# p.p_id in (select p_id from pieces_requests where r_id=5695);
>  p_id | p_name  | pr_ind
> --+-+
>  2731 | LSALESE |
>  2731 | LSALESE |
>  2731 | LSALESE |
>  2731 | LSALESE |
>  4884 | LSERVB  |
>  4900 | ESALES4 |
>  4900 | ESALES4 |
>  4917 | LSALESA |
>  4197 | WSERV1  |
>  4197 | WSERV1  |
>  4884 | LSERVB  |
>  5021 | WSERV7  |
>  5065 | LSALESG |
>  5065 | LSALESG |
>  4884 | LSERVB  |
>  5121 | LSALESL |
>  5088 | LADMIN1 |
>  5130 | LSALES3 |
>  5147 | ESALES5 |
>  5102 | LSALES6 |
>  5020 | LSALES5 |
>  5065 | LSALESG |
>  5147 | ESALES5 |
>  4917 | LSALESA |
>  5165 | LSERV8  |
>  4884 | LSERVB  |
>  5021 | WSERV7  |
>  5121 | LSALESL |
>  5130 | LSALES3 |
>  5088 | LADMIN1 |
>  4900 | ESALES4 |
>  4197 | WSERV1  |
>  2731 | LSALESE |
> (33 rows)
>
> users=#
>
> --
> Gary Stainburn
> Group I.T. Manager
> Ringways Garages
> http://www.ringways.co.uk
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: [SQL] weird join producing too many rows

[Gary Stainburn]

Hi,

Thanks for this.  I did eventually discover the cause being other rows in the 
pieces_requests table that I hadn't thought about.

The short answer to your second part is that I don't know why I did it that 
way. Presumably when I first wrote it there was a reason.

Gary

On Wednesday 12 September 2012 08:24:42 Samuel Gendler wrote:
> I'll admit I don't see any reason why you should get duplicate rows based
> on the data you've provided, but I am wondering why you are using the
> subquery instead of just 'where r.r_id = 5695'
>
> select p.p_id, r.pr_ind
> from pieces p
> join pieces_requests r on p.p_id = r.p_id
> where r.r_id = 5695
>
> Though I'll be the first to admit that that seems to me like it ought to
> return the exact same rows as both your queries.  Are you sure you don't
> have multiple rows in pieces_requests with the same p_id, r_id pairing?
>  Your join must be resulting in multiple rows for each p_id somehow.
>
-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk 


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D

[Rodrigo Rosenfeld Rosas]

This is my first message in this list :)

I need to be able to sort a query by column A, then B or C (which one
is smaller, both are of the same type and table but on different left
joins) and then by D.

How can I do that?

Thanks in advance,
Rodrigo.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D

[Samuel Gendler]

you put a conditional clause in the order by statement, either by
referencing a column that is populated conditionally, like this

select A, when B < C Then B else C end as condColumn, B, C, D
from ...
where ...
order by 1,2, 5

or

select A, when B < C Then B else C end as condColumn, B, C, D
from ...
where ...
order by A,condColumn, D

or you can just put the conditional statement in the order by clause (which
surprised me, but I tested it)

select A, B, C, D
from ...
where ...
order by A,when B < C then B else C end, D



On Wed, Sep 12, 2012 at 2:44 PM, Rodrigo Rosenfeld Rosas  wrote:

> This is my first message in this list :)
>
> I need to be able to sort a query by column A, then B or C (which one
> is smaller, both are of the same type and table but on different left
> joins) and then by D.
>
> How can I do that?
>
> Thanks in advance,
> Rodrigo.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: [SQL] ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D

[Rodrigo Rosenfeld Rosas]

Replied just to Samuel and forgot to include the list in my reply. Doing 
that now, sorry...


Em 12-09-2012 18:53, Samuel Gendler escreveu:
you put a conditional clause in the order by statement, either by 
referencing a column that is populated conditionally, like this


select A, when B < C Then B else C end as condColumn, B, C, D
from ...
where ...
order by 1,2, 5

or

select A, when B < C Then B else C end as condColumn, B, C, D
from ...
where ...
order by A,condColumn, D

or you can just put the conditional statement in the order by clause 
(which surprised me, but I tested it)


select A, B, C, D
from ...
where ...
order by A,when B < C then B else C end, D


Thank you for your insight on this, Samuel, and for your quick answer :)

But I don't think it would solve the issue I have.

I'm developing a query builder for a search engine.

The user is able to query any amount of available filters. And some 
fields may have any number of aggregate fields.


So, suppose you're looking for an event sponsored by some company.

In the events records there could be some fields like Sponsor, Sponsor 
2, Sponsor 3 and Sponsor 4. Yes, I know it is not a good design choice, 
but this is how the system I inherited works.


So, in the Search interface, there is no way to build OR statements. So, 
there is a notion of aggregate fields where Sponsor is the aggregator 
one and the others are aggregates from Sponsor. Only Sponsor shows up in 
the Search UI.


So, suppose the user wants to sort by event location and then by sponsor.

If there are multiple sponsors for a given event I want to be able to 
sort by the one that would be indexed first.


How could I create a generic query for dealing with something like this?

Thank you,
Rodrigo.






On Wed, Sep 12, 2012 at 2:44 PM, Rodrigo Rosenfeld Rosas 
mailto:rr.ro...@gmail.com>> wrote:


This is my first message in this list :)

I need to be able to sort a query by column A, then B or C (which one
is smaller, both are of the same type and table but on different left
joins) and then by D.

How can I do that?

Thanks in advance,
Rodrigo.


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D

[Rodrigo Rosenfeld Rosas]

Em 12-09-2012 19:34, Gavin Flower escreveu:

On 13/09/12 09:44, Rodrigo Rosenfeld Rosas wrote:

This is my first message in this list :)

I need to be able to sort a query by column A, then B or C (which one
is smaller, both are of the same type and table but on different left
joins) and then by D.

How can I do that?

Thanks in advance,
Rodrigo.



...
SELECT
*
FROM
tabc t
ORDER BY
t.a,
LEAST(t.b, t.c),
t.d
...


Thank you Gavin, I was looking for this LEAST function for a long time. 
I have tried MIN but it didn't work with strings.


I guess this will allow me to do what I want. Thank you so much!

Best,
Rodrigo.

[SQL] pg_restore problem

[Kjell Øygard]

Morning guys...

I have two servers , one with postgres 9.2rc1 and one with postgres 9.1.4.
I need to do a restore from a dump from 9.1.4 to 9.2rc1 and I get this
error:

pg_restore: [archiver (db)] Error from TOC entry 177675; 2613 579519 BLOB
579519 primar
pg_restore: [archiver (db)] could not execute query: ERROR:  duplicate key
value violates unique constraint "pg_largeobject_metadata_oid_index"
DETAIL:  Key (oid)=(579519) already exists.
Command was: SELECT pg_catalog.lo_create('579519');

This just keep repeat itself in the log.

The command used is: pg_restore -O -U user -d  database2 database2.dump
>dump.log 2>&1 &

Appreciate any help

-- 
Rgds
Kjell Inge Øygard
Electronic Chart Centre
www.ecc.no

Re: [SQL] ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D

[Gavin Flower]

On 13/09/12 09:44, Rodrigo Rosenfeld Rosas wrote:

This is my first message in this list :)

I need to be able to sort a query by column A, then B or C (which one
is smaller, both are of the same type and table but on different left
joins) and then by D.

How can I do that?

Thanks in advance,
Rodrigo.



I created a script 'variable_sort_order.sql'...

DROP TABLE IF EXISTS tabc;

CREATE TABLE tabc
(
id  serial PRIMARY KEY,
a   int,
b   int,
c   int,
d   int
);


INSERT INTO tabc (a, b, c, d)
VALUES (generate_series(1, 6),
3 * random(),
3 * random(),
generate_series(1, 5));


SELECT
*
FROM
tabc t
ORDER BY
t.a,
LEAST(t.b, t.c),
t.d
/**/;/**/

gavin=> \i variable_sort_order.sql
DROP TABLE
psql:variable_sort_order.sql:10: NOTICE:  CREATE TABLE will create 
implicit sequence "tabc_id_seq" for serial column "tabc.id"
psql:variable_sort_order.sql:10: NOTICE:  CREATE TABLE / PRIMARY KEY 
will create implicit index "tabc_pkey" for table "tabc"

CREATE TABLE
INSERT 0 30
 id | a | b | c | d
+---+---+---+---
 25 | 1 | 0 | 3 | 5
  7 | 1 | 1 | 1 | 2
  1 | 1 | 3 | 2 | 1
 13 | 1 | 2 | 3 | 3
 19 | 1 | 2 | 2 | 4
  8 | 2 | 0 | 2 | 3
 14 | 2 | 0 | 2 | 4
 26 | 2 | 2 | 1 | 1
 20 | 2 | 1 | 2 | 5
  2 | 2 | 2 | 2 | 2
  3 | 3 | 0 | 2 | 3
 21 | 3 | 1 | 1 | 1
 27 | 3 | 1 | 3 | 2
 15 | 3 | 3 | 1 | 5
  9 | 3 | 3 | 2 | 4
  4 | 4 | 0 | 1 | 4
 10 | 4 | 3 | 0 | 5
 16 | 4 | 1 | 3 | 1
 22 | 4 | 1 | 1 | 2
 28 | 4 | 2 | 3 | 3
 11 | 5 | 0 | 1 | 1
 17 | 5 | 0 | 3 | 2
 23 | 5 | 1 | 1 | 3
  5 | 5 | 3 | 1 | 5
 29 | 5 | 3 | 2 | 4
 18 | 6 | 2 | 0 | 3
 12 | 6 | 1 | 1 | 2
 24 | 6 | 3 | 1 | 4
 30 | 6 | 1 | 3 | 5
  6 | 6 | 3 | 2 | 1
(30 rows)

[SQL] generate_series() with TSTZRANGE

[Wolfe Whalen]

Hi everyone!

I'm new around here, so please forgive me if this is a bit trivial.  It
seems that generate_series() won't generate time stamp ranges.  I
googled around and didn't see anything handy, so I wrote this out and
thought I'd share and see if perhaps there was a better way to do it:

SELECT tstzrange((lag(a) OVER()), a, '[)') 
FROM generate_series('2012-09-16 12:00:00'::timestamp, '2012-09-17
12:00:00', '1 hour') 
AS a OFFSET 1;

Basically, it's generating a series of time stamps one hour apart, then
using the previous record and the current record to construct the
TSTZRANGE value.  It's offset 1 to skip the first record, since there is
no previous record to pair with it.

If you were looking at Josh Berkus' example at
http://lwn.net/Articles/497069/ you might use it like this to generate
data for testing and experimentation:

INSERT INTO room_reservations 
SELECT 'F104', 'John', 'Another Talk', 
tstzrange((lag(a) OVER()), a, '[)') 
FROM generate_series('2012-09-16 12:00:00'::timestamp, '2012-09-17
12:00:00', '1 hour') 
AS a OFFSET 1; 

Thanks!

-- 
  Wolfe Whalen
  wo...@quios.net


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql