Что лучше postgresql или ms sql

PostgreSQL vs SQL Server: 16 Critical Differences

With a vast assortment of databases to choose from in the market, users can often mull over PostgreSQL vs SQL Server to iron out the better option for their use case. Organizations that leverage PostgreSQL for their operations might want to switch to a database like Microsoft SQL Server since it primarily caters to different data warehousing solutions, ecommerce, and other lines of business. Historically, Microsoft SQL Server has been a favorite for organizations that depend on other Microsoft products, but PostgreSQL has made rapid strides to rise to the top of the niche not only because of the benefits of going open-source but also for its active community of users and handy features. This is what brings us to this discussion. Comparing PostgreSQL and SQL Server will help you better understand the possible advantages and trade-offs of the two systems, and which one is better suited for your purpose. Let’s dig in!

What Is PostgreSQL?

PostgreSQL has established itself as an enterprise-class, advanced open-source database that supports both JSON (non-relational) and SQL (relational) querying. This powerful and stable database management system has been backed by over thirty years of active community development that has contributed to its reputation for integrity, reliability, resilience, performance, and correctness. PostgreSQL is used as the main data warehouse or data store for various mobile, web, analytics, and geospatial applications. PostgreSQL also boasts a rich history of supporting advanced data types along with performance optimization commonly found across its commercial database counterparts, like Microsoft SQL Server and Oracle. On top of being free and open-source, PostgreSQL is also highly extensible. For instance, you can generate custom functions, define your data types, and even write code from various programming languages without having to recompile your database!

History

• Ingres was first developed in 1977.
• Michael Stonebraker and his associates developed Postgres in 1986.
• In 1990, support for PL/ pgSQL and ACID compliance was added to PostgreSQL.
• NYCPUG (New York City PostgreSQL User Group) joined the PgUS (United States PostgreSQL Association) back in 2013.
• In 2014, the PGconf ushered in a new era for PostgreSQL users.

Main Features

Now, let’s move on to take a look at some features of PostgreSQL that make it such an indispensable tool in the marketplace:

• Standards compliance and reliability: PostgreSQL’s write-ahead logging allows it to stand out as a highly fault-tolerant database. PostgreSQL is also ACID-compliant and provides full support for views, foreign keys, triggers, joins, and stored procedures, in various languages. It includes most SQL: 2008 data types, including BOOLEAN, NUMERIC, INTEGER, DATE, VARCHAR, TIMESTAMP, INTERVAL, and CHAR.
• Robust extensions: PostgreSQL houses robust feature sets such as point-in-time recovery, multi-version concurrency (MVCC), tablespaces, granular access controls, and online/hot backups. PostgreSQL is also locale-aware for case sensitivity, sorting, and formatting. It is highly scalable in both the quantity of data it can manage and in the number of simultaneous users that can be accommodated.
• Open-source license: You can avail of the PostgreSQL source code under an open-source license, giving you the freedom to modify, use, and implement it as you see fit, without any charge. On top of this, PostgreSQL incurs no licensing cost, which gets rid of the risk of over-deployment. PostgreSQL’s community of enthusiasts and contributors regularly find fixes and bugs, lending to the overall security of the database system.

Use Cases

PostgreSQL’s versatility allows it to be leveraged in a vast range of use cases like:

• Federated hub database: PostgreSQL’s JSON support and foreign data wrappers allow it to link with other data stores — including NoSQL data types — and serve as a federated hub for polyglot database systems.
• General-purpose OLTP database: Large enterprises and startups both use PostgreSQL as the main data store to support their internet-scale applications, products, and solutions.
• Geospatial database: PostgreSQL supports geographic objects when used with the PostGIS extension. It can also be used as a geospatial data store for geographic information systems (GIS) and location-based services.
• LAPP open-source stack: PostgreSQL can also run dynamic apps and websites as part of a robust alternative to the LAMP stack. LAPP stands for Linux, Apache, PostgreSQL, Perl, PHP, and Python.

SQL Server logo (Image Source: Software Engineering Stack Exchange)

SQL Server was developed by Microsoft as a relational database management system that boasts a long history, thirty-two years, to be precise. Microsoft SQL Server is considered a software product with the main function of retrieving and collating data as requested by other software applications.

These applications might either run on a different computer across a network or on the same computer. Microsoft SQL Server has seen a lot of updates over the years, to become one of the best supported and one of the most reputed RDBMSs in the market today.

SQL Server owes a large chunk of its popularity to its maker, Microsoft. When MS SQL Server was in its nascent stage, Microsoft had already established itself as a tech giant.

SQL Server has managed to make a mark as a database management system with its wide range of applications and tools that simplify working with data. Its comprehensive graphical user interface (GUI) allows intuitive and easy work with the database while allowing you to generate statistics for your reports.

History

Here is a brief overview of Microsoft SQL Server over the years:

• The evolution of SQL Server began in 1988 when Microsoft collaborated with Sybase and Ashton-Tate to develop database maintenance and creation software that would provide an impetus to Microsoft’s business database market.
• SQL Server 1.0 was rolled out in 1989. Back then, it leveraged the system administrator facility (SAF) to generate databases. It didn’t have any documentation, but it still allowed users to run SQL queries and set parameters. The first code snippet for Microsoft SQL Server was penned by Sybase.
• Gradually, newer versions were released with more improvements and features. SQL Server 2019, or Aries is the latest addition to a pantheon of comprehensive versions as it focuses on making the database features even more intuitive to use. This includes big data cluster options, giving users the choice to work with giant data sets.

Main Features

Enough history talk. Let’s take a look at a couple of pivotal features that make SQL Server such a great catch:

• Robust security platform: SQL Server allows you to protect your data in motion and at rest with built-in features for data protection, data classification, alerts, and monitoring. With SQL Server, you can easily encrypt sensitive data and perform rich computations on encrypted data, and allow customized role-based data access complete with complex row filtering.
• Industry-leading performance: SQL Server boasts record-breaking performance on Linux and Windows as it consistently leads across TPC-H data warehousing workload, TPC-E OLTP workload, and real-world application performance benchmarks. You can also use SQL Server’s in-memory database capabilities such as memory-optimized tempdb and persistent memory support to improve performance for your mission-critical workloads.
• Intelligence across all your data with big data clusters: SQL Server allows you to gain valuable insights from all your data by querying data across your entire data estate — Azure SQL Database, SQL Server, Teradata, MongoDB, Azure Cosmos DB, and many more — without having to replicate or move data. You can even build a shared data lake by combining both unstructured and structured data in SQL Server and accessing the data either through Spark or T-SQL.

Use Cases

SQL Server’s extensibility and performance allow it to be leveraged in a vast range of use cases like:

• Replication services: SQL Server replication services are utilized by SQL Server to harmonize and replicate database objects, either as a subset of the objects present or in their entirety. Replication services adhere to a subscriber/publisher model, i.e. the modifications are sent out by one database server (publisher) and are collected by others (subscribers).
• Notification services: Notification services were originally released as a post-release add-on for SQL Server 2000. It is a mechanism for creating data-driven modifications, which are then sent to the notification services subscribers.
• Machine learning services: SQL Server machine learning services operate within the SQL Server instance, letting people perform data analytics and machine learning without having to move data across the network or be hindered by the memory of their computers.
• Analysis Services: SQL Server analysis services (SSAS) adds data mining and OLAP capabilities for SQL Server databases. The OLAP engine offers support for relational online analytical processing (ROLAP), multidimensional online analytical processing (MOLAP), and hybrid online analytical processing (HOLAP) storage modes for data. SQL Server analysis services also support XML for the analysis standard as the fundamental communication protocol.

PostgreSQL vs SQL Server: Head-to-Head Comparison

Now that we have an idea about the salient aspects of SQL Server and PostgreSQL, let’s dig into the differences between the two. You can use the factors mentioned below to identify which database management system suits your needs best.

Availability

PostgreSQL offers a wide assortment of solutions to ensure high availability for users, including write-ahead log shipping, shared-disk failover, data partitioning, and various replication methods. Tools like EDB Postgres Failover Manager provide automatic failover to ensure high availability by monitoring for and identifying database failures.

On the other hand, SQL Server includes various high availability tools such as log shipping, failover clusters, and replication. SQL Server’s availability groups that work round the clock provide automatic failover when specific conditions are met. However, this offering can only be accessed in SQL Server’s enterprise edition.

