Error missing from clause entry for table postgres

I'm trying to execute a join query for 4 tables on postgres. Table names: scenarios_scenario payments_invoice payments_payment payments_action (all those weird names are generated by django -)...

I’m trying to execute a join query for 4 tables on postgres.

Table names:

  • scenarios_scenario
  • payments_invoice
  • payments_payment
  • payments_action

(all those weird names are generated by django -)))

Relations:

  • scenarios_scenario [has many] payments_actions
  • payments_action [has one] payments_invoice
  • payments_action [has one] payments_payment

Below one is a working query,

SELECT payments_invoice.*,
    (payments_payment.to_be_paid - payments_payment.paid) as remaining, 
    payments_action.identificator
FROM payments_invoice
JOIN payments_payment
  ON payments_invoice.action_id = payments_payment.action_id
  AND payments_payment.full_payment=2
JOIN payments_action
  ON payments_invoice.action_id = payments_action.id
  AND payments_action.identificator = %s

I just want to retrieve a related field from another table and wrote another query like

SELECT 
  scenarios_scenario.title, payments_invoice.*, 
  (payments_payment.to_be_paid - payments_payment.paid) as remaining, 
  payments_action.identificator, payments_action.scenario_id 
FROM payments_invoice 
JOIN scenarios_scenario 
  ON scenarios_scenario.id = payments_action.scenario_id
JOIN payments_payment 
  ON payments_invoice.action_id = payments_payment.action_id 
  AND payments_payment.full_payment=2 
JOIN payments_action 
  ON payments_invoice.action_id = payments_action.id 
  AND payments_action.identificator = 'EEE45667';

but facing with this error — 

ERROR:  missing FROM-clause entry for table "payments_action"
LINE 2: ...IN  scenarios_scenario ON scenarios_scenario.id = payments_a...
                                                             ^

Looked across SO for similar questions like this (missing FROM-clause entry for table) but weren’t able to find a way. Any help would be appreciated.

Источник

If you’re getting “ERROR:  missing FROM-clause entry for table” in PostgreSQL when using an operator such as UNION, INTERSECT, or EXCEPT, it could be because you’re qualifying a column name with its table name.

To fix this, either remove the table name or use a column alias.

Example of Error

Here’s an example of code that produces the error:

(SELECT TeacherName FROM Teachers)
UNION
(SELECT StudentName FROM Students)
ORDER BY Teachers.TeacherName ASC;

Result:

ERROR:  missing FROM-clause entry for table "teachers"
LINE 4: ORDER BY Teachers.TeacherName ASC;

In this case I tried to order the results by the TeacherName column, but I qualified that column with the table name (I used Teachers.TeacherName to reference the column name).

Referencing tables like this doesn’t work when ordering the results of UNION, EXCEPT, or INTERSECT.

Solution 1

One way to fix this issue is to remove the table name from the ORDER BY clause:

(SELECT TeacherName FROM Teachers)
UNION
(SELECT StudentName FROM Students)
ORDER BY TeacherName ASC;

Solution 2

Another way to fix it is to use an alias for the column:

(SELECT TeacherName t FROM Teachers)
UNION
(SELECT StudentName FROM Students)
ORDER BY t ASC;

With this option, we assign an alias to the column, and then reference that alias in the ORDER BY clause.

Источник

Содержание

  1. Database.Guide
  2. Beginners
  3. Categories
  4. Fix “ERROR: missing FROM-clause entry for table” in PostgreSQL when using UNION, EXCEPT, or INTERSECT
  5. Example of Error
  6. Solution 1
  7. Solution 2
  8. Missing FROM-clause entry for table « », что делать?
  9. Missing FROM-clause entry for table « », что делать?
  10. findAndCount, error: missing FROM-clause entry for table #7367
  11. Comments
  12. This comment has been minimized.
  13. This comment has been minimized.
  14. This comment has been minimized.
  15. This comment has been minimized.
  16. This comment has been minimized.
  17. Custom Column «ERROR: missing FROM-clause entry for table» Postgres #12304
  18. Comments

Database.Guide

Beginners

Categories

  • Azure SQL Edge (16)
  • Database Concepts (48)
  • Database Tools (70)
  • DBMS (8)
  • MariaDB (420)
  • Microsoft Access (17)
  • MongoDB (265)
  • MySQL (375)
  • NoSQL (7)
  • Oracle (296)
  • PostgreSQL (255)
  • Redis (185)
  • SQL (588)
  • SQL Server (888)
  • SQLite (235)

Fix “ERROR: missing FROM-clause entry for table” in PostgreSQL when using UNION, EXCEPT, or INTERSECT

If you’re getting “ERROR: missing FROM-clause entry for table” in PostgreSQL when using an operator such as UNION , INTERSECT , or EXCEPT , it could be because you’re qualifying a column name with its table name.

To fix this, either remove the table name or use a column alias.

Example of Error

Here’s an example of code that produces the error:

In this case I tried to order the results by the TeacherName column, but I qualified that column with the table name (I used Teachers.TeacherName to reference the column name).

Referencing tables like this doesn’t work when ordering the results of UNION , EXCEPT , or INTERSECT .

Solution 1

One way to fix this issue is to remove the table name from the ORDER BY clause:

Solution 2

Another way to fix it is to use an alias for the column:

With this option, we assign an alias to the column, and then reference that alias in the ORDER BY clause.

Источник

Missing FROM-clause entry for table « », что делать?

  • Вопрос задан более двух лет назад
  • 3618 просмотров

Простой 4 комментария

В FROM нужно указать таблицу teterika.users и условие связи с таблицей teterika.lessons.
если условие не указать, то свяжется каждая строка одной таблицы с каждой строкой другой таблицы, получится декартово произведение таблиц.

Кстати, ваш запрос не имеет смысла, потому что из teterika.users.role у вас извлечется только запись ‘tutor’ в соответствии с условием. Т.е. ваш запрос можно заменить на:

Разве что вам действительно нужно получить декартово произведение всех уроков со всеми учителями.

А как это называется,когда после запятой в select указывается условие ‘tutor’ ,этому есть название?

И кстати,мне и нужно извлечь только записи с ‘tutor’ , у меня их определённое количество

И ещё ,если моя команда находится долго в процессе обработки и не выводит мой запрос на экран,о чём это может говорить?

gowwa123, когда в SELECT указывается не имя поля, а конкретное значение, строковое или числовое, это называется константа. И это приводит к тому, что в выборку добавиться колонка, в которой, по каждой строке будет это значение.

И ещё ,если моя команда находится долго в процессе обработки и не выводит мой запрос на экран, о чём это может говорить?

Это может говорить о том, что выборка получается очень большая.

Давайте мы с вами поступим иначе. Вы опишите здесь, структуру ваших таблиц. И объясните, какую конкретно выборку надо получить. А я попробую посоветовать вам правильный запрос.

Источник

Missing FROM-clause entry for table « », что делать?

  • Вопрос задан более двух лет назад
  • 3618 просмотров

