01 March 2021

  • 3

  • 4,190 views

  • 0

01 March 2021

4,190 views
3 0

HOW CAN SQL SERVER DEVELOPERS GET STARTED WITH POSTGRE SQL?

WHAT IS SQL SERVER?

SQL server is a relational database management system. It is offered by Microsoft and is one of the most popular relational database management systems which are being utilized by several companies for their database requirements. There are numerous USPs of Microsoft SQL Server which makes it a popular choice. Some of them include: online analytical processing, data mining, interactive GUI and database performance enhancers and analyzers.

WHAT IS POSTGRE SQL?

PostgreSQL on the other hand is not offered by one specific company but is rather an open-source tool. When a piece of technology falls into the open-source category, it suggests that the code is being read and reviewed by numerous developers around the world. The worldwide developer community is capable of making significant contributions to the open-source projects which automatically increases the chances of quick bug fixes and more enhancements. PostgreSQL is extremely popular in terms of open-source relational database management systems. Developers are attracted to PostgreSQL because there is no fee to use PostgreSQL in its full capacity, it can be integrated with several other languages like C++ and Java and performs all the functionalities that are offered by any paid modern relational database management system.

WHAT CHANGES CAN YOU EXPECT WITH THE GUI?

In SQL server, the views and schemas are displayed in the Microsoft SQL Server Management Studio where the developer is able to see the connections between the relations, observe the structure of the table, write queries and see the output of those queries. In addition to this, SQL Server developers are also used to proper indenting and colored syntax highlighting while running their SQL queries in the studio, getting readymade templates for basic SQL functionalities and statistics regarding the time and the resources utilized when their queries are run.

On the other hand, in PostgreSQL, the views and schemas are displayed in the Data Architect which is the GUI offered by PostgreSQL for developers to visualize the structure and relationship of the entities and relations in their database. The number of tools available in Data Architect might overwhelm a SQL Server developer first but since the basic structure – visualization of schema, query running console, query output window etc. is similar, it won’t be long when the developer starts getting comfortable with PostgreSQL’s GUI.

WHAT VARIATIONS WILL YOU OBSERVE WITH COMMAND LINE UTILITIES?

For SQL Server developers sqlcmd utility is no brainer. Similarly, in PostgreSQL psql is the command line utility. Below mentioned are few examples and commands which the developer can perform in PostgreSQL using the psql utility:

  1. Variable Substitutions
    1. Set a variable:
    2. Create a list of variables:
    3. Using the defined variables as data to be inserted in the table:

  1. SQL Interpolation

WHAT CHANGES YOU SHOULD EXPECT IN THE SYNTAX OF SQL QUERIES?

Microsoft SQL Server utilises T-SQL or Transact SQL. T-SQL has all the features and functionalities of a language required for managing databases. It contains DDL, DML, user defined variables, functions and procedures. However, developers are also used to using several relational operators and table expressions while using the SQL Server. PostgreSQL supports all the basic functionalities of entry-level SQL. It was PostgreSQL which contributed in the development of several object-relational features. While PostgreSQL does not have inbuilt OLAP features, being open source, it can easily be connected with external OLAP offering servers.

WHAT VARIATIONS WILL YOU OBSERVE FROM SQL SERVER TO POSTGRESQL TAKING DATATYPES INTO CONSIDERATION?

SQL Server supports the following datatypes:

  1. Basic Datatypes: character, binary string, text, images.
  2. XML Type: This is used to store XML data.
  3. Sql_variant Type: It contains information about SQL scalar types with filter, join and sort functionalities being offered for columns which are of sql_variant datatypes.
  4. Table Type: This is a type which cannot be assigned to a column. This can only be assigned to variables which are being used in the structure. While creating functions and procedures, it is not advisable to use the table directly and hence a table type variable is used for performing various tasks related to a function or a procedure.
  5. Cursor Type: Cursor Type, like Table Type is a datatype which cannot be assigned to columns and can only be assigned to variables. It is used to refer to cursor objects.

