SQL syntax error using Heroku CLI, Postgres

Question

I'm using the Heroku CLI to connect to a postgres database of my Heroku webapp and want to insert an intial user into the user table.

When I run SELECT * FROM user; i get back my results just fine.

But when I want to INSERT INTO the same table, for example:

INSERT INTO user (username, email, password_hash, admin, isactive) VALUES ('John Doe', '[email protected]', 'randompassword','1','1');

it's throwing errors at me, pointing at the table name:

ERROR: syntax error at or near "user" LINE 1: INSERT INTO User (username, email......

(with an arrow pointing at the u in user)

I'm super lost...is Heroku using some weird SQL syntax? Does it not support INSERTS? What is going on?

marc_s · Accepted Answer · 2022-04-03 17:11:57Z

2

Had to use public.user instead.

(Noticed the table name when doing \d+ user was public.user )

As Chris pointed out below - this shouldn't normally be a problem. For all my other tables I'm quite comfortable querying using just the table - without having to prefix it with public.

Having said that, it seems Heroku creates a user table of its own where it stores database users (used for connectivity to the database itself).

So it turns out it's most likely an ambiguity issue as Heroku creates its own user table, but my model also has a user table.

If I (SELECT) query on just user - I get 'Heroku's table.

(and it seems you are unable to INSERT into this table hence the poorly described error)

If I query on public.user - I get my own table.

If only the error specified that... :)

edited Apr 3, 2022 at 17:11

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 14, 2022 at 21:42

Peter

698 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

voxobscuro Over a year ago

if this solved your issue, consider marking it as accepted stackoverflow.com/help/accepted-answer

Chris Over a year ago

This isn't the problem. Yes, out of the box with PostgreSQL things are placed in the public schema but the default search path should make everything work without having to manually specify public..

Chris Over a year ago

Note that the error message shows a capital "U" in "User". I suspect you created the table with a quoted capital letter. Table names should be lowercase in PostgreSQL and you can get weird errors like this if you force it to use capitals.

Peter Over a year ago

Thanks for the comments guys. I'll update my answer with my findings and mark it as the answer. (I am unableto mark my own answer to my own question until 48 hours after posting - hence the delay).

Collectives™ on Stack Overflow

SQL syntax error using Heroku CLI, Postgres

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related