Обсуждение: Syntax error needs explanation
I have the following script:
select c.company_nbr, c.company_name, i.industry,
from companies as c, industry as i, enforcement as e
where exists (
select c.company_nbr, count(e.action_date), sum(e.penalty_amt)
from e.enforcement
where c.company_nbr = e.company_nbr
)
group by industry
order by industry;
When I run it psql reports an error:
psql:companies-with-enforcement-actions.txt:127: ERROR: syntax error at or near "company_nbr"
LINE 1: company_nbr | company_name
^
and I'm not seeing the error. What am I missing?
TIA,
Rich
On Mon, Jul 14, 2025 at 12:12 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
I have the following script:
select c.company_nbr, c.company_name, i.industry,
from companies as c, industry as i, enforcement as e
where exists (
select c.company_nbr, count(e.action_date), sum(e.penalty_amt)
from e.enforcement
where c.company_nbr = e.company_nbr
)
group by industry
order by industry;
When I run it psql reports an error:
psql:companies-with-enforcement-actions.txt:127: ERROR: syntax error at or near "company_nbr"
LINE 1: company_nbr | company_name
^
and I'm not seeing the error. What am I missing?
The error indicates your script file is at least 127 lines long and you are showing like 9...also do you usually name your script files with a .txt extension?
David J.
On Mon, 14 Jul 2025, David G. Johnston wrote: > The error indicates your script file is at least 127 lines long and you > are showing like 9...also do you usually name your script files with a > .txt extension? David J., Agh! No the filename extension is .sql. But I was using the \o psql option to write script output to files and mistyped the script name. Mea culpa! Many thanks, Rich
On 7/14/25 12:12, Rich Shepard wrote:
> I have the following script:
>
> select c.company_nbr, c.company_name, i.industry, from companies as c,
> industry as i, enforcement as e
> where exists (
> select c.company_nbr, count(e.action_date), sum(e.penalty_amt)
> from e.enforcement
> where c.company_nbr = e.company_nbr
> )
> group by industry
> order by industry;
>
> When I run it psql reports an error:
> psql:companies-with-enforcement-actions.txt:127: ERROR: syntax error at
> or near "company_nbr"
> LINE 1: company_nbr | company_name
The above looks like the format 'aligned' output from a query.
When you did \0 you captured that.
As example:
production=# \o test.sql
production=# select * from cell_per;
production=# \e test.sql
line_id | category | cell_per | ts_insert | ts_update
| user_insert | user_update | plant_type | season | short_category
[...]
> ^
> and I'm not seeing the error. What am I missing?
>
> TIA,
>
> Rich
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 7/14/25 12:38, Rich Shepard wrote: Reply to list also. Ccing list. > On Mon, 14 Jul 2025, Adrian Klaver wrote: > >> The above looks like the format 'aligned' output from a query. > > Adrian, > > I mistyped the script's extension as .txt rather than .sql. That does not matter, that is more for user convenience in figuring out what the files are for. \o test.txt select * from cell_per; Then doing: psql -d production -U postgres -h localhost -p 5432 -f test.sql Null display is "NULL". psql:test.sql:82: ERROR: syntax error at or near "line_id" LINE 1: line_id | category | cell_per | ts_insert | psql -d production -U postgres -h localhost -p 5432 -f test.txt Null display is "NULL". psql:test.txt:82: ERROR: syntax error at or near "line_id" LINE 1: line_id | category | cell_per | ts_insert | ... The error is the same as psql just processes the file without regard to the extension. > > Regards, > > Rich -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Jul 14, 2025 at 12:45 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> I mistyped the script's extension as .txt rather than .sql.
That does not matter, that is more for user convenience in figuring out
what the files are for.
I think all that is being said is the error was a simple typo, choosing the wrong file to execute. Not reading the error message was the real issue, not a failure to understand how psql scripting works.
David J.
On Mon, 14 Jul 2025, Adrian Klaver wrote: > That does not matter, that is more for user convenience in figuring out what > the files are for. Adrian, Okay. I still have issues with the script. I want a count of companies with regulatory enforcement actions by industry. Since not all regulated companies have had such actions I want only those with rows in the enforcemewnt table and haven't before used the EXISTS operator and a subquery. The current version of the script: select c.company_nbr, c.company_name, c.industry from companies as c where exists ( select e.company_nbr from enforcement as e ) group by c.industry order by c.industry; And psql tells me that c.company_nbr must be in the group by clause. However, when I do that the output is a list of company numbers and names in each industry. My web searches on using the exists operator haven't provided the knowlege for me to use it properly. Rich
On Mon, Jul 14, 2025 at 12:59 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
The current version of the script:
select c.company_nbr, c.company_name, c.industry
from companies as c
where exists (
select e.company_nbr
from enforcement as e
)
group by c.industry
order by c.industry;
And psql tells me that c.company_nbr must be in the group by clause.
However, when I do that the output is a list of company numbers and names in
each industry.
My web searches on using the exists operator haven't provided the knowlege
for me to use it properly.
Yeah, you need both to read up on aggregate queries and correlated subqueries which is typically how one makes uses of exists (it's called a semi-join in this formulation)
Not tested, but:
select c.industry, count(*)
from companies as c
where exists (
select from enforcement as e
where e.company_nbr = c.company_nbr
)
group by c.industry;
David J.
On Mon, 14 Jul 2025, David G. Johnston wrote: > Yeah, you need both to read up on aggregate queries and correlated > subqueries which is typically how one makes uses of exists (it's called a > semi-join in this formulation) David, Thanks. I wasn't sure what to read. > Not tested, but: > > select c.industry, count(*) > from companies as c > where exists ( > select from enforcement as e > where e.company_nbr = c.company_nbr > ) > group by c.industry; Works as intended. Many thanks, Rich
On Mon, 2025-07-14 at 12:19 -0700, Rich Shepard wrote: > On Mon, 14 Jul 2025, David G. Johnston wrote: > > > The error indicates your script file is at least 127 lines long and you > > are showing like 9...also do you usually name your script files with a > > .txt extension? > > Agh! No the filename extension is .sql. But I was using the \o psql option > to write script output to files and mistyped the script name. > > Mea culpa! Apart from that, the subquery seems to be missing a GROUP BY clause. Yours, Laurenz Albe
On Monday, July 14, 2025, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Mon, 2025-07-14 at 12:19 -0700, Rich Shepard wrote:
> On Mon, 14 Jul 2025, David G. Johnston wrote:
>
> > The error indicates your script file is at least 127 lines long and you
> > are showing like 9...also do you usually name your script files with a
> > .txt extension?
>
> Agh! No the filename extension is .sql. But I was using the \o psql option
> to write script output to files and mistyped the script name.
>
> Mea culpa!
Apart from that, the subquery seems to be missing a GROUP BY clause.
Well, it’s more that an exists subquery with an aggregate generally doesn’t make sense (it would need to include a having clause at minimum)…it’s not missing a group by clause, it has aggregates it doesn’t need (they belong in they belong in the main query where the group clause exists without aggregates to justify its existence.
David J.
On Tue, 15 Jul 2025, Laurenz Albe wrote: > Apart from that, the subquery seems to be missing a GROUP BY clause. Laurenz, That was added. Thanks, Rich