Create tables on heroku postgres

Question

I'm trying to deploy my Rest Api to heroku. This API uses postgres as database, and so I need to deploy also it. So, I went to the heroku site, created the application, added the specific postgres database. At this point, I need to create tables in the database. So, I opened up the terminal, and wrote the following command, to connect to the database

psql --host=[HOSTNAME] --port=5432 --username=[USERNAME] --password --dbname=[DBNAME]

Then, I typed the given password and at that point I was able to enter the database, because the \dt command showed that there weren't any relation. At this point I need to create my database and tables, so I tryied to write CREATE DATABASE db;, but it gave me the following error: ERROR: permission denied to create database. This should be because I am not a high permission user, but I am not able to change that, even if I spent a lot of time searching for solution on youtube and in other stackoverflow questions.

So, I found another possible solution, that was typing in the CMD the following code: heroku pg:psql --app mad4feltre < database.sql, that should put all the code that is in the SQL file in the database, but the terminal gives an error due to the < character, that is reserved to future uses. Here is the error itself:

+ heroku pg:psql --app mad4feltre < database.sql
+                                 ~
Operator'<' reserved for future uses.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : RedirectionNotSupported

I would like to know how to insert the database and the table in my postgres database. If one of the two possibilities can be fixed, it would be a great thing.

Here is the video and the post that I followed the most:

https://www.youtube.com/watch?v=80oty2v4HsE

createdb: database creation failed: ERROR: permission denied to create database

Thank you for your time

score 1 · Accepted Answer · answered Mar 19 '22 at 13:05

There are two things at play here:

First, you cannot create databases using PostgreSQL commands on Heroku.

But that's okay: when you provisioned your Heroku Postgres add-on, Heroku created a database for you. You are already connected to it via the --dbname=[DBNAME] argument.

After connecting, you can start running queries right away.
You appear to be using PowerShell. Most of Heroku's documentation uses Unix-style shell commands that may not work on PowerShell.

In this case, the command is trying to use the contents of the database.sql file as standard input to the heroku pg:psql command. That works a bit differently on PowerShell.

Try this instead:
```
Get-Content database.sql | heroku pg:psql --app mad4feltre
```

Create tables on heroku postgres

1 Answers1