Wasting time and inefficiency with PSQL

Hello all, I’ve come up against some questions and issues as I’ve been working through the course so far that I’d like to discuss before moving on.

I’m struggling to understand the benefits of coding with SQL over just making a database in excel. It seems so time consuming and inefficient. For e.g. if I make a typo with a piece of data in Excel, I just have to double click on the cell and change it, whereas with SQL I have to write a whole line of code with punctuation marks and all the rest of it to achieve the same thing. Will the benefits of building a DB using a terminal and coding language become more apparent as I start using more advanced features?

Another thing is, in Excel, I can see my whole database all the time, and my changes are apparent in real time. I’m finding it very hard to keep my database and multiple tables in my mind as a whole when all I have to look at is a terminal window which uses ASCII characters to display tables. Having to type a command to see my table seems a lot less efficient when compared to building a db in a visual environment like Excel. Is there an editor or coding environment where I can build a database using PSQL which has a UI component which updates in realtime?

My biggest issue moment to moment with the course is that if I make one typo in a command, the whole thing is screwed. Look at this example:

That’s a good few minutes of typing, punctuation marks and capitalisation and all of it, wasted because I forgot to input one piece of data. This happens constantly whilst I’m working through this course. I think a good chunk of the time I spend on each project is spent retyping stuff because I made one typo a few lines into a command. It’s incredibly frustrating, and it makes me feel like I’m playing castlevania for the NES or something where one mistake erases the last few minutes of progress. It’s another thing that makes me wonder what the point is in building databases like this rather than using Excel. My questions are:

  • Is there a coding environment which checks for mistakes in syntax as I type, and points out missing parentheses, quote marks, commas, spaces where there shouldn’t be spaces, etc, before I hit enter? Or which allows me to go back and edit a command and resubmit it if there’s a mistake in it?
  • Is there a coding environment which isn’t just stark B+W? One which colour codes punctuation marks (there must be a programming-specific name for things like commas and quote marks), text in parentheses, commands like CREATE TABLE etc etc just to make it easier to see if I’m getting the syntax right?
  • Can I at least copy and paste code so I don’t have to re-write 20 lines of code that contained a single typo? Copy and paste doesn’t seem to work in the VSCode environ that the course uses.

Finally, is there a convenient resource for exploring PSQL commands and how they work, variations on them, how they can be chained together etc? Googling specific commands and looking for articles which talk about what I’m looking for helps me to an extent, but sometimes I want to explore the possibilities of a command beyond what the lesson provides and I don’t always know what to Google. For e.g., I wanted to see how I can write an UPDATE TABLE command that will update based on multiple conditions at once, rather than typing out two separate UPDATE commands. Or, I wanted to try and CREATE TABLE with all the primary and foreign keys already specified, in one compound command, but I couldn’t figure out the syntax. Is there a resource which allows me to explore all of this sort of thing in one place without Googling and searching for the right website each time?

As you can tell, I’m very new to this world. I’d love to be able to talk about the issues I’m having as I really feel like I’m missing something. I feel like if I had more knowledge, I’d find things easier.

Apologies for the long post. Thank you so, so much to the freecodecamp team for what you’ve created and for the hard work that you all clearly continue to do every day (for e.g. with the current issues of long load times with courses). This is an incredible resource, and the amount of work that clearly has gone and continues to go into it is staggering.

Your browser information:

User Agent is: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/101.0.4951.67 Safari/537.36

Challenge: Build a Celestial Bodies Database

Link to the challenge:

1 Like

Yes, it will.

There are GUIs for SQL but you can’t be reliant on a user interface for working with the data otherwise it’s not programmable. Comparing Excel with an SQL database also seems a bit shortsighted as they are not functionally the same nor do you interact with them in the same way.

Maybe someone wants to do a write-up and go more in-depth.

Google because I’m lazy:

Hi there!

Excel and Postgres are two different tools/technologies serving different purposes and targeting different users. Excel is best for GUI, where users can enter data and apply formulas. It is usually good for day-to-day use. It provides excellent reporting. Postgres, is a database, usually running on GUI-less servers where pretty dashboards are not an option. There you have to use the command line but very carefully. This doesn’t mean that there is no GUI for Postgres. Check the tool dbeaver for instance, there you can see tables, data etc.

Yes you can refer to this post.

Check out the official documentation of Postgres.

:bulb:The course is still in beta, and feedback is welcome. Kindly refer to other forum posts as well in case you run into issues.

Like I said, I am brand new to this. Right now I’ve only done things using PSQL that I could also have done more quickly using Excel.

Could you expand on what you mean when you said that relying on a GUI for working with data doesn’t work as the data isn’t programmable? Thanks

Thank you for this. So is it common for someone who works with postgres professionally to be working purely with the command line? It seems so unforgiving and inefficient to me right now, I can’t imagine working using only that. How do people deal with making typos and such during their normal workflow? Thanks again.

  1. Your application/users are not going to be interacting with the DB using a GUI.

  2. The administration of the DB will likely involve some scripting, or as said it might be running on a headless system or one that doesn’t have a GUI interface (even if it has a keyboard and a monitor hooked up to it).

To try and elaborate a little… when you go to a website and click “create account”, there isn’t someone entering a command in the terminal or playing with a GUI to add your new account to the database. There’s code that runs the command necessary to automatically add you to the database. That code would include a command similar or identical to what you would enter in the terminal. So it’s important to know the commands and how to interact with databases in many different ways.

I interact with databases using the terminal on almost a daily basis - and I use a GUI sometimes - I also write code to automatically run those commands.

Here’s an image

1 Like

If there are typos, you can always edit the command and run it again.

As a beginner, you can try to write the commands on a notepad and then paste into the terminal. This way you can edit and remove typos quickly.

And you can also access a previous command without re-typing by using the up arrow keys.

Thank you for this, putting what I’m learning into context like this really helps me see the bigger picture and understand why I’m learning it.

The notepad tip is wonderful, thanks so much. I should have thought of it myself. And using up arrow to go through previous commands has been life changing.

What you talk about editing the command, are these what you were referring to? Or is there some way to edit a command in the terminal?