[SQL] Please help with a query..
I'm stucked.. Say a car travels from X to Y then from Y to Z (and then from Z back to X but that's not relevant here). In the table below are the timestamps for each point in various dates. The complete trip X-Y-Z-X is in the table but each leg on a separate row. I'd need to get the time interval X-Y-Z on each date but I cannot rely on the date (can't make any joins based on the date part of timestamps). Instead I'd need to find out X-Y and Y-Z pairs with a minimal "stop" -time at Y and calculate total travel time for those. The output would be something like this: dep_dateX_Y_Z_time 2005-01-11 6 hours 15 mins 2005-01-12 5 hours 49 mins 2005-01-13 6 hours 05 mins (the times above are not correct) Any help would be greatly appreciated. Thanks, Timo CREATE temp TABLE foo ( pta text, atime timestamp without time zone, ptb text, btime timestamp without time zone ); COPY foo (pta, atime, ptb, btime) FROM stdin using delimiters ','; X,2005-01-11 06:06:00,Y,2005-01-11 08:00:00 X,2005-01-12 06:10:00,Y,2005-01-12 08:00:00 X,2005-01-13 06:14:00,Y,2005-01-13 08:20:00 X,2005-01-14 06:32:00,Y,2005-01-14 08:17:00 X,2005-01-17 06:14:00,Y,2005-01-17 08:02:00 X,2005-01-18 06:10:00,Y,2005-01-18 07:57:00 X,2005-01-19 06:14:00,Y,2005-01-19 08:06:00 X,2005-01-20 06:26:00,Y,2005-01-20 08:13:00 X,2005-01-21 07:52:00,Y,2005-01-21 09:45:00 X,2005-01-24 06:09:00,Y,2005-01-24 07:56:00 X,2005-01-25 06:18:00,Y,2005-01-25 08:07:00 X,2005-01-26 06:05:00,Y,2005-01-26 07:45:00 X,2005-01-27 06:16:00,Y,2005-01-27 07:54:00 X,2005-01-28 06:18:00,Y,2005-01-28 07:59:00 X,2005-01-31 06:50:00,Y,2005-01-31 08:44:00 X,2005-02-01 06:15:00,Y,2005-02-01 07:55:00 X,2005-02-02 06:12:00,Y,2005-02-02 07:59:00 X,2005-02-03 06:31:00,Y,2005-02-03 08:03:00 X,2005-02-04 06:08:00,Y,2005-02-04 07:53:00 X,2005-02-07 06:18:00,Y,2005-02-07 08:09:00 X,2005-02-08 06:02:00,Y,2005-02-08 07:49:00 X,2005-02-09 06:16:00,Y,2005-02-09 08:02:00 X,2005-02-10 06:12:00,Y,2005-02-10 08:07:00 X,2005-02-11 06:13:00,Y,2005-02-11 08:04:00 X,2005-02-14 06:20:00,Y,2005-02-14 08:11:00 X,2005-02-15 06:20:00,Y,2005-02-15 08:06:00 X,2005-02-16 06:11:00,Y,2005-02-16 08:01:00 X,2005-02-17 06:14:00,Y,2005-02-17 07:59:00 X,2005-02-18 06:13:00,Y,2005-02-18 07:59:00 X,2005-02-21 06:15:00,Y,2005-02-21 08:14:00 X,2005-02-22 06:23:00,Y,2005-02-22 08:10:00 Z,2005-01-11 10:15:00,X,2005-01-11 11:58:00 Z,2005-01-12 10:09:00,X,2005-01-12 11:47:00 Z,2005-01-13 10:18:00,X,2005-01-13 12:06:00 Z,2005-01-14 10:15:00,X,2005-01-14 12:06:00 Z,2005-01-17 10:25:00,X,2005-01-17 12:13:00 Z,2005-01-18 10:16:00,X,2005-01-18 11:55:00 Z,2005-01-19 10:15:00,X,2005-01-19 12:00:00 Z,2005-01-20 10:27:00,X,2005-01-20 12:17:00 Z,2005-01-21 11:28:00,X,2005-01-21 13:25:00 Z,2005-01-24 10:17:00,X,2005-01-24 12:05:00 Z,2005-01-25 10:20:00,X,2005-01-25 12:16:00 Z,2005-01-26 10:17:00,X,2005-01-26 12:21:00 Z,2005-01-27 10:30:00,X,2005-01-27 12:38:00 Z,2005-01-28 10:24:00,X,2005-01-28 12:19:00 Z,2005-01-31 10:30:00,X,2005-01-31 12:18:00 Z,2005-02-01 10:19:00,X,2005-02-01 12:22:00 Z,2005-02-02 10:17:00,X,2005-02-02 12:17:00 Z,2005-02-03 10:14:00,X,2005-02-03 12:04:00 Z,2005-02-04 10:18:00,X,2005-02-04 12:16:00 Z,2005-02-07 10:10:00,X,2005-02-07 12:02:00 Z,2005-02-08 10:10:00,X,2005-02-08 11:57:00 Z,2005-02-09 10:18:00,X,2005-02-09 12:06:00 Z,2005-02-10 10:19:00,X,2005-02-10 12:04:00 Z,2005-02-11 10:14:00,X,2005-02-11 11:58:00 Z,2005-02-14 11:11:00,X,2005-02-14 13:04:00 Z,2005-02-15 10:20:00,X,2005-02-15 12:13:00 Z,2005-02-16 10:34:00,X,2005-02-16 12:22:00 Z,2005-02-17 10:20:00,X,2005-02-17 12:09:00 Z,2005-02-18 10:23:00,X,2005-02-18 12:08:00 Z,2005-02-21 10:30:00,X,2005-02-21 12:24:00 Z,2005-02-22 10:19:00,X,2005-02-22 12:13:00 Y,2005-01-11 08:46:00,Z,2005-01-11 09:33:00 Y,2005-01-12 08:40:00,Z,2005-01-12 09:25:00 Y,2005-01-13 08:56:00,Z,2005-01-13 09:45:00 Y,2005-01-14 08:55:00,Z,2005-01-14 09:44:00 Y,2005-01-17 08:48:00,Z,2005-01-17 09:34:00 Y,2005-01-18 08:54:00,Z,2005-01-18 09:43:00 Y,2005-01-19 08:48:00,Z,2005-01-19 09:32:00 Y,2005-01-20 08:58:00,Z,2005-01-20 09:51:00 Y,2005-01-21 10:19:00,Z,2005-01-21 11:08:00 Y,2005-01-24 08:48:00,Z,2005-01-24 09:45:00 Y,2005-01-25 08:50:00,Z,2005-01-25 09:43:00 Y,2005-01-26 08:44:00,Z,2005-01-26 09:26:00 Y,2005-01-27 08:52:00,Z,2005-01-27 09:39:00 Y,2005-01-28 08:46:00,Z,2005-01-28 09:37:00 Y,2005-01-31 09:21:00,Z,2005-01-31 10:05:00 Y,2005-02-01 08:49:00,Z,2005-02-01 09:34:00 Y,2005-02-02 08:45:00,Z,2005-02-02 09:30:00 Y,2005-02-03 08:48:00,Z,2005-02-03 09:36:00 Y,2005-02-04 08:50:00,Z,2005-02-04 09:33:00 Y,2005-02-07 08:47:00,Z,2005-02-07 09:36:00 Y,2005-02-08 08:41:00,Z,2005-02-08 09:22:00 Y,2005-02-09 08:46:00,Z,2005-02-09 09:31:00 Y,2005-02-10 08:48:00,Z,2005-02-10 09:35:00 Y,2005-02-11 08:53:00,Z,2005-02-11 09:34:00 Y,2005-02-14 09:18:00,Z,2005-02-14 10:33:00 Y,2005-02-15 08:53:00,Z,2005-02-15 09:45:00 Y,2005-02-16 08:45:00,Z,2005-02-16 09:29:00 Y,2005-02-17 08:38:00,Z,2005-02-17 09:24:00 Y,2005-02-18 08:42:00,Z,2005-02-18 09:28:00 Y,2005-02-21 08:5
[SQL] SQL help (Informix outer to EnterpriseDB outer)
Hi all, I have been working on converting our Informix DB to PostgreSQL. There are some differences with SQL syntax. I have done many outer conversion so far, but all has either one outer or simple one. But this one I do not know how to do it. I have searched but could not find similar to what I need. This is the one works on InformixDB (OUTER inv_contracts ) connects to three different tables (1. inv_contracts.inv_id = invention.id AND 2. inv_contracts.con_id = con.id AND 3. inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = 2)) -informix outer --- select count(user.id) FROM user, ascpDef AS stateDef, address, invention, user as con , OUTER inv_contracts WHERE address.type = 'User' AND address_id = 1 AND user.id = address.type_id AND state_id = stateDef.id AND invention.user_id = user.id AND invention.inv_number = '1994376-A' AND inv_contracts.inv_id = invention.id AND inv_contracts.con_id = con.id AND inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = 2); -- If there were only one table connection (say 1. inv_contracts.inv_id = invention.id AND ) I would have done it as (,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = invention.id) but I can do same or similar conversion for 2. and 3. I have attempted to do as below but not giving correct count. postgres-- select count(dbuser.id) FROM dbuser, ascpDef AS stateDef, address --, invention --, dbuser as con --, OUTER inv_contracts --1 --,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = invention.id --,dbuser as con LEFT OUTER JOIN inv_contracts ON inv_contracts.con_id = con.id --,contractDef LEFT OUTER JOIN inv_contracts ON inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = 2) --1 gives ERROR: table name "inv_contracts" specified more than once -- --2 --,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = invention.id --,dbuser as con LEFT OUTER JOIN inv_contracts as inv2 ON inv2.con_id = con.id --,contractDef LEFT OUTER JOIN inv_contracts as inv3 ON inv3.contract_id IN (select id FROM contractDef WHERE phase_id = 2) --2 takes very long (cancelled/stoped by me) (informix returns 306229 within 10sec) --Cancel request sent --ERROR: canceling statement due to user request -- --3 --,inv_contracts LEFT OUTER JOIN invention ON inv_contracts.inv_id = invention.id --,inv_contracts as inv2 LEFT OUTER JOIN dbuser as con ON inv2.con_id = con.id --,inv_contracts as inv3 LEFT OUTER JOIN contractDef ON inv3.contract_id IN (select id FROM contractDef WHERE phase_id = 2) --3 takes very long (cancelled/stoped by me) (informix returns 306229 within 10sec) --Cancel request sent --ERROR: canceling statement due to user request -- --4 ,inv_contracts LEFT OUTER JOIN invention ON inv_contracts.inv_id = invention.id LEFT OUTER JOIN dbuser as con ON inv_contracts.con_id = con.id LEFT OUTER JOIN contractDef ON inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = 2) --4 returns 1 but (informix returns 306229 within 10sec) WHERE address.type = 'User' AND address_id = 1 AND dbuser.id = address.type_id AND state_id = stateDef.id AND invention.user_id = dbuser.id AND invention.inv_number = '1994376-A'; --- Thanks for help. - This mail sent through IMP: www.resolution.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SQL help (Informix outer to EnterpriseDB outer)
I could probably work this out for you but I have no time today. However, as a 'plan b' maybe try this... 1- create a temp table based on all tables & conditions in the query except for the outer table (i.e. user, ascpDef, address, invention, and user) 2- do an outer join on the above temp table and the inv_contracts table (inv_contracts as the outer) > Hi all, > I have been working on converting our Informix DB to PostgreSQL. There are > some differences with SQL syntax. > > I have done many outer conversion so far, but all has either one outer or > simple > one. But this one I do not know how to do it. I have searched but could not > find > similar to what I need. > > This is the one works on InformixDB (OUTER inv_contracts ) connects to > three different tables (1. inv_contracts.inv_id = invention.id AND > 2. inv_contracts.con_id = con.id AND > 3. inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id > = 2)) > > -informix outer --- > select count(user.id) > FROM user, ascpDef AS stateDef, address, invention, user as con > , OUTER inv_contracts > WHERE > address.type = 'User' AND > address_id = 1 AND > user.id = address.type_id AND > state_id = stateDef.id AND > invention.user_id = user.id AND > invention.inv_number = '1994376-A' AND > inv_contracts.inv_id = invention.id AND > inv_contracts.con_id = con.id AND > inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = > 2); -- > > If there were only one table connection (say 1. inv_contracts.inv_id = > invention.id AND ) > I would have done it as > (,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = > invention.id) > > but I can do same or similar conversion for 2. and 3. > > I have attempted to do as below but not giving correct count. > > > postgres-- > select count(dbuser.id) > FROM dbuser, ascpDef AS stateDef, address > --, invention > --, dbuser as con > --, OUTER inv_contracts > --1 > --,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = > invention.id > --,dbuser as con LEFT OUTER JOIN inv_contracts ON inv_contracts.con_id = > con.id --,contractDef LEFT OUTER JOIN inv_contracts ON > inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = > 2) > --1 gives ERROR: table name "inv_contracts" specified more than once > -- > --2 > --,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = > invention.id > --,dbuser as con LEFT OUTER JOIN inv_contracts as inv2 ON inv2.con_id = > con.id --,contractDef LEFT OUTER JOIN inv_contracts as inv3 ON > inv3.contract_id IN (select id FROM contractDef WHERE phase_id = 2) > --2 takes very long (cancelled/stoped by me) (informix returns 306229 > within 10sec) > --Cancel request sent > --ERROR: canceling statement due to user request > -- > --3 > --,inv_contracts LEFT OUTER JOIN invention ON inv_contracts.inv_id = > invention.id > --,inv_contracts as inv2 LEFT OUTER JOIN dbuser as con ON inv2.con_id = > con.id --,inv_contracts as inv3 LEFT OUTER JOIN contractDef ON > inv3.contract_id IN (select id FROM contractDef WHERE phase_id = 2) > --3 takes very long (cancelled/stoped by me) (informix returns 306229 > within 10sec) > --Cancel request sent > --ERROR: canceling statement due to user request > -- > --4 > ,inv_contracts LEFT OUTER JOIN invention ON inv_contracts.inv_id = > invention.id LEFT OUTER JOIN dbuser as con ON inv_contracts.con_id = con.id > LEFT OUTER JOIN contractDef ON inv_contracts.contract_id IN (select id FROM > contractDef WHERE phase_id = 2) > --4 returns 1 but (informix returns 306229 within 10sec) > WHERE > address.type = 'User' AND > address_id = 1 AND > dbuser.id = address.type_id AND > state_id = stateDef.id AND > invention.user_id = dbuser.id AND > invention.inv_number = '1994376-A'; > --- > > Thanks for help. > > - > This mail sent through IMP: www.resolution.com > > ---(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 ---(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] Special meaning of NL string
Title: RE: [SQL] Special meaning of NL string Tom, Thanks for your answer. The problem is indeed related to the statistics. The isssue now seems to find out why the statistics are 'incorrect' every day. My gues is the following: we run every night the command VACUUM ANALYZE. This command return wrong statistics info. Because when I run ANALYZE only on a table, everything is fine. We are on PosgreSQL 7.4. Groet, Fred Vellinga -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 11 April 2006 04:58 To: Vellinga, Fred Cc: 'pgsql-sql@postgresql.org' Subject: Re: [SQL] Special meaning of NL string "Vellinga, Fred" <[EMAIL PROTECTED]> writes: > The query > SELECT COUNT(*) FROM Table WHERE Field1 = 'NL' OR Field2 = 'NL' does a > sequence scan instead of an index scan, and is thus very slow. If I > replace NL by BE (Belgium) the query does an index scan. Probably, 'NL' is a lot more common than 'BE' in your table ... the planner does examine statistics while deciding what sort of scan to use. regards, tom lane
[SQL] Truncate and Foreign Key References question
This is in postgres 8.1:
PostgreSQL 8.1.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.2
20041017 (Red Hat 3.4.2-6.fc3)
I've got a table in one schema (work.client_collect_rates) which has an FK
constraint with a table, content.collections_l (definitions shown below).
There's about 500 entries currently in my collections_l table.
I need to wipe out the contents of the collections_l table nightly and refresh
it from a remote master source. (Don't ask ... long & sordid history)
As the sequence below shows, I dropped the FK constraint successfully, but when
I run TRUNCATE collections_l it says:
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "client_collect_rates" references "collections_l" via foreign
key constraint "$2".
HINT: Truncate table "client_collect_rates" at the same time.
This truncation of the second table is *not* an option, but since the
constraint "$2" is clearly gone, I am wondering what in the name of sweet
apples is going on ? Is this a bug ? Have a developed premature senility ?
Any clues for the clueless would be gratefully accepted!
TIA,
Greg Williamson
DBA
GlobeXplorer LLC
billing=# \d work.client_collect_rates
Table "work.client_collect_rates"
Column | Type | Modifiers
---+-+---
contract_id | integer | not null
collection_id | integer | not null
rate | numeric |
break_1 | numeric |
rate_1| numeric |
break_2 | numeric |
rate_2| numeric |
break_3 | numeric |
rate_3| numeric |
break_4 | numeric |
rate_4| numeric |
Indexes:
"clnt_colrate_ndx" UNIQUE, btree (contract_id, collection_id)
Foreign-key constraints:
"$1" FOREIGN KEY (contract_id) REFERENCES client_contracts(contract_id)
"$2" FOREIGN KEY (collection_id) REFERENCES
content.collections_l(collect_id)
billing=# \d content.collections_l
Table "content.collections_l"
Column| Type | Modifiers
--++
collect_id | integer| not null
owner| integer|
collection_name | character(50) |
begin_date | date |
end_date | date |
pos_accuracy | integer|
res_accuracy | integer|
loc_code | character(30) |
color| integer| default 0
category_id | integer|
is_mosaic| integer| not null default 0
detail_metadata_view | character varying(255) |
jdbc_url | character varying(255) |
jdbc_driver | character varying(255) |
Indexes:
"collections_l_pkey" PRIMARY KEY, btree (collect_id)
"collect_own_ndx" btree ("owner", collect_id)
billing=# alter table work.client_collect_rates drop constraint "$2";
ALTER TABLE
billing=# \d work.client_collect_rates
Table "work.client_collect_rates"
Column | Type | Modifiers
---+-+---
contract_id | integer | not null
collection_id | integer | not null
rate | numeric |
break_1 | numeric |
rate_1| numeric |
break_2 | numeric |
rate_2| numeric |
break_3 | numeric |
rate_3| numeric |
break_4 | numeric |
rate_4| numeric |
Indexes:
"clnt_colrate_ndx" UNIQUE, btree (contract_id, collection_id)
Foreign-key constraints:
"$1" FOREIGN KEY (contract_id) REFERENCES client_contracts(contract_id)
(Note that the "$2" FK is gone...)
billing=# truncate content.collections_l;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "client_collect_rates" references "collections_l" via foreign
key constraint "$2".
HINT: Truncate table "client_collect_rates" at the same time.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Please help with a query..
On Tue, Apr 11, 2006 at 02:34:22 -0700, Timo Tuomi <[EMAIL PROTECTED]> wrote: > > I'd need to get the time interval X-Y-Z on each date but I cannot rely > on the date (can't make any joins based on the date part of > timestamps). Instead I'd need to find out X-Y and Y-Z pairs with a > minimal "stop" -time at Y and calculate total travel time for those. If possible you should change your design, so that you can identify a route. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
