Re: [SQL] insert related data into two tables
You need to manage the transaction: begin a transaction, execute your first insert, retrieve the ID with curr_val(), execute the sencond insert and commit the transaction. --- [EMAIL PROTECTED] wrote: > Hello, > > I have two tables like these: > > TABLE_1: people registry > fields: ID_T1, SURNAME, NAME > ID_T1 is primary key > > TABLE_2: work groups > fields: ID_T2, TASK > ID_T2 is foreign key related to ID_T1 > > the first table is the list of employees, the second > the task. > > Sometime I need to insert a new employee and at the > same time the task > for him: > I want to INSERT TO table 1 JOHN DOE (the ID is > assigned automatically > since it's a primary key) and INSERT TO table 2 the > ID and the task > name for JOHN DOE. Is it possible to create a single > query? What is the > best way to do this? > > Thanks, > > Filippo > > > ---(end of > broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] timestamp query doesn't use an index ...
I'm trying to figure out some way to speed up the following query:
select ps2.page_id, ps2.template_component_id, max(ps2.start_time)
from page_schedule ps2
where ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18'
group by ps2.page_id, ps2.template_component_id
When run through EXPLAIN ANALYZE, it shows:
QUERY PLAN
---
HashAggregate (cost=2613.28..2614.17 rows=72 width=16) (actual
time=976.629..976.938 rows=128 loops=1)
-> Seq Scan on page_schedule ps2 (cost=0.00..2364.95 rows=33110 width=16)
(actual time=0.021..623.363 rows=94798 loops=1)
Filter: (timezone('MST7MDT'::text, start_time) <= '2006-05-17
08:09:18'::timestamp without time zone)
Total runtime: 977.224 ms
(4 rows)
I've tried doing a function index, like:
create index start_time_page_schedule
on page_schedule
using btree ( timezone('MST7MDT'::text, start_time ) );
But, same result ... whereas, if I change the <= to just =, the index is
used, but that is expected ...
Is there some other way I can either write above query *or* do an index,
such that it will use the index?
thanks ...
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED]
Yahoo . yscrappy Skype: hub.orgICQ . 7615664
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] timestamp query doesn't use an index ...
On May 21, 2006, at 10:42 , Marc G. Fournier wrote: -> Seq Scan on page_schedule ps2 (cost=0.00..2364.95 rows=33110 width=16) (actual time=0.021..623.363 rows=94798 loops=1) I don't know about rewriting the query, but it appears your statistics are a little out of date (e.g., rows expected/actual 33110/94798). Does running ANALYZE help? Michael Glaesemann grzm seespotcode net ---(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] timestamp query doesn't use an index ...
On Sun, 21 May 2006, Michael Glaesemann wrote:
On May 21, 2006, at 10:42 , Marc G. Fournier wrote:
-> Seq Scan on page_schedule ps2 (cost=0.00..2364.95 rows=33110
width=16) (actual time=0.021..623.363 rows=94798 loops=1)
I don't know about rewriting the query, but it appears your statistics are a
little out of date (e.g., rows expected/actual 33110/94798). Does running
ANALYZE help?
the data is idle, just loaded it on my desktop for testing purposes ...
being paranoid, I have been doing a vacuum analyze on the table as I
change the index's *just in case*, but, doing a full analyze on the whole
database doesn't change the results any:
Actually, the above results are odd anyway, since a second run of the
exact same query, shows more normal numbers:
QUERY PLAN
---
HashAggregate (cost=3051.91..3054.19 rows=183 width=16) (actual
time=1030.970..1031.257 rows=128 loops=1)
-> Seq Scan on page_schedule ps2 (cost=0.00..2364.95 rows=91594 width=16)
(actual time=0.019..636.599 rows=94798 loops=1)
Filter: (timezone('MST7MDT'::text, start_time) <= '2006-05-17
08:09:18'::timestamp without time zone)
Total runtime: 1031.681 ms
(4 rows)
So not 100% certain where the 33110/94798 gap came from ;)
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED]
Yahoo . yscrappy Skype: hub.orgICQ . 7615664
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
