I'm not sure if that's your desired syntax or not. Check your syntax for UPDATE
Currently, that's
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ FROM from_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
So if you provide table t1
, it's getting parsed as a table named table
. Actually, to do that you need to have it in quotes "table" t1
which you're doing, or your library is doing.
- As a design note, don't do that. In fact, don't name anything any SQL keywords.
- But, if you want to have some fun and see what's happening we can play..
Let's create some text data,
CREATE TABLE "table" AS
SELECT x AS column, x AS column2
FROM generate_series(1,12345) AS t(x);
Now we can try your original query and get your original result,
UPDATE "table" t1 SET t1.column=0 WHERE t1.column2=1234;
ERROR: column "t1" of relation "table" does not exist
LINE 1: UPDATE "table" t1 SET t1.column=0 WHERE t1.column2=1234;
And that's the problem you're getting. As with the table, if you're going to use a SQL keyword, you need to quote it. Interestingly, that's not enough here.
UPDATE "table" t1 SET t1."column"=0 WHERE t1.column2=1234;
ERROR: column "t1" of relation "table" does not exist
LINE 1: UPDATE "table" t1 SET t1."column"=0 WHERE t1.column2=1234;
In addition to that, it seems that table aliasing is not supported in the SET list, regardless of whether or not the column is reserved keyword.
UPDATE "table" t1 SET "column"=0 WHERE t1.column2=1234;
Why it's currently Working As Designed
Why you can not use aliases, xocolatl from IRC helps with that,
< xocolatl> EvanCarroll: the reason you can't use the alias on the left of the = is because of composite types
< xocolatl> EvanCarroll: so, it's not a bug but WAD
So in code to CREATE
a table with a custom composite type an execute an UPDATE
on it.
CREATE TYPE foo AS ( x int, y int );
CREATE TABLE foobar AS
SELECT v::foo AS mycol
FROM ( VALUES (1,2), (2,100) ) AS v;
UPDATE foobar SET mycol.x = 9;
So the syntax that permits the .
is mycol.type-address
, not tablealias.col-name
.
Solving the ambiguous syntax problem
If that didn't make sense, any behavior but this behavior would give you an ambiguous syntax,
CREATE TYPE foo AS ( mycol int, x int );
CREATE TABLE mytable AS
SELECT v::foo AS mycol, 1 AS x
FROM ( VALUES (1,2), (2,100) ) AS v;
UPDATE mytable AS mycol SET mycol.x = 9;
What does mycol.x
refer to there? As is it's not ambiguous, table-referencing and table-aliasing is disabled, so it's definintely 100% of the time a composite-type named mycol
, on the table mytable
.
First, I've reduced your tables by deleting FOREIGN KEYS just for the sake of the question.
create table task
(
id serial primary key,
title varchar (50) not null,
created_date date default current_date,
empl_id integer,
cat_id integer
);
create table employee
(
id serial primary key,
name varchar (20) not null,
surname varchar(30) not null,
registration_date date default current_date
);
insert into employee values
(1, 'Emp1', 'SEmp1', now()),
(2, 'Emp2', 'SEmp2', now()),
(3, 'Emp3', 'SEmp3', now());
insert into task values
(1, 'Task1', now(), 1, 0),
(2, 'Task2', now(), 1, 0),
(3, 'Task3', now(), 1, 0),
(4, 'Task4', now(), 2, 0),
(5, 'Task5', now(), 2, 0),
(6, 'Task6', now(), 3, 0);
I've used this query to calculate AVG():
select avg(tasks) as average
from (select empl_id, count(*) as tasks from task group by empl_id) t1;
| average |
| -----------------: |
| 2.0000000000000000 |
Then I've used it to filter employees > AVG().
select empl_id, count(*) as tasks
from task
group by empl_id
having count(*) > (select avg(tasks) as average
from (select empl_id, count(*) as tasks
from task
group by empl_id) t1);
empl_id | tasks
------: | ----:
1 | 3
dbfiddle here
Best Answer
The error is (from the column used inside the
OVER
) inSELECT
. You can't use a column alias defined in the select list for another column expression in the same select list:You just have to duplicate the expression inside the
OVER (ORDER BY ...)
:Irrelevant to the issue but this condition will stop the optimizer from using any indexes on
(created_at)
:I'd write it like this, so indexes can be used more effectively: