Horton
Menu

Thread

  1. Syntax error needs explanation

    Rich Shepard <rshepard@appl-ecosys.com> — 2025-07-14T19:12:07Z

    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

  2. Re: Syntax error needs explanation

    David G. Johnston <david.g.johnston@gmail.com> — 2025-07-14T19:14:47Z

    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.

  3. Re: Syntax error needs explanation [RESOLVED]

    Rich Shepard <rshepard@appl-ecosys.com> — 2025-07-14T19:19:44Z

    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

  4. Re: Syntax error needs explanation

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-07-14T19:36:19Z

    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

  5. Re: Syntax error needs explanation

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-07-14T19:45:45Z

    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

  6. Re: Syntax error needs explanation

    David G. Johnston <david.g.johnston@gmail.com> — 2025-07-14T19:56:16Z

    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.

  7. Re: Syntax error needs explanation

    Rich Shepard <rshepard@appl-ecosys.com> — 2025-07-14T19:59:02Z

    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

  8. Re: Syntax error needs explanation

    David G. Johnston <david.g.johnston@gmail.com> — 2025-07-14T20:07:53Z

    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.

  9. Re: Syntax error needs explanation [RESOLVED]

    Rich Shepard <rshepard@appl-ecosys.com> — 2025-07-14T20:13:17Z

    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

  10. Re: Syntax error needs explanation [RESOLVED]

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-07-15T05:46:43Z

    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

  11. Re: Syntax error needs explanation [RESOLVED]

    David G. Johnston <david.g.johnston@gmail.com> — 2025-07-15T05:55:33Z

    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.

  12. Re: Syntax error needs explanation [RESOLVED]

    Rich Shepard <rshepard@appl-ecosys.com> — 2025-07-15T12:40:26Z

    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