date:20100526

[SQL] inner join and limit

2010-05-26 Thread Michele Petrazzo - Unipex


Hi list,
I have two table that are so represented:
t1:
id int primary key
... other

t2:
id int primary key
t1id int fk(t1.id)
somedate date
... other

data t1:
1 | abcde
2 | fghi

data t2:
1 | 1 | 2010-05-23
2 | 1 | 2010-05-24
3 | 1 | 2010-05-25
4 | 2 | 2010-05-22
5 | 2 | 2010-05-26

I'm trying to create a query where the data replied are:
join t1 with t2 and return only the LIMIT 1 (or N) of the t2, with date 
order (of t2).

Data should be:

t1.id | t2.id | t2,somedate
1 | 3 | 2010-05-25
2 | 5 | 2010-05-26


As said, I'm trying, but without success...
Can be done for you?

Thanks,
Michele

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

[SQL] Rules and sequences

2010-05-26 Thread Ben Morrow

I am trying to implement a fairly standard 'audit table' setup, but
using rules instead of triggers (since it should be more efficient).
However, I'm running into problems when one of the audited tables has a
'serial' column that is allowed to default:

create table foo (id serial, bar text);
create table audit (ix bigserial, rec text);
create rule audit_insert as on insert to foo do also 
insert into audit (rec) values ((new.*)::text);
insert into foo (bar) values ('baz');
select * from foo;
 id | bar 
+-
  1 | baz
(1 row)
select * from audit;
 ix |   rec   
+-
  1 | (2,baz)
(1 row)

I can see why this is happening (the rule is essentially a macro, so the
NEW expression gets expanded twice, including the nextval call, so the
sequence is incremented twice), but is there any way to prevent it? Some
way of 'materialising' the NEW row so it is just plain values rather
than a list of expressions?

Ben


-- 
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] inner join and limit

2010-05-26 Thread Rolando Edwards

I can only see a LIMIT 1 possible. If someone can come up with LIMIT N on this 
one, please let us all know.

rolando=# drop table if exists t2;
DROP TABLE
rolando=# drop table if exists t1;
DROP TABLE
rolando=# create table t1 ( id int primary key, title varchar(10) );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for 
table "t1"
CREATE TABLE
rolando=# insert into t1 values (1,'abcde'),(2,'fghi');
INSERT 0 2
rolando=# create table t2 (id int primary key,t1id int not null references t1 
(id) ,somedate date);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for 
table "t2"
CREATE TABLE
rolando=# insert into t2 values
rolando-# (1,1,'2010-05-23'),
rolando-# (2,1,'2010-05-24'),
rolando-# (3,1,'2010-05-25'),
rolando-# (4,2,'2010-05-22'),
rolando-# (5,2,'2010-05-26');
INSERT 0 5
rolando=# select * from t1;
 id | title
+---
  1 | abcde
  2 | fghi
(2 rows)

rolando=# select * from t2;
 id | t1id |  somedate
+--+
  1 |1 | 2010-05-23
  2 |1 | 2010-05-24
  3 |1 | 2010-05-25
  4 |2 | 2010-05-22
  5 |2 | 2010-05-26
(5 rows)

rolando=# select t1.id,t2.id,t2.somedate
rolando-# from t1,t2,(select t1id,max(somedate) as somedate from t2 group by 
t1id) t3
rolando-# where t1.id=t2.t1id
rolando-# and t2.somedate=t3.somedate;
 id | id |  somedate
++
  1 |  3 | 2010-05-25
  2 |  5 | 2010-05-26
(2 rows)


Rolando A. Edwards
MySQL DBA (CMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Michele Petrazzo - Unipex
Sent: Wednesday, May 26, 2010 1:35 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] inner join and limit

Hi list,
I have two table that are so represented:
t1:
id int primary key
... other

t2:
id int primary key
t1id int fk(t1.id)
somedate date
... other

data t1:
1 | abcde
2 | fghi