Простой 4 комментария

В FROM нужно указать таблицу teterika.users и условие связи с таблицей teterika.lessons.
если условие не указать, то свяжется каждая строка одной таблицы с каждой строкой другой таблицы, получится декартово произведение таблиц.

Кстати, ваш запрос не имеет смысла, потому что из teterika.users.role у вас извлечется только запись ‘tutor’ в соответствии с условием. Т.е. ваш запрос можно заменить на:

Разве что вам действительно нужно получить декартово произведение всех уроков со всеми учителями.

А как это называется,когда после запятой в select указывается условие ‘tutor’ ,этому есть название?

И кстати,мне и нужно извлечь только записи с ‘tutor’ , у меня их определённое количество

И ещё ,если моя команда находится долго в процессе обработки и не выводит мой запрос на экран,о чём это может говорить?

gowwa123, когда в SELECT указывается не имя поля, а конкретное значение, строковое или числовое, это называется константа. И это приводит к тому, что в выборку добавиться колонка, в которой, по каждой строке будет это значение.

И ещё ,если моя команда находится долго в процессе обработки и не выводит мой запрос на экран, о чём это может говорить?

Это может говорить о том, что выборка получается очень большая.

Давайте мы с вами поступим иначе. Вы опишите здесь, структуру ваших таблиц. И объясните, какую конкретно выборку надо получить. А я попробую посоветовать вам правильный запрос.

Источник

findAndCount, error: missing FROM-clause entry for table #7367

When I add limit and offset to this query I get error: missing FROM-clause entry for table «Instruments». When limit and offset are not in this query, it works perfectly.

I expected it to work the same with or without limit or offset. There is just an error:

Dialect: postgres
Database version: 9.5
Sequelize version: 3.30.2

The text was updated successfully, but these errors were encountered:

The issue occurs only when you append limit.I’am also waiting for the fix on this.

This issue will be fixed by appending duplicating:false on include

Appending duplicating: false breaks offset for me — anyone else seeing this? Limit works fine.

With duplicating: false, it fixes the error from happening but instead of limit 10, I get a random number, like 3 or 2 and 2 total pages of results instead of the full 6 or 4 results and 1 total page.

@Mikeysax; this may or may not help you all that much but the way we’ve «worked around» this is to perform the query twice. Once to collect the IDs of the parent entities and another perform the limit and offset. Somewhat thankfully we built an abstraction layer above sequelize that allowed us the flexibility. There’s the obvious and large downside of it requiring two SELECTs on any query that includes a where clause but for us the accuracy outweights the marginal performance concerns (it’s a pretty low percentage of the whole response time).

Here’s a non-generified started for 10:

Not stale. We’re still having to perform a double query and manual count in order to get this working.

This issue contains a code snippet that shows the problem but is not entirely self-contained (i.e. I can’t just copy-paste it and run it). Can someone please provide a SSCCE (also known as MCVE/reprex)?

Same here — adding limit breaks query containing aggregate function logic.

on main query will fix this

Same here — adding limit breaks query containing aggregate function logic.

hi i solved this problem, for you who need count and data match.

if you want to get duplicating data, you must add raw: true, and duplicating: false. and now total data and count must be same. like this :

users.findAndCountAll( <
<
«raw»: true,
«where» : <
«jobid»:req.params.jobid
>,
«attributes»:
Object.keys(this.db.model(«Run»).attributes).concat([
[sequelize.fn(‘COUNT’,sequelize.col(‘messages.id’)),»msg_count»]
]),
«include»:[
<
«model»: this.db.model(«RunMessage»),
«as» : «messages»,
«attributes»:[],
«duplicating: false
>
],
«order»:[[«createdAt»,»DESC»]],
«logging»: console.log,
«limit»: 10,
«offset»: 1
>
>)

Same here — adding limit breaks query containing aggregate function logic.

Источник

Custom Column «ERROR: missing FROM-clause entry for table» Postgres #12304

Describe the bug

Metabase can’t create a custom column even without any calculations on them for some tables.
What these tables have in common is that on the DB they are named e.g. reports__us but on the UI they are shown as «reports us». This seems to break the custom column creation

Logs

Server logs do not show anything about this error. It is like it never happens

To Reproduce
Steps to reproduce the behavior:
0. Try following these on a table with a name like «prices» and a table with a name like «prices__us_today»

  1. From home page select your psql DB
  2. Choose a schema
  3. Choose a table
  4. Click on «show editor»
  5. Click on «Custom Column»
  6. On «field formula» just choose any column and pick a name for the new column
  7. Click on «Done»
  8. Click on «Visualize»

Expected behavior

Metabase should create a new custom column without any issues. For some other tables it works without any issues but for some others it does not work

Screenshots

Information about your Metabase Installation:
<
«browser-info»: <
«language»: «en-GB»,
«platform»: «MacIntel»,
«userAgent»: «Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.132 Safari/537.36»,
«vendor»: «Google Inc.»
>,
«system-info»: <
«file.encoding»: «UTF-8»,
«java.runtime.name»: «OpenJDK Runtime Environment»,
«java.runtime.version»: «11.0.5+10»,
«java.vendor»: «AdoptOpenJDK»,
«java.vendor.url»: «https://adoptopenjdk.net/»,
«java.version»: «11.0.5»,
«java.vm.name»: «OpenJDK 64-Bit Server VM»,
«java.vm.version»: «11.0.5+10»,
«os.name»: «Linux»,
«os.version»: «4.14.171-136.231.amzn2.x86_64»,
«user.language»: «en»,
«user.timezone»: «GMT»
>,
«metabase-info»: <
«databases»: [
«postgres»
],
«hosting-env»: «unknown»,
«application-database»: «postgres»,
«application-database-details»: <
«database»: <
«name»: «PostgreSQL»,
«version»: «11.6»
>,
«jdbc-driver»: <
«name»: «PostgreSQL JDBC Driver»,
«version»: «42.2.8»
>
>,
«run-mode»: «prod»,
«version»: <
«date»: «2020-04-02»,
«tag»: «v0.35.1»,
«branch»: «release-0.35.x»,
«hash»: «e67f169»
>,
«settings»: <
«report-timezone»: «UTC»
>
>
>

Severity

It is severe since most analyst can do any transformations on this data and makes Metabase way less usable for them

Additional context
Add any other context about the problem here.

The text was updated successfully, but these errors were encountered:

Hi @rubenarevalo
I cannot reproduce your issue. Can you supply a sample table?

I noticed a new pattern. Those tables in which I receive this error have a large number of columns (30

100).
Is there a cap on the number of columns on Metabase that might be causing this issue?

@rubenarevalo No, I’ve had tables with 240 columns.

@flamber But have you tried adding an extra column from the UI for such a large size table
?

@rubenarevalo Can you please supply a sample table?

These are the DDL of two tables. The first one seems to work but the second one causes the issue that I have reported. They are both on the same schema

