Карманный справочник: сравнение синтаксиса MS SQL Server и PostgreSQL
Я занимаюсь переводом кода из MS SQL Server в PostgreSQL с начала 2019 года и сегодня продолжу сравнение этих СУБД.
В прошлой публикации мы рассматривали отличия в быстродействии MS SQL Server и PostgreSQL для «1C».
В Ozon есть решения и на MS SQL Server, и на PostgreSQL: первая используется в логистике и системах внутренних сервисов, вторая — в mission critical-подсистемах, от которых напрямую зависит бизнес компании (склад, корзина, оплата картами, платежи, информация о товарах на сайте и др.).
Периодически появляются задачи перевода решений из огромных монолитных баз из MS SQL Server в PostgreSQL. Поэтому давайте сравним основные конструкции синтаксиса этих СУБД для правильного чтения кода, а также для того, чтобы быстро изменять код из MS SQL Server для PostgreSQL и наоборот.
Начнём с сопоставления типов.
Сопоставление типов
DOUBLE PRECISION, FLOAT8
INT, INTEGER, INT4
TIMESTAMP(n) WITH TIME ZONE, TIMESTAMPTZ
Примечание. Типы CHAR и VARCHAR лучше не использовать. Причины подробно описаны здесь.
Более подробно о типах данных:
Теперь перейдём к сопоставлению синтаксиса MS SQL Server и PostgreSQL.
Сопоставление синтаксиса MS SQL Server и PostgreSQL
I. Регистрозависимое обращение к схемам, таблицам (представлениям) и их полям и другим объектам базы данных
В MS SQL Server при обращениях к объектам можно использовать квадратные скобки (они обязательны, только если в названии объекта или его поля присутствуют недопустимые символы):
В PostgreSQL для этого используются двойные кавычки (они обязательны, только если в названии объекта присутствуют заглавные буквы или есть недопустимые символы в названии объекта или его поля):
II. Выборка заданных N данных
В MS SQL Server используется TOP:
В PostgreSQL используется LIMIT:
SELECT . LIMIT N;
III. Постраничная загрузка данных (скользящее окно)
Задача: извлечь 100 строк начиная с 202-й строки включительно по возрастанию даты рождения:
SELECT *
FROM tbl
ORDER BY BirthDate ASC
OFFSET 201 ROW FETCH
NEXT 100 ROWS ONLY;
select *
from tbl
order by BirthDate asc
[—offset 201 row fetch
next 100 rows only;]
LIMIT 100 OFFSET 200
Примечание. Вместо row можно использовать rows в любом месте запроса, а вместо next можно использовать first в обеих СУБД.
IV. Выборка первого непустого значения
V. Тернарный оператор IIF
CASE WHEN <условие> THEN <выражение_если_условие_истинно> ELSE <выражение_если_условие_ложно> END
case when <условие> then <выражение_если_условие_истинно> else <выражение_если_условие_ложно> end
VI. Создание псевдонима
VII. Выражения CASE
VIII. Работа с переменными
Объявление переменной
Примечание. В MS SQL Server при объявлении переменных используется знак @ перед именем, а в PostgreSQL — нет. Также, помимо PL/pgSQL, в PostgreSQL можно встраивать и другие языки, такие как PL/Python и PL/Perl.
Присвоение переменной значения
SET @переменная = значение;
Примечание. В PostgreSQL используется := для PL/pgSQL и просто = для PL/Python и PL/Perl.
Вывод значения на консоль
RAISERROR(@переменная, 1, 1) WITH NOWAIT;
RAISE NOTICE ‘%’, ‘строка’;
RAISE NOTICE ‘%’, <переменная>;
IX. Управление выполнением кода
Выполнение скрипта
В MS SQL Server:
Пример (вывод информации):
Пример (передача значения клиенту):
В DBeaver (бобре) нужно нажать CTRL+SHIFT+O при отсутствии окна вывода, а в pgAdmin вывод происходит автоматически.
В psql и так всё работает.
Цикл WHILE
Логическое ветвление
Более подробно про управление выполнением кода:
X. Функции для работы со строками
Определение длины строки (количество символов в строке)
Примечание. В MS SQL Server исключаются конечные пробелы. Если нужно учитывать и их, то необходимо воспользоваться функцией DATALENGTH (<строка>), которая возвращает суммарное количество байтов в символах строки.
Возвращение символа по его коду:
Конкатенация строк
Нахождение позиции вхождения подстроки
В MS SQL Server:
strpos(substring(<где_ищем>, <с_какой_позиции_ищем_начиная_с_1>, length(<где_ищем>)- <с_какой_позиции_ищем_начиная_с_1>+1), <что_ищем>)
Примечание. Точного соответствия не будет, если производить поиск не с начала строки.
Регистронезависимое сравнение и поиск данных
В MS SQL Server:
2. lower(a) = lower(b) или upper(a)=upper(b)
3. lower(a) <> lower(b) или upper(a)<>upper(b)
4. lower(a) in (lower(b1), . ) или upper(a) in (upper(b1), . )
Примечание. В PostgreSQL рекомендуется произвести оптимизацию через создание функционального индекса:
Более подробно про команду ANALYZE.
Слияние строк по запросу в одну строку по заданному разделителю
В MS SQL Server можно использовать функцию STUFF следующим образом:
Также начиная с версии 2017 доступна функция STRING_AGG.
В PostgreSQL для этого можно использовать функцию string_agg таким образом:
Более подробно про функции для работы со строками:
XI. Функции для работы с датой и временем
Получение текущей даты и времени (локальное время)
Получение текущей даты
CAST(GetDate() as DATE)
Пример преобразования формата даты и времени из строки public_date:
В MS SQL Server:
Приращение даты/времени
В MS SQL Server:
DateAdd(datepart, count, dt);
dt + (count * interval ‘1 datepart’);
или
dt + interval ‘count datepart’;
Более подробно про функции для работы с датой и временем:
XII. Получение количества строк, затронутых при выполнении последней команды
XIII. Выполнение динамического SQL-кода
XIV. Проверка и приведение типов
Проверка строки на то, что она является числом
В MS SQL Server:
Безопасное приведение типа
В MS SQL Server:
Примечание. try_cast в MS SQL Server возвращает NULL, если значение невозможно привести к заданному типу, в других случаях — работает как оператор CAST.
В PostgreSQL есть два способа:
1) через обработку ошибок:
2) через реализацию функции:
Функция в итоге не возвращает преобразованное в заданный тип значение.
Функция на вход принимает текст и возвращает текст.
Если значение невозможно привести к заданному типу, то возвращается NULL.
Пример использования (чтобы было как в MS SQL Server):
XV. DML-команды
Обновление данных
Пример в MS SQL Server:
Обновление поля Name в таблице Production.ScrapReason для тех строк, для которых есть соответствующие записи в таблице Production.WorkOrder по равенству ScrapReasonID и у которых значение ScrappedQty больше 300:
Ключевое слово OUTPUT позволяет получить данные об обновлении.
Пример в PostgreSQL:
Обновление поля Name в таблице production.scrapreason для тех строк, для которых есть соответствующие записи в таблице production.workorder по равенству scrapreasonid и у которых значение scrappedqty больше 300:
Ключевое слово returning позволяет получить данные об обновлении.
Более подробно о команде UPDATE:
Удаление данных
Пример в MS SQL Server:
Удаление из таблицы Sales.SalesPersonQuotaHistory тех записей, для которых есть соответствующие записи в таблице Sales.SalesPerson по равенству BusinessEntityID и у которых значение SalesYTD больше 2500000.00:
Ключевое слово OUTPUT позволяет получить данные об удалении.
Пример в PostgreSQL:
Удаление из таблицы sales.salespersonquotahistory тех записей, для которых есть соответствующие записи в таблице sales.salesperson по равенству businessentitid и у которых значение salesytd больше 2500000.00:
Ключевое слово returning позволяет получить данные об удалении.
Более подробно о команде DELETE:
Получение изменённых записей
В MS SQL Server:
insert/update/delete таблица
Output deleted/inserted.<столбец>
into [@/#] <таблица>
Values|From <запрос>
insert/update/delete таблица
values()|from <запрос>|using <запрос>
returning *, столбец/столбцы
В update есть доступ только к inserted.
Примечание. В PostgreSQL не нужна промежуточная таблица для получения изменённых записей.
Удаление дубликатов (дублирующих строк):
В MS SQL Server:
или более сложный вариант:
Примечание. Оптимальный вариант — внести в таблицу уникальный ключ, так как работа с метаданными увеличивает нагрузку на систему.
При наличии уникального ключа удалять дубликаты в PostgreSQL можно следующим образом:
XVI. DDL-команды для работы с таблицами
Удаление таблицы с предварительной проверкой
В MS SQL Server:
Для основной таблицы:
Для локальной временной таблицы:
Для глобальной временной таблицы:
#<table> — локальная временная таблица, которая видна только в текущей сессии
##<table> — глобальная временная таблица, которая видна всем пока она существует
Все временные таблицы живут, либо пока активна сессия, во время которой они были созданы, либо пока их явно не удалят.
Для основной таблицы:
Для временной таблицы:
Более детально про удаление таблиц:
Создание таблицы через выборку
В MS SQL Server:
Для основной таблицы:
Для временной таблицы:
Для основной таблицы:
Для временной таблицы:
Более детально про создание таблиц через выборку:
Создание/изменение и удаление значения по умолчанию для колонки таблицы
В MS SQL Server:
Выборка всех значений по умолчанию:
Изменение происходит через удаление и добавление.
Создание и изменение:
Выборка всех значений по умолчанию:
Изменение типа колонки таблицы
В MS SQL Server:
ALTER TABLE
<схема>.<таблица>
ALTER COLUMN <поле>
<новый_тип> [NULL|NOT NULL];
alter table
<схема>.<таблица>
alter column <поле>
type <новый_тип>;
Примечание. Если у изменяемого столбца есть какие-либо констрейнты или значения по умолчанию, то сначала нужно их сохранить, а затем удалить. Вернуть их можно будет после изменения типа.
Перенос автоинкрементных полей
В MS SQL Server делаем запрос вида:
Полученные скрипты применяем на стороне PostgreSQL.
Создание автоинкрементных полей
В MS SQL Server:
Более детально про создание таблиц:
Более детально про изменение таблиц:
XVII. Создание и изменение представления
В MS SQL Server:
CREATE OR ALTER VIEW
create or replace view
Примечание. В PostgreSQL лучше сначала удалять представление, а потом заново его создавать, если набор полей меняется или меняются названия выходных полей, иначе можно получить ошибку при обращении к изменённому представлению.
Более подробно про создание и изменение представлений:
XVIII. Построчная обработка строк в наборе
В MS SQL Server:
XIX. Системные информационные функции безопасности
Текущий пользователь
В MS SQL Server используется функция CURRENT_USER().
session_user — под каким пользователем открыта сессия
current_user (или просто user) — под каким контекстом (ролью) идёт выполнение (session_user переключается для выполнения — здесь важно, под каким правом делается переключение)
Получение имени экземпляра и IP-адреса сервера СУБД
В MS SQL Server:
Получить информацию об IP-адресе сервера СУБД:
Получить название экземпляра СУБД:
Получить IP-адрес сервера СУБД:
Получение названия экземпляра СУБД пока не реализовано.
Более подробно про системные информационные функции безопасности:
XX. Определение и вызов хранимой процедуры
Определение хранимой процедуры
Вызов хранимой процедуры
В MS SQL Server:
XXI. Создание скалярной функции
XXII. Передача табличного значения (вывод таблицы)
В MS SQL Server:
XXIII. DML-триггеры
Пример в MS SQL Server:
Здесь создаётся триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text после обновления данных, который для обновляемых строк проставляет текущие дату, время и пользователя соответственно.
Здесь удаляется триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text
Пример в PostgreSQL:
Здесь создаётся функция dbo.update_mod(), которая заполняет два поля текущими датой, временем и пользователем соответственно.
Здесь создаётся триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text до обновления данных, который для каждой строки вызывает выполнение функции dbo.update_mod().
Здесь удаляется триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text.
Важно! В триггере используйте ключевое слово before, когда хотите нашкодничать в той же таблице, для которой создаётся триггер, и after — для логирования в другую таблицу.
Более подробно про DML-триггеры:
И в качестве бонуса кратко рассмотрим сопоставление основных системных представлений и приведём ссылки для мониторинга.
Немного о сопоставлении системных представлений и мониторинге
Сопоставление системных представлений
MS SQL Server
PostgreSQL
Описание
Предоставляет статистику по выполненным запросам.
В MS SQL Server содержит только то, что в кеше, а в PostgreSQL — всю статистику.
CREATE EXTENSION pg_stat_statements;
Предоставляет статистику по вызовам пользовательских функций.
Предоставляет статистику по использованию всех пользовательских индексов.
Предоставляет статистические данные по каждой БД.
Системные представления MS SQL Server:
Мониторинг работы СУБД
Заключение
Мы рассмотрели сопоставление типов и основные конструкции синтаксиса MS SQL Server и PostgreSQL, что позволит быстрее адаптировать решения из одной СУБД под другую.
Также мы пробежались по сопоставлению системных представлений и ссылкам на документацию, в том числе о мониторинге, что позволит анализировать производительность реализованного или перенесённого кода.
Database Comparison: MS SQL Server vs PostgreSQL vs MongoDB
Hey There! I recently worked on a project where i was confused which Database should i use in the project. For that i picked some popular databases and compared them. Here i’m sharing my analysis for these databases.
Databases
For my analysis I used 3 databases:
- Microsoft SQL Server(MS SQL)
- PostgreSQL
- MongoDB
Here we have both SQL and NoSQL databases. The difference in them is —
- SQL databases are relational, NoSQL are non-relational
- SQL databases use structured query language and have a predefined schema. NoSQL databases have dynamic schema for unstructured data.
- SQL databases are vertically scalable, NoSQL databases are horizontally scalable
Microsoft SQL server is very popular RDBMS with highly restrictive licensing and high cost of ownership which is mainly used for e commerce and providing different data warehousing solutions. It is a cross platform database system but runs only on Windows and Linux.
For running it on MAC, programmer need to install Docker. This will enable you to run SQL Server from within a Docker container.
PostgreSQL is the world’s most advanced open source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. The PostgreSQL community is very strong and continuously improving existing features and implementing new features.
It is a cross platform database system. It provides support to many Operating Systems.
MongoDB is an open-source document database and leading NoSQL database. Documents consist of key-value pairs which are the basic unit of data in MongoDB.
It is a cross platform database system. It provides support to many Operating Systems.
Comparison in the Relational Databases(MSSQL vs PostgreSQL)
Both are popular choices in the market; let us discuss some of the major Difference:
1. Cross-platform and OS support: Both servers are cross platform supported. PostgreSQL supports many operating systems Like Windows, Linux, Unix, OS X etc. But MS SQL Server supports only Windows and Linux.
2. Procedural language features: PostgreSQL provides procedural language support. It supports JSON data type and has ultimate power and loads of flexibility included in a single package. A user can easily use Python, Perl, R, Java, PHP with SQL as they are supported as procedural languages in PostgreSQL. MS SQL Server also provides procedural language support as an inbuilt feature. But this feature is a bit messy, slow and has poor features. It also has small errors with different bugs always occurring.
3. Regular expressions: PostgreSQL provides a huge number of regex as a basis for analytical work. MS SQL Server, on the other hand, has like, substring, pattern index which are not as good when compared to expressions provided by PostgreSQL.
4. Popularity: PostgreSQL is used by businesses of all sizes, though it is more popular with smaller businesses due to its open-source nature. MS SQL Server is a relational database software that is most popular with mid-sized and large enterprises.
5. Good external language bindings: PostgreSQL is very easy to connect to and use from programming environments, because libpq, its external API, is very well-designed and very well-documented. MS SQL Server ‘s external language bindings vary. Sometimes you have to install extra drivers. Sometimes you have to create classes to store the data you are querying, which means knowing at compile time what that data looks like. Documentation is also a bit confusing and messy, which makes getting this done unnecessarily time-consuming and painful.
6. Cost: PostgreSQL is an Open-Source project so it is free. MS SQL server is a Microsoft’s product and for using it organization/User need to pay.
7. Partitioning methods: PostgreSQL uses declarative partitioning (by range or by list) since PostgreSQL 10.0. In MS SQL Server, tables can be distributed across several files (horizontal partitioning); sharding through federation.
8. Script-ability: PostgreSQL can be driven entirely from the command line. MS SQL Server is more driven through a GUI.
9. Replication: PostgreSQL uses master-slave replication. MS SQL Server supports snapshot replication, transactional replication, merge replication.
10. Concurrency: PostgreSQL has a better concurrency management system. It handles very well the case where multiple processes can access and modify shared data at the same time. MS SQL Server has underdeveloped concurrency and you can easily get various locked, blocked, and deadlocked reports in the log. Thus the performance of the database, as well as the application, will be slower.
From the above comparisons, in most of the cases PostgreSQL is more suitable.
Comparison in PostgreSQL, MongoDB
Both databases are awesome; let us discuss some of the major Differences:
1. Database Structure: MongoDB is an open-source software that is used for non-relational database management systems. In MongoDB, all the contents of the database are documents and files. PostgreSQL is an open-source software that is used for the relational database management system. As PostgreSQL handles relational database, it is object-oriented in nature.
2. Data Schema and orientation: MongoDB is a schema-free database and mainly document oriented. PostgreSQL is a schema-based database and uses object oriented.
3. Data Storage: In MongoDB, Collection is used for storing the related information and the document is used for getting information. In PostgreSQL, the tables are used for storing the related data information and the rows are used for getting information.
4. Architecture: MongoDB follows the distributed architecture. PostgreSQL follows the monolithic architecture.
5. Syntax: In MongoDB, syntax is different compared to other relational databases. PostgreSQL’s syntax is like other relational databases.
6. Multi-lingual: MongoDB is only available in English language. PostgreSQL is available in multiple languages.
7. Upfront planning: MongoDB gives you the flexibility to focus on getting application working without doing a lot of upfront database planning. When using PostgreSQL, you’ll need to spend a lot of time upfront planning the database schema because schema changes are difficult.
8. Scalability: MongoDB can be scaled up Horizontally, which means that you can scale by adding more servers and spreading the load and data across them. PostgreSQL can be scaled up by increasing the capacity of a single server. It can be implemented by increasing the server’s RAM.
9. Extensibility: MongoDB is extensible using plug-ins. PostgreSQL is highly extensible.
10. Write speed: PostgreSQL is magically faster than MongoDB if documents are stored in a tabular format but that’s not the case with MongoDB because documents are stored in JSON format. When data in Postgres is stored as JSON documents, it no longer is faster than MongoDB.
Both the databases are awesome but it depends on our use case…
If you would like to learn more / have feedback, please let me know, so I can improve & write more for you interested folks.
Keep learning! 🙂
PostgreSQL vs. SQL Server (MSSQL) — Extremely Detailed Comparison
PostgreSQL and SQL Server (or MSSQL) are two widely used relational databases. Although they share a number of core traits, there are major differences between them. In this article, we provide a detailed rundown of the similarities and differences between PostgreSQL and SQL Server. Among the most significant distinctions is that PostgreSQL is open source, while SQL Server is owned and licensed by Microsoft. In addition, you will learn about differences between the two systems when it comes to licensing and cost, ease of use, SQL syntax and compliance, data types, available features, performance, and security, among many others. Over 40 topics are covered in head-to-head comparisons. It will be particularly useful for organizations who are thinking of making the switch from a commercial to an open-source database, but need more information on the possible trade-offs and advantages of the two systems. However, it is intended for anyone who is curious to learn more about relational databases. We conclude that SQL Server has historically been popular with organizations that rely on other Microsoft products, but PostgreSQL has risen to the top of the field not only because of the advantages of going open source but also for its robust features and active community of users.
What is the difference between PostgreSQL and SQL Server licensing? Comparison of PostgreSQL vs. MSSQL Server licensing model
PostgreSQL
PostgreSQL is an open source database released under the PostgreSQL License, an Open Source Initiative Approved License. The use of PostgreSQL for any purpose, including commercial, is free. Under the PostgreSQL Global Development Group, PostgreSQL is available as free and open source software in perpetuity.
SQL Server
Microsoft SQL Server is available through commercial license and can be licensed on a per-core model or server and client access level (CAL) model. MSSQL is offered in two main editions, Enterprise Edition and Standard Edition, to meet the performance and price requirements of organizations and individuals. Licensing costs range from from $3,586 for the Standard Edition to $13,748 for the Enterprise edition (for two cores); for the server and CAL model it runs $899 for the server plus $209 per user. A free version is available to students and developers for building and testing.
What are the release update differences between PostgreSQL and SQL Server? Compare the release updates of PostgreSQL and MSSQL
PostgreSQL
PostgreSQL was created in 1986 at the University of California, Berkeley, and first released in 1989. It has undergone several major updates since then, and the project still maintains regular releases under an open-source license. The current version of Postgres is version 13, released in October 2019, with regular minor releases since then. Previous major versions are supported for five years after their initial release.
SQL Server
SQL Server was developed by Microsoft and first released in 1989, and new releases occur regularly. The current version, Microsoft SQL Server 2019, was released in November 2019. Previous versions continue to receive support from SQL Server 2012 onward. Extended support for recent versions is offered for 10 years, with an optional premium assurance paid extension after that for up to 16 years.
Which of PostgreSQL or SQL Server is easier to use? Compare the ease of use of PostgreSQL vs. MSSQL
- PostgreSQL is an advanced object-relational database management system that uses Structured Query Language (SQL) in addition to its own procedural language, PL/pgSQL. PostgreSQL is easy-to-use with a full stack of RDBMS database features and capabilities for handling data. It can be easily installed on Linux environments.
- SQL Server is a Relational Database Management System (RDBM) developed and operated by Microsoft. It uses a variant of Structured Query Language (SQL) called T-SQL (for Transact-SQL). It can run on Linux operating systems with Kubernetes support or on Windows. Users describe it as easy to use and reliable, with strong .NET compatibility.
What are the syntax differences between PostgreSQL and SQL Server? Compare PostgreSQL vs. MSSQL Server Syntax
PostgreSQL vs. SQL Server Syntax Comparison Table
SELECT
col1, col2
Select [col1], [col2]
Aliases for columns and tables
SELECT AVG(col1) AS avg1
SELECT AVG(col1)=avg1
Working with dates
CURRENT_DATE() CURRENT_TIME() EXTRACT()
GETDATE() DATEPART()
What are the data type differences between PostgreSQL and SQL Server? Compare data types in PostgreSQL vs. MSSQL
PostgreSQL vs. SQL Server Data Types Comparison Table
Data type
PostgreSQL
SQL Server
64-bit integer
BIGINT
BIGINT
Fixed length byte string
BYTEA
BINARY(n)
1, 0 or NULL
BOOLEAN
BIT
Fixed length char string, 1 <= n <= 8000
CHAR(n)
CHAR(n)
Variable length char string, 1 <= n <= 8000
VARCHAR(n)
VARCHAR(n)
Variable length char string, <= 2GB
TEXT
VARCHAR(max)
Variable length byte string , 1 <= n <= 8000
BYTEA
VARBINARY(n)
Variable length byte string , <= 2GB
BYTEA
VARBINARY(max)
Variable length Unicode UCS-2 string
VARCHAR(n)
NVARCHAR(n)
Variable length Unicode UCS-2 data, <= 2GB
TEXT
NVARCHAR(max)
Variable length character data, <= 2GB
TEXT
TEXT
Variable length Unicode UCS-2 data, <= 2GB
TEXT
NTEXT
Double precision floating point number
DOUBLE PRECISION
DOUBLE PRECISION
Floating point number
DOUBLE PRECISION
FLOAT(p)
32 bit integer
INTEGER
INTEGER
Fixed point number
NUMERIC(p,s)
NUMERIC(p,s)
Date includes year, month, and day
DATE
DATE
Date and time with fractional seconds
TIMESTAMP(p)
DATETIME, DATETIME2(p)
Date and time with time zone
TIMESTAMP(p) WITH TIME ZONE
DATETIMEOFFSET(p)
Date and time
TIMESTAMP(0)
SMALLDATETIME
Unsigned integer, 0 to 255 (8 bit)
SMALLINT
TINYINT
UUID (16 byte)
CHAR(16)
UNIQUEIDENTIFIER
Automatically updated binary data
BYTEA
ROWVERSION
Currency amount (32 bit)
MONEY
SMALLMONEY
Variable length binary data, <= 2GB
BYTEA
IMAGE
Geometric types
POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE
GEOMETRY
What are the geographic data differences between PostgreSQL and SQL Server? Compare geographic data in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL does not have a native data type for geographic data. The open-source PostGIS resource offers support for geographic objects.
SQL Server
SQL Server has the geography data type for storing geographic spatial data.
What are the case sensitivity differences between PostgreSQL and SQL Server? Compare index types in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL is case sensitive for evaluating strings. The LOWER() function allows users to convert strings to all lower case for evaluation purposes (there is also a similar UPPER() function). By default, PostgreSQL converts table and column names to lowercase, unless those names are placed in quotes. The citext module provides a case insensitive string data type citext for comparing values.
SQL Server
SQL Server is case insensitive by default. The case sensitivity can be changed by adjusting the SQL Server’s collation settings. The collation settings for case sensitivity can be set at the database or column level.
What are the index type differences between PostgreSQL and SQL Server? Compare index types in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL offers a number of options for index types, including B-tree, hash, Generalized Search Tree (GiST), Space Partitioned GiST, Generalized Inverted Index (GIN), and Block Range Index (BRIN). In addition, it supports expression indexes (indexes created with an expression or function rather than a column value) and partial indexes (indexes of part of a table).
SQL Server
SQL Server offers clustered and non-clustered indexes. Clustered indexes sort and data rows in the table or view based on key values (columns in the index definition). A table can have only one clustered index. Nonclustered indexes are stored separately from table data, and each key value entry has a pointer to the data. MSSQL creates these automatically when PRIMARY KEY and UNIQUE constraints are defined on table columns. The UNIQUE constraint creates a nonclustered index, while the PRIMARY KEY creates a clustered index unless one already exists.
What are the replication differences between PostgreSQL and SQL Server? Compare replication in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL has Primary-Secondary replication. Replication can be synchronous or asynchronous. Asynchronous replication is accomplished through the use of write-ahead logs (WALs) to share changes with the replica nodes. Streaming replication allows standby servers to be updated more immediately by streaming the WALs as they are created, rather than waiting for the file to be filled. Logical replication follows a publish and subscribe model; this method of replication is called logical because changes are based on the data’s replication identity (for example, a primary key) rather than its physical location. Physical replication deals with files and directories, without regard for the contents within those physical locations. PostgreSQL does not natively offer multi-master replication, but some third-party tools offer multi-master replication solutions.
SQL Server
SQL Server replication duplicates data from a Publisher server to a Subscriber offers three types of replication:
- transactional replication, for server-to-server environments, where changes are delivered from the publisher to the subscriber as they occur;
- merge replication, for server-to-client environments or in situations where conflicts might occur, where data can be changed and tracked on either the publisher or subscriber and later synchronized;
- snapshot replication, for when data is updated infrequently or does not need to be changed incrementally, where data is duplicated exactly as it appears at a specific moment.
Replication in SQL Server can be synchronous-commit or asynchronous commit. The Enterprise edition offers peer-to-peer replication, as an alternative solution to multi-master replication.
What are the differences in clustering between PostgreSQL and SQL Server? Compare clustering in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL allows clusters of servers but does not natively support multi-master or active/active clusters. Tools such as repmgr allow for easy maintenance of PostgreSQL clusters.
SQL Server
SQL Server offers Windows Server Failover Clustering, which can be configured for both active/passive and active/active nodes. The Standard edition only supports two nodes for clusters; additional nodes require an upgrade to the Enterprise edition.
What are the differences in high availability between PostgreSQL and SQL Server? Compare the high availability in PostgreSQL vs MSSQL
PostgreSQL
PostgreSQL offers a number of solutions to ensure high availability for users, including shared disk failover, write-ahead log shipping, data partitioning, and multiple replication methods. Tools like EDB Postgres Failover Manager provide automatic failover to ensure high availability by monitoring for and identifying database failure.
SQL Server
SQL Server includes a number of high availability tools in its various editions. These include replication, log shipping, and failover clusters. Its Always On availability groups, offered with the Enterprise edition, provide automatic failover when certain conditions are met.
What are the “views” differences between PostgreSQL and SQL Server? Compare the “views” in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL supports views—virtual tables that do not store data themselves. Updatable views are supported, but updates do not occur automatically unless it meets the following conditions:
- The query of that view must have precisely one section in the FROM clause and this can be a table or another updatable view.
- The selection list must not contain any window function any aggregate functions or any set-returning function.
- The query must not contain one of the following clauses at the top level: HAVING, LIMIT, DISTINCT, WITH, INTERSECT, EXCEPT, OFFSET AND LIMIT.
Views created with simple queries can be updated; ones created with complex queries cannot, but complex views can be updated by using rules. Materialized Views are also supported; the data in materialized views can be updated using the REFRESH MATERIALIZED VIEW statement.
SQL Server
SQL Server views can be used for security purposes to restrict user access to data. Both user-defined and system-defined views are supported. Views can be automatically updated using triggers. The data in a view can be updated when the modifications are made to a column from a single underlying base table and are referenced directly. Materialized views are known in SQL Server as Indexed Views; unlike materialized views in other relational databases, indexed views are synched to the underlying data and are thus updated automatically.
What are the trigger differences between PostgreSQL and SQL Server? Compare the triggers in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL has advanced triggers. Supported triggering events are AFTER, BEFORE, and INSTEAD OF, and they can be used for INSERT, UPDATE, and DELETE events. Functions can be used to execute a complex SQL when the trigger gets invoked. PostgreSQL can execute this dynamically.
SQL Server
SQL Server offers triggers for different types of database events:
- DML Triggers: for a data manipulation language (DML) specific event, such as inserting, updating, or deleting records. These triggers fire on events irrespective to the number of rows affected.
- DDL Triggers: for data definition language (DDL) events, such as CREATE, DROP, or ALTER statements. These are useful for preventing or auditing changes to the database schema.
- Logon Triggers: for logon events, such as when a user session is established. These triggers fire after successful authentication and before establishing the user session. They are useful for auditing and controlling login activity.
What are the stored procedures differences between PostgreSQL and SQL Server? Compare the stored procedures in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL supports stored procedures as user-defined functions with a RETURN VOID clause. Stored procedures are supported in various languages in addition to standard SQL syntax.
SQL Server
SQL Server supports stored procedures for languages supported by Microsoft .NET framework (common runtime languages or CLR, like VB, C#, or Python).
What are the query differences between PostgreSQL and SQL Server? Compare the query in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL offers the PL/pgSQL procedural programming language. Additional functionalities to standard SQL in PostgreSQL include advanced types and user-defined types, extensions and custom modules, JSON support, and additional options for triggers and other functionality.
SQL Server
SQL Server uses T-SQL, which has a similar query syntax to standard SQL. T-SQL includes additional support for strings and data processing, local variables, and procedural programming.
What are the full-text search differences between PostgreSQL and SQL Server? Compare full-text search in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL offers advanced functionality for full-text search. It uses full-text indexing and dictionaries for faster searches. Preprocessed text documents are stored as a tsvector data type, and processed queries are stored as the tsquery type. Preprocessing parses text documents into linguistic units known as lexemes, which allows you to find case insensitive variants of a word.
SQL Server
SQL Server offers full-text search as an optional component. Queries are run against a full-text index, and searches can be based on particular language rules. Searches are performed on columns or text data types (including char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM) using the T-SQL commands CONTAINS to match words and phrases and FREETEXT to match meaning. Thesaurus files can be used to help find synonyms of search terms.Full-text searches in SQL Server are not case sensitive.
What are the regular expression differences between PostgreSQL and SQL Server? Compare regular expressions in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL has three methods for evaluating regular expressions: LIKE, SIMILAR TO, and POSIX regular expressions.
SQL Server
SQL Server does not natively support regular expression evaluation; similar but limited results can be achieved using the T-SQL functions LIKE, SUBSTRING, and PATINDEX.
What are the partitioning differences between PostgreSQL and SQL Server? Compare the partitioning in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL offers built-in support for range, list and hash partitioning. Range partitioning groups a table is into ranges defined by a partition key column or set of columns—for example, by date range. List partitioning breaks a table into groups by explicitly listing predefined key values that appear in each partition.
EDB Postgres Advanced Server also supports Interval Partitioning, which automatically creates the interval partitions as data arrives without causing deadlocks.
SQL Server
SQL Server supports table and index partitioning. The data is partitioned horizontally and maps groups of rows into individual partitions. All partitions of a single index or table must reside in the same database, and the table or index is treated as a single entity for queries and updates.
What are the table scalability differences between PostgreSQL and SQL Server? Compare the table scalability in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL has several indexing and two types of partitioning options to improve data operations and query performance on a scalable table. Table partitions and Indexes can be placed in separate tablespaces on different disk file systems, which can greatly improve table scalability. Postgres does not support horizontal table partitioning, but several commercially developed products are available.
SQL Server
SQL Server contains scalability enhancements to the on-disk storage for memory-optimized tables. The current versions offer multiple concurrent threads to persist memory-optimized tables, multithreaded recovery and merge operations, dynamic management views. Scaling in SQL Server can be easily achieved through sharding.
What are the table statement differences between PostgreSQL and SQL Server? Compare the table statements in PostgreSQL vs. MSSQL
Truncate
In PostgreSQL, TRUNCATE removes all rows from a set of tables. It is faster than DELETE because it does not scan the tables first, and disk space is reclaimed immediately, without the need for a subsequent VACUUM operation. This is useful on large tables.
TRUNCATE customers;
In SQL Server TRUNCATE TABLE removes all rows from a table or specified partitions of a table, similar to a DELETE statement with no WHERE clause. TRUNCATE TABLE works faster than DELETE and uses fewer resources because it does not log individual row deletions.
TRUNCATE TABLE customers
WITH (PARTITIONS (2, 4, 6 to 8));
Inheritance
PostgreSQL supports object-oriented programming features, including the use of inheritance.
CREATE TABLE country (
Name text,
area real,
population real);
CREATE TABLE capitals (
city text) INHERITS (country);
When INHERITS is applied to a table, it inherits all the fields and properties of the parent table, which helps speed up development and improve readability.
SQL Server is not an object-oriented database and does not support table inheritance. However, a similar outcome is achievable through the use of DDL Triggers.
Nested
PostgreSQL does not explicitly support nesting data. It does support arrays of arbitrary types, which has an equivalent effect:
CREATE TYPE BeerType AS (
name CHAR(25),
kind CHAR(15),
percentage NUMERIC(2, 0)
CREATE TABLE BeerDrinkers (
name CHAR(35),
address AddrType,
beers BeerType[]
In SQL Server, a nested table can be created when two source tables contain a defined relationship, where items in one table can be related to those in the other. This can be a unique identifier shared by both tables. Nested tables can be useful for analyzing data.
What are the compliance differences between PostgreSQL and SQL Server? Compare the compliance in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL is an object-relational database management system (ORDBMS) designed to emphasize extensibility and standards compliance. It is ACID-compliant. EDB Postgres Advanced Server is also HIPAA, GDPR, and PCI compliant.
SQL Server
SQL Server is a relational database management system (RDBMS) with an emphasis on security and performance. It is ACID-compliant.
What are the column differences between PostgreSQL and SQL Server? Compare the columns in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL introduced a new constraint feature in version 10 called GENERATED AS IDENTITY. This is an SQL-compliant variant of the SERIAL column that allows you to assign a unique value to an identity column automatically.
For a SERIAL column to have a unique constraint or be a primary key, it must now be specified, just like other data types. Unique identifier columns are created using the data types smallserial, serial, and bigserial, similar to auto-increment features in other databases.
SQL Server
SQL Server’s identity column property creates an identity column for a table for generating key values for rows. Two values are specified when it is created: seed (initial value for the first row) and increment (amount to increase value over the previous row). By default, both the seed and incremental values are 1. Each table can only contain one identity column. Uniqueness of the values are not guaranteed unless PRIMARY KEY or UNIQUE constraints are imposed.
What are the computed column differences between PostgreSQL and SQL Server? Compare the computed column differences in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL uses the term generated columns for computed columns. This feature was introduced with version 12. Generated columns can be physically stored when they are marked STORED; otherwise, they are not stored and known as virtual.
Generated columns cannot have an identity definitions or be part of a partition key; they can only reference the current row and cannot use subqueries. Values cannot be specified using INSERT or UPDATE, but the keyword DEFAULT is accepted.
SQL Server
SQL Server computed columns are not physically stored in a table unless the column is marked with the PERSISTED property; the column can only be persisted when the value is deterministic, or always returns the same result.
If the computed column is deterministic and an acceptable data type, it can be used as a PRIMARY KEY or index, but it cannot be used as a DEFAULT or FOREIGN KEY constraint. Values cannot be specified using INSERT or UPDATE.
What are the differences between PostgreSQL and SQL Server when deleting tables? Compare the differences when deleting table data in PostgreSQL vs. MSSQL
You can delete data from a table in PostgreSQL using the DELETE statement:
The DELETE FROM clause specifies the table, and the rows to delete are specified by using the condition in the WHERE clause. The WHERE clause is optional, but if you omit it, the statement will delete all rows in the table.
The DELETE statement removes rows from a table in SQL SERVER:
The name of the table from which the rows are to be deleted is specified in the FROM clause, and the rows to delete are specified by the condition in the WHERE clause. The WHERE clause is optional, but if you skip it, all rows from the table will be removed.
You can specify the number or percent of random rows that will be deleted, by using the TOP clause.
This statement removes 10 random rows from the table. Because rows are stored in unspecified order, we do not know which 10 rows will be deleted. Similarly, you can delete the 10 percent of random rows:
What are the differences of integers between PostgreSQL and SQL Server? Compare the integers in PostgreSQL vs. MSSQL
PostgreSQL
There are three kinds of integers in PostgreSQL:
- SMALLINT (small integer, a 2-byte type with a range from -32,768 to 32,767)
- INT (integer, a 4-byte type with a range from -2,147,483,648 to 2,147,483,647)
- BIGINT (a large-range integer: -9223372036854775808 to 9223372036854775807)
SQL Server
SQL SERVER supports standard SQL integer types BIGINT, INT, SMALLINT, and TINYINT. The range and storage size of each type is as follows:
PostgreSQL vs. SQL Server Integers Comparison Table
Data type
Range
Storage
BIGINT
-2 63 (-9,223,372,036,854,775,808) to 2 63 -1 (9,223,372,036,854,775,807)
8 Bytes
INT
-2 31 (-2,147,483,648) to 2 31 -1 (2,147,483,647)
4 Bytes
SMALLINT
-2 15 (-32,768) to 2 15 -1 (32,767)
2 Bytes
TINYINT
0 to 255
1 Byte
What are the boolean type differences between PostgreSQL and SQL Server? Compare the boolean types in PostgreSQL vs. MSSQL
PostgreSQL
The PostgreSQL Boolean data type can have 3 states:
- TRUE,
- FALSE, and
- NULL.
SQL Server
In SQL SERVER the BIT data type is used to represent true/false boolean data. A BIT field’s value is either 1, 0, or null.
What are the NoSQL capability differences between PostgreSQL and SQL Server? Compare the NoSQL capabilities in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL, like many other relational databases, has added support for JSON data, the most common format for semi-structured data stored in NoSQL systems. But because SQL is the only way to interact with a PostgreSQL database, it should not be considered NoSQL.
SQL Server
SQL Server has native JSON functions that enable you to parse JSON documents using standard SQL language. You can store JSON documents in SQL Server and query that JSON data just as you would in a NoSQL database. Still, because SQL Server is an SQL database, it should not be considered NoSQL.
What are the security differences between PostgreSQL and SQL server? Compare the security in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL supports SSL (Secure Sockets Layer) connections to encrypt client-server communications. You can enable SSL by setting the ssl parameter in the postgresql.conf file. To meet an enterprise need, EDB Postgres Advanced Server includes additional built-in auditing features that capture more detailed data, integrated password policy management capabilities and data redaction.
SQL Server
SQL Server offers a range of features and functions to prevent security threats, because each application is unique in its security needs. The SQL Server security framework manages access to securable entities through authentication and authorization. SQL Server has support for a hierarchy of encryption options and supports TLS (transport layer security) for encrypting network traffic.
What are the analytical function differences between PostgreSQL and SQL server? Compare the analytical functions in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL supports various analytical functions, which perform aggregation on a set of rows. There are two types of analytical functions: window functions and aggregate functions. Aggregate functions perform aggregation and return a single aggregate value for a set of rows (like sum, avg, min, or max). Window functions return a single aggregate value for each of the rows.
PostgreSQL supports the following window functions:
Function
Description
CUME_DIST
Return the relative rank of the current row
DENSE_RANK
Rank the current row within its partition without gaps
FIRST_VALUE
Return a value evaluated against the first row within its partition
LAG
Return a value from a specified physical offset row before the current row within the partition
LAST_VALUE
Return a value evaluated against the last row within its partition
LEAD
Return a value from a row that is offset rows after the current row within the partition
NTILE
Divide rows in a partition as equally as possible then assign each row an integer from 1 to the argument value
NTH_VALUE
Return a value evaluated against the nth row in an ordered partition
PERCENT_RANK
Return the relative rank of the current row
RANK
Rank the current row within its partition with gaps
ROW_NUMBER
Number the current row within its partition starting from 1.
SQL Server
In SQL Server, analytic functions can return multiple rows for each group of rows that the function is performed on. They can be used to compute moving averages, running totals, percentages or top-N results within a group.
SQL Server supports the following analytic functions:
Function
Description
CUME_DIST (Transact-SQL)
Calculate the cumulative distribution of a value within a group
FIRST_VALUE (Transact-SQL)
Return the first value in an ordered set of values
LAG (Transact-SQL)
Return value of a previous row to compare values without requiring a self-join
LAST_VALUE (Transact-SQL)
Return the last value in an ordered set of values
LEAD (Transact-SQL)
Return value of a subsequent row to compare values without requiring a self-join
PERCENTILE_CONT (Transact-SQL)
Calculate a percentile based on continuous distribution of column values
PERCENTILE_DISC (Transact-SQL)
Calculate a percentile based on discrete distribution of column values
PERCENT_RANK (Transact-SQL)
Calculate relative rank of a row within group
What are the dynamic action differences between PostgreSQL and SQL Server? Compare dynamic actions in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL does not support dynamic actions. Most similar operations can be achieved using select statements.
SQL Server
SQL Server does not support dynamic actions. Some similar operations can be achieved using stored procedures.
What are the administration and GUI tools differences between PostgreSQL and SQL server? Compare the administration with GUI tools in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL can be administered through a GUI using Oracle’s SQL Developer, pgAdmin, OmniDB, DBeaver, and Postgres Enterprise Manager. Other GUI tools used for monitoring health and performance include Nagios, Zabbix, Cacti and EDB Postgres. SQLECTRON is a cross-platform option that is free and open source; it is compatible with a number of SQL databases including SQL Server.
SQL Server
SQL Server can be administered through a GUI on Windows using SQL Server Management Studio (SSMS), which is free. SQL Operations Studio is a free, opensource, cross-platform GUI for Mac. SQLECTRON is a cross-platform option that is free and open source; it is compatible with a number of SQL databases including PostgreSQL.
What are the performance differences between PostgreSQL and SQL server? Compare the performance of PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL offers speed and performance across data sets of all sizes, and it regularly outperforms other databases in both online transaction processing (OLTP) and online analytical processing (OLAP) speeds. It offers multi-version concurrency control (MVCC), allowing multiple transactions to be processed simultaneously, with fewer deadlocks than SQL Server. PostgreSQL offers many tools and parameters that allow users to monitor and optimize database performance.
SQL Server
SQL Server prides itself in the speed of its analytical and transaction processing. However, because the SQL Server user agreement prohibits the publication of benchmark testing without Microsoft’s prior written approval, head-to-head comparisons with other database systems are rare. Among features SQL Server highlights for optimizing performance and speed is its In-Memory OLTP, which takes advantage of in-memory data tables that perform better than writing directly to disk. The SQL Server Standard edition has some performance limitations for memory, partitioning, indexing, and other functionalities that require upgrading to the Enterprise version.
What are the concurrency differences between PostgreSQL and SQL Server? Compare concurrency in PostgreSQL vs. MSSQL.
PostgreSQL
PostgreSQL has well-developed multi-version concurrency control (MVCC) for handling multiple procedures at one time. MVCC provides snapshots of database info to avoid showing inconsistencies caused by simultaneous transactions or locking of data that occurs in other database systems. It uses Serializable Snapshot Isolation (SSI) to guarantee transaction isolation.
SQL Server
SQL Server has a less fully developed multi-version concurrency control system and by default relies on locking of data to prevent errors from simultaneous transactions. It offers an optimistic concurrency feature, which assumes that such conflicts only rarely appear; instead of locking a row, it is checked against a cached version to detect if any change has occurred.
What are the adoption differences between PostgreSQL and SQL server? Compare the adoption in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL is the world’s most advanced open source database. Businesses around the world are using PostgreSQL for mission critical workloads. The PostgreSQL community and a few companies such as EnterpriseDB and 2ndQuadrant are making sure that PostgreSQL adoption continues to expand on a global level.
SQL Server
SQL Server is popular with enterprises that rely on Microsoft products. It saw an increase in market share over the past two decades as Microsoft pushed it with its Windows Servers. But with more and more enterprises making a shift to Opensource in recent years, the popularity curve of SQL server is becoming more and more flat.
What are the environment and stack differences between PostgreSQL and SQL server? Compare the environment and stack in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL is popular with the LAPP stack (Linux, Apache, PostgreSQL, and PHP/Python. The LAPP stack is growing in popularity; large-platform service providers like Amazon and VMware provide services with readily installed LAPP stack modules.
SQL Server
SQL Server is a popular component of the Microsoft stack. It consists of Microsoft technologies like Microsoft WPF, ASP.NET, SharePoint, and Office 365.
What are the differences in scheduling tasks between PostgreSQL and SQL Server? Compare the scheduling tasks in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL doesn’t provide a built-in job scheduler like other SQL databases do. Recurring tasks require external tools like pgAgent, cron, or pg_cron on Linux, and Task Scheduler or SQLBackupAndFTP on Windows.
SQL Server
Tasks in the SQL Server can be scheduled using SQL Server Management Studio.
What are the differences about data redaction between PostgreSQL and SQL Server? Compare the data redaction in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL does not support data redaction for limiting the display of sensitive data for certain users. Enterprises looking for data redaction features to add more security can use EDB Postgres Advanced server, Oracle compatible fork of PostgreSQL.
SQL Server
SQL Server offers dynamic data masking (DDM). DDM limits sensitive data exposure by hiding it from users without the proper privileges. It complements other SQL Server security features like auditing, encryption, and row level security.
What are the different limitations between PostgreSQL and SQL Server? Compare the limitations of PostgreSQL vs. MSSQL
PostgreSQL
In addition to SQL and PL/pgSQL, PostgreSQL supports includes the procedural languages PL/Tcl, PL/Perl, and PL/Python in its distribution, and supports the external procedural languages PL/Java, PL/Lua, PL/R, PL/sh (Unix shell), and PL/JavaScript. It also supports user-defined functions in C-languages. It can be hosted on a wide range of server operating systems, including Linux, Mac, Windows, BSD, and Solaris. It can be deployed on Docker containers or Kubernetes.
SQL Server
In addition to T-SQL, SQL Server supports languages that are compatible with the Microsoft .NET framework, including C#, Java, PHP, and Python. SQL Server must run on Linux or Windows operating systems. It can be deployed on Docker containers and on Kubernetes with Microsoft’s Azure Kubernetes Services.
What are the access method differences between PostgreSQL and SQL server? Compare the access methods in PostgreSQL vs. MSSQL
PostgreSQL
PostgreSQL is compatible with the following access methods, protocols, and APIs for gaining access to its data: ADO.NET, JDBC, ODBC, and the native C library. It also supports a streaming API for binary large objects (BLOBs).
SQL Server
SQL Server is compatible with the following access methods, protocols, and APIs for gaining access to its data: ADO.NET, JDBC, ODBC, OLE DB, and TDS.
What are the bulk collect and binds differences between PostgreSQL and SQL server? Compare the bulk collect and binds in PostgreSQL vs. MSSQL
Bulk Collect
PostgreSQL does not have syntax for bulk collect, nor any close functional equivalent. Instead, you can create a temporary table with PL/PgSQL code or use a common table expression (CTE, or WITH query), if working within a single SQL statement.
There is no syntax for bulk collect in SQL Server. One alternative is to use a temporary table and a cursor.
Binds
PostgreSQL does not support bind variables, unlike some other relational databases like Oracle. Instead, PostgreSQL uses the PREPARE statement to achieve similar results.
SQL Server supports bind variables. Each parameter marker in an SQL statement must be bound to a variable before the statement can be executed using the SQLBindParameter function. Parameters can also be bound to arrays of program variables to process an SQL statement in batches. SQL Server also supports defining names for stored procedure parameters.
What are the differences about synonyms between PostgreSQL and SQL server? Compare the synonyms in PostgreSQL vs. MSSQL
SQL Server supports synonyms. Synonyms provide a layer of abstraction that protects a client application from changes made to base objects. A synonym belongs to a schema, and like other objects in a schema, its name must be unique. Binding is by name only; if a base object is modified, dropped, or replaced, the missing reference will only be found at run-time. PostgreSQL does not support synonyms.
TablePlus
PostgreSQL vs SQL Server — A quick database comparison
PostgreSQL and SQL Server are two of the most popular and widely used relational database management systems to date. In this post, we are going to quickly compare some of the features from the two database systems and see the differences between them.
Model
- PostgreSQL: Free and open source relational database management system, maintained by PostgreSQL Global Development Group and its prolific community.
- SQL Server: Commercial database management system, built and maintained by Microsoft.
Supported server operating systems:
- PostgreSQL seems to be more universal. It is widely available on multiple operating systems: FreeBSD, HP-UX, Linux, NetBSD, OpenBSD, OS X, Solaris, Unix, Windows.
- SQL Server is limited to Windows, and recently Linux.
Stored Procedure
- PostgreSQL has user-defined functions in proprietary language PL/pgSQL or with common languages like Perl, Python, Tcl etc.
- SQL Server uses Transact SQL and .NET languages.
Partitioning methods
- PostgreSQL: declarative partitioning (by range or by list) since PostgreSQL 10.0.
- SQL Server: tables can be distributed across several files (horizontal partitioning); sharding through federation.
Concurrency
- PostgreSQL has a better concurrency management system. It handles very well the case where multiple processes can access and modify shared data at the same time.
- On the other hand, SQL Server has underdeveloped concurrency and you can easily get various locked, blocked, and deadlocked reports in the log. Thus the performance of the database, as well as the application, will be slower.
Scalability
The scalability feature is directly dependent on the ability of compression of the data.
- MS SQL Server offers compression out of the box but you have to implement it manually.
- On the other hand, PostgreSQL offers it for free and the entire process is automatic.
Scriptability
- PostgreSQL can be driven entirely from the command line.
- MS SQL Server is more driven through a GUI.
Replication
SQL Server supports snapshot replication, transactional replication, merge replication. PostgreSQL uses master-slave replication.
In-memory capabilities
SQL Server has it while Postgres doesn’t.
Ease of installing and updating
- Installing MS SQL Server is slow. It involves immense downloads and lengthy processes.
- Install PostgreSQL is sometimes as easy as typing a single command, like this:
Updating
- SQL Server releases a new version after a few years.
- PostgreSQL releases updated version regularly.
But SSMS is better than PGAdmin though!
Need a good GUI Tool for PostgreSQL or SQL Server? Try TablePlus. It’s is a modern, native GUI that allows you to simultaneously manage multiple databases such as MySQL, MariaDB, PostgreSQL, SQLite, Microsoft SQL Server, CockroachDB… in a fast and easy way.