data t2:
1 | 1 | 2010-05-23
2 | 1 | 2010-05-24
3 | 1 | 2010-05-25
4 | 2 | 2010-05-22
5 | 2 | 2010-05-26

I'm trying to create a query where the data replied are:
join t1 with t2 and return only the LIMIT 1 (or N) of the t2, with date 
order (of t2).
Data should be:

t1.id | t2.id | t2,somedate
1 | 3 | 2010-05-25
2 | 5 | 2010-05-26


As said, I'm trying, but without success...
Can be done for you?

Thanks,
Michele

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

-- 
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] inner join and limit

2010-05-26 Thread Luigi Antognini

Hello
Here a suggestion for your problem.

SELECT a.id AS t1_id, d.id AS t2_id, d.somedate AS t2_somedate
FROM t1 a
JOIN 
  (
SELECT id, t1id, somedate
FROM t2 b
WHERE (t1id, somedate) IN 
  (
SELECT t1id, somedate
FROM t2 c
WHERE c.t1id = b.t1id
ORDER BY somedate DESC
LIMIT 1
  )
  ) d 
ON (a.id=d.t1id);


 t1_id | t2_id | t2_somedate
---+---+-
 1 | 3 | 2010-05-25
 2 | 5 | 2010-05-26
(2 rows)


Hope this helps

Regards (Saluti da Zurigo)
Luigi Antognini



-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of Michele Petrazzo - Unipex
Sent: Wednesday, May 26, 2010 7:35 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] inner join and limit

Hi list,
I have two table that are so represented:
t1:
id int primary key
... other

t2:
id int primary key
t1id int fk(t1.id)
somedate date
... other

data t1:
1 | abcde
2 | fghi

data t2:
1 | 1 | 2010-05-23
2 | 1 | 2010-05-24
3 | 1 | 2010-05-25
4 | 2 | 2010-05-22
5 | 2 | 2010-05-26

I'm trying to create a query where the data replied are:
join t1 with t2 and return only the LIMIT 1 (or N) of the t2, with date 
order (of t2).
Data should be:

t1.id | t2.id | t2,somedate
1 | 3 | 2010-05-25
2 | 5 | 2010-05-26


As said, I'm trying, but without success...
Can be done for you?

Thanks,
Michele

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


-- 
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] inner join and limit

2010-05-26 Thread Tim Landscheidt

Michele Petrazzo - Unipex  wrote:

> I have two table that are so represented:
> t1:
> id int primary key
> ... other

> t2:
> id int primary key
> t1id int fk(t1.id)
> somedate date
> ... other

> data t1:
> 1 | abcde
> 2 | fghi

> data t2:
> 1 | 1 | 2010-05-23
> 2 | 1 | 2010-05-24
> 3 | 1 | 2010-05-25
> 4 | 2 | 2010-05-22
> 5 | 2 | 2010-05-26

> I'm trying to create a query where the data replied are:
> join t1 with t2 and return only the LIMIT 1 (or N) of the
> t2, with date order (of t2).
> Data should be:

> t1.id | t2.id | t2,somedate
> 1 | 3 | 2010-05-25
> 2 | 5 | 2010-05-26

> As said, I'm trying, but without success...
> Can be done for you?

In addition to Luigi's and Rolando's responses, there are of
course the always glorious "DISTINCT ON" for the "LIMIT 1"
case:

| SELECT DISTINCT ON (t1.id) t1.id, t2.id, t2.somedate FROM t1 JOIN t2 ON t1.id 
= t2.t1id ORDER BY t1.id, somedate DESC;

and window functions for the generic one:

| SELECT t1_id, t2_id, t2_somedate FROM
|   (SELECT t1.id AS t1_id,
|   t2.id AS t2_id,
|   t2.somedate AS t2_somedate,
|   ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.somedate DESC) AS 
rn
|  FROM t1 JOIN t2 ON t1.id = t2.t1id) AS SubQuery
|   WHERE rn <= 2;

Tim


-- 
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] inner join and limit

2010-05-26 Thread Rolando Edwards

I found a good solution.