Data & Table Structure

PostgreSQL provides the PL/pgSQL procedural programming language to its users for simplicity. Additional functionalities to standard SQL in PostgreSQL include user-defined types, custom modules, extensions, JSON support, and extra options for triggers and other functionalities.

SQL Server uses T-SQL, which bears resemblance to standard SQL. T-SQL includes additional support for data and string processing, procedural programming, and local variables.

If you want to better understand how the systems cache and process requests, PostgreSQL isolates processes by treating them as separate OS processes. Each database has a separate memory and runs its process. This makes monitoring and management very easy, but at the same time, it makes scaling multiple databases more difficult.

SQL Server uses a buffer pool that can be limited or increased based on processing needs. All the work is carried out in a single pool, with no multiple pages, unlike PostgreSQL.

Both PostgreSQL and SQL Server provide support for temporary tables since it allows you to store intermediate results from branched complex logic and complex procedures. Temporary tables can help improve database organization and performance by isolating intermediary information from the essential as well.

Defragmentation

When developers modify different parts of an SQL database, the modifications take place at different points of the system and can be hard to track, read, and manage. Therefore, maintenance should also involve defragmentation — the process of collating the updated database by assigning indexes, generating new pages, and revisiting the structure. The databases can then free up the disk space that’s not utilized properly so that a database can run at a faster pace.

PostgreSQL scans the tables of a data layer to look for empty rows and gets rid of unnecessary elements. By doing so, the system liberates the disk space. However, this method needs a lot of CPU and can impact the application’s performance.

On the other hand, SQL Server provides an efficient garbage collector that doesn’t generate more than 15-20% of overhead. Technically, developers can also run garbage collectors continuously, because it is that effective. To summarize, SQL Server offers more defragmentation methods than PostgreSQL.

Indexes

The way a database tackles indexes is a testament to its usability because indexes are used to pinpoint data without looking for a particular row. You can also use indexes to refer to multiple columns or rows. You can assign the same index to files, present them in different places in the database, and collect all these pieces with a single search.

PostgreSQL supports index-based table organization, but the early versions didn’t use automatic index updates. It also allows you to look up many indexes in a single search, which means that you can discover a lot of information.

SQL Server provides rich automated functionality for index management. They can be organized in clusters and sustain the proper row order without manual involvement. SQL Server also supports partial indexes and multiple-index searches.

Features

PostgreSQL doesn’t offer a built-in job scheduler, unlike other SQL databases. Repetitive tasks need external tools like cron, pgAgent, or pg_cron on Linux, and SQLBackupAndFTP or Task Scheduler on Windows.

Tasks in SQL Server, on the other hand, can be easily scheduled via SQL Server Management Studio.

PostgreSQL has well-developed multi-version concurrency control (MVCC) to tackle multiple procedures at the same time. MVCC offers snapshots of database information to avoid displaying inconsistencies caused by simultaneous transactions or locking of data that takes place in other database systems. PostgreSQL leverages serializable snapshot isolation (SSI) to ensure transaction isolation.

SQL Server has a less developed multi-version concurrency control system and depends on locking of data to avoid errors from simultaneous transactions, by default. SQL Server also offers an optimistic concurrency feature, which assumes that such issues occur rarely. So, as opposed to locking a row, it’s verified against a cached version to find if any change has taken place.

While both sharding and partitioning are essentially about breaking a large dataset into smaller subsets, sharding implies that the data is spread across multiple computers while partitioning doesn’t.

From version 10.0, PostgreSQL supports declarative partitioning — partitioning by range, list, or hash.

MS SQL Server supports horizontal partitioning — splitting a table with many rows into several tables with lesser rows.

MS SQL Server also supports sharding through federation. “Federated partitioned views” are views where tables are spread across different servers to balance the processing load.

To retrieve the records from the servers, you need certain commands. These commands are called distributed partitioned views. They use typical SQL statements, along with the keyword UNION, to draw data from all the distributed servers.

Likewise, DML statements (INSERT, UPDATE, and DELETE) can be utilized when specific rules are observed on the underlying tables. Also, note that federated partitioned views are only supported on enterprise editions.

Though federated partitioned views can be implemented on any other edition as there’s no distinguishing syntax for them, they won’t be recognized as federated partitioned views. The rules to recognize the view as partitioned across servers are only available with enterprise editions.

Using this technique of partitioning, there’s usually an increase in performance by 20% to 30% in most applications. Hence, it’s a very useful tool if your business manages a lot of data.

Replication

While partitioning is splitting the database into smaller subsets and distributing the partitioned tables into different nodes, replication is copying the database across multiple databases to provide a quick look and less response time.

PostgreSQL offers primary-secondary replication. This can be asynchronous or synchronous. Write-ahead logs (WALs) allow for sharing changes with the replica nodes, hence enabling asynchronous replication.

The other kinds of replications mainly include logical replication, streaming replication, and physical replication.

• Logical replication follows a publish and subscribe model. Changes are based on the identity of the data replication, like its primary key, rather than its physical location.
• Streaming replication essentially streams the WALs as soon as the file is created, thus enabling standby servers to be quickly updated, instead of waiting for the file to be filled.
• Lastly, physical replication is usually implemented with files and directories, with no regard for the contents within the physical location. PostgreSQL doesn’t offer multi-primary replication natively, but it can be executed with the help of other third-party tools.

SQL Server replication duplicates data from a publisher server to a subscriber. It can be asynchronous or asynchronous depending on the SQL Server edition. It offers three types of replications, namely: transactional replication, snapshot replication, and merge replication.

• Transactional replication is typically implemented for server-to-server environments, where modifications are delivered from the publisher to the subscriber as they happen.
• Merge replication is usually implemented in situations where conflicts might occur, for server-to-client environments, or where data can be changed and tracked on either the subscriber or publisher and is subsequently synchronized.
• Snapshot replication is implemented when data is updated infrequently, doesn’t need to be altered incrementally, or where data is duplicated exactly as it appears at a specific moment. Furthermore, the enterprise edition offers peer-to-peer replication as an alternative solution to multi-primary node replication.

Language & Syntax

PostgreSQL is written in C language, and MS SQL is written in C and C++. In terms of language binding, PostgreSQL is very easy to use and connect to because of its external API libpq, which is very well-designed and documented.

However, SQL Server external language bindings may depend on several other factors. You may need to install extra drivers or create classes to store the data that’s queried; thus you’d have to know what the data looks like at compile time. You would probably have to refer to the documentation, and it could be rather time-consuming to follow up.

When it comes to procedural language features, both PostgreSQL and SQL Server provide robust support. PostgreSQL supports the JSON data type and users can easily use Python, Java, PHP, Perl, and R with SQL as they are backed by the procedural language feature.

While SQL Server does provide support, this feature is yet to be improved, as there are slight bugs that occur, and it may take some time to implement as it’s slow. The user needs to compile the code into a .dll file first.

In PostgreSQL, there’s no need to create a .dll file first. PostgreSQL also provides a huge number of regular expressions (regex) as a foundation for analytical work.

MS SQL Server comparatively has lesser regex and supports certain commands like substring, and pattern index, which may not be as good as PostgreSQL.

Performance

When it comes to performance, PostgreSQL trumps SQL Server in several ways. We touched upon partitioning, and while both PostgreSQL and SQL Server offer partitioning, PostgreSQL offers it for free, with more efficiency.

PostgreSQL also offers better concurrency, which is an important feature where multiple processes can access and alter shared data at the same time. The MVCC characteristic of PostgreSQL ensures a lesser chance of deadlock, only blocking if two queries try to modify the same row at the same time and serialize the updates made to that row.

The MVCC lock acquired for querying data doesn’t conflict with the locks acquired for writing data. This minimizes lock contention and provides better performance in multiuser environments.

On the other hand, SQL Server has an underdeveloped concurrency, and some processes could even be deadlocked. As opposed to the MVCC feature, whenever a row is updated, a new version of the row is created instead of overwriting the same row and both are maintained. Gradually, the older versions move into a system database called tempdb. However, its concurrency has a long way to go.

PostgreSQL also offers indexing support for several extensions, which boosts the performance of the database.

On the other hand, SQL servers are yet to improve on their indexing implementation, they are yet to include arrays — one of the most used variable types.

