[SQL] Originally created and last_mod by whom and when ?
Hello, In a web app (Pg 8.2.4 + php) I have product and other tables with fields like product_created timestamp without time zone product_created_user_id integer product_last_mod timestamp without time zone product_last_mod_user_id integer The person who last modified an item can obviously be someone else who originally created it. I can get the names and timestamps with two separate queries but how can I do a single query to get the names of both ? product_id | 1 ... product_created_user_id | 1 product_last_mod_user_id | 2 ID 1, created by X / date and time, last_mod by Y / date and time And a similar query to only one table, users ? user_id integer user_forename text ... user_created timestamp without time zone user_created_user_id integer user_last_mod timestamp without time zone user_last_mod_user_id integer ID 4, name Z, created by X / date and time, last_mod by Y / date and time Join, sub select ? I tried some but only managed to get only one name, errors, nothing at all or two rows with inner join + union ... Lotsa thanks for any help, Aarni -- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Originally created and last_mod by whom and when ?
Aarni Ruuhimäki wrote: Hello, In a web app (Pg 8.2.4 + php) I have product and other tables with fields like product_created timestamp without time zone product_created_user_id integer product_last_mod timestamp without time zone product_last_mod_user_id integer The person who last modified an item can obviously be someone else who originally created it. I can get the names and timestamps with two separate queries but how can I do a single query to get the names of both ? Alias the tables, so you can join to the user-table twice. SELECT p.*, u_cre.username as created_by, u_mod.username as modified_by FROM products p LEFT JOIN app_users u_cre ON p.product_created_user_id = u_cre.id LEFT JOIN app_users u_mod ON p.product_last_mod_user_id = u.mod.id ; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] ALL() question
Hello, I have a problem with the ALL() subquery expression. I have three tables: - specimens - test_bits - specimen_test_bits The specimen_test_bits table contains two foreign keys, one to specimens(id), another to test_bits(id). Here is an output of specimen_test_bits: muridae=> select * from specimen_test_bits; specimen_id | test_bit_id -+- 46096 | 1 46096 | 2 46096 | 3 46096 | 4 52894 | 1 52894 | 3 12546 | 2 What I would like is a query that returns all the specimen_id of this table which have _all_ the given test_bit_id. So in this case, with test_bit_id 1,2,3,4 it should return only specimen_id 46096. With the following I got a syntax error: select specimen_id from specimen_test_bits where test_bit_id = all(1,2,3,4); The following works but no rows are returned : select specimen_id from specimen_test_bits where test_bit_id = all(select id from test_bits where id in (1,2,3,4)); Any idea how I could do this ? I guess the problem is my ALL() expression ... In advance thanks, Julien ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] ALL() question
Julien Cigar wrote:
What I would like is a query that returns all the specimen_id of
this table which have _all_ the given test_bit_id.
[snip]
With the following I got a syntax error:
select specimen_id
from specimen_test_bits
where test_bit_id = all(1,2,3,4);
It's expecting an array here. You'd have to write
= all('{1,2,3,4}')
But that would have the same problem as...
The following works but no rows are returned :
select specimen_id
from specimen_test_bits
where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
It's testing each row individually and of course one row can't match ALL
four values.
What you want to do is count the distinct values. Something like:
SELECT
specimen_id
FROM foo
GROUP BY
specimen_id
HAVING
count(distinct test_bit_id) = 4
;
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] ALL() question
On Wed, 2007-11-14 at 11:56 +, Richard Huxton wrote:
> Julien Cigar wrote:
> >
> > What I would like is a query that returns all the specimen_id of
> > this table which have _all_ the given test_bit_id.
> [snip]
> > With the following I got a syntax error:
> > select specimen_id
> > from specimen_test_bits
> > where test_bit_id = all(1,2,3,4);
>
> It's expecting an array here. You'd have to write
> = all('{1,2,3,4}')
> But that would have the same problem as...
>
> > The following works but no rows are returned :
> > select specimen_id
> > from specimen_test_bits
> > where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
>
> It's testing each row individually and of course one row can't match ALL
> four values.
>
> What you want to do is count the distinct values. Something like:
>
> SELECT
>specimen_id
> FROM foo
> GROUP BY
>specimen_id
> HAVING
>count(distinct test_bit_id) = 4
> ;
>
I don't think it would work, for example if I have:
specimen_id | test_bit_id
+
100 1
100 3
101 1
101 2
the test_bit_ids are parameters, so with the given test_bit_id 1,3 it
would return specimen_id 101 too, which I don't want ...
What I would like is the specimen_id which match _exactly_ the given
test_bit_ids, so it should return only 100 in this example ..
from the documentation ALL() can take a subquery too, not only an ARRAY
(http://www.postgresql.org/docs/8.2/static/functions-subquery.html)
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] ALL() question
I finally found a solution:
SELECT specimen_id
FROM specimen_test_bits
GROUP BY specimen_id
HAVING array_accum(test_bit_id) = '{2,3,4}';
.. but I don't think it's very "clean" ..
what do you think ?
Thanks
On Wed, 2007-11-14 at 15:50 +0100, Julien Cigar wrote:
> On Wed, 2007-11-14 at 11:56 +, Richard Huxton wrote:
> > Julien Cigar wrote:
> > >
> > > What I would like is a query that returns all the specimen_id of
> > > this table which have _all_ the given test_bit_id.
> > [snip]
> > > With the following I got a syntax error:
> > > select specimen_id
> > > from specimen_test_bits
> > > where test_bit_id = all(1,2,3,4);
> >
> > It's expecting an array here. You'd have to write
> > = all('{1,2,3,4}')
> > But that would have the same problem as...
> >
> > > The following works but no rows are returned :
> > > select specimen_id
> > > from specimen_test_bits
> > > where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
> >
> > It's testing each row individually and of course one row can't match ALL
> > four values.
> >
> > What you want to do is count the distinct values. Something like:
> >
> > SELECT
> >specimen_id
> > FROM foo
> > GROUP BY
> >specimen_id
> > HAVING
> >count(distinct test_bit_id) = 4
> > ;
> >
>
> I don't think it would work, for example if I have:
> specimen_id | test_bit_id
> +
>100 1
>100 3
>101 1
>101 2
>
> the test_bit_ids are parameters, so with the given test_bit_id 1,3 it
> would return specimen_id 101 too, which I don't want ...
> What I would like is the specimen_id which match _exactly_ the given
> test_bit_ids, so it should return only 100 in this example ..
>
> from the documentation ALL() can take a subquery too, not only an ARRAY
> (http://www.postgresql.org/docs/8.2/static/functions-subquery.html)
>
>
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Originally created and last_mod by whom and when ?
On Wednesday 14 November 2007 13:28, Richard Huxton wrote: > Aarni Ruuhimäki wrote: > > Hello, > > > > In a web app (Pg 8.2.4 + php) I have product and other tables with fields > > like > > > > product_created timestamp without time zone > > product_created_user_id integer > > product_last_mod timestamp without time zone > > product_last_mod_user_id integer > > > > The person who last modified an item can obviously be someone else who > > originally created it. > > > > I can get the names and timestamps with two separate queries but how can > > I do a single query to get the names of both ? > > Alias the tables, so you can join to the user-table twice. > > SELECT p.*, u_cre.username as created_by, u_mod.username as modified_by > FROM >products p > LEFT JOIN >app_users u_cre ON p.product_created_user_id = u_cre.id > LEFT JOIN >app_users u_mod ON p.product_last_mod_user_id = u.mod.id > ; Charming ! Many thanks to you Richard. Aarni -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ALL() question
The doc says "The right-hand side is a parenthesized subquery, which must
return exactly one column..."
That's what you have if using "... where test_bit_id = all(select id from
test_bits where id in (1,2,3,4));"
The doc continues "...The left-hand expression is evaluated and compared to
each row of the subquery result using the given operator, which must yield a
Boolean result ..."
So your where expression is equivalent to:
where test_bit_id = (select id from test_bits where id = 1) AND
test_bit_id = (select id from test_bits where id = 2) AND
test_bit_id = (select id from test_bits where id = 3) AND
test_bit_id = (select id from test_bits where id = 4);
The doc continues "... The result of ALL is "true" if all rows yield true ..."
Since test_bit_id can never be 1, 2, 3 and 4 at the same time the result of ALL
will be false. So no records get returned.
>>> Julien Cigar <[EMAIL PROTECTED]> 2007-11-14 15:50 >>>
On Wed, 2007-11-14 at 11:56 +, Richard Huxton wrote:
> Julien Cigar wrote:
> >
> > What I would like is a query that returns all the specimen_id of
> > this table which have _all_ the given test_bit_id.
> [snip]
> > With the following I got a syntax error:
> > select specimen_id
> > from specimen_test_bits
> > where test_bit_id = all(1,2,3,4);
>
> It's expecting an array here. You'd have to write
> = all('{1,2,3,4}')
> But that would have the same problem as...
>
> > The following works but no rows are returned :
> > select specimen_id
> > from specimen_test_bits
> > where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
>
> It's testing each row individually and of course one row can't match ALL
> four values.
>
> What you want to do is count the distinct values. Something like:
>
> SELECT
>specimen_id
> FROM foo
> GROUP BY
>specimen_id
> HAVING
>count(distinct test_bit_id) = 4
> ;
>
I don't think it would work, for example if I have:
specimen_id | test_bit_id
+
100 1
100 3
101 1
101 2
the test_bit_ids are parameters, so with the given test_bit_id 1,3 it
would return specimen_id 101 too, which I don't want ...
What I would like is the specimen_id which match _exactly_ the given
test_bit_ids, so it should return only 100 in this example ..
from the documentation ALL() can take a subquery too, not only an ARRAY
(http://www.postgresql.org/docs/8.2/static/functions-subquery.html)
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] ALL() question
On Wed, Nov 14, 2007 at 02:39:02PM +0100, Julien Cigar wrote: > With the following I got a syntax error: > select specimen_id > from specimen_test_bits > where test_bit_id = all(1,2,3,4); where test_bit_id in (1,2,3,4) group by specimen_id having count(distinct test_bit_id) = 4; depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] ALL() question
Julien Cigar wrote:
I finally found a solution:
SELECT specimen_id
FROM specimen_test_bits
GROUP BY specimen_id
HAVING array_accum(test_bit_id) = '{2,3,4}';
.. but I don't think it's very "clean" ..
The key question is whether you can rely on getting (2,3,4) or whether
you might get (4,3,2) or some other ordering.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] ALL() question
Julien Cigar wrote:
On Wed, 2007-11-14 at 11:56 +, Richard Huxton wrote:
Julien Cigar wrote:
What I would like is a query that returns all the specimen_id of
this table which have _all_ the given test_bit_id.
[snip]
With the following I got a syntax error:
select specimen_id
from specimen_test_bits
where test_bit_id = all(1,2,3,4);
It's expecting an array here. You'd have to write
= all('{1,2,3,4}')
But that would have the same problem as...
The following works but no rows are returned :
select specimen_id
from specimen_test_bits
where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
It's testing each row individually and of course one row can't match ALL
four values.
What you want to do is count the distinct values. Something like:
SELECT
specimen_id
FROM foo
GROUP BY
specimen_id
HAVING
count(distinct test_bit_id) = 4
;
I don't think it would work, for example if I have:
specimen_id | test_bit_id
+
100 1
100 3
101 1
101 2
the test_bit_ids are parameters, so with the given test_bit_id 1,3 it
would return specimen_id 101 too, which I don't want ...
Not if you test for what you want too:
...
FROM foo
WHERE test_bit_id = ANY ('{1,3}')
...or...
WHERE test_bit_id IN (1,3)
What I would like is the specimen_id which match _exactly_ the given
test_bit_ids, so it should return only 100 in this example ..
from the documentation ALL() can take a subquery too, not only an ARRAY
(http://www.postgresql.org/docs/8.2/static/functions-subquery.html)
Yes, but that doesn't help with your query - one row can't match ALL
your values.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] ALL() question
Thanks :) it works as expected Julien On Wed, 2007-11-14 at 14:31 +0100, hubert depesz lubaczewski wrote: > On Wed, Nov 14, 2007 at 02:39:02PM +0100, Julien Cigar wrote: > > With the following I got a syntax error: > > select specimen_id > > from specimen_test_bits > > where test_bit_id = all(1,2,3,4); > > where test_bit_id in (1,2,3,4) > group by specimen_id > having count(distinct test_bit_id) = 4; > > depesz > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Temporal databases
Keith Carr wrote: > On Monday 12 November 2007 09:07, you wrote: > > Hi Philippe, > I do not know of any "extension" or "plugin" that can be used to give > database engines "temporality". Temporality will always be a tricky > subject and it would be impossible to code something general that > would work for any single situation. For example, on some tables you > may only want Valid Time recorded, on some tables only Transaction > Time and in some cases both (or in other cases neither). How would it > know which one? Also, when it came to queries updatating, deleting or > even just selecting, which criteria would it now to do this by in > this situation? > > There is no SQL standard for querying temporal databases as far as I > am aware. > This would be considered to be "schema" and so i presume, outside the > remit of the SQL standards committee. The closest that the SQL > standards committee will get to this is defining the structure within > which dates and times will be held and operated on in a "logical" > sense. > > Sure this makes temporal databases hard work, but this is the whole > point of a SQL database and SQL programmers - data integrity for a > given situation! > Otherwise we may as well be letting the company's accountants go off > designing databases using Access and spreadsheets?! And we ALL > know we don't want that, because when it goes wrong (because there > was no data > integrity) you will be the one left to sort the mess out.. ;) > > Hope this has helped in some way. > Keith Hi Keith, Thanks for your answer. I haven't been playing with temporal databases at all, so pardon my lack of precision, but naively I was imaginating something that would more or less look like: --- CREATE TABLE foo ( id integer, s varchar(64) ) WITH TEMPORAL VALID TIME; SET CURRENT_TIME = '2007-06-01'::date; INSERT INTO foo(v, s) VALUES (1, 'first line'); INSERT INTO foo(v, s) VALUES (2, 'second line'); INSERT INTO foo(v, s) VALUES (3, 'third line'); SET CURRENT_TIME = '2007-06-02'::date; INSERT INTO foo(v, s) VALUES (4, 'fourth line'); DELETE FROM foo WHERE v = 1; --- Now "SET CURRENT_TIME = '2007-06-01'::date; SELECT * from foo;" would return: -- id s -- 1first line 2second line 3third line -- And "SET CURRENT_TIME = '2007-06-02'::date; SELECT * from foo;" would return: -- id s -- 2second line 3third line 4fourth line -- I guess it is much easier to imagine than to develop! For sure I've been watching "Back to future" too much when I was younger. Philippe Lang ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] ALL() question
Julien Cigar escreveu: Hello, I have a problem with the ALL() subquery expression. I have three tables: - specimens - test_bits - specimen_test_bits The specimen_test_bits table contains two foreign keys, one to specimens(id), another to test_bits(id). Here is an output of specimen_test_bits: muridae=> select * from specimen_test_bits; specimen_id | test_bit_id -+- 46096 | 1 46096 | 2 46096 | 3 46096 | 4 52894 | 1 52894 | 3 12546 | 2 What I would like is a query that returns all the specimen_id of this table which have _all_ the given test_bit_id. So in this case, with test_bit_id 1,2,3,4 it should return only specimen_id 46096. With the following I got a syntax error: select specimen_id from specimen_test_bits where test_bit_id = all(1,2,3,4); The following works but no rows are returned : select specimen_id from specimen_test_bits where test_bit_id = all(select id from test_bits where id in (1,2,3,4)); Any idea how I could do this ? I guess the problem is my ALL() expression ... Unclear, but works... SELECT DISTINCT stb.specimen_id FROM specimen_test_bits stb WHERE NOT EXISTS (SELECT * FROM (VALUES (1), (2), (3) , (4)) AS foo(id) WHERE NOT EXISTS (SELECT stb1.test_bit_id FROM specimen_test_bits stb1 WHERE foo.id = stb1.test_bit_id AND stb.specimen_id = stb1.specimen_id)); Osvaldo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] postgres bogged down beyond tolerance
Hi Everybody, The postgres server I have (on redhat linux with recent Dell hardware) is running terribly slow. A job it should have gotten done in less than 1 hour took 7.5 hours last night. I checked kernel parameter shmmax and it was set as 33554432. I "fixed" it as suggested by the manual: http://www.postgresql.org/docs/8.2/static/kernel-resources.html Namely, I shutdown the database, issued two commands: /sbin/sysctl -w kernel.shmmax=134217728 /sbin/sysctl -w kernel.shmall=2097152 and rebooted the computer. After it came up, I checked the shmmax and it is set as 33554432. Which surprised me. Since I used -w flag, I thought it should've written to /etc/sysctl.conf, but there is no such entry at all and the data of this file is from 2006. Can somebody please give me a tip, insight as to what I am missing, doing wrong? Here's tail of serverlog file in my data directory: [2007-11-14 08:53:48.062 PST] LOG: unexpected EOF on client connection [2007-11-14 08:53:59.001 PST] LOG: unexpected EOF on client connection [2007-11-14 08:54:10.782 PST] LOG: unexpected EOF on client connection [2007-11-14 08:54:22.557 PST] LOG: unexpected EOF on client connection [2007-11-14 08:54:34.282 PST] LOG: unexpected EOF on client connection [2007-11-14 09:13:36.444 PST] LOG: unexpected EOF on client connection [2007-11-14 09:13:43.637 PST] LOG: unexpected EOF on client connection [2007-11-14 09:17:16.242 PST] LOG: unexpected EOF on client connection [2007-11-14 09:39:22.841 PST] ERROR: relation "msysconf" does not exist [2007-11-14 09:39:22.842 PST] STATEMENT: SELECT Config, nValue FROM MSysConf Many thanks in advance. Regards, Tena Sakai [EMAIL PROTECTED]
Re: [SQL] postgres bogged down beyond tolerance
--- On Wed, 11/14/07, Tena Sakai <[EMAIL PROTECTED]> wrote: > The postgres server I have (on redhat linux with recent > Dell hardware) is running terribly slow. Hello Tena, If you do not get a response to your question regarding performance, you might try resending this email to : [EMAIL PROTECTED] . This mailing list ( pgsql-sql@postgresql.org ) is really intended for discussions of how to construct SQL queries to achieve desired results in a PostgreSQL database. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
