Re: [SQL] timestamp precision - can I control precision at select time
"[EMAIL PROTECTED]" wrote: > > I have a database with several tables that use timestamp without time > zone type. I upgraded from an older pgsql and have code that does not > expect the precision in the select output. Is there a way I can get the > effect of zero precision without modifying all the tables? > > The docs say it uses "default precision" as set in the "timestamp > literal". I wasn't able to find docs describing what literals are. If > there's a way I can set this to zero precision for everything, that'd > save me a bunch of time. > > with thanks, > Royce > I was facing the same obstacle. The only cure I've found was either SELECT CURRENT_TIMESTAMP ; timestamptz -- 2005-03-22 09:43:51.05193+01 (1 row) SELECT CURRENT_TIMESTAMP::TIMESTAMP(0) ; timestamp - 2005-03-22 09:44:11 (1 row) resp. change column types to TIMESTAMP(0) But I assume you've found this already by yourself. Regards, Christoph ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Your question about date
That's a good one, but it has the disadvantage of giving a null result row: insert into test values ( '2004-12-22' ) ; The SELECT from below gives result 31 33 25 (null) 42 (5 rows) Also, I am not sure about the order of values. Anyway, Béatrice, thanks for your time. Regards, Christoph Béatrice Yueksel wrote: > > Dear Christoph, > perhaps you could try something like this example. > Regards, > BĂŠatrice > > The table: > -- > # select * from test; > date > > 2005-02-02 > 2005-03-05 > 2005-04-07 > 2005-05-02 > (4 rows) > > The query > -- > > SELECT > (( select test1.date >from test test1 >where test1.date > test.date limit 1) >- test.date ) AS result from test; > > t1.date > > RESULT: > --- > result > > 31 > 33 > 25 > > Am Montag, den 21.03.2005, 10:54 +0100 schrieb Christoph Haller: > > Octavio Alvarez wrote: > > > > > > Sorry, I tried to make my subject as good as possible. > > > > Ahem, what subject? > > > > > > I have a table where I store the dates in which I take out of my inventory > > > (like "installation dates") > > > > > > table > > > --- > > > row_id SERIAL > > > date DATE > > > fk_item INTEGER > > > > > > and that's pretty much it. > > > > > > I want to have a query returning how long have been certain items lasting. > > > > > > Say I have: > > > > > > SELCT date FROM table WHERE fk_item = "INKJET_INK-BW" > > > > > > date > > > - > > > 2005-02-02 > > > 2005-03-05 > > > 2005-04-07 > > > 2005-05-02 > > > > > > I need something to calculate the intervals between those dates, like > > > this: > > > > > > intervals (in days) > > > > > > 31 > > > 34 > > > 25 > > > > > > So I can get the stddev and stuff from the "duration" of the items. > > > > > > I've been having a hard time with it. I'm trying NOT to program new > > > functions. > > > > I cannot see how this could be achieved without the use of a function. > > But if there is a way after all, I would be interested in learning it. > > > > > > Any help will be appreciated. > > > > > > --Octavio > > > -- > > > > Regards, Christoph > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Your question about date
Perhaps it will be better if you put an 'order by' in the select. SELECT (( select test1.date from test test1 where test1.date > test.date order by date limit 1) - test.date ) AS result from test order by date; result 42 31 33 25 Regards, Beatrice Am Dienstag, den 22.03.2005, 10:38 +0100 schrieb Christoph Haller: > That's a good one, but it has the disadvantage of > giving a null result row: > insert into test values ( '2004-12-22' ) ; > The SELECT from below gives > result > > 31 > 33 > 25 > (null) > 42 > (5 rows) > Also, I am not sure about the order of values. > Anyway, BÃatrice, thanks for your time. > Regards, Christoph > Am Montag, den 21.03.2005, 06:06 -0800 schrieb Octavio Alvarez Piza: > YES! It definitely should work. You guys are good! I'll make it > first thing to try after vacation. I think I will only need to > add an ORDER BY test1.date ASC clause. > > Heh... This method could also work for getting next/last-record on > serials... > > Octavio. > > > BÃatrice Yueksel wrote: > > > > Dear Christoph, > > perhaps you could try something like this example. > > Regards, > > BÄÅatrice > > > > The table: > > -- > > # select * from test; > > date > > > > 2005-02-02 > > 2005-03-05 > > 2005-04-07 > > 2005-05-02 > > (4 rows) > > > > The query > > -- > > > > SELECT > > (( select test1.date > >from test test1 > >where test1.date > test.date limit 1) > >- test.date ) AS result from test; > > > > t1.date > > > RESULT: > > --- > > result > > > > 31 > > 33 > > 25 > > > > Am Montag, den 21.03.2005, 10:54 +0100 schrieb Christoph Haller: > > > Octavio Alvarez wrote: > > > > > > > > Sorry, I tried to make my subject as good as possible. > > > > > > Ahem, what subject? > > > > > > > > I have a table where I store the dates in which I take out of my > > > > inventory > > > > (like "installation dates") > > > > > > > > table > > > > --- > > > > row_id SERIAL > > > > date DATE > > > > fk_item INTEGER > > > > > > > > and that's pretty much it. > > > > > > > > I want to have a query returning how long have been certain items > > > > lasting. > > > > > > > > Say I have: > > > > > > > > SELCT date FROM table WHERE fk_item = "INKJET_INK-BW" > > > > > > > > date > > > > - > > > > 2005-02-02 > > > > 2005-03-05 > > > > 2005-04-07 > > > > 2005-05-02 > > > > > > > > I need something to calculate the intervals between those dates, like > > > > this: > > > > > > > > intervals (in days) > > > > > > > > 31 > > > > 34 > > > > 25 > > > > > > > > So I can get the stddev and stuff from the "duration" of the items. > > > > > > > > I've been having a hard time with it. I'm trying NOT to program new > > > > functions. > > > > > > I cannot see how this could be achieved without the use of a function. > > > But if there is a way after all, I would be interested in learning it. > > > > > > > > Any help will be appreciated. > > > > > > > > --Octavio > > > > -- > > > > > > Regards, Christoph > > > -- BÃatrice Yueksel Softwareentwicklung it-raum Sperrstrasse 91 4057 Basel Telefon +41 061 683 05 05 Fax +41 061 683 93 50 [EMAIL PROTECTED] http://www.it-raum.ch - eine Unternehmung von Kiebitz ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Self-referencing table question
I have a table that looks like: Column | Type | Modifiers | Description -+--+---+- from_id | integer | not null | to_id | integer | not null | val | numeric(4,3) | | Indexes: "correlation_pkey" PRIMARY KEY, btree (from_id, to_id) "correlation_from_id_idx" btree (from_id) "correlation_to_id_idx" btree (to_id) "correlation_val_idx" btree (val) Has OIDs: yes The table describes a pairwise correlation matrix between about 7700 vectors (so the table has n^2= 60652944 rows, to be exact). I am trying to choose the top 100 correlated vectors with a seed vector; this is easily: select to_id from correlation where from_id=623 order by val desc limit 100; Then, I want to take those 100 values and find all from_id,to_id tuples where val>0.5 (to construct a graph where all "ids" are nodes and are connected to each other when their correlation is >0.5). I can do this like: explain analyze select from_id,to_id,val from exprsdb.correlation where from_id in (1,2,3,4,5,6,7,8,10,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27 ,28,29,30) and to_id in (1,2,3,4,5,6,7,8,10,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27 ,28,29,30) and from_id>to_id and val>0.5; However, this does not scale well AT ALL. The actual (very messy) explain analyze output is below. The thing I notice is that the index on to_id is not used. Also, the primary key index on (from_id, to_id is not used, it seems. Finally, with only 30 values, this already takes 2.6 seconds and I am proposing to do this on 100-200 values. Any hints on how better to accomplish this set of tasks? Index Scan using correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx on correlation (cost=0.00..129377.49 rows=62 width=17) (actual time=340.563..2603.967 rows=19 loops=1) Index Cond: ((from_id = 1) OR (from_id = 2) OR (from_id = 3) OR (from_id = 4) OR (from_id = 5) OR (from_id = 6) OR (from_id = 7) OR (from_id = 8) OR (from_id = 10) OR (from_id = 9) OR (from_id = 11) OR (from_id = 12) OR (from_id = 13) OR (from_id = 14) OR (from_id = 15) OR (from_id = 16) OR (from_id = 17) OR (from_id = 18) OR (from_id = 19) OR (from_id = 20) OR (from_id = 21) OR (from_id = 22) OR (from_id = 23) OR (from_id = 24) OR (from_id = 25) OR (from_id = 26) OR (from_id = 27) OR (from_id = 28) OR (from_id = 29) OR (from_id = 30)) Filter: (((to_id = 1) OR (to_id = 2) OR (to_id = 3) OR (to_id = 4) OR (to_id = 5) OR (to_id = 6) OR (to_id = 7) OR (to_id = 8) OR (to_id = 10) OR (to_id = 9) OR (to_id = 11) OR (to_id = 12) OR (to_id = 13) OR (to_id = 14) OR (to_id = 15) OR (to_id = 16) OR (to_id = 17) OR (to_id = 18) OR (to_id = 19) OR (to_id = 20) OR (to_id = 21) OR (to_id = 22) OR (to_id = 23) OR (to_id = 24) OR (to_id = 25) OR (to_id = 26) OR (to_id = 27) OR (to_id = 28) OR (to_id = 29) OR (to_id = 30)) AND (from_id > to_id) AND (val > 0.5)) Total runtime: 2604.383 ms Thanks, Sean ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Self-referencing table question
I answer my own question, if only for my own records. The following
query is about 5-6 times faster than the original. Of course, if
anyone else has other ideas, I'd be happy to hear them.
Sean
explain analyze select from_id,to_id,val from exprsdb.correlation where
from_id in (select to_id from exprsdb.correlation where from_id=2424
order by val desc limit 100) and to_id in (select to_id from
exprsdb.correlation where from_id=2424 order by val desc limit 100) and
val>0.6 and to_id
QUERY PLAN
Hash IN Join (cost=4709.94..74758.01 rows=555 width=17) (actual
time=110.291..1671.767 rows=973 loops=1)
Hash Cond: ("outer".to_id = "inner".to_id)
-> Nested Loop (cost=2354.97..72181.72 rows=43154 width=17)
(actual time=54.036..1612.746 rows=1482 loops=1)
-> HashAggregate (cost=2354.97..2354.97 rows=100 width=4)
(actual time=53.656..54.062 rows=100 loops=1)
-> Subquery Scan "IN_subquery" (cost=2353.47..2354.72
rows=100 width=4) (actual time=53.473..53.595 rows=100 loops=1)
-> Limit (cost=2353.47..2353.72 rows=100
width=13) (actual time=53.469..53.507 rows=100 loops=1)
-> Sort (cost=2353.47..2415.03 rows=24624
width=13) (actual time=53.467..53.481 rows=100 loops=1)
Sort Key: val
-> Index Scan using
correlation_from_id_idx on correlation (cost=0.00..557.42 rows=24624
width=13) (actual time=0.199..17.717 rows=7788 loops=1)
Index Cond: (from_id = 2424)
-> Index Scan using correlation_from_id_idx on correlation
(cost=0.00..692.87 rows=432 width=17) (actual time=2.765..15.560
rows=15 loops=100)
Index Cond: (correlation.from_id = "outer".to_id)
Filter: ((val > 0.6) AND (to_id < from_id))
-> Hash (cost=2354.72..2354.72 rows=100 width=4) (actual
time=56.239..56.239 rows=0 loops=1)
-> Subquery Scan "IN_subquery" (cost=2353.47..2354.72
rows=100 width=4) (actual time=56.004..56.121 rows=100 loops=1)
-> Limit (cost=2353.47..2353.72 rows=100 width=13)
(actual time=56.001..56.038 rows=100 loops=1)
-> Sort (cost=2353.47..2415.03 rows=24624
width=13) (actual time=55.999..56.012 rows=100 loops=1)
Sort Key: val
-> Index Scan using correlation_from_id_idx
on correlation (cost=0.00..557.42 rows=24624 width=13) (actual
time=0.517..20.307 rows=7788 loops=1)
Index Cond: (from_id = 2424)
Total runtime: 1676.966 ms
On Mar 22, 2005, at 2:33 PM, Sean Davis wrote:
I have a table that looks like:
Column | Type | Modifiers | Description
-+--+---+-
from_id | integer | not null |
to_id | integer | not null |
val | numeric(4,3) | |
Indexes:
"correlation_pkey" PRIMARY KEY, btree (from_id, to_id)
"correlation_from_id_idx" btree (from_id)
"correlation_to_id_idx" btree (to_id)
"correlation_val_idx" btree (val)
Has OIDs: yes
The table describes a pairwise correlation matrix between about 7700
vectors (so the table has n^2= 60652944 rows, to be exact). I am
trying to choose the top 100 correlated vectors with a seed vector;
this is easily:
select to_id from correlation where from_id=623 order by val desc
limit 100;
Then, I want to take those 100 values and find all from_id,to_id
tuples where val>0.5 (to construct a graph where all "ids" are nodes
and are connected to each other when their correlation is >0.5). I
can do this like:
explain analyze select
from_id,to_id,val
from exprsdb.correlation
where from_id in
(1,2,3,4,5,6,7,8,10,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,2
7,28,29,30)
and to_id in
(1,2,3,4,5,6,7,8,10,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,2
7,28,29,30)
and from_id>to_id
and val>0.5;
However, this does not scale well AT ALL. The actual (very messy)
explain analyze output is below. The thing I notice is that the index
on to_id is not used. Also, the primary key index on (from_id, to_id
is not used, it seems. Finally, with only 30 values, this already
takes 2.6 seconds and I am proposing to do this on 100-200 values.
Any hints on how better to accomplish this set of tasks?
Index Scan using correlation_from_id_idx, correlation_from_id_idx,
correlation_from_id_idx, correlation_from_id_idx,
correlation_from_id_idx, correlation_from_id_idx,
correlation_from_id_idx, correlation_from_id_idx,
correlation_from_id_idx, correlation_from_id_idx,
correlation_from_id_idx, correlation_from_id_i
Re: [SQL] Self-referencing table question
Sean Davis wrote: I answer my own question, if only for my own records. The following query is about 5-6 times faster than the original. Of course, if anyone else has other ideas, I'd be happy to hear them. Sean explain analyze select from_id,to_id,val from exprsdb.correlation where from_id in (select to_id from exprsdb.correlation where from_id=2424 order by val desc limit 100) and to_id in (select to_id from exprsdb.correlation where from_id=2424 order by val desc limit 100) and val>0.6 and to_idMight not be any faster, but you can do this as a self-join with subquery: SELECT c1.from_id, c1.to_id, c1.val FROM correlation c1, ( SELECT to_id FROM correlation WHERE from_id=2424 ORDER BY val DESC LIMIT 100 ) AS c2 ( SELECT to_id FROM correlation WHERE from_id=2424 ORDER BY val DESC LIMIT 100 ) AS c3 WHERE c1.from_id = c2.to_id AND c1.to_id = c3.to_id AND c1.val > 0.5 AND c1.to_id < from_id ; I think PG should be smart enough nowadays to figure out these two queries are basically the same. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Permissions on tables and views
Hi, I have a requirement where I have a table and a view on top of it. I want to make some changes so that a user in a given group would be able to insert/update only on the view (ofcourse i have a rule which inserts/updates the table) but not on the underlying table. I tried revoke the insert/update permissions on the table, but it fails even the inserts/updates on the view. How can i set these permissions ? Thanks, Subhash. ---(end of broadcast)--- TIP 3: 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] Permissions on tables and views
On Tue, Mar 22, 2005 at 02:28:08PM -0700, [EMAIL PROTECTED] wrote: > I have a requirement where I have a table and a view on top of it. I want to > make some changes so that a user in a given group would be able to > insert/update > only on the view (ofcourse i have a rule which inserts/updates the table) but > not on the underlying table. I tried revoke the insert/update permissions on > the > table, but it fails even the inserts/updates on the view. How can i set these > permissions ? Did you grant insert and update permission on the view? Does the underlying table have a sequence? If so, did you grant update permission on it? If you still have trouble, then please post a small, self-contained example that demonstrates the problem, including the exact text of any error messages. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Self-referencing table question
- Original Message - From: "Richard Huxton" To: "Sean Davis" <[EMAIL PROTECTED]> Cc: "PostgreSQL SQL" Sent: Tuesday, March 22, 2005 3:59 PM Subject: Re: [SQL] Self-referencing table question Sean Davis wrote: I answer my own question, if only for my own records. The following query is about 5-6 times faster than the original. Of course, if anyone else has other ideas, I'd be happy to hear them. Sean explain analyze select from_id,to_id,val from exprsdb.correlation where from_id in (select to_id from exprsdb.correlation where from_id=2424 order by val desc limit 100) and to_id in (select to_id from exprsdb.correlation where from_id=2424 order by val desc limit 100) and val>0.6 and to_idMight not be any faster, but you can do this as a self-join with subquery: SELECT c1.from_id, c1.to_id, c1.val FROM correlation c1, ( SELECT to_id FROM correlation WHERE from_id=2424 ORDER BY val DESC LIMIT 100 ) AS c2 ( SELECT to_id FROM correlation WHERE from_id=2424 ORDER BY val DESC LIMIT 100 ) AS c3 WHERE c1.from_id = c2.to_id AND c1.to_id = c3.to_id AND c1.val > 0.5 AND c1.to_id < from_id ; I think PG should be smart enough nowadays to figure out these two queries are basically the same. Richard, In another email, I posted what I did (which was what you suggest), along with explain analyze output. It looks like the subquery is 4-6 times faster, which is getting into the acceptible for my little web application. Thanks for the help. Sean ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] view function on pg_toast
hello guys, I need help. I have functions but it is saved to pg_toast, when I dump the database, the function from pg_toast is not copied. so is there any sql or syntax to view function data ? Thanks Ricky ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] view function on pg_toast
On Wed, Mar 23, 2005 at 11:39:31AM +0800, bandeng wrote: > I need help. I have functions but it is saved to pg_toast, when I > dump the database, the function from pg_toast is not copied. so is > there any sql or syntax to view function data ? What exactly do you mean by "it is saved to pg_toast" and how do you know that? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] view function on pg_toast
Hello, bandeng. March, 23 2005, 8:39:31: b> hello guys, b> I need help. I have functions but it is saved to pg_toast, when I b> dump the database, the function from pg_toast is not copied. so is b> there any sql or syntax to view function data ? xxx:=\d pg-catalog.pg_proc xxx:=select procsrc from pg-catalog.pg_proc where proname=... and pronamespace=...; -- Regards, Mihail Nasedkin mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] view function on pg_toast
MN> March, 23 2005, 8:39:31: b>> hello guys, b>> I need help. I have functions but it is saved to pg_toast, when I b>> dump the database, the function from pg_toast is not copied. so is b>> there any sql or syntax to view function data ? MN> xxx:=\d pg-catalog.pg_proc xxx:=\d pg_catalog.pg_proc MN> xxx:=select procsrc from pg-catalog.pg_proc where proname=... and MN> pronamespace=...; xxx:=select procsrc from pg_catalog.pg_proc where proname=... and pronamespace=...; -- Regards, Mihail Nasedkin mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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