@rubenarevalo Which one of the tables are similar to prices ? Can you provide a full example with data as well? See #12248 (comment) for how an example could look like.
I’m pretty sure that you’re seeing errors during the sync+scan process, which is probably the root cause of this. Try doing a forced sync+scan via Admin > Databases > (db), and then check Admin > Troubleshooting > Logs for any warnings/errors during that process.

When running the async these are the errors I get this one multiple times:

Also this warn multiple times:

Also there is this info shown:

@rubenarevalo So where does the column type _text come from? I’m guessing it might have something to do with the failed sync. But it seems like the main problem relates to the failed sync, and not the double-underscore.

the double underscore is not the issue. That’s why I changed the title earlier. The only common pattern that I see it is the amount of columns. And the problem still is that when I check a table from my data source in Psql I can not create a custom column sometimes. @flamber

The fields that we complain are of unknown type _text , what are they in the actual schema? Both on the Postgres side and what you get in Admin > Data Model > Your DB > myschema.bad and check «Show original schema»

@sbelak I do not know which field Metabase complains about because it is not shown at all on the logs.
This is how the log looks like for this particular type of Warning.

I reproduced this issue on internal instance (almost 0.35.3), while working on #11519 (comment):

This is very likely fixed with #12328. Mind trying there

. it’s not 🙁 Seems to be a lazyness bug as it only crops up with big tables.

Источник

Источник

I don’t understand the reasoning here, as I thought I’d created the
temp table a_cte and could freely use it within the scope

You are facing with SQL Error [42P01]: ERROR: missing FROM-clause entry for table "a_cte" error because you never select from a_cte.

Use:

WITH a_cte AS (
     SELECT min(x) AS min_a
     FROM a
)
SELECT b.* 
FROM b  
inner join a_cte on b.y <= a_cte.min_a

https://dbfiddle.uk/Ec_MtFek

WITH Queries (Common Table Expressions)

Edit.

I don’t understand how this is fundamentally different to my usage
though, just has a join instead of a where. Can you write it so that
WHERE works please?

As I already mentioned, when using WITH a_cte AS () you need to use a_cte.

The where is redundant on your case , it is used on the join condition.

Maybe you are confusing the old join syntax which I do not recommend. This is still a join , but written differently

WITH a_cte AS (
     SELECT min(x) AS min_a
     FROM a
)
SELECT * FROM b, a_cte
WHERE b.y <= a_cte.min_a;

https://dbfiddle.uk/uk_98YSX

Источник

I reproduced this issue on internal instance (almost 0.35.3), while working on #11519 (comment):

