close

March 2018

Tech

Converting SQL Server Views to PostgreSQL

One of the most important steps of database migration from Microsoft SQL server to PostgreSQL is processing views. Syntax of CREATE VIEW queries in MS SQL and PostgreSQL is similar but not the same. The primary challenge of conversion process is that these two DBMS have distinguished sets of built-in functions. Consequently, each CREATE VIEW statement must be converted into the appropriate equivalent before importing into the destination database. This article discovers the most important patterns of converting views from SQL Server to PostgreSQL.

1) There are embedded functions in MS SQL that have on direct equivalent in PostgreSQL. Such functions must be implemented manually as follows:

CREATE OR REPLACE FUNCTION datediff(date, date)

RETURNS int AS $$

SELECT $1 – $2

$$ LANGUAGE plpgsql;

/*

*/

CREATE OR REPLACE FUNCTION isdate(text) RETURNS INT AS $$

DECLARE x TIMESTAMP;

BEGIN

     if ($1 is null) then return 0; end if;

     x = $1::TIMESTAMP;

    RETURN 1;

EXCEPTION WHEN others THEN

    RETURN 0;

END;

$$ LANGUAGE plpgsql;

/*

*/

CREATE OR REPLACE FUNCTION isnumeric(text) RETURNS BOOLEAN AS $$

DECLARE x NUMERIC;

BEGIN

    x = $1::NUMERIC;

    RETURN TRUE;

EXCEPTION WHEN others THEN

    RETURN FALSE;

END;

$$ LANGUAGE plpgsql;

/*

*/

CREATE OR REPLACE FUNCTION patindex(

pattern VARCHAR,

expression VARCHAR)

RETURNS INT AS $$

     SELECT

           COALESCE( STRPOS($2, (

                SELECT( REGEXP_MATCHES( $2,'(‘ ||

REPLACE(

REPLACE(

TRIM( $1, ‘%’ ), ‘%’, ‘.*?’ ), ‘_’, ‘.’ ) || ‘)’) )[ 1 ] LIMIT 1 ) ), 0

           );

$$ LANGUAGE plpgsql;

2) Function ISNULL($var, $expr) that returns $expr if $var is NULL in MS SQL, must be replaced by PostgreSQL equivalent COALESCE($var, $expr)

3) Function GETDATE() returns the current date/time in SQL Server, it must be replaced by NOW() in PostgreSQL

4) Function DATEADD($interval, $n, $datetime) is used to add specified interval to $datetime in SQL Server. PostgreSQL allows to implement the same using operator ‘+’ as follows:

If $interval=’day’ then SELECT ($datetime + $n * interval ‘1 day’)::date

If $interval=’month’ then SELECT ($datetime + $n * interval ‘1 month’)::date

If $interval=’year’ then SELECT ($datetime + $n * interval ‘1 year’)::date

5) SQL Server provides multiple functions to extract particular part of the date/time value such as DAY(), MONTH(), YEAR(). These functions must be replaced by DATE_PART() in PostgreSQL as follows:

YEAR(datetime) -> DATE_PART(‘year’, datetime)

MONTH(datetime) -> DATE_PART(‘month’, datetime)

DAY(datetime) -> DATE_PART(‘day’, datetime)

6) In SQL Server function CHARINDEX get position of substring in the specified string, it has direct equivalent in PostgreSQL called POSITION

The guide above indicated that converting MS SQL queries into PostgreSQL format is not an easy procedure. It is reasonable to use special tools to automate the conversion, especially for large databases with a lot of views. One of such migration tools is MS SQL to PostgreSQL converter offered by Intelligent Converters, software vendor working in database migration field more than 15 years. The tool handles more than 80% of all possible syntax constructions used in CREATE VIEW queries. The price for the product is $49 that includes lifetime license, 1-year subscription for updates and 24/7 support.

More details about MS SQL to PostgreSQL converter can be found on the official page:  https://www.convert-in.com/mss2pgs.htm

read more
Health

What can you learn from the power of crowdfunding?

Call it an economic revolution, but crowdfunding has changed the way the social sector operates, be it a non profit, a start-up, a social enterprise, or even individuals raising money for personal reasons. With each year, philanthropists find different ways of engaging in charitable activities. Such a trend of steady change is not only inevitable, but also necessary for the Indian social landscape. In the recent past, India’s younger generations have revealed a new take on social responsibility and giving. This article focuses on the growth and scope of a crowdfunding India.

The rise of crowdfunding India

Gone are the days when nonprofits relied big checks from philanthropists, because it offered a risky and unstable financial solution. Things have changed now, and while it may be difficult to keep a single donor engaged in a cause for a long time, it is much easier to rely on your ability to communicate your initiative to a large number of people. This gave rise to crowdfunding platforms, where individuals, groups, nonprofits, and social enterprises launch campaigns making an appeal to people to donate for their cause.

The nature of giving

Even though disposable income of people in India increases everyday, you won’t see the current generation writing out big checks to charitable organizations. Strategic giving is complex and multidimensional, depending on an individual’s goals, aspirations, resources, and time available. Even if an individual is not in the position to make a tangible financial donation, their reach and influence on social media platforms may very well be an asset to nonprofits. Crowdfunding platforms like Impact Guru have taken note of such leaders and influencers of the social media world, to ensure that every share is made equivalent to a monetary donation.

Crowdfunding adapts to the digital revolution

It is needless to say that the progression in technology is largely responsible for how people view impact giving. People have less disposable income, but they have numbers. The behavioural traits of what we call Gen Y, are impulsive by nature, for they are driven by a strong sense of individuality. The same impulsive behaviour is applicable to their approach on philanthropy. They are much more likely to donate small amounts to various causes, as opposed to large sums to a single cause. Such a mindset is further encouraged by online payments, or in this case, online donations, which gave rise to the phenomenon of crowdfunding.

The way crowdfunding is saving lives

One would think that the most practical measure to seek help with medical bills is to have medical insurance. But realistically speaking, the greater number of our population can’t afford medical insurance. So they resort to other ways, such as applying for loans, or asking friends and family for money, and then spend the rest of their lives repaying it. Crowdfunding websites like Impact Guru have been influential in encouraging a crowdfunding India that raises funds online for medical treatments, so that no loved one ever has to forgo treatment over unavailability of funds.

There’s a lot more to come

India is still brimming with pressing social issues, and the need for charitable and philanthropic activities isn’t going away anytime soon. The upswing has been quite apparent, and highly motivating for those striving to achieve their social goals, but there are still many fronts that require a stronger push. We have fallen back on the Human Development Index, and the Sustainable Development Growth index. An estimated 533 lakh crores will be required if the SDG goals are to be met. This means that despite government schemes, individual donations will play a critical role in the progress of this country.

Crowdfunding websites have found innovative ways to incentivize people for donations, by offering tax exemptions, monetary rewards, merchandise etc. All these minor changes are expected to result into something much larger, where more people will be inclined to get involved in social activities, not just be donating, but also fueling their own ideas for impact.

read more