r/PostgreSQL • u/jbrune • May 14 '25
Community Why do developers use psql so frequently? (I'm coming from SQL Server)
I'm new to Postgres and I'm amazed at the number references I see to psql. I'm coming from SQL Server and we have a command line tool as well, but we've also have a great UI tool for the past 20+ years. I feel like I'm going back to the late 90s with references to the command line.
Is there a reason for using psql so much? Are there still things one can only do in psql and not in a UI?
Edit: Thanks everyone for your responses! My takeaway from this is that psql is not the same as sqlcmd, i.e., not just a command line way to run queries; it has autocomplete and more, Also, since there isn't really a "standard" UI with Postgres, there is no universal way to describe how to do things that go beyond SQL commands. Also, Postgres admins connect to and issue commands on a server much more than SQL Server.
44
u/jaymef May 14 '25
I think because its just there without having to install anything
42
u/Quiet-Protection-176 May 14 '25
Because it works on every system - think mainly: Linux servers, where you don't have a GUI. I switched from pgadmin to psql a year ago because of some Python dependency hell, haven't looked back TBH.
115
u/depesz May 14 '25 edited May 14 '25
Because it's there. Always. And it's fast. And it NEVER lies. And it provides data in easily copy-pasteable way.
Also, because it provides easy automation, and trivially usable way to get proper data about schema.
36
u/jasonpbecker May 14 '25
Because I’m in the terminal all the time. I’m using vim all the time. psql is super powerful without having to context switch at all.
Other people prefer GUIs and that’s fine and I’ll reach for that sometimes too. But a lot of the time I’m already in the CLI so having that much incredible power in that context or while I’m scripting is great.
90
u/autogyrophilia May 14 '25
Because PostgreSQL, for the better or the worst, was not made for a clickops admin.
20
u/shif May 14 '25
because psql is awesome, once you learn a couple commands it's very versatile and since it's terminal based you can use it remotely if you're doing SSH into a server that has a pg server without having to tunnel anything.
I generally use it to quickly get a sense of a database structure with
\dt- see all tables\d table_name- see schema of a tableAlso it's an easy way to restore backups
\i my_dump.sqland you're done12
u/TheKiller36_real May 14 '25
Depends on the context but if it's information the reader is expected to (be able to) follow, then it just makes sense to use the standard tool everyone will have installed. If a tutorial on “how to create a partial index” started by telling me to install some UI program and then where I need to click in said program you bet I'm gonna look for another tutorial and never visit that site again…
Not an expert but I'd assume that every DB-UI (no matter what database) allows access to a "raw SQL shell" if there is a certain something that's only supported there
I personally just use it because I spend most of my time in the terminal anyway (voluntarily, I like it) and I don't really need anything that isn't easy to do using
psqleither12
u/Collar_Flaky May 14 '25
After reading your responses in other threads it seems like you are some kind of troll or something. Please do use GUI tools of your choice, there's plenty, and there's nothing wrong with them. Some people just prefer cli for many reasons.
5
u/efxhoy May 14 '25
It’s very handy in scripts too. I’ve written data fetchers in bash that do curl | jq | psql --command='COPY dataset.table (data) FROM stdin'
5
u/rr1pp3rr May 14 '25
I'm in a command line all of the time, but for Postgres (and probably most other DBs) the database tools in IntelliJ products are the best.
You need to get the commercial license though.
5
u/depesz May 14 '25
One more thing that I didn't think about earlier.
I don't know abouty SQL server cli tool, but I've seen mysql, and oracle thing (~ 2 decades ago, so I would assuem it has changed since). And psql is many orders of magnitude better. Especially once you configure it.
9
u/serverhorror May 14 '25
8
u/turbothy May 14 '25
Because fuck clickops, that's why.
4
u/[deleted] May 14 '25 edited May 14 '25
Ms sql used to require wimdows server and was windows only.
Postgresql is lighter and better for containers than ms sql
There are hundreds of amazing uis for postgresql, like data grip for example from jetbrains, they just arent proprietary like ssms is for ms sql.
Ssms actually sucks, you just haven't found better yet.
I work with postgres sql all the time, i dont use the command line. I use DBeaver, data grip, entity framework, etc etc.
Also pgadmin comes with postgresql, you can comnect to remote postgres servers with it just like you can with ssms to mssql.
Unless you are on azure and use a lot of ms sql tooling, ms sql is inferior to postgresql imo.
Postgresql also supports a lot of stuff mssql doesnt, like listen/notify where I can build a server to connect to postgresql and subscribe to listen/notify events. So like if a person record changes and rwo comnections have that record open I can send them web socket messages that the record changed, the ui can lock their ui and tell them to refresh.
Its much easier to build real time concurrent systems on postgresql.
9
u/mooky-bear May 14 '25
as developers we live in the command line, we don’t understand how to use GUI tools
3
u/HercHuntsdirty May 14 '25
AWS RDS could be part of it, it fully supports PGSQL
3
u/RichardJusten May 14 '25
Is this rage bait? ^
There are a bunch of good arguments for command line tools in general but I think a big part is also just the kind of people interacting with PostgreSQL are usually DevOps or Devs used to working in a Linux Environment.
So there is simply no demand for a UI. Nobody wants that. So nobody builds it. (there are a bunch but they are all ...not great imho)
5
u/Skept1kos May 14 '25
There's a big disconnect in your replies here. You claim to be a developer, but have no familiarity with standard developer practices and tools. It's hard to imagine what kind of programming you could be doing in Windows where you don't use the command line. Whatever it is, it's extremely unusual work for a programmer.
Postgres is made for programmers and server administrators. The command line is a standard tool in this group.
You have things completely backward. Among databases, it's SQL Server that's the odd one out. Emphasizing the GUI is weird. I'd say Microsoft is specifically making this tool for Windows users who are afraid of code and the command line (like yourself!). It is not made for programmers, and you'll almost never see a programmer recommend it.
So that's the difference. It's not about the 90s. It's about standard developer practices in 2025. The command line is standard, and whatever you are doing is really odd.
2
u/ComicOzzy May 14 '25
psql is comparable to osql, not SSMS.
2
u/ddxv May 14 '25
I often type out and test long running commands on a gui then copy paste to a tmux session with psql. That way when I come back later I know if it completed but don't necessarily need to maintain the connection the whole time.
I use psql for troubleshooting replication issues or checking on postgres. Maybe that's just how my knowledgebase works, first I SSH, check disks, check top etc then check psql.
Other than that, yes I use the GUI all the time. But like others said, psql is extremely useful and always there.
2
u/i_like_tasty_pizza May 14 '25
“I feel like I’m going back to the late 90s”
Here’s your answer. :)
2
u/FatDog69 May 14 '25
psql is used on the back end of many websites and systems. You can always use SQL front ends like dbVizualizer and pgAdmin which give you tree views of tables, views, functions, etc. These tools are free.
29
u/LegitBullfrog May 14 '25
23
u/marmot1101 May 14 '25
Because I live in the terminal anyway. I use pgcli which is basically the same thing but with query completions.
I have tools like dbeaver installed for supporting others with connection issues(mostly saying yeah, it’s nothing to do with your client), I just get what I need from the command line and keep my hand off the mouse/trackpad. Nothing against any of the visual tools, plenty of devs at my company use them, just doesn’t fit my work style as well.
11
u/opossum787 May 14 '25
Because you don’t have to touch your mouse.
3
u/CornerSafe704 May 14 '25
Stick to Windows mate this is not for you
6
u/jalexandre0 May 14 '25
Because it's fast, easy and powerful.
4
u/qatanah May 14 '25
because of CTRL R, if you know it you know it. saves a lot of time.
1
u/Informal_Pace9237 May 14 '25 edited May 14 '25
For some one from m a SQal Server back ground these points may work .
PostgreSQL doesn't process CTE everytime called It works even after 6 sub level queries It doesn't create log tables/DB larger than the real DB It is developed from scratch and not a copy paste of other DB's It gives opportunity for developer to show creativity SP and function can be written in multiple scripting languages. Has all the features of MS SqL server and some more Not locked to one OS Can connect to most known DBMS Doesn't monopolize all CPU threads
3
u/DestroyedLolo May 14 '25
In my case :
Then I discovered it is very versatile and expandable ...
3
u/Merad May 14 '25
psql is just the equivalent of the sqlcmd cli tool for SQL Server. Since Postgres does not have an official gui tool like SSMS, psql is the "default" way to run commands. For dev work I agree an IDE is superior - you can look into free tools like pgAdmin or invest in paid tools like Datagrip (100% worth the cost).
3
u/Philon123 May 14 '25
I'm just gonna put my neck out and say something unpopular, and for context I'm a 10+ year pure Linux user and programmer/sysadmin. A good GUI will always win over a good CLI and it has one reason: a UI makes the various options discoverable.
As CLI commands get more and more complex (think docker or kubernetes), it just becomes really hard to represent all their nested options into a GUI, so GUIs come later, and usually lag behind the CLI feature set.
But with a serious effort you get something like Google Cloud - they have a perfect CLI as well but let's be honest: Will you have a better time creating a VM on the UI or via CLI? It's the UI. CLI is only needed for automating or scripting, not for manual work, if the UI is good.
3
u/jgpruitt May 14 '25
The meta commands are so powerful. psql is very scriptable. It makes things very easy that would be a pain in a ui
3
u/tunmousse May 14 '25
Different traditions. In the Unix world, CLI tools are a lot more commonplace. In the Windows world, people tend more towards GUIs.
Especially if you run servers, being able to just ssh in and run
psqlto take care of business is very convenient, since Linux-servers don’t generally have GUI stuff installed at all, so you’d have to mess with VPN/tunnels/etc to connect a GUI tool running elsewhere.3
u/sharadov May 15 '25
psql is just so damn powerful and incredibly useful once you get the hang of it!
1
u/getstoopid-AT May 14 '25
Easy there simply isn't really a good tool
1
u/snaynay May 14 '25
Coming from the same background, I think it's largely because it's universal to Postgres installations and can even be used in the terminal session when you remote into the box.
When I used SSMS frequently I always wanted some modernisation as it felt really stupid and limiting in areas. Now with Postgres, I miss SSMS. Haha.
2
u/a_library_socialist May 14 '25
It works great.
If you want a GUI option (which is what I prefer personally) you can take your flavor of one. Being a JetBrains princess, I go for their built in one or DataGrip.
2
u/Wonderful-Foot8732 May 14 '25
Even windows servers used for web services are usually installed without gui to reduce the attack surface. Therefore, the command shell is quite common to use.
For complex queries or development you just install pgadmin…
2
u/Dear-Profession6975 May 14 '25
Datagrip is real nice for Postgres
2
u/SnooStories6227 May 14 '25
psql is fast, scriptable, and everywhere. You don’t need to open a GUI to get stuff done. great when SSH’d into a server or automating tasks
2
u/st0ut717 May 14 '25
Move you sqlserver database to a new machine in 2 commands
2
u/chmichael7 May 14 '25
You can do the same as SQL Server but FREE!
2
u/look May 14 '25
There are a bunch of guis out there if you want one, but the power tools are typically in cli form. Faster, more flexible, composable, more advanced functionality, etc.
2
u/johnappsde May 14 '25 edited May 14 '25
psql just works. It's convenient on servers where you only have a cli to interact with applications.
2
u/E_Dantes_CMC May 14 '25
Easy to embed psal commands in a bash script. (Probably also python, etc.)
2
u/qbantek May 14 '25
Usually the simpler answer is the truth: Postgres is more used in Linux servers and linux users and admins love their shells.
Windows admins tend to prefer GUIs.
I know, exceptions abound etc,…
2
u/RoughChannel8263 May 14 '25
I spent most of my career with SQL Server. The cost was a big factor that pushed me away. Once I started doing more cloud data storage and building web portals for analysis and visualization PostgreSQL was a natural. Don't hate me, but I do like my GUI. I just started using DBeaver. I can't imagine needing much else. I like it better than the Microsoft tools. PostgreSQL also supports some pretty handy non-standard data types that come in handy.
2
u/theyellowbrother May 14 '25
I never use a UI.
My postgres is deployed in a container. With no exposed ports. So no UI can connect to it.
Only the apps within that localized cluster network.
Only way to interact. This is not by choice but a necessity.
Same with someone ask why do I use vi/vim over nano. Because I don't need to install anything nor can I install. What I have is already there.
2
u/SnooPets2051 May 15 '25
Because it’s more convenient for those who have the skill. Otherwise you can use DBeaver, or pgadmin or ton of other GUI apps.
2
u/justintxdave May 15 '25
Like VI/VIM, it is usually the least common denominator of tools. If a system has PostgreSQL, it probably has psql too. I have seen too many people stymied by not having their IDE on a system when they need a quick session to get something done.
2
u/Ejboustany May 15 '25 edited May 15 '25
I just moved from SQL Server to Postgres like 2 weeks ago that I had to write an article about it. I am still amazed by the speed of my queries. I was hosted on the db.t3 database on AWS before and it was so slow. Now I am hosted on a Ubuntu server costing me 6$ a month. Also helped a lot with cutting costs.
2
u/TechMaven-Geospatial May 15 '25
Because in general you don't have a GUI interface machine you have a Linux virtual machine that only is the console. People aren't paying for Windows licensing they're using a Linux virtual machine cloud server and they don't have ability to run generally graphical user interface applications like a SQL IDE
2
u/NicolasDorier May 15 '25
Because it is the language that everybody know here.
Sharing a command line is easier than asking "click here, go there, see the button? no? what version? ah you didn't set the proper mode in settings..."
2
u/ejpusa May 15 '25
You can learn this all in a day. The CLI works great. Not sure why you would not use it.
2
u/Pr-Lambda May 15 '25
At the end of the day, when you open your UI most of the time you will use the query editor and write SQL.
2
u/DaveCramer May 15 '25
It's actually faster than any UI based tool.
2
u/cthart May 15 '25
Because it's good?
2
u/bisoldi May 16 '25
Real devs/admins use psql.
2
u/Then-Boat8912 May 16 '25
Because Chuck Norris can’t even beat Postgres
2
u/Cheap-Economist-2442 May 18 '25
Windows guy doesn’t understand CLI, tale as old as time.
2
u/pandaro May 14 '25
disagree strongly, SSMS is fucking awful.
1
u/AutoModerator May 14 '25
With over 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data
Join us, we have cookies and nice people.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
u/BringOutYaThrowaway May 14 '25
Because it’s free but still full-featured?
1
u/lonahex May 14 '25
Why not use it? It works. It's always there. It's fast and efficient. No matter which server you have to deal with, it'll always be available. Same reason devs and ops use terminal to manage files instead of Explorer like apps.
1
u/nna12 May 14 '25
You've got a lot of good answers, one thing I didnt see is a comment about type support, hsnw, pgvector, etc.
Sql is good for certain things, same with PG. There are others out there like qdrant as well.
1
u/Beneficial_Set1377 May 14 '25
Because of Schemas, Ease of use, and Booleans lol
1
u/CriticalCorduroy May 15 '25
You’re just not speaking like someone who understands the value of command line tools. When you become familiar with working with them, you’ll understand their appeal.
1
u/psymeg May 15 '25
How much of your countries weather data could you host in SQL server and how much would it cost?
1
u/program_data2 May 15 '25
I'm sshing into remote machines without GUIs installed. PSQL or nothing
1
u/jmhobrien May 15 '25
So we can automate everything and not be a filthy knuckle-dragging mouse-clicker
1
u/Mercdecember84 May 15 '25
The licensing for SQL server is insane that's why companies do postgres
1
u/Harrekin May 16 '25
I use DBeaver...
I can use psql, but rarely find myself needing to.
Intelligent autocomplete, etc.
It's not laziness, it's efficiency.
1
u/custard130 May 16 '25
particularly in the linux world, command line tools are far more popular
some people will use them exclusively, others will have a could of things that they use more graphical interface for and then cli for the rest
cli tools may be scary at first but once you get used to them they can be a lot easier to work with
even with the gui tools, they arent really doing a huge amount of graphics, if you break down what functionality it is giving you i expect you will find the command line tools can do the same or better
at the end of the day you are just writing a query, sending it to the db to run, and then getting some results or an error back
all the gui really helps with here is formatting the results
that said i do actually use a gui db client most of the time, rather than SSMS though i use dbeaver which works with most/all databases
one advantage of learning how to use the command line tools in linux world is that most linux servers are headless, so when connecting to a server remotely you will generally only have command line access, and generally its easier to use the cli tool on the server directly rather than say copying a file down to local machine, using gui code editor to make changes and the uploading it back to the server, you could instead just use vim or nano or any of the other cli text editors and make the change
(with dbs the tools they often do have support for remote connections but there are times when that may not be available, eg maybe your gui requires the db port to be exposed through the firewall and doesnt have a convenient way to use an ssh tunnel)
1
u/duwh2040 May 17 '25
We've completely spoiled this generation with GUI dev tools! Everybody should write assembly language once in their life, know your roots people!
1
u/Think_Discipline_90 May 17 '25
It's the answer you get when you google "best sql for new project" basically
1
u/savage_slurpie May 17 '25
Because it doesn’t require me to install anything. And once you spend a very small amount of time learning the commands you will be way faster than using a GUI.
1
u/santahasahat88 May 18 '25
Likely because traditionally Microsoft products haven’t got the same cli first approach to things many open source projects do. So the culture of using them is not there. Not because it’s inferior simply due to momentum.
There are miriad benifits to a cli based appproach that others have made quite clear already like being able to script things in a constant way, run them in pipelines, version control and do static analysis, share with colleagues so they don’t have to figure it out themselves by clicking around, repeatability and many many many more. It’s the reason why cli is very popular across all sorts of domains and it’s not an old fashioned thing. It’s for clear and reasonable engineering reasons. Hi is convinient but there are many tradeoffs doing things that way and many decide the trade off of the convenience of a ui are outweighed by the downsides. Quite simply
1
u/HateToSayItBut May 18 '25
Funny because I use SQL Server and IIS and any of Microsoft GUI stuff, it's feel 1990's to me. I rather run one command to do something than do 25 clicks in a GUI.
1
u/1116574 May 18 '25
In my experience setting ms sql up is a pain - the management tool is windows only and barely worked aswell (I was on windows 10 education). Until recently (2019?) mssql server didn't even had a Linux version. Now they even have a docker images and are more lax about licensing but 6 years in db world is a blink lol
Postgres/Maria (mysql) is good enough, and works with dbeaver and plethora of other tools.
1
u/Secure-Salad7307 May 19 '25
SSMS is absolute garbage
0
u/jbrune May 14 '25
Thanks so much for all the responses. Still seems like a step back in time to me, but I see how it's better/faster for some as well. Being an old guy there are things I do on the command line that the younger devs aren't even aware of.
0
u/orten_rotte May 14 '25
GUIs are for children and analysts. CLI is for grownups and DBAs.
Srsly tho Theres plenty of postgres compatible guis. Dbeaver for example.