[15934dc5-12d2-40e0-86d7-aebacc08e318] 2020-04-20T19:39:24+02:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: null
{:database_id 8,
 :started_at (t/zoned-date-time "2020-04-20T17:39:22.832778Z[GMT]"),
 :state "42P01",
 :json_query
 {:type "query", :query {:source-table 21, :expressions {:Team LOB ["+" ["field-id" 416] ["field-id" 378]]}}, :database 8, :parameters [], :middleware {:add-default-userland-constraints? true}},
 :native
 {:query
  "SELECT "source"."game_id" AS "game_id", "source"."away_err_ct" AS "away_err_ct", "source"."away_finish_pit_id" AS "away_finish_pit_id", "source"."away_hits_ct" AS "away_hits_ct", "source"."away_lineup1_bat_id" AS "away_lineup1_bat_id", "source"."away_lineup1_fld_cd" AS "away_lineup1_fld_cd", "source"."away_lineup2_bat_id" AS "away_lineup2_bat_id", "source"."away_lineup2_fld_cd" AS "away_lineup2_fld_cd", "source"."away_lineup3_bat_id" AS "away_lineup3_bat_id", "source"."away_lineup3_fld_cd" AS "away_lineup3_fld_cd", "source"."away_lineup4_bat_id" AS "away_lineup4_bat_id", "source"."away_lineup4_fld_cd" AS "away_lineup4_fld_cd", "source"."away_lineup5_bat_id" AS "away_lineup5_bat_id", "source"."away_lineup5_fld_cd" AS "away_lineup5_fld_cd", "source"."away_lineup6_bat_id" AS "away_lineup6_bat_id", "source"."away_lineup6_fld_cd" AS "away_lineup6_fld_cd", "source"."away_lineup7_bat_id" AS "away_lineup7_bat_id", "source"."away_lineup7_fld_cd" AS "away_lineup7_fld_cd", "source"."away_lineup8_bat_id" AS "away_lineup8_bat_id", "source"."away_lineup8_fld_cd" AS "away_lineup8_fld_cd", "source"."away_lineup9_bat_id" AS "away_lineup9_bat_id", "source"."away_lineup9_fld_cd" AS "away_lineup9_fld_cd", "source"."away_lob_ct" AS "away_lob_ct", "source"."away_score_ct" AS "away_score_ct", "source"."away_start_pit_id" AS "away_start_pit_id", "source"."away_team_id" AS "away_team_id", "source"."base1_ump_id" AS "base1_ump_id", "source"."base2_ump_id" AS "base2_ump_id", "source"."base3_ump_id" AS "base3_ump_id", "source"."base4_ump_id" AS "base4_ump_id", "source"."daynight_park_cd" AS "daynight_park_cd", "source"."dh_fl" AS "dh_fl", "public"."games"."edit_record_ts" AS "edit_record_ts", "public"."games"."field_park_cd" AS "field_park_cd", "public"."games"."game_ct" AS "game_ct", "public"."games"."game_dt" AS "game_dt", "public"."games"."game_dy" AS "game_dy", "public"."games"."gwrbi_bat_id" AS "gwrbi_bat_id", "public"."games"."home_err_ct" AS "home_err_ct", "public"."games"."home_finish_pit_id" AS "home_finish_pit_id", "public"."games"."home_hits_ct" AS "home_hits_ct", "public"."games"."home_lineup1_bat_id" AS "home_lineup1_bat_id", "public"."games"."home_lineup1_fld_cd" AS "home_lineup1_fld_cd", "public"."games"."home_lineup2_bat_id" AS "home_lineup2_bat_id", "public"."games"."home_lineup2_fld_cd" AS "home_lineup2_fld_cd", "public"."games"."home_lineup3_bat_id" AS "home_lineup3_bat_id", "public"."games"."home_lineup3_fld_cd" AS "home_lineup3_fld_cd", "public"."games"."home_lineup4_bat_id" AS "home_lineup4_bat_id", "public"."games"."home_lineup4_fld_cd" AS "home_lineup4_fld_cd", "public"."games"."home_lineup5_bat_id" AS "home_lineup5_bat_id", "public"."games"."home_lineup5_fld_cd" AS "home_lineup5_fld_cd", "public"."games"."home_lineup6_bat_id" AS "home_lineup6_bat_id", "public"."games"."home_lineup6_fld_cd" AS "home_lineup6_fld_cd", "public"."games"."home_lineup7_bat_id" AS "home_lineup7_bat_id", "public"."games"."home_lineup7_fld_cd" AS "home_lineup7_fld_cd", "public"."games"."home_lineup8_bat_id" AS "home_lineup8_bat_id", "public"."games"."home_lineup8_fld_cd" AS "home_lineup8_fld_cd", "public"."games"."home_lineup9_bat_id" AS "home_lineup9_bat_id", "public"."games"."home_lineup9_fld_cd" AS "home_lineup9_fld_cd", "public"."games"."home_lob_ct" AS "home_lob_ct", "public"."games"."home_score_ct" AS "home_score_ct", "public"."games"."home_start_pit_id" AS "home_start_pit_id", "public"."games"."home_team_id" AS "home_team_id", "public"."games"."inn_ct" AS "inn_ct", "public"."games"."input_record_ts" AS "input_record_ts", "public"."games"."inputter_record_id" AS "inputter_record_id", "public"."games"."lf_ump_id" AS "lf_ump_id", "public"."games"."lose_pit_id" AS "lose_pit_id", "public"."games"."method_record_cd" AS "method_record_cd", "public"."games"."minutes_game_ct" AS "minutes_game_ct", "public"."games"."park_id" AS "park_id", "public"."games"."pitches_record_cd" AS "pitches_record_cd", "public"."games"."precip_park_cd" AS "precip_park_cd", "public"."games"."rf_ump_id" AS "rf_ump_id", "public"."games"."save_pit_id" AS "save_pit_id", "public"."games"."scorer_record_id" AS "scorer_record_id", "public"."games"."sky_park_cd" AS "sky_park_cd", "public"."games"."start_game_tm" AS "start_game_tm", "public"."games"."temp_park_ct" AS "temp_park_ct", "public"."games"."translator_record_id" AS "translator_record_id", "public"."games"."wind_direction_park_cd" AS "wind_direction_park_cd", "public"."games"."wind_speed_park_ct" AS "wind_speed_park_ct", "public"."games"."win_pit_id" AS "win_pit_id", "Team LOB" AS "Team LOB", "source"."name" AS "name" FROM (SELECT ("public"."games"."home_lob_ct" + "public"."games"."away_lob_ct") AS "Team LOB", "public"."games"."home_lob_ct" AS "home_lob_ct", "public"."games"."away_lob_ct" AS "away_lob_ct", "public"."games"."game_id" AS "game_id", "public"."games"."away_err_ct" AS "away_err_ct", "public"."games"."away_finish_pit_id" AS "away_finish_pit_id", "public"."games"."away_hits_ct" AS "away_hits_ct", "public"."games"."away_lineup1_bat_id" AS "away_lineup1_bat_id", "public"."games"."away_lineup1_fld_cd" AS "away_lineup1_fld_cd", "public"."games"."away_lineup2_bat_id" AS "away_lineup2_bat_id", "public"."games"."away_lineup2_fld_cd" AS "away_lineup2_fld_cd", "public"."games"."away_lineup3_bat_id" AS "away_lineup3_bat_id", "public"."games"."away_lineup3_fld_cd" AS "away_lineup3_fld_cd", "public"."games"."away_lineup4_bat_id" AS "away_lineup4_bat_id", "public"."games"."away_lineup4_fld_cd" AS "away_lineup4_fld_cd", "public"."games"."away_lineup5_bat_id" AS "away_lineup5_bat_id", "public"."games"."away_lineup5_fld_cd" AS "away_lineup5_fld_cd", "public"."games"."away_lineup6_bat_id" AS "away_lineup6_bat_id", "public"."games"."away_lineup6_fld_cd" AS "away_lineup6_fld_cd", "public"."games"."away_lineup7_bat_id" AS "away_lineup7_bat_id", "public"."games"."away_lineup7_fld_cd" AS "away_lineup7_fld_cd", "public"."games"."away_lineup8_bat_id" AS "away_lineup8_bat_id", "public"."games"."away_lineup8_fld_cd" AS "away_lineup8_fld_cd", "public"."games"."away_lineup9_bat_id" AS "away_lineup9_bat_id", "public"."games"."away_lineup9_fld_cd" AS "away_lineup9_fld_cd", "public"."games"."away_score_ct" AS "away_score_ct", "public"."games"."away_start_pit_id" AS "away_start_pit_id", "public"."games"."away_team_id" AS "away_team_id", "public"."games"."base1_ump_id" AS "base1_ump_id", "public"."games"."base2_ump_id" AS "base2_ump_id", "public"."games"."base3_ump_id" AS "base3_ump_id", "public"."games"."base4_ump_id" AS "base4_ump_id", "public"."games"."daynight_park_cd" AS "daynight_park_cd", "public"."games"."dh_fl" AS "dh_fl", "public"."games"."edit_record_ts" AS "edit_record_ts", "public"."games"."field_park_cd" AS "field_park_cd", "public"."games"."game_ct" AS "game_ct", "public"."games"."game_dt" AS "game_dt", "public"."games"."game_dy" AS "game_dy", "public"."games"."gwrbi_bat_id" AS "gwrbi_bat_id", "public"."games"."home_err_ct" AS "home_err_ct", "public"."games"."home_finish_pit_id" AS "home_finish_pit_id", "public"."games"."home_hits_ct" AS "home_hits_ct", "public"."games"."home_lineup1_bat_id" AS "home_lineup1_bat_id", "public"."games"."home_lineup1_fld_cd" AS "home_lineup1_fld_cd", "public"."games"."home_lineup2_bat_id" AS "home_lineup2_bat_id", "public"."games"."home_lineup2_fld_cd" AS "home_lineup2_fld_cd", "public"."games"."home_lineup3_bat_id" AS "home_lineup3_bat_id", "public"."games"."home_lineup3_fld_cd" AS "home_lineup3_fld_cd", "public"."games"."home_lineup4_bat_id" AS "home_lineup4_bat_id", "public"."games"."home_lineup4_fld_cd" AS "home_lineup4_fld_cd", "public"."games"."home_lineup5_bat_id" AS "home_lineup5_bat_id", "public"."games"."home_lineup5_fld_cd" AS "home_lineup5_fld_cd", "public"."games"."home_lineup6_bat_id" AS "home_lineup6_bat_id", "public"."games"."home_lineup6_fld_cd" AS "home_lineup6_fld_cd", "public"."games"."home_lineup7_bat_id" AS "home_lineup7_bat_id", "public"."games"."home_lineup7_fld_cd" AS "home_lineup7_fld_cd", "public"."games"."home_lineup8_bat_id" AS "home_lineup8_bat_id", "public"."games"."home_lineup8_fld_cd" AS "home_lineup8_fld_cd", "public"."games"."home_lineup9_bat_id" AS "home_lineup9_bat_id", "public"."games"."home_lineup9_fld_cd" AS "home_lineup9_fld_cd", "public"."games"."home_score_ct" AS "home_score_ct", "public"."games"."home_start_pit_id" AS "home_start_pit_id", "public"."games"."home_team_id" AS "home_team_id", "public"."games"."inn_ct" AS "inn_ct", "public"."games"."input_record_ts" AS "input_record_ts", "public"."games"."inputter_record_id" AS "inputter_record_id", "public"."games"."lf_ump_id" AS "lf_ump_id", "public"."games"."lose_pit_id" AS "lose_pit_id", "public"."games"."method_record_cd" AS "method_record_cd", "public"."games"."minutes_game_ct" AS "minutes_game_ct", "public"."games"."park_id" AS "park_id", "public"."games"."pitches_record_cd" AS "pitches_record_cd", "public"."games"."precip_park_cd" AS "precip_park_cd", "public"."games"."rf_ump_id" AS "rf_ump_id", "public"."games"."save_pit_id" AS "save_pit_id", "public"."games"."scorer_record_id" AS "scorer_record_id", "public"."games"."sky_park_cd" AS "sky_park_cd", "public"."games"."start_game_tm" AS "start_game_tm", "public"."games"."temp_park_ct" AS "temp_park_ct", "public"."games"."translator_record_id" AS "translator_record_id", "public"."games"."wind_direction_park_cd" AS "wind_direction_park_cd", "public"."games"."wind_speed_park_ct" AS "wind_speed_park_ct", "public"."games"."win_pit_id" AS "win_pit_id", "parkcodes__via__park_id"."name" AS "name", "parkcodes__via__park_id"."park_id" AS "park_id_2" FROM "public"."games" LEFT JOIN "public"."parkcodes" "parkcodes__via__park_id" ON "public"."games"."park_id" = "parkcodes__via__park_id"."park_id") "source" LIMIT 2000",
  :params nil},
 :status :failed,
 :class org.postgresql.util.PSQLException,
 :stacktrace
 ["org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497)"
  "org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233)"
  "org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)"
  "org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)"
  "org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)"
  "org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)"
  "org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:108)"
  "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)"
  "--> driver.sql_jdbc.execute$fn__70473.invokeStatic(execute.clj:267)"
  "driver.sql_jdbc.execute$fn__70473.invoke(execute.clj:265)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:389)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:377)"
  "driver.sql_jdbc$fn__72509.invokeStatic(sql_jdbc.clj:50)"
  "driver.sql_jdbc$fn__72509.invoke(sql_jdbc.clj:48)"
  "query_processor.context$executef.invokeStatic(context.clj:59)"
  "query_processor.context$executef.invoke(context.clj:48)"
  "query_processor.context.default$default_runf.invokeStatic(default.clj:69)"
  "query_processor.context.default$default_runf.invoke(default.clj:67)"
  "query_processor.context$runf.invokeStatic(context.clj:45)"
  "query_processor.context$runf.invoke(context.clj:39)"
  "query_processor.reducible$pivot.invokeStatic(reducible.clj:34)"
  "query_processor.reducible$pivot.invoke(reducible.clj:31)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__42822.invoke(mbql_to_native.clj:26)"
  "query_processor.middleware.check_features$check_features$fn__42136.invoke(check_features.clj:42)"
  "query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__42987.invoke(optimize_datetime_filters.clj:133)"
  "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__46884.invoke(wrap_value_literals.clj:137)"
  "query_processor.middleware.annotate$add_column_info$fn__40765.invoke(annotate.clj:577)"
  "query_processor.middleware.permissions$check_query_permissions$fn__42011.invoke(permissions.clj:64)"
  "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__43486.invoke(pre_alias_aggregations.clj:40)"
  "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__42209.invoke(cumulative_aggregations.clj:61)"
  "query_processor.middleware.resolve_joins$resolve_joins$fn__44018.invoke(resolve_joins.clj:183)"
  "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__38952.invoke(add_implicit_joins.clj:245)"
  "query_processor.middleware.limit$limit$fn__42808.invoke(limit.clj:38)"
  "query_processor.middleware.format_rows$format_rows$fn__42789.invoke(format_rows.clj:81)"
  "query_processor.middleware.desugar$desugar$fn__42275.invoke(desugar.clj:22)"
  "query_processor.middleware.binning$update_binning_strategy$fn__41309.invoke(binning.clj:229)"
  "query_processor.middleware.resolve_fields$resolve_fields$fn__41817.invoke(resolve_fields.clj:24)"
  "query_processor.middleware.add_dimension_projections$add_remapping$fn__38488.invoke(add_dimension_projections.clj:272)"
  "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__38708.invoke(add_implicit_clauses.clj:147)"
  "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__39101.invoke(add_source_metadata.clj:105)"
  "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__43683.invoke(reconcile_breakout_and_order_by_bucketing.clj:98)"
  "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__40950.invoke(auto_bucket_datetimes.clj:125)"
  "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__41864.invoke(resolve_source_table.clj:46)"
  "query_processor.middleware.parameters$substitute_parameters$fn__43468.invoke(parameters.clj:97)"
  "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__41916.invoke(resolve_referenced.clj:80)"
  "query_processor.middleware.expand_macros$expand_macros$fn__42531.invoke(expand_macros.clj:158)"
  "query_processor.middleware.add_timezone_info$add_timezone_info$fn__39132.invoke(add_timezone_info.clj:15)"
  "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__46768.invoke(splice_params_in_response.clj:32)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__43694$fn__43698.invoke(resolve_database_and_driver.clj:33)"
  "driver$do_with_driver.invokeStatic(driver.clj:61)"
  "driver$do_with_driver.invoke(driver.clj:57)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__43694.invoke(resolve_database_and_driver.clj:27)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__42737.invoke(fetch_source_query.clj:243)"
  "query_processor.middleware.store$initialize_store$fn__46777$fn__46778.invoke(store.clj:11)"
  "query_processor.store$do_with_store.invokeStatic(store.clj:46)"
  "query_processor.store$do_with_store.invoke(store.clj:40)"
  "query_processor.middleware.store$initialize_store$fn__46777.invoke(store.clj:10)"
  "query_processor.middleware.cache$maybe_return_cached_results$fn__41793.invoke(cache.clj:208)"
  "query_processor.middleware.validate$validate_query$fn__46786.invoke(validate.clj:10)"
  "query_processor.middleware.normalize_query$normalize$fn__42835.invoke(normalize_query.clj:22)"
  "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__38970.invoke(add_rows_truncated.clj:36)"
  "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__46753.invoke(results_metadata.clj:128)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__42152.invoke(constraints.clj:42)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__43557.invoke(process_userland_query.clj:136)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__42095.invoke(catch_exceptions.clj:174)"
  "query_processor.reducible$async_qp$qp_STAR___37771$thunk__37772.invoke(reducible.clj:101)"
  "query_processor.reducible$async_qp$qp_STAR___37771.invoke(reducible.clj:107)"
  "query_processor.reducible$sync_qp$qp_STAR___37780$fn__37783.invoke(reducible.clj:133)"
  "query_processor.reducible$sync_qp$qp_STAR___37780.invoke(reducible.clj:132)"
  "query_processor$process_userland_query.invokeStatic(query_processor.clj:218)"
  "query_processor$process_userland_query.doInvoke(query_processor.clj:214)"
  "query_processor$fn__46952$process_query_and_save_execution_BANG___46961$fn__46964.invoke(query_processor.clj:233)"
  "query_processor$fn__46952$process_query_and_save_execution_BANG___46961.invoke(query_processor.clj:225)"
  "query_processor$fn__46996$process_query_and_save_with_max_results_constraints_BANG___47005$fn__47008.invoke(query_processor.clj:245)"
  "query_processor$fn__46996$process_query_and_save_with_max_results_constraints_BANG___47005.invoke(query_processor.clj:238)"
  "api.dataset$fn__50294$fn__50297.invoke(dataset.clj:52)"
  "query_processor.streaming$streaming_response_STAR_$fn__33809$fn__33810.invoke(streaming.clj:73)"
  "query_processor.streaming$streaming_response_STAR_$fn__33809.invoke(streaming.clj:72)"
  "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:66)"
  "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:64)"
  "async.streaming_response$do_f_async$fn__22877.invoke(streaming_response.clj:85)"],
 :context :ad-hoc,
 :error "ERROR: missing FROM-clause entry for table "games"n  Position: 1757",
 :row_count 0,
 :running_time 0,
 :preprocessed
 {:type :query,
  :query
  {:source-table 21,
   :expressions {:Team LOB [:+ [:field-id 416] [:field-id 378]]},
   :fields
   [[:field-id 393]
    [:field-id 357]
    [:field-id 358]
    [:field-id 359]
    [:field-id 360]
    [:field-id 361]
    [:field-id 362]
    [:field-id 363]
    [:field-id 364]
    [:field-id 365]
    [:field-id 366]
    [:field-id 367]
    [:field-id 368]
    [:field-id 369]
    [:field-id 370]
    [:field-id 371]
    [:field-id 372]
    [:field-id 373]
    [:field-id 374]
    [:field-id 375]
    [:field-id 376]
    [:field-id 377]
    [:field-id 378]
    [:field-id 379]
    [:field-id 380]
    [:field-id 381]
    [:field-id 382]
    [:field-id 383]
    [:field-id 384]
    [:field-id 385]
    [:field-id 386]
    [:field-id 387]
    [:field-id 388]
    [:field-id 389]
    [:field-id 390]
    [:field-id 391]
    [:field-id 392]
    [:field-id 394]
    [:field-id 395]
    [:field-id 396]
    [:field-id 397]
    [:field-id 398]
    [:field-id 399]
    [:field-id 400]
    [:field-id 401]
    [:field-id 402]
    [:field-id 403]
    [:field-id 404]
    [:field-id 405]
    [:field-id 406]
    [:field-id 407]
    [:field-id 408]
    [:field-id 409]
    [:field-id 410]
    [:field-id 411]
    [:field-id 412]
    [:field-id 413]
    [:field-id 414]
    [:field-id 415]
    [:field-id 416]
    [:field-id 417]
    [:field-id 418]
    [:field-id 419]
    [:field-id 420]
    [:field-id 421]
    [:field-id 422]
    [:field-id 423]
    [:field-id 424]
    [:field-id 425]
    [:field-id 426]
    [:field-id 427]
    [:field-id 428]
    [:field-id 429]
    [:field-id 430]
    [:field-id 431]
    [:field-id 432]
    [:field-id 433]
    [:field-id 434]
    [:field-id 435]
    [:field-id 436]
    [:field-id 438]
    [:field-id 439]
    [:field-id 437]
    [:expression "Team LOB"]
    [:joined-field "parkcodes__via__park_id" [:field-id 508]]],
   :limit 2000,
   :joins [{:strategy :left-join, :source-table 38, :alias "parkcodes__via__park_id", :fk-field-id 427, :condition [:= [:field-id 427] [:joined-field "parkcodes__via__park_id" [:field-id 510]]]}]},
  :database 8,
  :middleware {:add-default-userland-constraints? true},
  :info
  {:executed-by 44,
   :context :ad-hoc,
   :nested? false,
   :query-hash [3, 81, -63, -112, -107, -22, 34, 97, 111, -90, 80, 107, 107, -29, -75, 13, -49, 113, 116, 42, -60, 1, -36, 24, 78, -96, -25, 123, 76, 78, -73, -31]},
  :constraints {:max-results 10000, :max-results-bare-rows 2000}},
 :data {:rows [], :cols []}}