On the other hand, you will observe some more advanced (or different) data types when you are starting with PostgreSQL. Make sure you have familiarized yourself with the use cases of these data types to work with them smoothy.

  1. Base Types: These are abstract data types which include basic data types like int, complex etc.
  2. Composite Types: These datatypes are created automatically by PostgreSQL whenever a new table is created. It contains information about the rows of the table. They can be declared by the developer as well.
  3. Domains: Domains are another kind of base types.
  4. Pseudo Types: Pseudo types are data types which cannot be assigned to a column. They can be used either as arguments or function return types.
  5. Polymorphic Types: anyelement and anyarray are two pseudo types which are known as polymorphic types.
  6. Geometric Data Type: This datatype is used to represent 2D shapes on which different in-built geometric functions can be performed.
  7. Storage of network addresses: PostgreSQL also offers a datatype wherein network addresses can be stored. Pretty handy if you are making an application which requires geo-tracking and geolocation services and also has support to perform operations on these addresses.
  8. Bit type: For storage of data in binary and perform operations on it.

TABULAR COMPARISON BETWEEN THE SYNTAX AND FEATURES OF POSTGRE SQL AND MS SQL SERVER FOR REFERENCE

Given below are some common parameters which have been used to draw a comparison between the structural query languages used by both PostgreSQL and MS SQL Server. The rows highlighted in green indicate that there is no difference between the SQL syntax of PostgreSQL and MS SQL Server.

Parameter

PostgreSQL

MS SQL Server

NATURAL JOIN

select firstname from scientist natural left join not_scientist;

No support.

USING keyword

select * from scientist inner join not_scientist using(id);

No support.

FULL JOIN

select * from scientist full join not_scientist on scientist.id = not_scientist.id;

select firstname from scientist full outer join not_scientist on scientist.id=not_scientist.id;

CROSS JOIN / CARTESIAN PRODUCT

select * from scientist cross join not_scientist;

SELECT item_name FROM items CROSS JOIN market;

COPY TABLE

create table copycat_scientist as select * from scientist where false;

insert into copycat_scientist (id, firstname, lastname) values (1, ‘albert’, ‘einstein’);

insert into copycat_scientist (id, firstname, lastname) values (2, ‘isaac’, ‘newton’);

insert into copycat_scientist (id, firstname, lastname) values (3, ‘marie’, ‘curie’);

select * from copycat_scientist;

select * into copy_scientist from scientist where 1<>1;

insert into copy_scientist (id, firstname, lastname) values (1, ‘albert’, ‘einstein’);

insert into copy_scientist (id, firstname, lastname) values (2, ‘isaac’, ‘newton’);

insert into copy_scientist (id, firstname, lastname) values (3, ‘marie’, ‘curie’);

select * from copy_scientist;

ORDERING OUTPUT

select firstname from scientist order by firstname;

select firstname from not_scientist order by firstname;

LIMIT

select firstname from scientist order by firstname limit 2;

SELECT TOP 5 item_name FROM items ORDER BY item_id ASC;

INSERT

insert into not_scientist (id, firstname, lastname) values (3, ‘marie’, ‘curie’);

 

insert into not_scientist (id, firstname, lastname) values (3, ‘marie’, ‘curie’);

BOOLEAN DATATYPE

Supports.

Supports as BIT Datatype

CHAR DATATYPE

Supports.

Supports.

TIMESTAMP

Supports.

Supports but not exactly timestamp, it supports datetime.

CHARACTER_LENGTH FUNCTION

select char_length(‘albert’);

Does not support character length.
Instead:
Len(‘Dior’) and DATALENGTH(‘Dior’s Handbag’) are used.

SUBSTRING

select substring(‘the horse and the grass and the stable’ from 10 for 20);

select substring(‘the horse and the grass and the stable’, 0, 10);

REPLACE

update scientist set firstname = replace(firstname,’albert’,’not_albert’);

select * from scientist;

select REPLACE(‘Merry Christmas’,’rr’,’bb’);

TRIM

select trim(trailing ‘y’ from ‘monkey’);

select LTRIM(‘ Merry’);
CONCATENATION

