Syntax error needs explanation

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