Источник

I get an error while attempting to solve this query:

Table locales.countries:

locl_ctry_id icc
218 387
219 385
220 381
297 382

Table locales.networks:

locl_ntwrk_id locl_ctry_id
21890 218
21805 218
21803 218
21910 219
21902 219
21901 219
21912 219

And table pricings.charges:

locl_ntwrk_id t_valid locl_ccy_id cost
21890 2017-02-06 00:00:00.000000 978 1.0
21890 2019-04-01 00:00:00.000000 978 2.0
21805 2019-04-01 00:00:00.000000 978 2.0
21805 2017-02-06 00:00:00.000000 978 1.0
21803 2017-02-06 00:00:00.000000 978 1.0

I have multiple prices per each locl_ntwrk_id, so what I am trying to do is to get only the last price per each locl_ntwrk_id.

This is my SQL:

SELECT
    locales.countries.locl_ctry_id,
    locales.countries.icc,

    locales.networks.locl_ntwrk_id,
    locales.networks.locl_ctry_id,

    pricings.charges.locl_ntwrk_id,
    pricings.charges.t_valid,
    pricings.charges.locl_ccy_id,
    pricings.charges.cost

FROM locales.countries

LEFT JOIN locales.networks
    ON locales.networks.locl_ctry_id = locales.countries.locl_ctry_id

