Querying Baseball data off platform project

Hi Dav! This is helping me make great progress when I had been running into this issue for awhile now. However, when I run this, I got prompted for the password for my user but was unable to type anything. Do you know how I could add it to the original prompt so it is included?

also, if anyone who needs it, this stack overflow answer solved a problem with the psql command being recognized: https://stackoverflow.com/questions/30401460/postgres-psql-not-recognized-as-an-internal-or-external-command

4 Likes

Try adding a -U flag followed by the user that you want to open the database with? (Not sure if that’ll work!)

That is,

psql -U <username> -d <your-baseball-database-name> -f baseball_database.sql

If you’re not sure what to put for a username, or your username isn’t working, try “postgres”.

8 Likes

That solved it! Yeah ha KD so much for your help now I’m cooking

2 Likes

hey @davbyron thanks… that solved the issue

1 Like

This worked for me as well. Thank you for sharing! And thank you to everyone trying to help.

1 Like

how is this solved on Windows 10?

Here’s how to do that in Windows 10:
1- right click on the windows (Aka “Start button”)
2- select “System” (you can reach there from: Settings - About)
3- in the right menu, click on “Advanced system settings”
4- in the window that popped, click on the bottom button that reads “Environment Variables…”
5- in the upper block, click on the variable that reads “Path” (double-click)
6- in the “Edit environment variable” click new
7- in the new line you should enter your path to \bin folder of your postgreSQL (default for version 13: “C:\Program Files\PostgreSQL\13\bin” yours might be different look it up)
8- back track with “Ok” to close all the 3 open windows.
9- now you can open cmd and enter the command @davbyron provided: psql -U <username> -d <your-baseball-database-name> -f baseball_database.sql

voila !

I hope that helps

5 Likes

Thank you for your help! I did all that and this pops up:

psql: error: baseball_database.sql: No such file or directory

or when I write the directory:

psql: error: C:/Users/Katerina/Downloads/TheBestOfBaseballAwards/TheBestOfBaseballAwards: Permission denied

1 Like

Yep, I’m having the same problem. Wish opening the .sql file on Postbird worked…

Update: I got all the data to load. It’s been about a week since I tried, but I uninstalled postgres version 13.1 and downloaded postgres version 12.5 instead which may have been the difference (or maybe someone fixed the .sql file not sure either way it works now).

3 Likes

This worked for me. I tried some of the command line stuff with no luck. Simply downloading postgres 12.5 instead of 13 made it all work. I’m using the same SQL file, so it is the version of postgres that is the issue.

3 Likes

Hey everyone, I was facing a major problem when trying to import Baseball.sql file.
I kept getting this error “authentication method 10 not supported”.
I managed to overcome this error by using the SQL Shell (psql) provided after installing PostgreSQL and Postbird, well one of them :see_no_evil:.

  1. You have to login into the postgres user or whatever user you created
  2. connect to the database you want the data inserted too, I named mine Baseball: postgres=# \c Baseball
  3. Using the ‘\i’ command just select the past the file path for the sql file you want to import: Baseball=# \i '.../TheBestOfBaseballAwards/baseball_database.sql'.
4 Likes

Every time have error like this:
Select database ‘baseball’
ERROR:
Can not find psql in a system. Make sure PostgreSQL client is installed.
On Ubuntu/Debian - package ‘postgresql-client’
On RHEL/Fedora - package ‘postgresql’
FAILURE

Reinstalled Postbird - didn’t help
Reinstalled PostgreSQL to version 12 - didn’t help
Downloaded ‘postgresql client’ - didn’t help

Maybe it’s time to download SQLite and forget about postgres…

Thanx!! I’ve tried all the other options and didn’t work,. I didn’t want to downgrade postgres as it is suggested above… but your method did the trick! You are awesome!
(I work on windows10, postgres13)

I did everything @siger212 said and it worked for me. Make sure you close your cmd window and reopen it prior to running the final command that @davbyron provided. I also work on Windows 10, thanks for the help all!

Step one: create database “baseball” in postbird

Step 2: Watch These two videos in order:
1: 'psql' is not recognized as an internal or external command - YouTube
2: psql: error: could not connect to server: FATAL: password authentication failed for user - YouTube

1 Like

if you need a little more detail than what darkcookie says (thanks for putting me on the right path darkcookie):

  1. Open command prompt and go to where your psql bin is. Mine was “C:\Program Files\PostgreSQL\13\bin”
  2. Login to the database with user via command like this: “psql -U postgres -D codecademy_baseball”
  3. Type the \i and then the full path to your file in quotes. psql doesnt like , it expects / so you can copy your file location from file explorer but replace the backslashes with forwardslashes. Like: “\i ‘C:\Users\MysteryMachine\Downloads\TheBestOfBaseballAwards\TheBestOfBaseballAwards\baseball_database.sql’”

The reason is whatever editor is reading the sql query… it has no concept of reading from stdin (thats a programming thing I havent used in a LONG time, where you tell the system to read in from the command line). So when it (pgadmin or other non-command line tools) hits that plain text data its expecting a sql statement (since its after the statement terminator of a :wink: and instead it gets a number etc so it doesnt know how to interpret and errors.

What I didnt know, till now, is there is this notion of “import” in Psql where it runs it like a command prompt which you could do in Postbird. Unfortunately however Postbird authenticates to run in this command prompt mode uses an old authentication method so its not working with the newest postgres.

I was able to load this in remotely without much trouble running postgres 13 on a raspberry pi.

  1. Run a Postgres docker container on the Pi
    docker run -d -p 5432:5432 -e POSTGRES_USER=baseball -e POSTGRES_PASSWORD=changeme postgres
  2. Run psql.exe on laptop to connect to the database (will be called baseball) -pgadmin and postgres come with this tool
  3. Run \i against the .sql file’s path - on windows psql still uses forward slash - if you do backslash it will give you “access denied” e.g. \i c:/temp/baseball.sql

I am using pgadmin so the GUI did not have a pretty frontend for importing this type of SQL dump.

@davbyron solution worked out for me but I had to try multiple times until I realized after “f-” you must include the path to your file. Perhaps obvious to most but I’m a total beginner so I guess others will face the same issue.

@davbyron solution : psql -U -d -f baseball_database.sql
After Replacing required fields + path : psql -U postgres -d baseball -f C:\Users\alexa\baseball_database.sql

You are then required to type your database password. When you type, you’ll see nothing on your screen. By default, Microsoft CMD does not display what you type, and here’s how it looks in CMD :