drop table if exists t3;
drop table if exists t2;
drop table if exists t1;
create table t1 ( id int primary key, title varchar(10) );
insert into t1 values (1,'abcde'),(2,'fghi');
create table t2 (id int primary key,t1id int not null references t1 (id) 
,somedate date);
insert into t2 values
(1,1,'2010-05-23'),
(2,1,'2010-05-24'),
(3,1,'2010-05-25'),
(4,2,'2010-05-22'),
(5,2,'2010-05-26');
create table t3 ( t1id int not null,idcount int,vector varchar[][] );
insert into t3 (t1id,idcount) select t1id,count(1) as idcount from t2 group by 
t1id;
update t3 set vector=array(select somedate from t2 where t2.t1id=t3.t1id ORDER 
BY somedate DESC LIMIT 2);
select * from t1;
select * from t2;
select * from t3;

I got this output...

rolando=# select * from t1;
 id | title
+---
  1 | abcde
  2 | fghi
(2 rows)

rolando=# select * from t2;
 id | t1id |  somedate
+--+
  1 |1 | 2010-05-23
  2 |1 | 2010-05-24
  3 |1 | 2010-05-25
  4 |2 | 2010-05-22
  5 |2 | 2010-05-26
(5 rows)

rolando=# select * from t3;
 t1id | idcount | vector
--+-+
2 |   2 | {2010-05-26,2010-05-22}
1 |   3 | {2010-05-25,2010-05-24}


Notice the arrays are length 2 because I have the clause "LIMIT 2" in the 
UPDATE command.
You can set LIMIT to whatever. Give it a try !!!

BTW I think another person already replied a solution without an extra table. 
Give that a try, too !!!

-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Michele Petrazzo - Unipex
Sent: Wednesday, May 26, 2010 1:35 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] inner join and limit

Hi list,
I have two table that are so represented:
t1:
id int primary key
... other

t2:
id int primary key
t1id int fk(t1.id)
somedate date
... other

data t1:
1 | abcde
2 | fghi

data t2:
1 | 1 | 2010-05-23
2 | 1 | 2010-05-24
3 | 1 | 2010-05-25
4 | 2 | 2010-05-22
5 | 2 | 2010-05-26

I'm trying to create a query where the data replied are:
join t1 with t2 and return only the LIMIT 1 (or N) of the t2, with date 
order (of t2).
Data should be:

t1.id | t2.id | t2,somedate
1 | 3 | 2010-05-25
2 | 5 | 2010-05-26


As said, I'm trying, but without success...
Can be done for you?

Thanks,
Michele

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

-- 
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] Rules and sequences

2010-05-26 Thread Tom Lane

Ben Morrow  writes:
> I am trying to implement a fairly standard 'audit table' setup, but
> using rules instead of triggers (since it should be more efficient).

Rules are sufficiently tricky that I would never, ever rely on them for
auditing.  Use a simple AFTER trigger instead.

regards, tom lane

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

[SQL] cast record type to array?

2010-05-26 Thread Gerardo Herzig

Hi all. Im not being able to cast a record variable into an array.

Im writing a trigger, and i would like to store NEW (and OLD) as text[].
There is a way to do it in plpgsql? (w/o any contrib modules)

thanks!
Gerardo

-- 
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] Rules and sequences

2010-05-26 Thread A. Kretschmer

In response to Tom Lane :
> Ben Morrow  writes:
> > I am trying to implement a fairly standard 'audit table' setup, but
> > using rules instead of triggers (since it should be more efficient).
> 
> Rules are sufficiently tricky that I would never, ever rely on them for
> auditing.  Use a simple AFTER trigger instead.

There are a ready solution:
http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

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

[SQL] inner join and limit

[SQL] Rules and sequences

Re: [SQL] inner join and limit

Re: [SQL] inner join and limit

Re: [SQL] inner join and limit

Re: [SQL] inner join and limit

Re: [SQL] Rules and sequences

[SQL] cast record type to array?

Re: [SQL] Rules and sequences

9 matches

Site Navigation

Mail list logo

Footer information