Pricing

PostgreSQL was released under the PostgreSQL License, a liberal open-source license. The PostgreSQL Global Development Group remains committed to making PostgreSQL available as free and open-source software forever. There are no plans to alter or release PostgreSQL under a different license.

MS SQL Server was released under a commercial license as a part of Microsoft products. In early 2016, the database was made available as a free tool for developers, but it only supports one processor and 1GB of maximum memory. While it’s free, it lacks several features that you may need for a business. You may need to pay $899 per server if you require more servers. Lately, the SQL Server enterprise edition costs $13,748.

Scalability

The ability of the database system to continue to function well when data is increased to meet a user’s need without compromising on its performance is called scalability.

PostgreSQL offers many features in terms of scalability and can employ several CPU cores to parallelly implement queries quickly.

SQL Server can also use cores, however, the standard version is limited to twenty-four cores of CPU. The enterprise version allows for the usage of unlimited CPU cores. SQL Server also possesses a hyper-scale feature, where you can determine the lower and upper limits allowing you to scale downwards and upwards as per the requirements.

Security

With the rise of data theft, hacking, and piracy, security does indeed become the most important requirement in database systems. However, both SQL Server and PostgreSQL provide excellent data encryption and authentication.

Authentication Methods

On the server-side, PostgreSQL offers advanced authentication methods including lightweight directory access protocol (LDAP) and pluggable authentication module (PAM), which potentially reduce the attack surface of the PostgreSQL database servers. Other server-level security enhancements to PostgreSQL include PostgreSQL server listen address, host-based authentication, and certificate authentication.

In MS SQL Server, there are two server-level security enhancement features: Windows authentication mode and mixed-mode which includes an authentication process by both Windows Server and MS SQL Server. The security model of MS SQL Server is a tight integration between the Windows authentication mode of Windows Server and the database.

Data Encryption

PostgreSQL provides data encryption and allows you to use secure sockets layer (SSL) certificates when your data is traveling through the web or public network highways. It also allows you to implement client certificate authentication tools as an option. In addition, you can use cryptogenic functions to store encrypted data in PostgreSQL that support both symmetric-key and public-key encryptions.

In MS SQL Server, the available data encryption features include transparent data encryption (TDE), always encrypted, and column-level encryption. TDE uses the advanced encryption standard (AES) algorithm for encrypting physical files, which include both data and log files. The always encrypted feature allows you to encrypt certain columns in both states, at rest or in motion (i.e. the data remains encrypted in memory as well).

User-Level Privileges

Further, you can manage different users along with their permissions (read, write) in both PostgreSQL and SQL Server.

PostgreSQL includes user-level privileges as role assignments, table-level privileges via roles, and role inheritance. The auditing option allows you to review users’ and groups’ data access activities in your database, which provides a layer of extra security.

SQL Server achieves this via user groups and roles. Resource permissions are granted directly to the user account, and permissions are inherited from a parent resource.

You can also identify concurrency issues, long-running queries, and regular workload metrics by monitoring and auditing activities in SQL Server.

Storage

Storage is one of the key factors of any database system’s performance. With the rise of the servers’ processing power and large-scale memory support, it becomes almost essential for the databases to allow for more storage capabilities in the system.

PostgreSQL is an object-relational database, while Microsoft SQL Server is a relational database system. This means PostgreSQL offers more complex data types and allows object inheritance, though it also makes working with PostgreSQL more complex. It has a single ACID-compliant storage engine and initializes a new system process with its memory allocation for each client connection. Hence, with the increase of client connections on systems, more memory needs to be allocated.

SQL Server 2016 and above can use a maximum of a hundred computers or virtual machines with a maximum of five instances running per computer. However, this too depends on its edition. The enterprise edition allows for maximum bandwidth, hence more storage capabilities would be granted. While the latest standard edition allows for use of up to 128GB of memory, you can use unlimited memory in the enterprise version.

Support & Community

PostgreSQL releases updated versions regularly for free. Recently, The PostgreSQL Global Development Group has released an update to all supported versions of the database system, fixing over 55 bugs reported over the last three months. PostgreSQL boasts a vast community of developers, third-party companies, and enthusiasts that provide support and also try to develop the system by fixing the reported bugs.

SQL Server releases a new version every few years. The support costs depend on the terms and conditions of the license. Microsoft SQL Server also has a support community, where database analysts, developers, system administrators, or anyone interested in the platform can ask questions, or learn more about SQL Server through podcasts and webcasts like the SQL Server Radio with Guy Glantser and Eitan Blumin, where users get to know a lot regarding the SQL Server and its compatibility with other Microsoft tools.

Both PostgreSQL and SQL Server can be equipped with plugins. The pricing and compatibility depend on the plugin. Plugins can be helpful to manage, clean, back up your database, or more.

For example, Adminer is a data management tool and can be used to manage data in both PostgreSQL and MS SQL Server. Several other database plugins can be used to improve the efficiency of your database as well namely: WP-Optimize, Better Search Replace, WP Database Backup, to name a few. When you upgrade any database system, there might be data migration costs, but this is normal for any other standard DBMS.

Triggers & Events

PostgreSQL has various advanced triggers you can choose from depending on your use case. Supported triggering events are AFTER, BEFORE, and INSTEAD OF, and they can be used for INSERT, UPDATE, and DELETE events to manipulate data. As mentioned before, PostgreSQL can run these triggers dynamically and doesn’t need to compile them into a .dll file before execution. The above functions can be used to execute a complex query when the trigger gets invoked.

SQL Server offers various triggers for different types of database events, namely DML triggers, DDL triggers, and logon triggers:

• DML triggers or data manipulation language triggers are triggers used to manipulate data, by inserting, updating, or deleting records.
• DDL triggers are for data definition language (DDL) events, like creating, dropping, or altering a database.
• Logon triggers are used for logon events, like 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.

Views

Views are essentially virtual tables that don’t store data physically. They are generally used for security purposes to limit user access to data. Both PostgreSQL and SQL Server support updatable views.

However, in PostgreSQL, the updates don’t occur automatically unless the following prerequisites are satisfied:

• There should be a section in the FROM clause in the query of that view. The section can be from a table or another updatable view.
• There should be no window functions, aggregate functions, or set-returning functions in the selection list.
• The query mustn’t include the commands HAVING, LIMIT, DISTINCT, WITH, INTERSECT, EXCEPT, OFFSET or LIMIT at the top level.

In short, the views created with simple queries can be updated whereas it’s almost impossible to update the ones created with complex queries. On the other hand, complex views can be updated by using rules. While PostgreSQL may not provide the facility for running materialized views, it has a module called matviews, which can help in rebuilding any materialized view.

In SQL Server, views can be automatically updated, and both user-defined and system-defined views are supported. Moreover, two table views are updated at the same time if they have different keys and the update statement doesn’t involve more than one table.

Additionally, the user can utilize triggers to update complex views. SQL Server also provides facilities to run materialized views also known as indexed views. Unlike materialized views in other relational databases, indexed views are synced to the underlying data and are thus updated automatically.

Shortcomings of SQL Server and PostgreSQL

While we’ve discussed all the details on PostgreSQL and SQL Server, both have their drawbacks.

While PostgreSQL is free, it isn’t owned by a single organization. Because of that, it has struggled to find footing among the masses, despite being heavily featured. PostgreSQL also focuses on compatibility more than speed. Thus, modifications that are created for speed improvement demand more work.

On the other hand, SQL Server has often been criticized for its poor user interface. It has complex performance tuning features and no native support for source control. If you’re using it for your company, the enterprise version may burn a hole in your pocket. SQL Server 2019 enterprise edition alone costs $13,748 — that’s like thirteen rooms worth of Ikea furniture! Moreover, the licensing can be quite challenging to comprehend and is continuously changing.

If PostgreSQL and SQL Server aren’t quite to your liking, you can try out MongoDB or MariaDB for your unique use case.

MongoDB is a document-oriented, free-to-use, cross-platform database program that can easily leverage JSON-like documents.

MariaDB, on the other hand, is a commercially-supported fork of MySQL whose pluggable and purpose-built storage engines support workloads that generally required a vast variety of different databases.

PostgreSQL vs SQL Server: Which Database Should You Choose?