LEFT OUTER JOIN (
    SELECT
           pricings.charges.locl_ntwrk_id,
           MAX(pricings.charges.t_valid) AS MaxDate
    FROM pricings.charges
    WHERE pricings.charges.t_valid <= CURRENT_TIMESTAMP(6)
    GROUP BY pricings.charges.locl_ntwrk_id
) AS temp ON temp.locl_ntwrk_id = locales.networks.locl_ntwrk_id

WHERE
    left(locales.countries.icc::text, 2) IN ('38')

Expected outcome if CURRENT_TIMESTAMP is 2019-04-02:

locl_ctry_id icc locl_ntwrk_id t_valid locl_ccy_id cost
218 387 21890 2019-04-01 00:00:00.000000 978 2.0
218 387 21805 2019-04-01 00:00:00.000000 978 2.0
218 387 21803 2017-02-06 00:00:00.000000 978 1.0

Expected outcome if CURRENT_TIMESTAMP is 2019-03-20:

locl_ctry_id icc locl_ntwrk_id t_valid locl_ccy_id cost
218 387 21890 2017-02-06 00:00:00.000000 978 1.0
218 387 21805 2017-02-06 00:00:00.000000 978 1.0
218 387 21803 2017-02-06 00:00:00.000000 978 1.0
Источник

