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?

CodePudding user response：

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 quering 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... :)