Error missing from clause entry for table users

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.

Источник

Содержание

Database.Guide
Beginners
Categories
Fix “ERROR: missing FROM-clause entry for table” in PostgreSQL when using UNION, EXCEPT, or INTERSECT
Example of Error
Solution 1
Solution 2
Missing FROM-clause entry for table « », что делать?
Missing FROM-clause entry for table « », что делать?
findAndCount, error: missing FROM-clause entry for table #7367
Comments
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
Custom Column «ERROR: missing FROM-clause entry for table» Postgres #12304
Comments

Database.Guide

Beginners

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 комментария

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

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

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

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

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

Источник

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»

From home page select your psql DB
Choose a schema
Choose a table
Click on «show editor»
Click on «Custom Column»
On «field formula» just choose any column and pick a name for the new column
Click on «Done»
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.

Источник

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.

Источник

Hello Everyone.

@pleerock @Kononnable I think this could be possibly reopened.

I started facing the same issue today with TypeORM 0.2.24 using PostgreSQL driver when trying to fetch entities with @ManyToMany relation.

I don’t know why, but the generated query is missing the double quotes which seems to be the reason of missing FROM-clause entry for table errors.

Here is a simplified extract from my entities:

@Entity()
export class ProductOption {

    @ManyToMany(() => OptionValue, (value: OptionValue) => value.options)
    @JoinTable({ name: 'product_option_value' })
    public values: OptionValue[];
}

@Entity()
export class OptionValue {

    @Column({
        type: 'simple-json',
    })
    public value: { baseValue: string, alternativeValue: string }; // just an example

    @ManyToMany(() => ProductOption, (productOption: ProductOption) => productOption.values)
    public options: ProductOption[];
}

See I have both sides of ManyToMany relation defined with a custom-named @JoinTable.

Now, when I try to fetch Options with related OptionValues, e.g.

productOptionRepository.find({ relations: ["values"] });

I get this query generated and this FAILS:

query failed: SELECT ProductOption_values_rid.optionValueId AS «optionValueId», ProductOption_values_rid.productOptionId AS «productOptionId» FROM «option_value» «option_value» INNER JOIN «product_option_value» «ProductOption_values_rid» ON (ProductOption_values_rid.productOptionId = $1 AND ProductOption_values_rid.optionValueId = «option_value».»id») ORDER BY ProductOption_values_rid.optionValueId ASC, ProductOption_values_rid.productOptionId ASC — PARAMETERS: [123]

error: missing FROM-clause entry for table «productoption_values_rid»

Please note the bold parts — they are missing double quotes. I compared that to a different but similar relation in other part of my apps, and there the query includes double quotes! This is what I found in different sources can cause such missing FROM-clause entry for table errors.

@ishan123456789 provided me a hint to check if the simple-json may be causing this issue. So I removed the relation:

productOptionRepository.find();

and voliá! It magically works. There has to be something wrong with the query and/or pivot table being generated automatically by TypeORM for @ManyToMany relation when we have an entity containing simple-json column.

Any help kindly appreciated!

Источник

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

Источник

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.

oracle

sql-server

Источник

I am using postgresql and pgadmin and I am new to this. I have a table «Users» that has the following columns

(Username, Name, Email, Phone, Discount, Password, token, serial, created_on, updated_on, points, reference)

. I was trying to create a trigger for the table so that everytime a new insert occurs with an existing username in the reference field, the points of the used username will be incremented by 50. So there are two operations — updating the existing table, inserting the new values.

I tried creating a trigger function like this:

create or replace function points() 
returns trigger as  
$BODY$ BEGIN        
 if  new."Users"."reference" in (old."Users"."username") then 
Insert into "Users"(Username,Name,Email,Phone,Discount,Password,token,serial,created_on,updated_on,points,reference)          values(new.Username,new.Name,new.Email,new.Phone,new.Discount,new.Password,new.token,new.serial,new.created_on,new.updated_on,new.points,new.reference); 
                           
update "Users"
set old."Users"."points" = old."Users"."points" + 50 
where "Users"."username" = (select "Users"."username" 
                        from "Users" 
                        where new."Users"."reference" in (old."Users"."username"));                                                                                      end if;    
RETURN new; 
END; 
$BODY$ language plpgsql;

and a trigger like this:

create trigger referece_points 
after insert 
on "Users"
 for each row 
execute procedure points();

But when I try to insert a new value in the «Users» table, I get the following error:

ERROR: missing FROM-clause entry for table "Users" LINE 1: SELECT new."Users"."reference" in (old."Users"."username") ^ Query: SELECT new."Users"."reference" in (old."Users"."username") CONTEXT: PL/pgSQL function points() line 2 at IF

What did I do wrong?

Источник

Database.Guide

Beginners

Categories

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

Example of Error

Solution 1

Solution 2

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

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

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

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

Example of Error

Solution 1

Solution 2

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

Most engaging questions

postgresql

mongodb

mysql

More questions

More questions with similar tag

oracle

sql-server

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