Querying Baseball data off platform project

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.

1 Like

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 :

3 Likes

I am a complete beginner and constantly hitting a wall. I did realize that I need to create a path to psql, however, I cannot locate the bin file… My PostgreSQL folder used to have way more subfolders and now it contains only one! What have I done to mess up with the folders and what can I do to retrieve/restore the bin file? Thanks!!

I am trying to follow your steps as I need to specify the path to my Postgres bin. Nevertheless, the file does not exist (I am using Mac). How can I locate this file? Any piece of advice would be of tremendous help!

davbyron’s solution worked for me in Linux Ubuntu after I altered the pg_hba.conf file to no longer require permissions/password for the user ‘postgres’. I followed the instructions here:

Changing “peer” to “md5” as listed in those instructions prompted me for a password for the ‘postgres’ user, which I didn’t have. I instead changed “peer” to “trust” and the issue was solved.

Hello, everyone, I have the same problem. My system is windows 10. Postgres 13, pgadmin 4. I get a syntax error in my client. tried these command-line prompts but still the same error I get. (syntax error in line 688 ) Also, I get errors in the command line as well. I am now so frustrated as I uninstalled and reinstalled everything a few times . Also tried different versions of Postgres but still, nothing seems to be working for me. I’m so close to giving up this course :(. Does anyone have any suggestions for me, please?

Hello, guys!

Here is my tutorial how to set up the server and copy the database. I’m Mac user, but I hope that it will be useful for Windows users too.

  1. First of all open Postgres and start your local server. Also note your port number, by default it is 5432.

  2. You will see three default databases. Choose one and click it twice.

  3. It will open Postgres process in Terminal. Copy and save the link of psql with the port number, it will be needed later.

  4. Enter the command CREATE DATABASE baseball; and press Enter button. It will create the empty database named baseball.

  5. Don’t close Terminal window. Click into the database area in the Postgres app for to refresh it. You will see your new database.

  6. Next step open downloaded folder with necessary files. Also open new Terminal window. Move baseball_database.sql file into new Terminal window for to see the full path of this file. Copy and save this path.

  7. Now we need copy existing baseball_database.sql database into our empty baseball database. We will use command like this our_database_name < existing_database_name. Here we need our copied and saved lines.
    In the new Terminal window or in the window where we have found out the database path print the_first_copied_line baseball < the_second_copied_line and print Enter button.

Now we have the baseball database which is the copy of baseball_database.sql.

We can open and see at baseball files. I use Postico free version as PostgreSQL Client.
Choose New Favorite and fill fields with your information. Print baseball as database. Click Connect. Remember that your local Postgres server should be active.

And here all tables from baseball_database.sql.

I hope it was useful. :slightly_smiling_face:

6 Likes

one more detail. on windows i got win32 error when trying to do this. In the file path of the .sql file, you need to add one more forward slash beside each forward slash, then it works.

1 Like

Thanks Dav. Worked successfully.

It works for me.
Thanks a lot!!

Hello,
I’m a beginner to SQL. I’m trying to install and start the Postbird, and when is starts I enter the username: postgres and password: postgres (as per the instructions) but when I click the Connect button I get a Connection Error. Please who can help.
Postbird

Scroll further up in this thread. There are answers to this question above.

My system is windows 10. Postgres 14, pgadmin 4. I tried these steps but still I cannot upload the database file (baseball_database). Here is a screenshot of the error:

Here’s what worked for me (Windows 10, Postgres version 14):

The steps I took:

1 - inside Postbird, created a database named ‘baseball

2 - copied the “baseball_database.sql” file provided by Codecademy to C:\Users\Public (this might not be necessary, but I had some trouble trying to import a CSV file another time, and accesing it from Public solved it, so, why not?)

3 - added psql to filepath, as described in @code3074961728 's answer to this thread (I’ll also copy the instructions here (bottom of this answer).

4 - from command prompt, cd into C:\Program Files\PostgreSQL\14\bin

5 - from command prompt, ran the following command psql -U postgres -d baseball < C:\Users\Public\baseball_database.sql

6 - it asked me for my pass, so I typed in

Finally, it worked.

Add psql to filepath (credit: @code3074961728):

To add it to your filepath, go to advanced settings in windows, then environment variables, then path in systen variables. I added the following path C:\Program Files\PostgreSQL\13\bin
The path you add depends on where your bin file is inside your PostgreSQL. Note I also have version 13 downloaded. You bin file is probably located in a similar location on your computer and should not be too hard to find. The bin file, i believe, is just the binary code of the computer program.

6 Likes

Can the Codecademy Team please compile a debugging list for the various issues? It is super tedious to trial and error your way through this thread, and many solutions expect some previous knowledge on PSQL that is simply not taught in the course.

5 Likes

@siger212 thanks a lot - it worked!!!