Both PostgreSQL and SQL Server are widely used relational databases, but who takes the cake? From the above comparisons, PostgreSQL trumps SQL Server in several scenarios. Not only is it open-source and free, but it also has several features that are easily available and can be implemented automatically, unlike Microsoft SQL Server.

Moreover, PostgreSQL has a more suitable concurrency management system. It can brilliantly handle cases where multiple processes can access and modify shared data at the same time.

If you are running a small business, PostgreSQL could be a suitable choice for you as it’s free and offers several features that are useful to manage the data. It’s easy to install and can be implemented in almost all kinds of operating systems. However, for businesses with a huge investment in the Microsoft SQL Server stack, SQL Server has its benefits over PostgreSQL.
Comparing PostgreSQL and SQL Server will help you understand the advantages of the two systems, and which one is best suited for you.�� Let’s dive in! ✅ Click to Tweet

Summary

All in all, both PostgreSQL and SQL Server are functional and multifaceted databases. While PostgreSQL can be used for almost any operating system and is suitable for small businesses that require maximum functionality, SQL Server is the best for huge businesses, especially those that require the use of Microsoft products.

In this article, we’ve covered the major differences between PostgreSQL and SQL Server, and their functions. The “right” choice will eventually come down to how you plan to run your business.

Between PostgreSQL vs SQL Server, which database would you plan on using for your next project, and why? We’d love to hear your thoughts! Share them in the comment section below.

Get all your applications, databases, and WordPress sites online and under one roof. Our feature-packed, high-performance cloud platform includes:

• Easy setup and management in the MyKinsta dashboard
• 24/7 expert support
• The best Google Cloud Platform hardware and network, powered by Kubernetes for maximum scalability
• An enterprise-level Cloudflare integration for speed and security
• Global audience reach with up to 35 data centers and 260 PoPs worldwide

Get started with a free trial of our Application Hosting or Database Hosting. Explore our plans or talk to sales to find your best fit.

Salman Ravoof

• Website
• LinkedIn
• Twitter

PostgreSQL против SQL Server – все, что вам нужно знать

Практически невозможно говорить об управлении данными, не упомянув PostgreSQL и SQL Server. Являясь двумя наиболее популярными технологиями систем управления реляционными базами данных (СУБД), они имеют свои уникальные сильные и слабые стороны. Если вам интересно, чем PostgreSQL и SQL Server отличаются друг от друга и как их можно использовать вместе, то вы попали по адресу.

Мы рассмотрим все эти темы и многое другое в этой статье. Мы объясним, что делает каждая технология, обсудим различия между ними и выясним, как Astera Centerprise интегрируется как с PostgreSQL, так и с SQL Server. К концу этой статьи вы должны лучше понимать обе технологии и знать, как извлечь из них максимальную пользу. Готовый? Давайте начнем!

Что такое PostgreSQL?

PostgreSQL — это платформа с открытым исходным кодом. объектно-ориентированная система баз данных. Это одна из самых популярных баз данных, используемых сегодня, с готовыми функциями и совместимостью с рядом языков программирования. PostgreSQL обладает широкими возможностями настройки, что позволяет пользователям определять свои собственные типы данных, функции и даже собственные операторы.

Возможности и производительность PostgreSQL:

• PostgreSQL хранит и обрабатывает данные быстро и эффективно, обрабатывая большие наборы данных без ущерба для скорости.
• Он подходит для сложных приложений и решений для обработки больших данных благодаря способности хранить и обрабатывать сложные запросы.
• PostgreSQL известен своей стабильностью, надежностью и стабильной производительностью, что делает его предпочтительным выбором для многих специалистов по базам данных.
• Активное сообщество разработчиков PostgreSQL с открытым исходным кодом предоставляет поддержку, ресурсы для устранения неполадок и постоянные улучшения для оптимизации производительности.
• PostgreSQL предлагает горизонтальную масштабируемость с помощью таких методов, как секционирование таблиц, сегментирование и потоковая репликация, гарантируя эффективную обработку больших наборов данных.
• Он оптимизирован по производительности, что позволяет быстро и эффективно извлекать и обрабатывать данные.

Что такое SQL-сервер?

SQL Server — это система управления реляционными базами данных (СУБД), разработанная Microsoft. Он предоставляет платформу корпоративного уровня для хранения, управления и анализа данных, которая также может похвастаться мощными возможностями аналитики и отчетности. С помощью SQL Server вы можете создавать мощные приложения и быстро получать доступ к своим данным, а также создавать надежные базы данных с помощью настраиваемых расширений и инструментов.

Возможности и производительность SQL Server:

• SQL Server позволяет создавать мощные приложения и обеспечивает быстрый доступ к данным.
• Он предлагает широкую совместимость с другими системами, позволяя подключаться к различным хранилищам структурированных данных локально и в облаке.
• SQL Server поддерживает сочетание традиционных источников данных (например, электронных таблиц Excel) со службами потоковой передачи в реальном времени.
• Он соответствует основным отраслевым стандартам, таким как SQL, ODBC и JDBC, что упрощает интеграцию с существующими приложениями и продуктами сторонних производителей.
• SQL Server предоставляет больше корпоративных функций для масштабирования и секционирования, что делает его подходящим для предприятий, работающих с большими объемами данных или нуждающихся в распределенном хранилище в разных географических точках.
• В нем особое внимание уделяется оптимизации производительности для обеспечения эффективной обработки и извлечения данных, что делает его хорошо подходящим для обработки растущих наборов данных и требований к высокой производительности.

Сравнение PostgreSQL и SQL Server

При сравнении PostgreSQL и SQL Server важно отметить следующие различия:

• Право собственности и лицензирование: PostgreSQL — это база данных с открытым исходным кодом, управляемая сообществом, а SQL Server — это собственная база данных, принадлежащая корпорации Microsoft.
• Операционные системы: PostgreSQL предназначен для работы в нескольких операционных системах, включая Linux, macOS и Windows, тогда как SQL Server в первую очередь предназначен для работы в Windows, но также может работать и в Linux.
• Стоимость: PostgreSQL можно использовать бесплатно, тогда как SQL Server требует лицензии и может оказаться дорогостоящим, особенно для крупных организаций.
• Масштабируемость: SQL Server имеет готовые пакеты, упрощающие масштабирование, в то время как PostgreSQL требует больше ручных усилий для масштабирования базы данных.
• Особенности: PostgreSQL предлагает более широкий набор функций, чем SQL Server, включая поддержку собственного типа данных JSON, наследование таблиц и многие другие расширенные функции. SQL Server, с другой стороны, предлагает функции, специфичные для продуктов Microsoft, такие как интеграция с Microsoft Office.
• Производительность: SQL Server обеспечивает более высокую производительность для определенных приложений благодаря своей более традиционной структуре, в то время как PostgreSQL известен своей производительностью и может обрабатывать большие объемы данных.
• Пользовательские типы данных: PostgreSQL поддерживает пользовательские тип данныхs, что делает его немного более гибким, чем SQL Server.
• Встроенные функции: PostgreSQL имеет больший набор встроенных функций по сравнению с SQL Server.
• Хранимые процедуры: PostgreSQL поддерживает хранимые процедуры, написанные на нескольких языках, тогда как SQL Server поддерживает хранимые процедуры, написанные только на T-SQL.

Однако между двумя базами данных есть и некоторые сходства, о которых стоит упомянуть.

И PostgreSQL, и SQL Server:

• Используйте триггеры для выполнения функций в ответ на событие внутри базы данных.
• Соединяйте таблицы через индексы, чтобы повысить производительность запросов.
• Предоставляет обширную библиотеку встроенных функций для манипулирования и анализа данных.
• Поддержка хранимых процедур для выполнения команд и запросов к базе данных.

PostgreSQL и SQL Server: некоторые плюсы и минусы

PostgreSQL	SQL Server
Плюсы
Открытый исходный код и поддержка сообщества	Надежная система управления базами данных корпоративного уровня (принадлежит Microsoft)
Высоко настраиваемый	Полная интеграция с продуктами и технологиями Microsoft.
Отличная масштабируемость и производительность	Хорошо документированы и доступны обширные ресурсы поддержки.
Широкая поддержка платформ	Повышенная производительность для некоторых приложений
Богатый набор функций с расширенными возможностями	Предлагает готовые пакеты и инструменты для упрощения масштабирования.
Минусы
Масштабирование требует больше ручных усилий	Требуется лицензия (может быть дорого для крупных организаций)
Ограниченная интеграция с продуктами Microsoft	В первую очередь разработан для работы в Windows с ограниченной поддержкой Linux.
Ресурсы документации и поддержки могут быть ограничены.	Ограниченная поддержка пользовательских типов данных по сравнению с PostgreSQL.
Функции корпоративного уровня могут быть не такими обширными	Функции могут быть в большей степени ориентированы на технологии, специфичные для Microsoft.
Варианты лицензирования и поддержки могут быть не стандартизированы.	Возможности настройки могут быть более ограниченными по сравнению с PostgreSQL.

PostgreSQL против SQL-сервера – что лучше?

Выбор лучшего сервера, PostgreSQL или SQL Server, зависит от конкретных требований и предпочтений. PostgreSQL имеет открытый исходный код и широкие возможности настройки, масштабируемость, расширенные функции и мощную поддержку сообщества. Для масштабирования может потребоваться больше усилий вручную, а интеграция с продуктами Microsoft ограничена.

SQL Server, как собственная база данных, предлагает бесшовную интеграцию с технологиями Microsoft, встроенные инструменты масштабируемости и обширную поддержку. Однако по сравнению с PostgreSQL он требует затрат на лицензирование и ограниченных возможностей настройки.

В конечном итоге выбор между PostgreSQL и SQL Server сводится к конкретным потребностям, предпочтениям и технической среде. Организациям следует тщательно оценить такие факторы, как бюджет, желаемые функции, требования к масштабируемости, совместимость платформ и наличие опыта, чтобы принять обоснованное решение о том, какой сервер лучше подходит для их конкретного случая использования.

Ниже приведены некоторые варианты использования серверов PostgreSQL и SQL:

Варианты использования PostgreSQL:

1. Веб-приложения: надежная, масштабируемая и многофункциональная база данных для веб-приложений.
2. Геопространственные приложения: расширенная поддержка геопространственных данных в ГИС и сервисах, основанных на местоположении.
3. Хранилище данных и бизнес-аналитика. Эффективно обрабатывайте большие наборы данных для хранения данных и бизнес-аналитики.
4. Системы управления контентом (CMS): Надежное и стабильное хранилище данных для платформ CMS.

Варианты использования SQL Server:

1. Корпоративные приложения: комплексные функции и масштабируемость для приложений корпоративного уровня.
2. Бизнес-приложения: интеграция с технологиями Microsoft для финансов, управления персоналом, управления запасами и CRM.
3. Анализ данных и отчетность: интегрированные инструменты бизнес-аналитики для анализа данных и составления отчетов.
4. Электронная коммерция и онлайн-торговля: транзакционные возможности для платформ электронной коммерции и онлайн-торговли с высоким трафиком.

Важно отметить, что это всего лишь несколько примеров, и как PostgreSQL, так и SQL Server могут использоваться в широком спектре приложений в зависимости от конкретных требований.

Использование PostgreSQL и SQL Server с Astera Centerprise

Astera Centerprise обеспечивает бесшовную интеграцию с различными базами данных, включая PostgreSQL и SQL Server. Он позволяет пользователям легко подключаться к своим источникам данных, извлекать данные, преобразовывать их и загружать в нужные целевые системы. Используя эти базы данных с открытым исходным кодом, пользователи могут повысить их экономичность, надежность и безопасность.

Одно из ключевых преимуществ Astera Centerprise это его подход к ETL без кода. Благодаря интерфейсу перетаскивания пользователи могут легко создавать задания ETL, визуально проектируя поток данных и преобразования, без написания кода. Это значительное преимущество для предприятий, у которых нет специального технического персонала или которым требуется более быстрый результат.

Astera Centerpriseподход без использования кода сокращает кривую обучения и время разработки для создания ETL-задания. Кроме того, это сводит к минимуму вероятность ошибок, которые могут возникнуть при написании кода. Более того, это позволяет бизнес-аналитикам и другим нетехническим пользователям активно участвовать в задачах интеграции данных. Это дает им возможность более эффективно выполнять анализ данных.

В дополнение к тому, чтобы быть инструмент ETL без кода, Astera Centerprise позволяет пользователям создавать высокопроизводительные задания ETL. Эти задания дают более быстрые результаты с использованием меньшего количества ресурсов. Это также позволяет пользователям автоматизировать весь процесс ETL с помощью динамических параметров. Таким образом, это позволяет легко настраивать и автоматизировать сложные процессы без ручного вмешательства.

В целом сочетание PostgreSQL и SQL Server с Astera Centerprise предоставляет пользователям мощное решение для интеграции данных, которое может обрабатывать широкий спектр типов и форматов данных и эффективно предоставлять аналитическую информацию из больших наборов данных.

Заключение

В заключение, хотя PostgreSQL и Microsoft SQL Server могут иметь существенные различия между собой. . PostgreSQL — это база данных с открытым исходным кодом, а SQL Server — коммерческое предложение. Кроме того, PostgreSQL обладает полезными функциями, такими как возможность добавления пользовательских функций. Принимая во внимание, что SQL Server предлагает один из самых сложных доступных пользовательских интерфейсов.

Обе базы данных могут использоваться совместно с Astera Centerprise. Astera Centerprise — это платформа интеграции данных, позволяющая предприятиям более эффективно перемещать и управлять своими данными. В конечном итоге выбор между PostgreSQL и SQL-сервером будет зависеть от конкретных потребностей вашего бизнеса.

PostgreSQL vs MS SQL для 1С

Давайте признаемся честно, хоть 1С Предприятие и совместимо со многими СУБД, но по факту 99 процентов работают либо в MS SQL или в бесплатной PostgreSQL.

Другими словами эти две «субдешки» завоевали рынок клиент-серверной 1С.

И можно смело предполагать, что если компания не работает в MS SQL то, скорее всего, просто используют PostgreSQL.

Соответственно сравнивать «Постгрес» есть смысл разве что только с MS SQL.

Сегодня много пишут, как об MS SQL так и о PostgreSQL, но обычно объективно не сравнивают их.

В этой статье мы же разберем основные технические моменты бесплатной PostgreSQL, сравнивая ее c MS SQL.

Конечно, данная тема также подымается и на курсе: Администратор 1С!

Что позволит Вам в будущем сделать оптимальный выбор и быть готовым к разным «неожиданностям» или что будет более правильно «особенностям» работы в этой бесплатной СУБД.

Оценивать будем все как есть, не приплюсовуя Постгресу тех заслуг, которых у него нет и, не приукрашивая платный MS.

Сразу отвечу на вопрос, который волнует многих новичков!

ДА! MS SQL работает быстрее PostgreSQL, это факт! И на это есть ряд причин!

Возможно, я кого-то прямо сразу разочаровал, и возможно, Вы не согласны с таким утверждением, извиняюсь, но сама физика работы этой бесплатной СУБД не позволяет ему опередить MS SQL, особенно если мы говорим о такой связке как «Монстр 1С» и PostgreSQL.

Подобные доводы Вы не раз встретите на различных конференциях и семинарах посвященных этой СУБД. Никто ничего не скрывает и не отрицает, факт есть факт.

Тем не менее, производительности PostgreSQL вполне достаточно, для того, чтоб пользователи могли комфортно работать в 1С.

Будь это десяток пользователей или даже несколько сотен, одновременно работающих в 1С Предприятии.

Почему «Монстр 1С» ?

Собственно так 1С видит PostgreSQL без установленных специальных «патчей» и расширений.

Да, что называется из коробки, скачав дистрибутив PostgreSQL на оф. сайте Вы не сможете использовать его для работы в связке с 1С. 1С-ка будет жутко тормозить и просто останавливаться, отказываться работать.

Почему так происходит, и зачем «патчи»?

Дело в том что 1С Предприятие создает огромное количество временных таблиц в процессе своей работы, речь может идти о тысячах таблиц в секунду, а если взять, например регистр «Срез последних» – «ОстаткиИОбороты», там вполне могут и по миллиону строк быть.

Дело в том что по умолчанию (без «патчей») PostgreSQL не считает статистику по этим большим временным таблицам, другими словами оптимизатор запросов который руководствуется данными из статистики (а она как помним пуста, нечего считать) грубо говоря, делает выборку методом SELECT * что конечно будет работать очень и очень медленно!

Отсюда грандиозные тормоза в 1С!

Конечно это не все проблемы, которые нужно решить, чтоб PostgreSQL работал в паре с 1С нормально. Нужны будут и другие «патчи» и специальные расширения и после 15-20 пользователей, еще и доп. настройки в “конфиге”

Да, на самом деле в реалии все выглядит намного сложнее, чем я описал выше, но вот так если сильно упростить и будет выглядеть основная проблема медленной работы 1С с PostgreSQL.

Второе что мне сильно не нравится в PostgreSQL это отсутствие многопоточности в рамках одного запроса в сравнении с MS SQL.

(Начиная с версии 9.6 сделали первую попытку распараллеливания запросов, но пока работает плохо, иногда эффект обратный ). но за попытку 5! )

Что конечно влияет на производительность, чтоб Вы понимали простым языком –

PostgreSQL способен уложить Ваш 48-ми ядерный сервер, одним большим запросом!

Все просто, распараллеливания потоков в рамках одного запроса нет и один большой запрос «грузит» только одно ядро.

Да, если запросов много, тогда все ядра будут нагружены, и все будет работать хорошо.

И чуть не забыл, сравниваем мы PostgreSQL c MS SQL Standard не Express!

Express хоть и можно использовать в коммерческих целях, но целый ряд ограничений

таких как 10 Гб на базу, использование одного процесора, 1 Гб оперативной памяти,

делает использование такого продукта почти нереальным для работы в 1С Предприятии.

Разве что у вас очень маленькая база и всего пара пользователей, (да и то бывают тормоза 1 гб для СУБД очень мало).

Так что сравниваем PostgreSQL с популярной версией Standard.

СКРИПТЫ.

PostgreSQL это прежде всего скрипты в сравнении с MS SQL, большинство операций приходится делать руками, да можно установить конечно pgAdmin 4 и некоторые базовые вещи выполнять через интерфейс, но подчеркну, что базовые , а шаг влево шаг вправо и нужно писать скрипт, или БАШ на Линуксе или cmd, powershell наWindows.

Просмотр и анализ трассировок с помощью приложения SQL Server Profiler.

Всем известный SQL Server Profiler в PostgreSQL отсутствует, причем под словом «отсутствует» я имею, введу напрочь, увы, нет ничего подобного в PostgreSQL.

Есть, конечно, утилиты, которые позволяют, если успеть перехватить запрос или поставить точку останова 1С в отладчике и что-то получит и посмотреть, но в сравнении с Профайлером как говорится и близко не стояло.

Можно настроить лог и потом это все перебирать – но долго!

Вот пример:

Программист 1С пытается отладить какой-нибудь большой запрос, он долго выполняется, например 30 минут, так вот в PostgreSQL, чтоб данные попали в лог, этот запрос должен выполнится! Представляете, как долго можно отлаживать такой запрос?

В то время как в MS SQL можно прервать выполнение запроса и в Профайлере его разобрать, так как он там уже будет, но со статусом «failed».

По разновидности создания «бэкапов» Постгресу нет равных!

Здесь Вам и инкрементный «бекап» и полное резервное копирование и непрерывное WAL архивирование.

Как собственно есть и частичное резервное копирование и частичное восстановление данных.

Можно настроить непрерывное архивирование и восстановление на момент времени (Point-in-Time Recovery (PITR)).

Также репликация, доступна изначально в PostgreSQl без каких либо «патчей» утилит и дополнений!

• Каскадная репликация
• Потоковая репликация
• Синхронная репликация
• Непрерывное архивирование на резервном сервере

Все это есть, уже изначально в PostgreSQl и конечно нет в «экспрессе» и недоступно на версии MS SQL Standard.

Чтоб получить все выше перечисленное в MS SQL, нужно покупать очень дорогой MS SQL Enterprise, сейчас что-то около 15 000$ долларов.

Чего нет в сравнении с MS SQL ?

НЕТ диференциального «бэкапа»

Да в PostgreSQl нет дифференциального «бэкапа», но есть различные аналоги инкрементного создания «бэкапов».

Например, инкрементный «бэкап» на уровне блоков.

ЕСТЬ разделение TABLESPACE-ов, что уже по умолчанию поддерживает 1С!

Которого к слову нет в MS SQL!

Например, Вы можете настроить на каком диске у вас будут «индексы» и на каком диске будет находиться «таблица», очень удобно при планировании IТ инфраструктуры, когда речь идет о больших базах данных 1С.

PostgreSQl намного быстрее обновляет статистику (если точнее – считает ее):

Например тот же MS SQL в терабайтной базе может считать статистку час, а вот PostgreSQL минуты 2-3. )

Внимание!

MS SQL в «бэкап» помещает уже посчитанную статистику, чего не делает PostgreSQL!

И когда Вы восстановитесь из такого «бэкапа» будут тормоза, пока не сделаете ONLINE_ANALYZE, чтоб пересчитать статистику. Тоже самое касается файла *dt.

Выгрузили – загрузили, нужно считать статистику ONLINE_ANALYZE!

Используя PostgreSQl очень редко нужен REINDEX!

Фактически стоит использовать только когда, есть подозрения, что целостность базы данных повреждена.

Можно делать «бэкапы» с исключением таблиц!

Например, у вас в компании работают несколько программистов 1С, они гарантированно будут делать себе резервные копии создавать «бэкапы» для дальнейшей разработки.

В итоге страдают пользователи, база тормозит во время создания большого «бэкапа» особенно если в этой базе есть такие вещи как различные прикрепленные файлы, архивы, документы из писем. Такие таблицы с файлами запросто могут содержать сотни гигабайт. И их же можно исключить в PostgreSQl создавая «бэкап», тем самым малого размера, и со всем функционалом одновременно.

Так мы лишний раз не нагружаем сетевые устройства, не забиваем канал, тратим намного меньше времени на создание такого «бэкапа»

В итоге все в выигрыше! И пользователи, и программисты и админы спят спокойно.

В этой статье мы разобрали лишь базовые отличия PostgreSQl от MS SQL, (есть и другие) но определится с выбором в пользу той или иной СУБД, статья должна помочь!

P.S. Сейчас работаю над новым курсом “1С и PostgreSQL” (Уже на стадии записи, ждите, скоро!)

Если Вы хотите больше узнать о технической стороне 1С, тогда регистрируйтесь на первый бесплатный модуль курса: Администратор 1С >>>

С уважением, Богдан.

Присоединяетесь к нашей группе телеграмм: Здесь вы получите ценную помощь от умного бота «Коли» (ChatGPT) + знания сотен ее участников.

Карманный справочник: сравнение синтаксиса 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 при обращениях к объектам можно использовать квадратные скобки (они обязательны, только если в названии объекта или его поля присутствуют недопустимые символы):

[schema] [table] [view] [object] [table].[field] [view].[field] [schema].[table] [schema].[view] [schema].[object] [schema].[table].[field] [schema].[view].[field]

В PostgreSQL для этого используются двойные кавычки (они обязательны, только если в названии объекта присутствуют заглавные буквы или есть недопустимые символы в названии объекта или его поля):

"schema" "table" "view" "table"."field" "view"."field" "schema"."table" "schema"."view" "schema"."table"."field" "schema"."view"."field"

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

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 ‘%’, ‘строка’;

IX. Управление выполнением кода

Выполнение скрипта

В MS SQL Server:

declare @_query int; set @_query=777; set @query=1+8; RAISERROR(@_query, 1, 1) WITH NOWAIT; --PRINT @_query;

do $$ begin end; $$;

Пример (вывод информации):

do $$ declare _query int; begin _query:=777; _query:=1+8; RAISE NOTICE '%', _query; end; $$;

Пример (передача значения клиенту):

do $$ declare _query int; begin _query:=777; _query:=1+8; PERFORM set_config('my._query', _query::text, FALSE); end; $$; SELECT current_setting ('my._query');

1. В DBeaver (бобре) нужно нажать CTRL+SHIFT+O при отсутствии окна вывода, а в pgAdmin вывод происходит автоматически.
2. В psql и так всё работает.

Цикл WHILE

Логическое ветвление

Более подробно про управление выполнением кода:

1. Управление выполнением кода в MS SQL Server
2. Управляющие структуры в PostgreSQL

X. Функции для работы со строками

Определение длины строки (количество символов в строке)

Примечание. В MS SQL Server исключаются конечные пробелы. Если нужно учитывать и их, то необходимо воспользоваться функцией DATALENGTH (), которая возвращает суммарное количество байтов в символах строки.

Возвращение символа по его коду:

Конкатенация строк

Нахождение позиции вхождения подстроки

В MS SQL Server:

Примечание. Точного соответствия не будет, если производить поиск не с начала строки.

Регистронезависимое сравнение и поиск данных

В 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 рекомендуется произвести оптимизацию через создание функционального индекса:

create [concurrently] index idx_lower_ on . (lower()); --После создания concurrently-индекса, --его необходимо проверить на наличие битых индексов следующим запросом: SELECT indexrelid::regclass FROM pg_index where not indisvalid; --Далее для обновления статистики по нужной таблице --необходимо выполнить команду ANALYZE: ANALYZE ;

Более подробно про команду ANALYZE.

Слияние строк по запросу в одну строку по заданному разделителю

В MS SQL Server можно использовать функцию STUFF следующим образом:

STUFF(( SELECT DISTINCT ', ' + CONVERT(varchar, tbl.) FROM . tbl [WHERE ] FOR XML PATH('')) , 1 , 1 , '') AS STUFF_tbl;

Также начиная с версии 2017 доступна функция STRING_AGG.

В PostgreSQL для этого можно использовать функцию string_agg таким образом:

string_agg((SELECT distinct ', ' || cast(tbl. as VARCHAR) FROM . tbl, [WHERE ] ), 1, 1, '') AS string_agg_field;

Более подробно про функции для работы со строками:

XI. Функции для работы с датой и временем

Получение текущей даты и времени (локальное время)

Получение текущей даты

CAST(GetDate() as DATE)

Пример преобразования формата даты и времени из строки public_date:

В MS SQL Server:

FORMAT(public_date, 'dd.MM.yyyy HH:mm:ss', 'ru-RU') — предпочтительный способ convert(varchar(32),convert(datetime,public_date,104),120)

to_char(to_timestamp(public_date, 'dd.MM.yyyy hh24.mi'), 'yyyy-mm-dd hh24:mi:ss')

Приращение даты/времени

В MS SQL Server:

DateAdd(datepart, count, dt);

dt + (count * interval ‘1 datepart’);
или
dt + interval ‘count datepart’;

Более подробно про функции для работы с датой и временем:

XII. Получение количества строк, затронутых при выполнении последней команды

XIII. Выполнение динамического SQL-кода

XIV. Проверка и приведение типов

Проверка строки на то, что она является числом

В MS SQL Server:

CREATE OR REPLACE FUNCTION dbo.isnumeric(_input varchar(255) DEFAULT NULL::varchar(255)) RETURNS bit LANGUAGE plpgsql AS $function$ /* Проверяет, является ли входная строка числом */ declare _result bit; begin begin perform _input::numeric; _result:=1::bit; exception when others THEN _result:=0::bit; end; return _result; end; $function$ ;

Безопасное приведение типа

В MS SQL Server:

try_cast(val as )

Примечание. try_cast в MS SQL Server возвращает NULL, если значение невозможно привести к заданному типу, в других случаях — работает как оператор CAST.

В PostgreSQL есть два способа:

1) через обработку ошибок:

declare _result оператор CAST ; . BEGIN _result := cast(val as ); exception when others then _result :=null; end;

2) через реализацию функции:

CREATE OR REPLACE FUNCTION dbo.try_cast(value character varying, typename CHARACTER varying) returns text LANGUAGE plpgsql AS $function$ declare _sql_command text; DECLARE _result text; begin _result=value; _sql_command := 'select cast('||''''|| value||''''||' as '|| typename||');'; BEGIN execute _sql_command; exception when others then _result :=null; end; return _result; end; $function$ ;

Функция в итоге не возвращает преобразованное в заданный тип значение.
Функция на вход принимает текст и возвращает текст.
Если значение невозможно привести к заданному типу, то возвращается NULL.

Пример использования (чтобы было как в MS SQL Server):

cast(dbo.try_cast(val::text, '') as )

XV. DML-команды

Обновление данных

Пример в MS SQL Server:

Обновление поля Name в таблице Production.ScrapReason для тех строк, для которых есть соответствующие записи в таблице Production.WorkOrder по равенству ScrapReasonID и у которых значение ScrappedQty больше 300:

UPDATE sr SET sr.Name = 'Name' OUTPUT deleted.* , inserted.* FROM Production.ScrapReas sr JOIN Production.WorkOrder wo ON (sr.ScrapReasonID = wo.ScrapReasonID) AND (wo.ScrappedQty > 300);

Ключевое слово OUTPUT позволяет получить данные об обновлении.

Пример в PostgreSQL:

Обновление поля Name в таблице production.scrapreason для тех строк, для которых есть соответствующие записи в таблице production.workorder по равенству scrapreasonid и у которых значение scrappedqty больше 300:

update production.scrapreason as sr set sr.Name = 'Name' from production.workorder as wo where (sr.scrapreasoid = wo.scrapreasonoid) and (wo.scrappedqty > 300) returning *;

Ключевое слово returning позволяет получить данные об обновлении.

Более подробно о команде UPDATE:

Удаление данных

Пример в MS SQL Server:

Удаление из таблицы Sales.SalesPersonQuotaHistory тех записей, для которых есть соответствующие записи в таблице Sales.SalesPerson по равенству BusinessEntityID и у которых значение SalesYTD больше 2500000.00:

DELETE FROM spqh OUTPUT deleted.* FROM Sales.SalesPersonQuotaHistory spqh INNER JOIN Sales.SalesPerson sp ON (spqh.BusinessEntityID = sp.BusinessEntityID) WHERE (sp.SalesYTD > 2500000.00);

Ключевое слово OUTPUT позволяет получить данные об удалении.

Пример в PostgreSQL:

Удаление из таблицы sales.salespersonquotahistory тех записей, для которых есть соответствующие записи в таблице sales.salesperson по равенству businessentitid и у которых значение salesytd больше 2500000.00:

delete from sales.salespersonquotahistory AS spqh using sales.salesperson AS sp where (spqh.businessentityid = sp.businessentitid) and (sp.salesytd > 2500000.00) returning *;

Ключевое слово 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:

with dbl_in_stage as ( select row_number() over (partition by , . order by 1) as rn from . as stg ) delete from dbl_in_stage where rn > 1;

with x as ( select a, ctid, row_number() over(partition by a order by ctid) rn from t ) delete from t using x where t.a = x.a and t.ctid = x.ctid and x.rn > 1;

или более сложный вариант:

delete from . where ctid=any( array(select unnest(ctids[2:]) from ( select array_agg( ctid order by string_to_array( regexp_replace(ctid::text, E'\\(|\\)','','g'),',')::bigint[]) ctids FROM . as T group by T::text) as T)::tid[]);

Примечание. Оптимальный вариант — внести в таблицу уникальный ключ, так как работа с метаданными увеличивает нагрузку на систему.

При наличии уникального ключа удалять дубликаты в PostgreSQL можно следующим образом:

delete from . where in (select from ( select *, row_number() over (partition by , . order by 1) as rn from . ) as tbl where rn > 1);

XVI. DDL-команды для работы с таблицами

Удаление таблицы с предварительной проверкой

В MS SQL Server:

Для основной таблицы:

DROP TABLE IF EXISTS .;

Для локальной временной таблицы:

IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#%') BEGIN DROP TABLE #; END;

Для глобальной временной таблицы:

IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '##%') BEGIN DROP TABLE ##; END;

Все временные таблицы живут, либо пока активна сессия, во время которой они были созданы, либо пока их явно не удалят.

Для основной таблицы:

drop table if exists .;

Для временной таблицы:

drop table if exists ;

Более детально про удаление таблиц:

Создание таблицы через выборку

В MS SQL Server:

Для основной таблицы:

select . into from …

Для временной таблицы:

select . into # from …

Для основной таблицы:

create table as select . 

Для временной таблицы:

create temp table as select …

Более детально про создание таблиц через выборку:

Создание/изменение и удаление значения по умолчанию для колонки таблицы

В MS SQL Server:

ALTER TABLE . ADD CONSTRAINT DEFAULT FOR ;

Выборка всех значений по умолчанию:

SELECT SCHEMA_NAME(t.[schema_id]) AS sch , t.name AS tbl , col.name AS colname , dc.definition AS def FROM sys.default_constraints dc INNER JOIN sys.columns col ON dc.parent_object_id = col.[object_id] INNER JOIN sys.tables t ON t.[object_id] = col.[object_id];

DROP DEFAULT IF EXISTS ;

Изменение происходит через удаление и добавление.

Создание и изменение:

alter table . alter column set default ;

Выборка всех значений по умолчанию:

select col.table_schema, col.table_name, col.column_name, col.column_default from information_schema.columns as col;

alter table . alter column drop default;

Изменение типа колонки таблицы

В MS SQL Server:

Примечание. Если у изменяемого столбца есть какие-либо констрейнты или значения по умолчанию, то сначала нужно их сохранить, а затем удалить. Вернуть их можно будет после изменения типа.

Перенос автоинкрементных полей

В MS SQL Server делаем запрос вида:

SELECT 'do $$ declare start_with_val bigint; declare sql_statement varchar; begin start_with_val := coalesce((select max(' + c.[name] + ') from ' + s.[name] + '.' + o.[name] + '),0)+1; sql_statement := ''alter table ' + s.[name] + '.' + o.[name] + ' alter ' + c.[name] + ' add generated by default as identity (start with '' || cast(start_with_val as varchar)||'');''; execute sql_statement; end; $$;' AS plsql_statement --select distinct s.name FROM sys.all_columns c INNER JOIN sys.all_objects o ON o.[object_id] = c.[object_id] INNER JOIN sys.schemas s ON s.[schema_id] = o.[schema_id] WHERE is_identity <> 0 AND SCHEMA_NAME(o.[schema_id]) <> 'sys' AND o.[type] = 'U';

do $$ declare start_with_val bigint; declare sql_statement varchar; begin start_with_val := coalesce((select max(ID) from dbo.ExchangeQueue),0)+1; sql_statement := 'alter table dbo.ExchangeQueue alter ID add generated by default as identity (start with ' || cast(start_with_val as varchar)||');'; EXECUTE sql_statement; end; $$;

Полученные скрипты применяем на стороне PostgreSQL.

Создание автоинкрементных полей

В MS SQL Server:

ALTER TABLE [схема].[таблица] ADD bigint IDENTITY(1, 1) NOT NULL;

do $$ DECLARE start_with_val bigint; DECLARE sql_statement varchar; BEGIN start_with_val := coalesce((select max() from .),0)+1; sql_statement := 'alter table . alter add generated by default as identity (start with ' || cast(start_with_val as varchar)||');'; EXECUTE sql_statement; END; $$;

Более детально про создание таблиц:

Более детально про изменение таблиц:

XVII. Создание и изменение представления

В MS SQL Server:

CREATE OR ALTER VIEW

create or replace view

Примечание. В PostgreSQL лучше сначала удалять представление, а потом заново его создавать, если набор полей меняется или меняются названия выходных полей, иначе можно получить ошибку при обращении к изменённому представлению.

Более подробно про создание и изменение представлений:

XVIII. Построчная обработка строк в наборе

В MS SQL Server:

--объявление переменных @field_1, . @field_N DECLARE CURSOR LOCAL FOR ; OPEN ; FETCH NEXT FROM INTO @field_1, . @field_N; WHILE (@@FETCH_STATUS = 0) BEGIN --оперируем значениями переменных @field_1, . @field_N . FETCH NEXT FROM INTO @field_1, . @field_N; END CLOSE ; DEALLOCATE ;

do $$ declare _val record; begin drop table if exists _tmp_tbl; create temp table _tmp_tbl as for _val in (select field_1, . field_n from_tmp_tbl) loop --можно обратиться к любому выбранному ранее полю через _val.. Например, _val. end loop; end $$

XIX. Системные информационные функции безопасности

Текущий пользователь

В MS SQL Server используется функция CURRENT_USER().

1. session_user — под каким пользователем открыта сессия
2. current_user (или просто user) — под каким контекстом (ролью) идёт выполнение (session_user переключается для выполнения — здесь важно, под каким правом делается переключение)

Получение имени экземпляра и IP-адреса сервера СУБД

В MS SQL Server:

Получить информацию об IP-адресе сервера СУБД:

SELECT CONNECTIONPROPERTY(' net_transport') AS net_transport , CONNECTIONPROPERTY(' protocol_type') AS protocol_type , CONNECTIONPROPERTY(' auth_scheme') AS auth_scheme , CONNECTIONPROPERTY(' local_net_address') AS local_net_address , CONNECTIONPROPERTY(' local_tcp_port') AS local_tcp_port , CONNECTIONPROPERTY(' client_net_address') AS client_net_address;

Получить название экземпляра СУБД:

SELECT @@SERVERNAME;

Получить IP-адрес сервера СУБД:

do $$ declare title varchar(100) :=host(inet_server_addr()); begin raise notice '%', title; end; $$;

Получение названия экземпляра СУБД пока не реализовано.

Более подробно про системные информационные функции безопасности:

XX. Определение и вызов хранимой процедуры

Определение хранимой процедуры

CREATE OR ALTER PROCEDURE [схема].[назание_процедуры] [=], . AS BEGIN . END

CREATE OR REPLACE PROCEDURE . ( [INOUT] [=], . ) LANGUAGE plpgsql AS $body$ [] BEGIN . END; $body$ ;

Вызов хранимой процедуры

В MS SQL Server:

EXEC . =, . OUT[PUT];

call . ( =, . );

XXI. Создание скалярной функции

CREATE OR ALTER FUNCTION [схема].[название_функции] ( [=], . ) RETURNS AS BEGIN . RETURN . END

CREATE OR REPLACE FUNCTION . ( [=], . ) RETURNS LANGUAGE plpgsql AS $body$ [] begin . return ( select . ); end; $body$ ;

XXII. Передача табличного значения (вывод таблицы)

В MS SQL Server:

CREATE OR ALTER PROCEDURE [схема].[название_хранимой_процедуры] , .  AS BEGIN . SELECT . END

create or replace function . ( , . ) return table ( , . ) language 'plpgsql' as $body$ [] begin return query (select . ); end; $body$;

XXIII. DML-триггеры

Пример в MS SQL Server:

CREATE TRIGGER [info].[tr_isupoll_question_text_last_update_trigger] ON [info].[isupoll_question_text] FOR UPDATE AS UPDATE info.isupoll_question_text SET last_update_date = GETDATE() , last_update_user = SUSER_NAME() FROM info.isupoll_question_text ds INNER JOIN INSERTED i ON ds.isupoll_question_text_id = i.isupoll_question_text_id;

Здесь создаётся триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text после обновления данных, который для обновляемых строк проставляет текущие дату, время и пользователя соответственно.

DROP TRIGGER IF EXISTS [tr_isupoll_question_text_last_update_trigger] on [info].[isupoll_question_text];

Здесь удаляется триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text

Пример в PostgreSQL:

CREATE OR REPLACE FUNCTION dbo.update_mod() RETURNS trigger LANGUAGE plpgsql AS $function$ begin new.last_update_date=now(); new.last_update_user=session_user; return new; end; $function$ ;

Здесь создаётся функция dbo.update_mod(), которая заполняет два поля текущими датой, временем и пользователем соответственно.

create trigger tr_isupoll_question_text_last_update_trigger before update on info.isupoll_question_text for each row execute function dbo.update_mod();

Здесь создаётся триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text до обновления данных, который для каждой строки вызывает выполнение функции dbo.update_mod().

drop trigger if exists tr_isupoll_question_text_last_update_trigger on info.isupoll_question_text;

Здесь удаляется триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text.

Важно! В триггере используйте ключевое слово before, когда хотите нашкодничать в той же таблице, для которой создаётся триггер, и after — для логирования в другую таблицу.

Более подробно про DML-триггеры:

И в качестве бонуса кратко рассмотрим сопоставление основных системных представлений и приведём ссылки для мониторинга.

Немного о сопоставлении системных представлений и мониторинге

Сопоставление системных представлений

MS SQL Server

PostgreSQL

Описание