https://sqlines.com/ 2026-04-21T08:38:55+00:00 https://sqlines.com/ https://sqlines.com/lib/images/favicon.ico text/html 2024-10-23T13:48:41+00:00 https://sqlines.com/sql-server-to-postgresql/add_assign?rev=1729691321&do=diff In SQL Server, the += operator adds or concatenates value to the variable. In PostgreSQL, you have to use var := var + value for numbers, and var := var || value for strings. SQL Server: DECLARE @var INT = 1; DECLARE @str VARCHAR(30) = 'a'; -- For numbers += works as addition SET @var += 3; -- For strings += works as concatenation SET @str += 'b'; SELECT @var, @str; # 4 ab text/html 2022-09-20T14:18:32+00:00 https://sqlines.com/sql-server-to-postgresql/alias_single_quote?rev=1663683512&do=diff SQL Server allows you to use single quoted column aliases in SELECT statements: SQL Server: -- Using single quotes for aliases 'Current Datetime' and 'B' for two columns SELECT GETDATE() as 'Current Datetime', 'A' 'B' In PostgreSQL you have to use double quoted aliases: text/html 2024-10-21T20:26:23+00:00 https://sqlines.com/sql-server-to-postgresql/atan?rev=1729542383&do=diff In SQL Server and PostgreSQL ATAN function returns arctangent. SQL Server: -- Get arctangent SELECT ATAN(-45.01); # -1.54858269620627 PostgreSQL: -- Get arctangent SELECT ATAN(-45.01); # -1.5485826962062663 For more information, see SQL Server to PostgreSQL Migration. text/html 2024-10-21T20:38:46+00:00 https://sqlines.com/sql-server-to-postgresql/atn2?rev=1729543126&do=diff In SQL Server ATN2 function returns arctangent of two numbers. In PostgreSQL you can use ATAN2 function. SQL Server: -- Get arctangent SELECT ATN2(35.17, 129.44); # 0.265303934222674 PostgreSQL: -- Get arctangent SELECT ATAN2(35.17, 129.44); # 0.2653039342226741 text/html 2024-12-11T20:38:48+00:00 https://sqlines.com/sql-server-to-postgresql/begin_transaction?rev=1733949528&do=diff In SQL Server and PostgreSQL, the BEGIN TRANSACTION statement starts a transaction. Note that PostgreSQL does not allow using BEGIN TRANSACTION in a stored procedure, although you can use the COMMIT and ROLLBACK statements in procedures. SQL Server and PostgreSQL: text/html 2025-12-02T08:26:12+00:00 https://sqlines.com/sql-server-to-postgresql/bit?rev=1764663972&do=diff In SQL Server, the BIT data type stores 0, 1 or NULL values. In PostgreSQL, you can use the BOOLEAN data type but you need to convert 0 and 1 values, and the string representation (output in a query) for BOOLEAN is t and f. SQL Server: -- Sample table CREATE TABLE specs ( data BIT ); -- Inserting sample values INSERT INTO specs VALUES (0); INSERT INTO specs VALUES (1); -- SQL Server allows using 'FALSE' and 'TRUE' string literals INSERT INTO specs VALUES ('FA… text/html 2024-10-21T12:14:28+00:00 https://sqlines.com/sql-server-to-postgresql/cast?rev=1729512868&do=diff In SQL Server you can use CAST function to convert an expression to another data type. In PostgreSQL you can also use CAST function but data types can be different. SQL Server: -- Convert number to string SELECT CAST(123.45 AS VARCHAR); -- Convert string to datetime SELECT CAST('2024-10-21 14:54:27.123' AS DATETIME); text/html 2024-12-12T09:34:55+00:00 https://sqlines.com/sql-server-to-postgresql/ceiling?rev=1733996095&do=diff In SQL Server and PostgreSQL, the CEILING function rounds up the specified number to the nearest integer value. SQL Server and PostgreSQL: -- Round to the nearest larger integer SELECT CEILING(123.7); /* 124 */ -- Still rounds to larger integer even if it is closer to smaller integer SELECT CEILING(123.1); /* 124 */ text/html 2024-10-19T16:47:12+00:00 https://sqlines.com/sql-server-to-postgresql/char?rev=1729356432&do=diff In SQL Server, CHAR(n) data type stores fixed-length, blank padded character strings up to n bytes with the maximum of 8,000 bytes. In PostgreSQL, you can also use CHAR(n). SQL Server: CREATE TABLE specs ( item CHAR(30), name CHAR(100) ); text/html 2024-11-07T09:17:25+00:00 https://sqlines.com/sql-server-to-postgresql/char_function?rev=1730971045&do=diff In SQL Server, the CHAR function converts an integer ASCII code (0-255) to the corresponding character value. In PostgreSQL you can use the CHR function but note that CHR(0) is not allowed. SQL Server: -- CHAR(32) returns a blank character SELECT CHAR(32); /* Result: ' ' */ -- NULL is returned for values exceeding 255 SELECT CHAR(270); /* NULL */ -- Return <NUL> character (ASCII code 0x00) SELECT CHAR(0); /* Result: <NUL> character */ text/html 2024-12-04T20:45:20+00:00 https://sqlines.com/sql-server-to-postgresql/charindex?rev=1733345120&do=diff In SQL Server, the CHARINDEX function returns the position of the specified substring in the string. In PostgreSQL, you can use the POSITION function. Both CHARINDEX and POSITION functions return 0 if the substring is not found. SQL Server: -- Find the position of 'ello' SELECT CHARINDEX('ello', 'Hello, world!'); /* 2 */ -- You can specify the starting position for search, but return the position in the original string(!) SELECT CHARINDEX('ello', 'Hello, hello, world!', 5); /* … text/html 2024-12-11T21:03:28+00:00 https://sqlines.com/sql-server-to-postgresql/commit?rev=1733951008&do=diff In SQL Server and PostgreSQL, the COMMIT statement commits the current transaction. SQL Server and PostgreSQL: -- Sample table CREATE TABLE colors (name VARCHAR(30)); BEGIN TRANSACTION; -- Insert a sample row INSERT INTO colors VALUES('Black'); COMMIT TRANSACTION; text/html 2024-10-22T18:57:58+00:00 https://sqlines.com/sql-server-to-postgresql/concat?rev=1729623478&do=diff In SQL Server and PostgreSQL, the CONCAT function concatenates one or more strings. SQL Server: -- Concatenate strings SELECT CONCAT('Hello,', ' world', '!'); # Hello, world! -- NULL value is considered as an empty string SELECT CONCAT('Hello,', NULL, ' world', '!'); # Hello, world! text/html 2023-01-19T16:25:09+00:00 https://sqlines.com/sql-server-to-postgresql/convert_datetime?rev=1674145509&do=diff In SQL Server you can use CONVERT function to convert a string expression in the specified format (style) to a datetime data type (DATE, DATETIME etc.). In PostgreSQL you can to use TO_TIMESTAMP function. Note that SQL Server CONVERT and PostgreSQL formats are different. text/html 2024-12-04T21:04:36+00:00 https://sqlines.com/sql-server-to-postgresql/convert_string_datetime?rev=1733346276&do=diff In SQL Server, you can use the CONVERT function to convert a DATETIME value to a string with the specified format. In PostgreSQL, you can use the TO_CHAR function. Note that SQL Server CONVERT and PostgreSQL TO_CHAR formats are different. SQL Server: text/html 2022-12-27T22:44:14+00:00 https://sqlines.com/sql-server-to-postgresql/convert_time?rev=1672181054&do=diff In SQL Server you can use CONVERT function to convert an expression (a string i.e.) to TIME data type. In PostgreSQL you can to use CAST or various type conversion functions. SQL Server: -- Convert string to TIME SELECT CONVERT(TIME, '11:11'); # 11:11:00.0000000 text/html 2024-10-22T14:13:36+00:00 https://sqlines.com/sql-server-to-postgresql/cos?rev=1729606416&do=diff SQL Server and PostgreSQL provide COS function that returns the trigonometric cosine. SQL Server: -- Get cosine SELECT COS(35.17); # -0.818224321457963 PostgreSQL: -- Get cosine SELECT COS(35.17); # -0.8182243214579626 For more information, see SQL Server to PostgreSQL Migration. text/html 2024-11-18T17:19:54+00:00 https://sqlines.com/sql-server-to-postgresql/count?rev=1731950394&do=diff In SQL Server, the COUNT and COUNT_BIG are aggregte functions that return the number of rows. COUNT returns INT, while COUNT_BIG returns BIGINT. In PostgreSQL, you can use the COUNT function that returns BIGINT. Consider the following sample table: text/html 2024-11-18T17:21:24+00:00 https://sqlines.com/sql-server-to-postgresql/count_big?rev=1731950484&do=diff In SQL Server, the COUNT_BIG function is similar to the COUNT function but returns the number of rows as BIGINT. For more information, see COUNT and COUNT_BIG Functions - SQL Server to PostgreSQL Migration. text/html 2024-11-18T18:00:09+00:00 https://sqlines.com/sql-server-to-postgresql/cross_apply?rev=1731952809&do=diff SQL Server CROSS APPLY clause allows you to perform a correlated inner join (lateral join) often using a table-valued function or subquery. Correlated means that the right operand of a join clause contains a reference to the left operand. In PostgreSQL you can use CROSS JOIN LATERAL clause. text/html 2025-06-25T06:56:31+00:00 https://sqlines.com/sql-server-to-postgresql/cross_join?rev=1750834591&do=diff In SQL Server and PostgreSQL, the CROSS JOIN clause allows you to perform a cartesian product (cross-product) i.e. join each row from the first table with each row from the second table. Consider sample data: -- Sample tables CREATE TABLE colors (name VARCHAR(30)); CREATE TABLE products (name VARCHAR(30)); -- Sample rows INSERT INTO colors VALUES ('Green'); INSERT INTO colors VALUES ('Red'); INSERT INTO colors VALUES ('Yellow'); INSERT INTO products VALUES ('Apple'); INSE… text/html 2024-10-22T18:57:48+00:00 https://sqlines.com/sql-server-to-postgresql/current_timestamp?rev=1729623468&do=diff In SQL Server, the CURRENT_TIMESTAMP function returns the current date and time with milliseconds precision without time zone (it uses the server time zone). In PostgreSQL, you can also use the CURRENT_TIMESTAMP function but it returns the current date and time with microseconds precision and includes the time zone. text/html 2025-02-26T18:23:12+00:00 https://sqlines.com/sql-server-to-postgresql/dateadd?rev=1740594192&do=diff Although PostgreSQL does not provide a DATEADD function similar to SQL Server, you can use datetime arithmetic with interval literals to get the same results. SQL Server: -- Add 1 day to the current date November 21, 2012 SELECT DATEADD(day, 1, GETDATE()); /* 2012-11-22 17:22:01.423 */ -- Add 1 quarter SELECT DATEADD(qq, 1, '2025-02-26'); /* 2025-05-26 00:00:00.000 */ text/html 2024-11-18T09:32:49+00:00 https://sqlines.com/sql-server-to-postgresql/datediff?rev=1731922369&do=diff In SQL Server, the DATEDIFF and DATEDIFF_BIG functions get the datetime difference in specified units. In PostgreSQL, you can use EXTRACT function with various INTERVAL expressions. It is very important to note that DATEDIFF does not return datetime difference in full units, so the conversion to PostgreSQL with exactly the same results requires some adjustments, see examples below. text/html 2024-11-18T09:35:47+00:00 https://sqlines.com/sql-server-to-postgresql/datediff_big?rev=1731922547&do=diff In SQL Server, the DATEDIFF_BIG function is similar to the DATEDIFF function but returns the datetime difference as BIGINT. For more information, see DATEDIFF and DATEDIFF_BIG Functions - SQL Server to PostgreSQL Migration. text/html 2025-08-04T19:25:18+00:00 https://sqlines.com/sql-server-to-postgresql/datename?rev=1754335518&do=diff In Microsoft SQL Server (MS SQL), the DATENAME function extracts the specified unit (a date part such as year, month, day etc.) from a datetime value as string. In PostgreSQL, you can use the TO_CHAR function with the specific format type. SQL Server: text/html 2025-08-04T19:36:56+00:00 https://sqlines.com/sql-server-to-postgresql/datepart?rev=1754336216&do=diff In Microsoft SQL Server (MS SQL) you can use DATEPART function to extract the specified unit (a date part such as year, month, day etc.) from a datetime value as number. In PostgreSQL you can use DATE_PART function but note that unit can be different and specified as a string literal. text/html 2024-10-19T19:13:51+00:00 https://sqlines.com/sql-server-to-postgresql/datetime?rev=1729365231&do=diff In SQL Server, DATETIME data type stores date and time with milliseconds accuracy. In PostgreSQL, you can use TIMESTAMP(3) data type. SQL Server: -- Sample table with DATETIME with milliseconds accuracy CREATE TABLE specs ( item VARCHAR(30), created_dt DATETIME ); text/html 2024-10-21T10:17:14+00:00 https://sqlines.com/sql-server-to-postgresql/datetime2?rev=1729505834&do=diff In SQL Server, DATETIME2(p) data type stores date and time with fractional seconds. In PostgreSQL, you can use TIMESTAMP(p) data type. SQL Server: -- Sample table with DATETIME2 with milliseconds accuracy CREATE TABLE specs ( item VARCHAR(30), created_dt DATETIME2(3) ); text/html 2024-11-15T10:11:25+00:00 https://sqlines.com/sql-server-to-postgresql/datetimeoffset?rev=1731665485&do=diff In SQL Server, the DATETIMEOFFSET(p) data type stores date and time with fractional seconds and time zone. In PostgreSQL, you can use the TIMESTAMP(p) WITH TIME ZONE data type. SQL Server: -- Sample table with DATETIMEOFFSET with milliseconds accuracy CREATE TABLE specs ( item VARCHAR(30), created_dt DATETIMEOFFSET(3) ); -- Insert a sample row INSERT INTO specs VALUES ('A', '2024-11-15T13:07:35.123+01:00'); text/html 2025-04-05T13:07:50+00:00 https://sqlines.com/sql-server-to-postgresql/datetrunc?rev=1743858470&do=diff In Microsoft SQL Server, the DATETRUNC function (available since SQL Server 2022) truncates a datetime value to the specified unit. In PostgreSQL, you can use the DATE_TRUNC function but note that the corresponding unit can be different and specified as a string literal. text/html 2022-12-27T23:04:27+00:00 https://sqlines.com/sql-server-to-postgresql/day?rev=1672182267&do=diff In SQL Server you can use DAY function to get the day (an integer number) of the specified datetime. In PostgreSQL you have to use EXTRACT(DAY FROM datetime) function. SQL Server: -- Get the day SELECT DAY('2022-12-28'); # 28 PostgreSQL: text/html 2024-11-04T12:05:16+00:00 https://sqlines.com/sql-server-to-postgresql/declare_cursor?rev=1730721916&do=diff In SQL Server, the DECLARE CURSOR statement declares a cursor variable and assigns a query to it. In PostgreSQL, you can also use a CURSOR variable defined in the declaration block. Consider a sample table: CREATE TABLE colors (name VARCHAR(30)); INSERT INTO colors VALUES ('Green'); INSERT INTO colors VALUES ('Black'); INSERT INTO colors VALUES ('Red'); text/html 2024-10-22T14:14:52+00:00 https://sqlines.com/sql-server-to-postgresql/degrees?rev=1729606492&do=diff SQL Server and PostgreSQL provide DEGREES function that converts radians to degrees. SQL Server: -- Get degrees SELECT DEGREES(1.7); # 97.402825172239943186 PostgreSQL: -- Get degrees SELECT DEGREES(1.7); # 97.40282517223994 For more information, see SQL Server to PostgreSQL Migration. text/html 2024-11-05T13:19:50+00:00 https://sqlines.com/sql-server-to-postgresql/drop_table?rev=1730812790&do=diff In SQL Server and PostgreSQL, the DROP TABLE statement removes the table. SQL Server: -- Drop table if exists DROP TABLE IF EXISTS tab1; /* Commands completed successfully. */ PostgreSQL: -- Drop table if exists DROP TABLE IF EXISTS tab1; /* NOTICE: table "tab1" does not exist, skipping */ text/html 2025-05-16T14:03:18+00:00 https://sqlines.com/sql-server-to-postgresql/error_message?rev=1747404198&do=diff In SQL Server, the ERROR_MESSAGE function returns the error text of an exception and can only be used within a CATCH block. In PostgreSQL, you can use the SQLERRM variable, which contains the error message of the most recent exception. SQL Server: text/html 2025-05-16T14:21:47+00:00 https://sqlines.com/sql-server-to-postgresql/error_number?rev=1747405307&do=diff In SQL Server, the ERROR_NUMBER function returns the error code of an exception and can only be used within a CATCH block. In PostgreSQL, you can use the SQLSTATE variable, which contains the error code of the most recent exception. Note that the error code can be different for the same error in SQL Server and PostgreSQL. text/html 2024-12-11T14:31:36+00:00 https://sqlines.com/sql-server-to-postgresql/exec_proc?rev=1733927496&do=diff In SQL Server, the EXEC (or EXECUTE) statement can be used to execute a stored procedure. In PostgreSQL, you can use the CALL statement. SQL Server: -- Sample procedure CREATE PROCEDURE sp1 @param1 VARCHAR(30), @param2 VARCHAR(30) AS PRINT @param1 + ', ' + @param2 GO -- Executing the procedure EXEC sp1 'Hello', 'world' /* Result: Hello, world */ -- Using named parameters EXEC sp1 @param1 = 'Hello', @param2 = 'world' /* Result: Hello, world */ -- Named parameter… text/html 2024-07-29T10:31:08+00:00 https://sqlines.com/sql-server-to-postgresql/exec_resultset?rev=1722249068&do=diff In SQL Server stored procedure you can retun a dynamic result set to the caller as follwos: SQL Server: -- Return a dynamic result set from stored procedure CREATE PROCEDURE sp_getColors AS EXECUTE(' SELECT ''Red'' AS color UNION ALL SELECT ''Blue'' AS color UNION ALL SELECT ''Green'' AS color') GO text/html 2025-02-23T08:17:33+00:00 https://sqlines.com/sql-server-to-postgresql/fetch_status?rev=1740298653&do=diff In SQL Server, the @@FETCH_STATUS variable holds the status of the last FETCH statement. In PostgreSQL, you can use the FOUND boolean variable. SQL Server PostgreSQL @@FETCH_STATUS = 0 Fetch is successful FOUND @@FETCH_STATUS <> 0 Fetch failed or no more rows found NOT FOUND @@FETCH_STATUS = -1 Consider a sample table: text/html 2024-10-21T07:46:23+00:00 https://sqlines.com/sql-server-to-postgresql/float?rev=1729496783&do=diff In SQL Server, FLOAT(p) data type stores approximate single or double precision floating point numbers. In PostgreSQL you can use REAL and DOUBLE PRECISION data types. SQL Server: CREATE TABLE specs ( value FLOAT, value2 FLOAT(24), value3 FLOAT(53) ); text/html 2024-12-12T09:31:42+00:00 https://sqlines.com/sql-server-to-postgresql/floor?rev=1733995902&do=diff In SQL Server and PostgreSQL, the FLOOR function rounds down the specified number to the nearest integer value. SQL Server and PostgreSQL: -- Round to the nearest smaller integer SELECT FLOOR(123.4); /* 123 */ -- Still rounds to smaller integer even if it is closer to larger integer SELECT FLOOR(123.9); /* 123 */ text/html 2024-11-05T09:22:05+00:00 https://sqlines.com/sql-server-to-postgresql/func_default?rev=1730798525&do=diff In SQL Server, you can define a user-defined function with default parameter values, but you must specify DEFAULT keyword if you skip the parameter values when you call the function. In PostgreSQL, you can omit the parameter to apply its default value, and the DEFAULT keyword is not allowed. text/html 2025-01-12T14:43:57+00:00 https://sqlines.com/sql-server-to-postgresql/function_returns_table?rev=1736693037&do=diff In SQL Server, a user-defined function can return a table (it is called table-valued function). PostgreSQL also allows you to return a table from a function: SQL Server: -- Table-valued function CREATE FUNCTION fn_get_cities() RETURNS TABLE AS RETURN ( SELECT 'Boston' as city UNION ALL SELECT 'Barcelona' as city UNION ALL SELECT 'Munich' as city ) text/html 2024-10-21T09:12:41+00:00 https://sqlines.com/sql-server-to-postgresql/geography?rev=1729501961&do=diff SQL Server and PostgreSQL provide GEOGRAPHY data type that stores spatial data. Note that you have to install and enable PostGIS extension to use GEOGRAPHY data type in PostgreSQL. SQL Server and PostgreSQL: -- Sample table CREATE TABLE geo ( geodata GEOGRAPHY ); -- Inserting sample value INSERT INTO geo VALUES ('POLYGON ((-72 -21, -70 -18, -69 -17, -72 -21))'); text/html 2024-10-21T09:14:02+00:00 https://sqlines.com/sql-server-to-postgresql/geometry?rev=1729502042&do=diff SQL Server and PostgreSQL provide GEOMETRY data type that stores spatial data. Note that you have to install and enable PostGIS extension to use GEOMETRY data type in PostgreSQL. SQL Server and PostgreSQL: -- Sample table CREATE TABLE geo ( geodata GEOMETRY ); -- Inserting sample value INSERT INTO geo VALUES ('POLYGON ((-72 -21, -70 -18, -69 -17, -72 -21))'); text/html 2024-12-11T09:37:21+00:00 https://sqlines.com/sql-server-to-postgresql/getdate?rev=1733909841&do=diff In SQL Server, the GETDATE function returns the current date and time with milliseconds precision without the time zone of the current database instance. In PostgreSQL you can use the NOW function but it includes time zone. SQL Server: -- Get the current date and time with milliseconds precision SELECT GETDATE(); # 2024-07-28 13:13:11.640 text/html 2024-11-18T16:57:13+00:00 https://sqlines.com/sql-server-to-postgresql/grouping_id?rev=1731949033&do=diff In SQL Server, the GROUPING_ID function returns the GROUPING SETS level of GROUP BY using a bit mask with 1 value in the corresponding bit position if the column is not used in GROUP BY for the resulting aggregated row. In PostgreSQL, you can use the GROUPING function. text/html 2024-11-18T16:45:03+00:00 https://sqlines.com/sql-server-to-postgresql/grouping_sets?rev=1731948303&do=diff In SQL Server and PostgreSQL, the GROUPING SETS clause of the GROUP BY clause of a SELECT statement allows you to define one or more GROUP BY expressions. Consider a sample table: CREATE TABLE orders ( product VARCHAR(30), category VARCHAR(30), items INT ); -- Sample rows INSERT INTO orders VALUES ('Apple', 'Fruits', 1); INSERT INTO orders VALUES ('Orange', 'Fruits', 3); INSERT INTO orders VALUES ('Apple', 'Fruits', 7); INSERT INTO orders VALUES ('Milk', '… text/html 2025-06-10T15:52:39+00:00 https://sqlines.com/sql-server-to-postgresql/hashbytes?rev=1749570759&do=diff In SQL Server, the HASHBYTES function computes a binary hash of the input data using the specified algorithm (such as MD5, SHA1, SHA2_256, or SHA2_512). In PostgreSQL, you can use the MD5, SHA256, SHA512 and other functions. SQL Server: -- Get SHA-512 hash SELECT HASHBYTES('SHA2_512', 'abc'); /* 0xDDAF35A193617ABACC417349AE2... */ -- Get SHA-256 hash SELECT HASHBYTES('SHA2_256', 'abc'); /* 0xBA7816BF8F01CFEA414140DE5DA... */ -- Get MD5 hash SELECT HASHBYTES('MD5', 'abc'… text/html 2022-02-19T20:18:17+00:00 https://sqlines.com/sql-server-to-postgresql/identity?rev=1645301897&do=diff The IDENTITY property allows you to automatically assign incremental integer values to a column. In PostgreSQL you can use the GENERATED AS IDENTITY property: SQL Server: -- Table with identity value starting at 7 and step 3 CREATE TABLE colors ( id INT IDENTITY(7, 3), name VARCHAR(10) ); -- Insert a few sample rows INSERT INTO colors (name) VALUES ('blue'); INSERT INTO colors (name) VALUES ('white'); INSERT INTO colors (name) VALUES ('green'); -- id was auto… text/html 2024-10-22T19:40:25+00:00 https://sqlines.com/sql-server-to-postgresql/iif?rev=1729626025&do=diff In SQL Server, the IIF function returns an expression based on the condition. In PostreSQL, you can use the CASE expression. SQL Server: -- If condition is true return the first expression, otherwise return the second expression SELECT IIF(2 > 1, 'Yes', 'No') # Yes text/html 2024-10-21T12:16:26+00:00 https://sqlines.com/sql-server-to-postgresql/isnull?rev=1729512986&do=diff In SQL Server you can use ISNULL function to replace NULL values. In PostgreSQL you can use COALESCE function. SQL Server: -- Replace NULL with N/A string SELECT ISNULL(NULL, 'N/A'); # N/A PostgreSQL: -- Replace NULL with N/A string SELECT COALESCE(NULL, 'N/A'); # N/A text/html 2024-10-22T20:05:09+00:00 https://sqlines.com/sql-server-to-postgresql/left?rev=1729627509&do=diff In SQL Server and PostgreSQL, the LEFT function returns the specified number of characters from the left part of the string. SQL Server: -- Return 5 left characters SELECT LEFT('Hello, world!', 5); # Hello PostgreSQL: -- Return 5 left characters SELECT LEFT('Hello, world!', 5); # Hello text/html 2024-10-22T20:07:36+00:00 https://sqlines.com/sql-server-to-postgresql/len?rev=1729627656&do=diff In SQL Server, the LEN function returns the length of the string in characters excluding trailing spaces. In PostgreSQL, you can use the LENGTH function that includes the traling blanks, so you can also apply the RTRIM function if you want to exclude them. text/html 2022-09-16T20:33:31+00:00 https://sqlines.com/sql-server-to-postgresql/month?rev=1663360411&do=diff In SQL Server, you can use MONTH function to get the month (an integer number 1-12) of the specified datetime. In PostgreSQL, you have to use EXTRACT(MONTH FROM datetime) function. SQL Server: -- Get the month SELECT MONTH('2022-09-16'); # 9 text/html 2024-11-15T12:18:27+00:00 https://sqlines.com/sql-server-to-postgresql/nchar_function?rev=1731673107&do=diff In SQL Server, the NCHAR function converts a Unicode code point to to the corresponding Unicode character value. In PostgreSQL you can use the CHR function but note that CHR(0) is not allowed. SQL Server: -- Get Euro sign SELECT NCHAR(0x20AC); /* Result: € */ -- Return <NUL> character (Unicode code point U+0000) SELECT NCHAR(0); /* Result: <NUL> character */ text/html 2024-10-22T20:23:30+00:00 https://sqlines.com/sql-server-to-postgresql/nullif?rev=1729628610&do=diff In SQL Server and PostgreSQL the NULLIF function returns NULL is two expressions are equal otherwise it returns the first expression SQL Server: -- Return NULL if expressions are equal SELECT NULLIF(1, 1); # NULL -- Returns the first expression SELECT NULLIF(1, 3); # 1 text/html 2026-01-20T20:33:25+00:00 https://sqlines.com/sql-server-to-postgresql/nvarchar?rev=1768941205&do=diff In SQL Server, the NVARCHAR(n) data type stores variable-length Unicode UCS-2 or UTF-16 character strings up to n byte-pairs with the maximum of 4,000 byte-pairs or 2 GB if NVARCHAR(max) is specified. In PostgreSQL, you can use the VARCHAR(n) that can store up to 1 GB (bytes). text/html 2024-11-26T17:44:15+00:00 https://sqlines.com/sql-server-to-postgresql/offset_fetch_first?rev=1732643055&do=diff In SQL Server 2012 or later, you can use the OFFSET and FETCH FIRST (or NEXT) clause to limit the rows returned by the query. In PostgreSQL you can use the OFFSET and LIMIT clauses. Consider the following sample table: CREATE TABLE products (name VARCHAR(30)); INSERT INTO products VALUES ('Apple'); INSERT INTO products VALUES ('Melon'); INSERT INTO products VALUES ('Orange'); text/html 2024-11-16T17:31:03+00:00 https://sqlines.com/sql-server-to-postgresql/outer_apply?rev=1731778263&do=diff SQL Server OUTER APPLY clause allows you to perform a correlated outer join (lateral join) often using a table-valued function or subquery. Correlated means that the right operand of a join clause contains a reference to the left operand. In PostgreSQL you can use LEFT OUTER JOIN LATERAL ON TRUE clause. text/html 2024-10-23T08:14:13+00:00 https://sqlines.com/sql-server-to-postgresql/patindex?rev=1729671253&do=diff In SQL Server, the PATINDEX function returns the position of the first occurrence of the pattern in the string. Depending on the database collation, the pattern search can be case-insensitive. In PostgreSQL, you can use the REGEXP_INSTR function but note that the order of parameters and the pattern syntax are different. text/html 2025-02-24T14:57:28+00:00 https://sqlines.com/sql-server-to-postgresql/pivot?rev=1740409048&do=diff In SQL Server, you can use the PIVOT clause to rotate (pivot) column rows into columns. In PostgreSQL, you can use the CASE expressions. Consider a sample table: CREATE TABLE orders ( product VARCHAR(30), items INT ); -- Sample rows INSERT INTO orders VALUES ('Apple', 1); INSERT INTO orders VALUES ('Orange', 3); INSERT INTO orders VALUES ('Apple', 7); INSERT INTO orders VALUES ('Orange', 11); text/html 2024-11-26T18:57:08+00:00 https://sqlines.com/sql-server-to-postgresql/power?rev=1732647428&do=diff SQL Server and PostgreSQL provide the POWER function that raises the number to the specified power. SQL Server and PostgreSQL: -- Raise 2 to power 3 SELECT POWER(2, 3); /* 8 */ For more information, see SQL Server to PostgreSQL Migration. text/html 2024-10-22T14:58:33+00:00 https://sqlines.com/sql-server-to-postgresql/radians?rev=1729609113&do=diff SQL Server and PostgreSQL provide RADIANS function that converts degrees to radians. SQL Server: -- Get radians SELECT RADIANS(90.0); # 1.570796326794896558 -- Note that rounded integer is returned if operand is an integer (!) SELECT RADIANS(90); # 1 text/html 2024-10-23T09:21:12+00:00 https://sqlines.com/sql-server-to-postgresql/replicate?rev=1729675272&do=diff In SQL Server, the REPLICATE function repeats the string the specified number of times. In PostgreSQL, you can use the REPEAT function. SQL Server: -- Repeat 'ab' 3 times SELECT REPLICATE('ab', 3); # ababab PostgreSQL: -- Repeat 'ab' 3 times SELECT REPEAT('ab', 3); # ababab text/html 2022-09-20T11:51:15+00:00 https://sqlines.com/sql-server-to-postgresql/return_resultset?rev=1663674675&do=diff SQL Server allows a stored procedure to return a result set to the caller by executing a standalone SELECT statement: SQL Server: -- Return a result set from stored procedure CREATE PROCEDURE sp_getCities AS SELECT 'Boston' as city UNION ALL SELECT 'Barcelona' as city UNION ALL SELECT 'Munich' as city GO text/html 2024-10-23T09:35:59+00:00 https://sqlines.com/sql-server-to-postgresql/reverse?rev=1729676159&do=diff In SQL Server and PostgreSQL, the REVERSE function returns the string in the reverse order of characters. SQL Server: -- Reverse string SELECT REVERSE('abc'); # cba PostgreSQL: -- Reverse string SELECT REVERSE('abc'); # cba For more information, see SQL Server to PostgreSQL Migration. text/html 2024-12-11T20:56:03+00:00 https://sqlines.com/sql-server-to-postgresql/rollback?rev=1733950563&do=diff In SQL Server and PostgreSQL, the ROLLBACK statement rolls back the current transaction. SQL Server and PostgreSQL: -- Sample table CREATE TABLE colors (name VARCHAR(30)); BEGIN TRANSACTION; -- Insert a sample row INSERT INTO colors VALUES('Black'); ROLLBACK TRANSACTION; SELECT * FROM colors; /* No rows returned */ text/html 2024-11-15T16:11:25+00:00 https://sqlines.com/sql-server-to-postgresql/round?rev=1731687085&do=diff In SQL Server, the ROUND function rounds the number to the nearest value with the specified precision, or truncates the number to the specified precision. In PostgreSQL you can use the ROUND and TRUNC functions. SQL Server: -- Round to 1 decimal digit (rounded to lower value as 4 <= 5) SELECT ROUND(123.34, 1); /* 123.30 */ -- Round to 1 decimal digit (rounded to upper value as 6 > 5) SELECT ROUND(123.36, 1); /* 123.40 */ -- When the 3rd parameter is non-zero then the… text/html 2024-11-15T20:54:30+00:00 https://sqlines.com/sql-server-to-postgresql/row_number?rev=1731704070&do=diff In SQL Server and PostgreSQL, the ROW_NUMBER function is a window function that numbers the output rows. Consider the following sample table: CREATE TABLE colors (name VARCHAR(30), category CHAR(1)); -- Insert sample rows INSERT INTO colors VALUES ('Green', 'A'); INSERT INTO colors VALUES ('White', 'B'); INSERT INTO colors VALUES ('Black', 'A'); INSERT INTO colors VALUES ('Red', 'B'); text/html 2024-11-01T13:37:11+00:00 https://sqlines.com/sql-server-to-postgresql/scope_identity?rev=1730468231&do=diff In SQL Server, the SCOPE_IDENTITY function returns the last inserted identity in the current scope (current stored procedure, trigger, function or batch). In PostgreSQL, you can use the RETURNING clause of the INSERT statement. SQL Server: -- Sample table CREATE TABLE colors ( id INT IDENTITY(5,1), name VARCHAR(30) ); DECLARE @last_id INT; INSERT INTO colors (name) VALUES ('White'); -- Get the inserted ID SET @last_id = SCOPE_IDENTITY(); PRINT @last_id; #… text/html 2022-12-11T16:23:51+00:00 https://sqlines.com/sql-server-to-postgresql/select_alias?rev=1670775831&do=diff In SQL Server you can use SELECT alias = expression syntax to define the column alias (do not confuse with SELECT @variable = expression variable assignment syntax). In PostgreSQL you have to use standard SELECT expression [AS] alias syntax to define the column aliases. text/html 2026-02-03T14:49:08+00:00 https://sqlines.com/sql-server-to-postgresql/select_for_xml_path?rev=1770130148&do=diff In SQL Server, SELECT FOR XML PATH statement allows you to convert a multi-row multi-column query result to a single-row single-column XML value. See also STUFF and SELECT FOR XML PATH string aggregation conversion. Consider a sampe table: CREATE TABLE colors (name VARCHAR(30), category CHAR(1)); -- Insert sample rows INSERT INTO colors VALUES ('Green', 'G'); INSERT INTO colors VALUES ('White', 'W'); INSERT INTO colors VALUES ('Black', 'B'); text/html 2024-11-04T16:20:11+00:00 https://sqlines.com/sql-server-to-postgresql/select_from_function?rev=1730737211&do=diff In SQL Server and PostgreSQL, you can query a table-valued function in the FROM clause of the SELECT statement. SQL Server: -- A table-valued function CREATE FUNCTION getColors() RETURNS TABLE AS RETURN ( SELECT 'Red' AS name, 'R' AS category UNION ALL SELECT 'Blue' AS name, 'B' AS category UNION ALL SELECT 'Green' AS name, 'G' AS category ) GO -- Use the function in FROM SELECT * FROM getColors(); -- Using alias with AS keyword SELECT t.name, t.c… text/html 2022-09-13T13:38:50+00:00 https://sqlines.com/sql-server-to-postgresql/select_into_temp?rev=1663076330&do=diff In SQL Server you can use a SELECT INTO statement to create a temporary table based on the query results. In PostgreSQL you can also use SELECT INTO TEMPORARY statement, but only as a standalone SQL statement (not PL/pgSQL). If you need to create a temporary table in PL/pgSQL function or procedure you have to use CREATE TEMPORARY TABLE AS SELECT statement. text/html 2024-03-29T10:51:51+00:00 https://sqlines.com/sql-server-to-postgresql/select_xml_nodes?rev=1711709511&do=diff In SQL Server you can use a NODES method of XML data type to convert XML items into table rows. In PostgreSQL you can use XPATH and UNNEST functions. Note that unlike SQL Server, PostgreSQL XML data must be a well formed XML document. In particular, it must have a single root node element. text/html 2024-12-11T08:49:08+00:00 https://sqlines.com/sql-server-to-postgresql/serverproperty?rev=1733906948&do=diff In SQL Server, the SERVERPROPERTY function returns the current value for the specified server property. In PostgreSQL, you have to use a specific function or expression for every individual property. Also, as most properties are SQL Server specific, they maybe irrelevant in PostgreSQL. text/html 2024-10-31T17:48:47+00:00 https://sqlines.com/sql-server-to-postgresql/set?rev=1730396927&do=diff In SQL Server, the SET statement assigns a value to a variable. In PostgreSQL, you can use := operator. SQL Server: DECLARE @var VARCHAR(30); SET @var = 'Hello, world!' PostgreSQL: DO $$ DECLARE var VARCHAR(30); BEGIN var := 'Hello, world!'; END$$; text/html 2024-10-22T15:24:22+00:00 https://sqlines.com/sql-server-to-postgresql/sin?rev=1729610662&do=diff SQL Server and PostgreSQL provide SIN function that returns the trigonometric sine. SQL Server: -- Get sine SELECT SIN(35.17); # -0.574899086600994 PostgreSQL: -- Get sine SELECT SIN(35.17); # -0.5748990866009935 For more information, see SQL Server to PostgreSQL Migration. text/html 2026-01-14T13:43:29+00:00 https://sqlines.com/sql-server-to-postgresql/smalldatetime?rev=1768398209&do=diff In SQL Server, the SMALLDATETIME data type stores date and time values with minute-level accuracy (seconds are always 00). In PostgreSQL, you can use the TIMESTAMP(0) data type along with a DATE_TRUNC expression to set the seconds to 00. SQL Server: text/html 2024-11-26T20:34:33+00:00 https://sqlines.com/sql-server-to-postgresql/stdev?rev=1732653273&do=diff In SQL Server, the STDEV is aggregte and analytic window function that calculates the standard deviation. In PostgreSQL, you can use the STDDEV function. Consider the following sample table: CREATE TABLE products (name VARCHAR(30), category CHAR(1), items INT); -- Insert sample rows INSERT INTO products VALUES ('Apple', 'A', 5); INSERT INTO products VALUES ('Orange', 'A', 10); INSERT INTO products VALUES ('Melon', 'B', 15); text/html 2025-02-21T13:13:28+00:00 https://sqlines.com/sql-server-to-postgresql/str?rev=1740143608&do=diff In SQL Server, you can use the STR function to convert a decimal number to string specifying the total number of digits as well as fractional part and its rounding. In PostgreSQL, you can use the TO_CHAR function with the corresponding format. SQL Server: text/html 2024-11-18T12:40:10+00:00 https://sqlines.com/sql-server-to-postgresql/string_agg?rev=1731933610&do=diff In SQL Server and PostgreSQL, the STRING_AGG function concatenates non-NULL string values from multiple rows into a single-row single-column value using the specified delimiter. Note that syntax of the ORDER BY clause differs in SQL Server and PostgreSQL. text/html 2025-06-10T14:38:22+00:00 https://sqlines.com/sql-server-to-postgresql/string_split?rev=1749566302&do=diff In SQL Server, the STRING_SPLIT is a table-valued function that splits a string into a set of rows based on the specified separator. In PostgreSQL, you can use the STRING_TO_TABLE, or UNNEST and STRING_TO_ARRAY functions. SQL Server: -- Split string using comma (,) as separator SELECT * FROM STRING_SPLIT('a,b,c', ','); text/html 2026-02-03T13:35:14+00:00 https://sqlines.com/sql-server-to-postgresql/stuff?rev=1770125714&do=diff In SQL Server, the STUFF function inserts a string into another string at a specified position, deleting a specified number of characters. In PostgreSQL you can use the INSERT function. See also STUFF and SELECT FOR XML PATH conversion. SQL Server: text/html 2026-01-27T09:28:31+00:00 https://sqlines.com/sql-server-to-postgresql/stuff_select_for_xml?rev=1769506111&do=diff In SQL Server, STUFF with SELECT FOR XML PATH statement is widely used to concatenate strings from multiple rows into a single row value. Consider a sample table: CREATE TABLE colors (name VARCHAR(30)); -- Insert sample rows INSERT INTO colors VALUES ('Green'); INSERT INTO colors VALUES ('White'); INSERT INTO colors VALUES ('Blue'); text/html 2024-10-23T09:46:56+00:00 https://sqlines.com/sql-server-to-postgresql/substring?rev=1729676816&do=diff In SQL Server and PostgreSQL, the SUBSTRING function returns the substring. SQL Server: -- Get substring starting at position 2 and length 3 SELECT SUBSTRING('abcde', 2, 3); # bcd PostgreSQL: -- Get substring starting at position 2 and length 3 SELECT SUBSTRING('abcde', 2, 3); # bcd text/html 2024-12-11T09:40:17+00:00 https://sqlines.com/sql-server-to-postgresql/sysdatetimeoffset?rev=1733910017&do=diff In SQL Server, the SYSDATETIMEOFFSET function returns the current date and time with 100 nanoseconds precision with the time zone of the current database instance. In PostgreSQL you can use the NOW function that also includes time zone. SQL Server: text/html 2024-10-22T15:29:11+00:00 https://sqlines.com/sql-server-to-postgresql/tan?rev=1729610951&do=diff SQL Server and PostgreSQL provide TAN function that returns the trigonometric tangent. SQL Server: -- Get tangent SELECT TAN(35.17); # 0.702617939267074 PostgreSQL: -- Get tangent SELECT TAN(35.17); # 0.7026179392670738 For more information, see SQL Server to PostgreSQL Migration. text/html 2024-11-15T10:41:44+00:00 https://sqlines.com/sql-server-to-postgresql/tinyint?rev=1731667304&do=diff In SQL Server, the TINYINT data type stores 8-bit integer values from 0 to 255. In PostgreSQL, you can use the SMALLINT data type, a 16-bit data type that can store integers from -32,768 to 32,767. SQL Server: -- Sample table CREATE TABLE specs ( item VARCHAR(30), cnt TINYINT ); text/html 2024-11-05T12:53:58+00:00 https://sqlines.com/sql-server-to-postgresql/true_false?rev=1730811238&do=diff In SQL Server, you can use 'TRUE' and 'FALSE' string constants with the BIT data type. TRUE and FALSE keywords use is also allowed in some contexts but it is very limited (see below). In PostgreSQL, you can use 'TRUE' and 'FALSE' string constants as well as TRUE and FALSE keywords to represent boolean values. text/html 2024-11-05T13:48:24+00:00 https://sqlines.com/sql-server-to-postgresql/try_catch?rev=1730814504&do=diff In SQL Server, you can use BEGIN TRY and BEGIN CATCH blocks to handle exceptions. In PostgreSQL, you can use the EXCEPTION section of BEGIN-END block. Consider a sample table with a unique constraint: -- Sample table CREATE TABLE colors (name VARCHAR(30) PRIMARY KEY NOT NULL); text/html 2024-10-21T10:09:23+00:00 https://sqlines.com/sql-server-to-postgresql/uniqueidentifier?rev=1729505363&do=diff SQL Server UNIQUEIDENTIFIER data type stores Universally Unique Identifiers (UUID) or GUID data. In PostgreSQL you can use UUID data type. SQL Server: -- Sample table CREATE TABLE specs ( data UNIQUEIDENTIFIER ); -- Inserting sample value INSERT INTO specs VALUES ('7F9619FF-8B86-D011-B42D-00C04FC964FF'); text/html 2025-05-16T14:54:28+00:00 https://sqlines.com/sql-server-to-postgresql/update?rev=1747407268&do=diff In SQL Server and PostgreSQL, the UPDATE statement updates rows in the table, however, there are some differences in syntax, clauses and restrictions. Qualified Columns in SET Clause SQL Server allows you to use the qualified columns i.e. table.column in the SET and WHERE clauses: text/html 2024-10-18T13:55:54+00:00 https://sqlines.com/sql-server-to-postgresql/varchar?rev=1729259754&do=diff In SQL Server, VARCHAR(n) data type stores variable-length character strings up to n bytes with the maximum of 8,000 bytes or 2 GB if VARCHAR(max) is specified. In PostgreSQL, you can use VARCHAR(n) that can store up to 1 GB (bytes). SQL Server: text/html 2025-05-21T12:33:35+00:00 https://sqlines.com/sql-server-to-postgresql/with_cube?rev=1747830815&do=diff In SQL Server, you can use the legacy WITH CUBE clause in the GROUP BY clause of a SELECT statement. In PostgreSQL, you can use the GROUP BY CUBE clause, which is also supported by SQL Server and is ISO-compliant. Consider a sample table: CREATE TABLE orders ( product VARCHAR(30), category VARCHAR(30), items INT ); -- Sample rows INSERT INTO orders VALUES ('Apple', 'Fruits', 1); INSERT INTO orders VALUES ('Orange', 'Fruits', 3); INSERT INTO orders VALUES … text/html 2025-05-21T12:33:52+00:00 https://sqlines.com/sql-server-to-postgresql/with_rollup?rev=1747830832&do=diff In SQL Server, you can use the legacy WITH ROLLUP clause in the GROUP BY clause of a SELECT statement. In PostgreSQL, you can use the GROUP BY ROLLUP clause, which is also supported by SQL Server and is ISO-compliant. Consider a sample table: CREATE TABLE orders ( product VARCHAR(30), category VARCHAR(30), items INT ); -- Sample rows INSERT INTO orders VALUES ('Apple', 'Fruits', 1); INSERT INTO orders VALUES ('Orange', 'Fruits', 3); INSERT INTO orders VAL… text/html 2024-11-05T15:44:51+00:00 https://sqlines.com/sql-server-to-postgresql/xact_state?rev=1730821491&do=diff In SQL Server, the XACT_STATE function indicates if a transaction is running. It is typically used to check if the procedure or batch can execute a ROLLBACK or COMMIT statements. In PostgreSQL, you can use the PG_CURRENT_XACT_ID_IF_ASSIGNED function but note that the return valus are different and you may need to change the conditional logic. text/html 2024-11-01T09:08:08+00:00 https://sqlines.com/sql-server-to-postgresql/xml?rev=1730452088&do=diff In SQL Server and PostgreSQL, the XML data type stores XML data. SQL Server: -- Sample table CREATE TABLE specs ( data XML ); -- Inserting sample value INSERT INTO specs VALUES ('<item>Abc</item>'); PostgreSQL: -- Sample table CREATE TABLE specs ( data XML ); -- Inserting sample value INSERT INTO specs VALUES ('<item>Abc</item>'); text/html 2022-09-16T20:42:03+00:00 https://sqlines.com/sql-server-to-postgresql/year?rev=1663360923&do=diff In SQL Server, you can use YEAR function to get the year (an integer number) of the specified datetime. In PostgreSQL, you have to use EXTRACT(YEAR FROM datetime) function. SQL Server: -- Get the year SELECT YEAR('2022-09-16'); # 2022 PostgreSQL: