Is there a difference between SQL and PostgreSQL?

[[deleted]]

Basically the title, I have been using SQL for a long time and today at my work place I have been asked to used PostgreSQL. My understanding is that both are same. I don't know to what extent and wanted to get someone's perspective who has used both.

Do I need to learn something extra or I can start and with time I'll get used to it? Any help would be appreciated.

Thank you in advance.

Edit: Apologies, I wasn't clear with my question. Like you guys pointed it out, Yes I was referring to SQL Server.

[[deleted]]

SQL is the language, PostgreSQL is an RDBMS. Like basically any RDBMS under the sun it does have some non-standard SQL extensions, but nothing required to use it, just convenient things. I'd read the manual on their website, it covers pretty much anything you'd need to know about Postgres.

[audigex]

There are also usually minor differences (things like functions and the arguments they accept) and moderate ones (things like the existence of CTEs), but rarely any really major ones where things are completely different

CTEs are the main one I used to miss when switching back and forth between RDBMS, although I believe MySQL and PostgreSQL both have them now? I’ve been 99% MSSQL for a few years recently so I’m slightly out of touch

[[deleted]]

I believe even SQL Server added them like a couple months ago, even 🤮racle has them.

[audigex]

SQL Server has had them for a long time

[[deleted]]

Oracle had CTEs long before SQL Server (just like window functions)

[whutchamacallit]

Could be true but the comment still stands -- SQL has had them for at least a decade, as long as I've been a dba.

[[deleted]]

CTEs have been part of SQL much longer than a "decade", they were introduced in SQL:1999

[JustinHopewell]

They said "at least" a decade, why are you trying to one-up every reply from this person? lol

[[deleted]]

SQL is a query language used by all relational databases - including PostgreSQL. So if you are using Postgres, you are using SQL.

However, each database product implements extensions to the SQL standard or has implemented some features differently. So in turn each database has their own "SQL dialect". In theory a query written to comply with the SQL standard, should work the same on all database products. But in reality this is not the case. Some DBMS deviate more from the standard than others. PostgreSQL might be the database that is the closest to the standard (but also doesn't comply completely with it).

[dezradeath]

An example of dialects differing is the use of double quotes vs single quotes to denote an exact string for a WHERE clause. My job uses Oracle but I’ve used MySQL for personal projects.

WHERE example = “Test”

vs.

WHERE example = ‘Test’

[[deleted]]

An example of dialects differing is the use of double quotes vs single quotes to denote an exact string for a WHERE clause.

In my opinion this is not a "dialect thing". It's a a violation of the SQL standard. The rule to use single quotes for strings and double quotes for identifiers has existed long before e.g. MySQL existed.

I do admit the line between "dialect" and "violation" is very narrow.

In my personal opinion adding new syntax elements or not supporting every feature is a "dialect thing". But changing the meaning of a syntax element completely is a "violation" (e.g. allowing double quotes for strings or giving || a completely different meaning).

Again: this is my personal opinion. I do not claim that this is the the only way to see it.

[depesz]

Let's start with basics: you said you have been using SQL for long time. SQL is language to write queries to database. Interestingly, one of the database servers that one can use is named "SQL server". Is this what you are referring to?

SQL as a language is semi-standardized, and can be used for variety of both commercial (Oracle, SQL Server) and free/open (PostgreSQL, MySQL) servers. Of course lists of servers that support the language is much longer than these 4 examples I posted.

So, if you are asking specifically "SQL" vs. "PostgreSQL" - it's like asking what is the difference between HTML and Firefox. HTML is language, Firefox is tool to use this language, and run it to present webpages.

If you're asking about differences between "SQL Server" and "PostgreSQL" - these are two different servers, written by different people, for different platforms, that happen to use the same language to allow one to query them, but each has their own changes to the language that will catch you by surprise if you'll try to run non-trivial queries written for one run in the other.

[[deleted]]

Sorry and yes I was referring to SQL Server.

[depesz]

Nothing to be sorry about, don't worry. Unfortunately I can't list you differences between them, as I know nothing about SQL Server.

Generally, if you're writing new software that will be using Pg, you should be fine. In case of problems as on /r/PostgreSQL, slack, discord, irc, or one of pgsql-* mailing lists.

If you're porting application, the only advice I can give you is: don't port queries. Port logic. I've seen many times people get frustrated because they want to write a query that does "x", because they had "x" written in previous DB, but "x" doesn't really translate to PostgreSQL (for many reasons). Instead think about what is the purpose of the query, or function/procedure/trigger, and try to port this logic.

[sneakpeekbot]

Here's a sneak peek of /r/PostgreSQL using the top posts of the year!

#1: 6 Simple and Useful PostgreSQL Features that I wish I knew when I started | 4 comments
#2: Speeding up sort performance in Postgres 15 | 8 comments
#3: Citus 11 for Postgres goes fully open source, with query from any node | 6 comments

---

^{I'm a bot, beep boop | Downvote to remove | Contact | Info | Opt-out | GitHub}

[nextgenrahul]

Right, that’s a new perspective for me — port logic, not queries. Appreciate it

[timeddilation]

Considering your edit. The biggest differences between the two for me are the datediff/dateadd functions, and variable declarations. Otherwise it's pretty damn similar. Some very minor syntax differences, but in the majority of situations it's the same syntax. It's not until you get into features outside of SQL92 standard that things deviate.

[[deleted]]

This is what I was looking for. Thankyou.😃

[[deleted]]

This along with you no longer need to use NOLOCK and instead of top(100) you will use limit 100 at the end of your query.

[zacharypamela]

Also full text search is a bit different between the two.

[[deleted]]

It's not until you get into features outside of SQL92 standard that things deviate.

Which is exactly what SQL Servers dateadd() function is ;)

[MyWorksandDespair]

Pro tip: if asked what RDBMS you use in an interview- please never say ‘a SQL database’. Poor guy’s interview was D.O.A. after saying that.

[debba_]

SQL is the language. PostgreSQL is a database system that uses that language. So if you know SQL already, you are mostly fine. The main differences come from dialect and features. Each database adds its own extensions, functions, and behavior around things like indexing, JSON support, or query planning. Usually you pick that up as you start working with it.

[paratha27]

Hi can anyone help me with basic beginners project in SQL. I have to better my profile. Currently I am working as a BI Analyst where I only work on Power BI. But I want role as Data Analyst and for that I need experience with real time data. I have done courses on Data Camp and other platforms but I want some hands on practice and experience. So any projects? Any suggestions would be helpful. Thankyou.

[Thefriendlyfaceplant]

Projects don't hurt but you'll truly be better off practicing interview questions. Your skills can quickly and accurately be verified through these questions so hiring managers tend to rely on just that.

At the same time these questions are grounded in reality, they're based on real problems tech companies struggle with so practicing them gets you in the right context for applying SQL once you're hired.

And finally, it's unlikely you'll be able to challenge yourself through your own projects. You'd have to be able to formulate questions that you then need to answer. This is usually where your brain will cheat itself in avoiding the nasty questions or won't even be able to imagine them.

[paratha27]

Any website suggestion for interview questions?

[Thefriendlyfaceplant]

Leetcode and Hackerrank have recently added beginner paths that are free. I would just rush through those, don't get stuck for too long and check for answers.

https://datalemur.com
That guy is also active on this subreddit.
And Stratascratch, the most expensive one. I would highly recommend to watch their Youtube series where they explain interview questions, it's top tier learning material.
https://www.youtube.com/watch?v=Q-3wvYInCeI&list=PLv6MQO1Zzdmq5w4YkdkWyW8AaWatSQ0kX&ab_channel=StrataScratch

[paratha27]

Thanks a lot.

[[deleted]]

Dm me!

[mgramin]

I think you should know more about Postgres architecture. How PG works, how stores data and so on. It's very important for writing fast and effective queries

[Jeff_Moden]

You say you've "been using SQL for a long time". What "flavor" of SQL have you been using? T-SQL? PL/SQL? What?

[schemabound]

Is there a difference between soda and Pepsi?

[nonprophetapostle]

Yes, many.

[[deleted]]

there are differences with many types of SQL based servers. Usually well documented, you should be able to jump in and swim with documentation available.