Missing from-clause entry for table / undefined table error-postgresql

Most engaging questions

postgresql

  • postgres table privileges do not allow user to create/select view
  • Rails & Postgresql: how to group queries by hour?
  • Why I am getting «column reference *** is ambiguous»?
  • Postgres : No suitable Driver found for jdbc
  • Update postgresql table with values from another table
  • Django prefetch_related optimize query but still very slow
  • PostgreSQL 9.5: Convert intervals INTO milliseconds
  • md5() works with literal, but not with column data
  • Copy value from other table on insert trigger, postgres
  • Sequelize grouping by hours of a date range

mongodb

  • MongoDB: No server chosen by ReadPreferenceServerSelector
  • Simple boolean data update with mongdb?
  • Change an existing object in an array but still preserve key uniqueness
  • Spring — mongodb — aggregation — The ‘cursor’ option is required
  • Number of requests until last reset
  • @DbRef is not saving child object automatically in spring data mongodb
  • systemctl not found while building mongo image
  • Importing file with unknown encoding from Python into MongoDB
  • Partial update of embedded document in mongoDB using mgo
  • Mongodb query. How find the nearest to current date?

mysql

  • Mysql order items by the newest of 2 dates
  • Use specific mysql index with rails
  • cannot find -lmysqlclient
  • SQL: Move column data to other table in same relation
  • MySQL For Visual Studio 2012/2013
  • Is there a reason not to use <=> (null safe equals operator) in mysql instead of =?
  • How can my application benefit from temporary tables?
  • Storing data of rich text box to database with formatting
  • phpMyAdmin: secret passphrase?
  • Convert HEX column to DEC in MySQL

score:0

Accepted answer

Just did the query without union and it works!
It wasn’t working earlier because the result set it returned looked different for some reaosn.

More questions

  • PostgreSQL missing from clause entry for table in function
  • Missing from-clause entry for table / undefined table error
  • Missing FROM clause entry for table «product»
  • missing FROM clause entry for table
  • Rails Includes Error : UndefinedTable: ERROR: missing FROM-clause entry for table
  • Using results from CTE giving ERROR: missing FROM-clause entry for table «cte»
  • Getting from clause entry error when trying to join table
  • Using the alias from the select in the WHERE clause leads to error : «Missing FROM-clause entry for a table»
  • ERROR: missing FROM- clause entry for table «Users»
  • missing FROM-clause entry for table
  • Mixing explicit and implicit joins fails with «There is an entry for table … but it cannot be referenced from this part of the query»
  • Rails PG::UndefinedTable: ERROR: missing FROM-clause entry for table
  • Postgres: missing FROM-clause entry for table
  • Sequelize missing FROM-clause entry for table Postgres
  • TypeORM: [364] ERROR: missing FROM-clause entry for table «userorganisation» at character 401
  • Missing FROM-clause entry for a table
  • ERROR: missing FROM-clause entry for table «new»
  • Knex error: missing FROM-clause entry for table
  • Jooq ERROR: missing FROM-clause entry for table for nested query(sum and group by)
  • Another missing FROM-clause entry for table
  • ERROR: missing FROM-clause entry for table «p»
  • ERROR: missing FROM-clause entry for table when running function
  • HQL 42P01: missing FROM-clause entry for table
  • postgresql 9.2.2. there is an entry for table «yyy», but it cannot be referenced from this part of the query
  • PG::UndefinedTable: ERROR: missing FROM-clause entry for table when using SELECT DISTINCT ON
  • ERROR: missing FROM-clause entry for table «cte» while executing query in CTE
  • Ruby — PG::UndefinedTable: ERROR: missing FROM-clause entry for table
  • RETURNING causes error: missing FROM-clause entry for table
  • How to update or insert data with WITH subquery? and avoid `PG::UndefinedTable: ERROR: missing FROM-clause entry for table` error
  • Missing FROM-clause entry for table «token»

More questions with similar tag

  • Find previous/next rows with order by when querying for a specific id
  • Join on dynamic column name
  • Enabling hstore when deploying with rubber
  • PostgreSQL — multiple aggregate queries from the same function call
  • Generate series count on multiple columns
  • What are some open-source applications written in C/C++ using PostgreSQL?
  • Postgresql write csv to table loop failing unexpectedly mid-loop
  • Trying to unnest a column of type Text[] is skipping the columns that are null — Postgres
  • Postgres: Force scientific notation when serialization jsonb number
  • CSV data into postgreSQL using Python
  • Good Postgres graphical client for Windows
  • Access set variables in psql for Postgres function bodies
  • SQL difference between two rows with timestamp
  • Postgresql: notification if a view is modified
  • Sort by associated table’s column
  • FiveTran connects with PostgreSQL database restored every day
  • Django Raw Query Output Inconsistent with pgAdmin Output
  • How to remove old WAL file in postgresql?
  • Changing persistence.xml to use PostgreSQL instead of MySQL
  • Update Postgresql table straight from a JSON object
  • How to timeout idle user sessions in Redshift?
  • How to query PostgrSQL JSONB array with Any with Dapper
  • Is it possible to create view for insert query
  • Connection pooling with PHP, Zend Framework and PostgreSQL
  • PDO bind params placeholders PostgreSQL conflict
  • Using SQL AND only when the value is not null
  • Install PostGIS on Ubuntu 16.04 Xenial
  • Add db_index to Django User model?
  • Error: No module named psycopg2.extensions django postgresql
  • Hashing a String to a Numeric Value in PostgreSQL

Ezoicreport this ad

Ezoicreport this ad

oracle

sql-server

Источник

Dancing_god

15 / 2 / 1

Регистрация: 18.09.2015

Сообщений: 227

1

15.06.2020, 23:48. Показов 11858. Ответов 8

Метки нет (Все метки)

Здравствуйте. Возникла ошибка:

SQL 
1
2