select concat(‘goofy’,’ ‘,’monkey’);

select concat(‘The Goofy’, ‘Monkey’) as nickname;

UNIQUE CONSTRAINT

CREATE TABLE nicknames ( nickname_no integer UNIQUE,

Nickname text

);

CREATE TABLE nicknames ( nickname_no INT UNIQUE,

Nickname VARCHAR(50)

);

TRUNCATE TABLE

TRUNCATE nicknames;

TRUNCATE TABLE nicknames;

CONCLUSION

As a MS SQL Server developer, you already understand the intricacies of databases, relational databases, database management systems and their use cases. The only milestone you have to achieve now is to get familiarized with the GUI of PostgreSQL and practice the commands a bit. Start with a blank slate in your mind keeping in consideration the syntax of the language and since you already are well versed with the conceptual aspect of SQL and RDBMS, you will be able to draw an analogy between both the tools and hence, you will start using PostgreSQL for your applications in no time!

Here are some resources for you to kickstart your journey in learning PostgreSQL:

  1. “The Complete Python/PostgreSQL Course 2.0 by Codestars by Rob Percival, Jose Salvatierra, Teclado by Jose Salvatierra available at Udemy”
    Description: This course will not only walk you through the syntax of PostgreSQL but also guide you in creating 9 real-world projects.
    Language: English
    Link: https://www.udemy.com/course/complete-python-postgresql-database-course/?altsc=781502
  2. “PostgreSQL by FreeCodeCamp”
     Description: FreeCodeCamp is well known for their free and amazingly curated content. This course is a 4 hour, no break, no Ad course which will take you from the basics to advanced with tonnes of examples and practice queries.
    Language: English
    Link: https://www.youtube.com/watch?reload=9&v=qw–VYLpxG4
  3. “PostgreSQL: Advanced SQL Queries by Pinal Dave Available at PluralSight”
    Description: This course contains advanced concepts of PostgreSQL, perfectly suitable for a developer well versed in MS SQL Server.
    Language: English
    Link: https://www.pluralsight.com/courses/postgresql-advanced-sql-queries?clickid=WsGxLk1WsxyLW7fwUx0Mo3QBUkEUMJy9%3AWyb1s0&irgwc=1&mpid=1193463&aid=7010a000001xAKZAA2&utm_medium=digital_affiliate&utm_campaign=1193463&utm_source=impactradius
  4. PostgreSQL Tutorial
     This is an online bible for the explanation of all the commands, concepts and queries.
    Here is the link: https://www.postgresqltutorial.com/
  5. Books
    1. Practical PostgreSQL by O’Reilly
    2. PostgreSQL Up and Running by O’Reilly
    3. Mastering PostgreSQL 12: Advanced Techniques to Build and Administer Scalable and Reliable PostgreSQL Database Applications, 3rd Edition
  6. Official Documentation of PostgreSQL
    There is nothing better than the official documentation to learn about a new piece of technology. It can be hard to go through sometimes, but one thing that is certain is that you will get all the information you need under one hood.
    Link: https://www.postgresql.org/docs/

Happy Learning!

 

  • 3

  • 4,190 views

Rate this article

Click to rate this post!
[Total: 4 Average: 3.8]

Pramodh Kumar

I am a Senior Business Intelligence Engineer with 10+ years of Experience. I have done my Master's Degree in State University of Newyork. I have extensive experience in various domains and in developing data warehouses, Writing SQL Queries, ETL, cubes and reports using the Microsoft Business Intelligence stack. I am very passionate about exploring various BI tools and its features with real-time analytics, predictive analytics and integration.

Follow Pramodh Kumar via

View all articles by Pramodh Kumar

Load comments

Related articles

 08 May 2024
08 May 2024

Edit The JSON of a Fabric Pipeline

0
1
A Fabric Pipeline uses JSON as source code. They are also saved in repositories as JSON. We first idea we get is editing the pipeline in JSON format. We can copy the JSON and create new pipelines with small variations, making changes directly on the JSON. However, at first sight we get disappointed, because the … Read more
0
1