Обсуждение: ERROR: missing FROM-clause entry for table
I am receiving this error for the query pasted below. Is the LEFT JOIN on the table not enough? What needs to happen here? I am guess something to do with derived tables http://pastie.org/10715876 -- View this message in context: http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Wed, Feb 10, 2016 at 4:11 PM, bigkev <kevin.waterson@gmail.com> wrote:
> I am receiving this error for the query pasted below.
> Is the LEFT JOIN on the table not enough?
> What needs to happen here?
> I am guess something to do with derived tables
>
> http://pastie.org/10715876
Please be sure to copy the content of your query directly in the emails sent here. External websites like the one you have your content on are short-living things, so the content that you are referring to would get lost from the Postgres archives once your data is removed there or considered out-of-sync.
What is the complete error message that you think is a bug?
--
Michael
Hello > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of bigkev > Sent: Mittwoch, 10. Februar 2016 08:11 > To: pgsql-general@postgresql.org > Subject: [GENERAL] ERROR: missing FROM-clause entry for table > > I am receiving this error for the query pasted below. > Is the LEFT JOIN on the table not enough? > What needs to happen here? > I am guess something to do with derived tables > > http://pastie.org/10715876 It would help to know for which table the clause entry is missing. I guess that the order of the joins is not correct: left join generate_series(c.start_time, c.end_time, '2 weeks'::interval) f(fortnight) ON g.day=f.fortnight LEFT JOIN call_schedule c on extract(dow from c.start_time) = extract(dow from g.day) AND f.fortnight IS NOT NULL AND g.dayBETWEEN c.start_time AND c.end_time In the first line you use c, but this is declared on the following line. Bye Charles > > > > -- > View this message in context: http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On 2/9/2016 11:11 PM, bigkev wrote: > I am receiving this error for the query pasted below. > Is the LEFT JOIN on the table not enough? > What needs to happen here? > I am guess something to do with derived tables > > http://pastie.org/10715876 > Your error is in the reference to c.start_time, c.end_time. During the parse, the system doesn't know about "c" yet. and swapping fortnight and "c" won't help - you can't reference c.start_time in the "from" portion of the join. So - substituting static values for c.start_time, c.end_time : select * FROM generate_series('2016-01-22', '2017-12-31', '1 day'::interval) g(day) left join generate_series('2015-01-25', '2016-07-01', '2 weeks'::interval) f(fortnight) ON g.day=f.fortnight generates results... but I'm not sure it is giving you what you want. Exactly what are you trying to achieve with the fortnight construct? BTW - assuming call_schedule.start_time is a timestamp... do your start/end times cross day boundaries? the test g.day between start/end will never be true otherwise - you are dealing with "midnight" values for time. e.g. '2016-01-23' does not fall between '2016-01-23 08:30:01' and "2016-01-23 10:45:01' Roxanne -- [At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching themthe science. Donald Knuth
Yes, c.start_time is a timestamp. My goal is to list events (calls) which are scheduled on fortnightly basis. The query works with hard coded values, but I need to generate results for days beginning at the start_time and ending with the end_time.: eg: 2016-02-06 2016-02-07 2016-02-08 | Some account name | other info 2016-02-09 2016-02-10 .... 2016-02-22 | Some account name | other info 2016-02-23 2016-02-24 etc etc All dates within the first generate_series are listed, 1 row for each day. The second generate_series is to show events(calls) which begin on c.start_time and end at c.start_time. Records would be displayed, at each 14 days from the start_time. In the above example, this would be 2016-02-08 and end_time would be a date in the future. -- View this message in context: http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750p5886777.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 2/10/2016 1:33 AM, bigkev wrote: > Yes, c.start_time is a timestamp. It is really late where I am, so I apologize if I'm being dense... > My goal is to list events (calls) which are scheduled on fortnightly basis. > The query works with hard coded values, but I need to generate results for > days beginning at the start_time and ending with the end_time.: eg: > > 2016-02-06 > 2016-02-07 > 2016-02-08 | Some account name | other info > 2016-02-09 > 2016-02-10 > .... > 2016-02-22 | Some account name | other info > 2016-02-23 > 2016-02-24 > etc etc Is this exactly the format of the data you are looking for? If there was an event on 02/10, would it show or not? [because it doesn't fit in a fortnight schedule from 02/08, but could still be scheduled to run fortnightly [e.g. 02/10, 02/24...] > All dates within the first generate_series are listed, 1 row for each day. > The second generate_series is to show events(calls) which begin on > c.start_time and end at c.start_time. > Records would be displayed, at each 14 days from the start_time. I am confused about "which" start time. Your description implies there is just one start time that bounds your entire query. You have a column "start_time" in the Call Event table which is presumably the start of an event. However, each row in the table potentially has a totally different value. So what do you mean by "the start time"? There isn't a 14 days from "a" start time if you are using c.start_time and calculating 14 days into the future for each row in the Call Event table.. Each event would then have a fortnight period and your query results are not bounded by time at all. > In the above example, this would be 2016-02-08 and end_time would be a date > in the future. So for "the start time" do you mean the first (minimum) start time in the Call Event table? Or are you trying to gather all the call events that are scheduled 14 days apart, listed in order by day? Is there anything that ties the call events together as being in a set other than they take place exactly 14 days apart? Roxanne -- [At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching themthe science. Donald Knuth
The call_schedule table looks like this id | integer | not null default nextval('call_schedule_id_seq'::regclass) account_id | integer | not null user_id | integer | not null call_type_id | integer | not null call_frequency_id | integer | not null start_time | timestamp without time zone | not null duration | integer | not null end_time | timestamp without time zone | not null Indexes: "call_schedule_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "call_schedule_account_id_fkey" FOREIGN KEY (account_id) REFERENCES accounts(id) "call_schedule_call_frequency_id_fkey" FOREIGN KEY (call_frequency_id) REFERENCES call_frequency(id) "call_schedule_call_type_id_fkey" FOREIGN KEY (call_type_id) REFERENCES call_types(id) "call_schedule_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) This stores the start_time, which is the date and time a series of calls begins. The duration defines how long this event(call) will go for, and the end_time defines when the schedule will stop. I need to generate a calendar eg: 1 year with intervals of 1 day, and include fortnightly calls at the appropriate dates. the call_frequency_id would be 5 for a fortnightly call. I an post schema if you need. -- View this message in context: http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750p5886990.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 2/11/2016 2:15 AM, bigkev wrote:
If I've read your original query correctly.. Based on this ... I think you were just off a little bit on your idea.... This stores the start_time, which is the date and time a series of calls begins. The duration defines how long this event(call) will go for, and the end_time defines when the schedule will stop. I need to generate a calendar eg: 1 year with intervals of 1 day, and include fortnightly calls at the appropriate dates. the call_frequency_id would be 5 for a fortnightly call. I an post schema if you need.
Consider moving the fortnight generation into a sub-query join with the call_schedule... e.g.
SELECT g.*, c.meeting, a.name AS account_name, u.name AS user_name, c.start_time,
c.start_time::timestamp+c.duration * '1s'::interval AS end_time,
ct.name, extract(dow from c.start_time) AS start_day
FROM generate_series('2016-01-22', '2017-12-31', '1 day'::interval) g(day)
LEFT JOIN (
select *, generate_series(c.start_time, c.end_time, '2 week'::interval) meeting
from call_schedule c
where call_frequency_id = 5
) c on ((g.day, '1 day'::interval) OVERLAPS (meeting, c.duration * '1s'::interval))
LEFT JOIN users u ON c.user_id=u.id
LEFT JOIN accounts a ON c.account_id=a.id
LEFT JOIN call_types ct ON c.call_type_id=ct.id
ORDER BY g.day
The generate_series in the sub-query could be genericized to deal with any frequency
through the use of a user defined function or a join on your call_frequency table if it stores "interval" data.
Tuning wise, you may need to move the 3 dependent joins into the sub-query for better performance.
I'm not exactly sure what the sub-query does to the planner for index usage for joins...
I am also not sure what the performance difference might be between using OVERLAPS and BETWEEN.
Roxanne
-- [At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science. Donald Knuth
Yep, that nailed it. Thanks for you time and help. -- View this message in context: http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750p5887183.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.