ERROR:  missing FROM-clause entry FOR TABLE "a"
LINE 4: ...OM forum_album WHERE (forum_album.creation_date = A.creation..

При попытке выполнения запроса:

SQL 
1
2
3
4
5

WITH A AS (SELECT forum_album.title_id, MAX(forum_album.creation_date) AS creation_date FROM forum_album
WHERE forum_album.owner_id_id = 2 GROUP BY forum_album.title_id),
C AS (SELECT forum_album.title_id, forum_album.photo, forum_album.creation_date 
FROM forum_album WHERE (forum_album.creation_date = A.creation_date AND forum_album.title_id = A.title_id))
SELECT * FROM forum_albums AS B LEFT JOIN C ON B.title_id=C.title_id WHERE (B.owner_id_id = 2);

Не очень понимаю, где именно Postgres просит указать оператор FROM. И в целом не особо понятно, почему СУБД не может использовать значения полей таблицы A, ведь оператором WITH она создается, и по сути должна быть доступна для дальнейших взаимодействий с ней.

Помогите, пожалуйста, разобраться и решить проблему.

__________________
Помощь в написании контрольных, курсовых и дипломных работ, диссертаций здесь



0

1187 / 917 / 367

Регистрация: 02.09.2012

Сообщений: 2,790

16.06.2020, 10:46

2

уберите большие буквы из псевдонимов таблиц и CTE

Добавлено через 31 секунду
замените маленькими, и лучше использовать больше чем одну букву, чтобы не было случайной неоднозначности



0

15 / 2 / 1

Регистрация: 18.09.2015

Сообщений: 227

16.06.2020, 11:22

 [ТС]

3

Заменил большие буквы на маленькие, не помогло. Остаётся та же ошибка.



0

1187 / 917 / 367

Регистрация: 02.09.2012

Сообщений: 2,790

16.06.2020, 14:26

4

А! ну так из одного CTE нельзя напрямую ссылаться в другой CTE.
Вы видимо в «c» хотели написать FROM a.
Зачем вам второй раз forum_album шерстить??



0

Dancing_god

15 / 2 / 1

Регистрация: 18.09.2015

Сообщений: 227

16.06.2020, 18:30

 [ТС]

5

Цитата
Сообщение от grgdvo
Посмотреть сообщение

А! ну так из одного CTE нельзя напрямую ссылаться в другой CTE

Такая же ошибка была и тогда, когда второй СТЕ прописывался как подзапрос, вне конструкции WITH … AS …

Цитата
Сообщение от grgdvo
Посмотреть сообщение

Зачем вам второй раз forum_album шерстить??

Есть таблица forum_album:

SQL 
1
2
3
4
5
6
7
8
9
10
11

id |               photo                | owner_id_id | title_id |         creation_date         
----+------------------------------------+-------------+----------+-------------------------------
  6 | images/avreliy_JmlXnah.jpeg        |           2 | Кипр     | 2020-06-14 20:36:52.747169+03
  7 | images/epiktet_iArdxPI.jpeg        |           2 | Кипр     | 2020-06-14 20:36:52.747169+03
  8 | images/seneka_dGsotKx.jpeg         |           2 | Кипр     | 2020-06-14 20:36:52.747169+03
  9 | images/afrika_1_t37qqcL.jpeg       |           3 | Африка   | 2020-06-14 21:26:40.429191+03
 10 | images/afrika_2_h9FI9V4.jpg        |           3 | Африка   | 2020-06-14 21:26:48.300588+03
 11 | images/afrika_3_wNPDuhm.jpeg       |           3 | Африка   | 2020-06-14 21:26:53.593285+03
 12 | images/collisey_c5AflI7.jpg        |           2 | Рим      | 2020-06-14 23:21:26.93603+03
 13 | images/dzhek_uezerford_e865Fgu.jpg |           2 | Кипр     | 2020-06-14 23:43:12.519803+03
 14 | images/collisey_u4N9I2Z.jpg        |           2 | Кипр     | 2020-06-15 20:27:10.868565+03

В ней нужно выбрать последнее фото в каждом альбоме для данного пользователя. У Postgres ограничения на GROUP BY, и следующий запрос:

SQL 
1
2

SELECT forum_album.title_id, forum_album.photo, MAX(forum_album.creation_date) FROM forum_album
WHERE forum_album.owner_id_id=2 GROUP BY forum_album.title_id, forum_album.photo HAVING forum_album.creation_date = MAX(forum_album.creation_date);

некорректен, поскольку поле forum_album.creation_date должно либо входить в агрегатную функцию, либо в условие GROUP BY.
И еще у меня сомнения по такому запросу:
1. В условии HAVING агрегатная функция будет вычисляться по группам или по всей таблице?
2. СУБД сможет по условию HAVING для даты создания фотографии отобрать записи forum_album.photo для каждой группы? Или надо также отдельно применять какое-то условие группировки для поля forum_album.photo?

Из-за всего вышеперечисленного, я решил сперва отобрать последние даты добавления фотографий в каждый альбом данного пользователя, а потом уже по этим датам и названиям альбомов найти соответствующие им фотографии.



0

grgdvo

1187 / 917 / 367

Регистрация: 02.09.2012

Сообщений: 2,790

17.06.2020, 08:17

6
SQL 
1
2
3
4
5
6
7
8
9

SELECT 
owner_id, title, photo
FROM 
forum_album AS q
WHERE
q.creation_date >=
(SELECT MAX(creation_date)
 FROM forum_album AS subq
 WHERE subq.owner_id=q.owner_id AND subq.title=q.title)



1

15 / 2 / 1

Регистрация: 18.09.2015

Сообщений: 227

17.06.2020, 12:42

 [ТС]

7

Цитата
Сообщение от grgdvo
Посмотреть сообщение

1
2
3
4
5
6
7
8
9
SELECT
owner_id, title, photo
FROM
forum_album AS q
WHERE
q.creation_date >=
(SELECT MAX(creation_date)
FROM forum_album AS subq
WHERE subq.owner_id=q.owner_id AND subq.title=q.title)

Спасибо большое. Т.е. здесь получается агрегатная функция MAX применяется к каждой группе, отобранной по условию WHERE?



0

1187 / 917 / 367

Регистрация: 02.09.2012

Сообщений: 2,790

17.06.2020, 13:07

8

хуже, она применяется к каждой записи.
я план запроса не строил, но предполагаю, что здесь будет seqscan (=полный перебор) таблицы в основном запрос и многократный seq scan (=полный перебор) таблицы во вложенном запросе с подсчетом значения агрегата и с последующей проверкой условия (когда условие ложно, запись просто отбрасывается, а перебор и подсчет в подзапросе делали напрасно).

Так что
с точки зрения простоты написания и понимания — это удобный запрос.
с точки зрения оптимальной работы — это отвратительный запрос, требующий оптимизации ))



0

Dancing_god

15 / 2 / 1

Регистрация: 18.09.2015

Сообщений: 227

17.06.2020, 16:02

 [ТС]

9

Цитата
Сообщение от grgdvo
Посмотреть сообщение

это отвратительный запрос, требующий оптимизации

Пока мне это нужно для учебного проекта. Но в теории это будет социальная сеть, поэтому и альбомов и фотографий может быть очень много.
Вроде бы получилось сделать через оконную функцию:

SQL 
1
2
3

SELECT * FROM
(SELECT forum_album.photo, forum_album.title_id, forum_album.creation_date, MAX(forum_album.creation_date) OVER (PARTITION
BY forum_album.title_id) AS last_creation_date FROM forum_album WHERE forum_album.owner_id_id=1) AS T1 WHERE T1.creation_date = T1.last_creation_date;



0
Источник

Понравилась статья? Поделить с друзьями:

Читайте также:

  • Error missing credentials for plain
  • Error missing client jvm at c program files
  • Error missing binary operator before token
  • Error mismatching image and device error
  • Error mismatched parentheses at or near

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии