Sybase ASE to PostgreSQL Migration

SQLines tools help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from SAP Sybase Adaptive Server Enterprise (Sybase ASE) to PostgreSQL.

SQLines SQL Converter - SQL Scripts Conversion and Assessment tool. Try Online
SQLines Data - Data Transfer, Schema Migration and Validation tool

Databases:

Sybase Adaptive Server Enterprise 16, 15, 12.x and 11.x
PostgreSQL 17.x, 16.x, 15.x, 14.x, 13.x, 12.x, 11.x, 10.x and 9.x

Migration Reference

SQL Language Elements

Converting SQL language elements:

	Sybase ASE		PostgreSQL
1	string1 + string2	String concatenation	string1 \|\| string2
2	exp != NULL	Non SQL-standard check for NOT NULL	exp IS NOT NULL
	exp <> NULL
	exp = NULL	Non SQL-standard check for NULL	exp IS NULL
3	@@ROWCOUNT	Get the number of affected rows	ROW_COUNT

Data Types

Converting character data types:

	Sybase ASE		PostgreSQL
1	CHAR(n)	Fixed-length string	CHAR(n)
2	NCHAR(n)	Fixed-length national character string	CHAR(n)
3	NVARCHAR(n)	Variable-length national character string	VARCHAR(n)
4	TEXT	Variable-length character data, ⇐ 2 GB	TEXT
5	UNICHAR(n)	Fixed-length Unicode UTF-16 string	CHAR(n)
6	UNITEXT	Variable-length Unicode UTF-16 data, ⇐ 1 GB	TEXT
7	UNIVARCHAR(n)	Variable-length Unicode UTF-16 string	VARCHAR(n)
8	VARCHAR(n)	Variable-length string	VARCHAR(n)

Converting other data types:

	Sybase ASE		PostgreSQL
1	BIGDATETIME	Date and time with fraction	TIMESTAMP
2	BIGINT	64-bit integer	BIGINT
3	BIGTIME	Time (Hour, minute, second and fraction)	TIME
4	BINARY(n)	Fixed-length binary data, 1 ⇐ n ⇐ pagesize	BYTEA
5	BIT	0 or 1; NULL is not allowed	BOOLEAN
6	DATE	Date (year, month and day)	DATE
7	DATETIME	Date and time with fraction	TIMESTAMP
8	DECIMAL(p,s), DEC(p,s)	Fixed-point number	DECIMAL(p,s), DEC(p,s)
9	DOUBLE PRECISION	Double-precision floating-point number	DOUBLE PRECISION
10	FLOAT(p)	Floating-point number	DOUBLE PRECISION
11	IMAGE	Variable-length binary data, ⇐ 2G	BYTEA
12	INT, INTEGER	32-bit integer	INT, INTEGER
13	MONEY	64-bit currency amount	MONEY
14	NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
15	REAL	Single-precision floating-point number	REAL
16	SMALLDATETIME	Date and time	TIMESTAMP(0)
17	SMALLINT	16-bit integer	SMALLINT
18	SMALLMONEY	32-bit currency amount	MONEY
19	TIME	Time (Hour, minute, second and fraction)	TIME
20	TINYINT	8-bit unsigned integer, 0 to 255	SMALLINT
21	UNSIGNED BIGINT	64-bit unsigned integer	NUMERIC(20)
22	UNSIGNED INT	32-bit unsigned integer	NUMERIC(10)
23	UNSIGNED SMALLINT	16-bit unsigned integer	NUMERIC(5)
24	VARBINARY(n)	Variable-length binary string	BYTEA

Built-in SQL Functions

Converting string functions:

	Sybase ASE		PostgreSQL
1	CHAR_LENGTH(string)	Number of characters in string	CHAR_LENGTH(string)
2	CHARINDEX(substring, string)	Get substring position in string	POSITION(substring IN string)
3	CONVERT(VARCHAR, datetime, style)	Convert datetime to string	TO_CHAR(datetime, format)
4	LEN(string)	Length in characters	LENGTH(string)
5	STR_REPLACE(str, substring, with)	Replace substring	REPLACE(str, substring, with)
6	SUBSTRING(string, start, length)	Return substring	SUBSTRING(string, start, length)

Converting datetime functions:

	Sybase ASE		PostgreSQL
1	CONVERT(DATETIME, expr, style)	Converts expr to datetime	TO_TIMESTAMP(expr, format)
2	CONVERT(TIME, expr)	Converts expr to TIME	CAST(expr AS TIME)
3	DATEADD(dd, int, datetime)	Add days to datetime	datetime + INTERVAL 'int DAY'
3	DATEADD(dd, exp, datetime)	Add days to datetime	datetime + exp * INTERVAL '1 DAY'
4	DATENAME(unit, datetime)	Extract unit from datetime	TO_CHAR(datetime, format)
5	DAY(datetime)	Get the day of datetime	EXTRACT(DAY FROM datetime)
6	GETDATE()	Get the current date and time	NOW()

Numeric functions:

	Sybase ASE		PostgreSQL
1	CONVERT(BIGINT, exp)	Convert to integer	CAST(exp AS BIGINT)
	CONVERT(INT \| INTEGER, exp)		CAST(exp AS INT \| INTEGER)
	CONVERT(SMALLINT, exp)		CAST(exp AS SMALLINT)
	CONVERT(TINYINT, exp)		CAST(exp AS SMALLINT)
2	CONVERT(NUMERIC(p,s), exp)	Convert to number	CAST(exp AS NUMERIC(p,s))

NULL handling functions:

	Sybase ASE		PostgreSQL
1	ISNULL(exp, replacement)	Replace NULL with the specified value	COALESCE(exp, replacement)

SELECT Statement

Converting SQL queries:

	Sybase ASE		PostgreSQL
1	SELECT TOP n … FROM …	Select n rows only	SELECT … FROM … LIMIT n
2	SELECT … INTO #tmp_table	Create a temporary table using SELECT	SELECT … INTO TEMPORARY tmp_table
3	SELECT alias = expr …	Non-standard column alias form	SELECT expr alias …
4	= and =	Legacy outer joins	LEFT OUTER and RIGHT OUTER

DELETE Statement

Converting DELETE statement:

	Sybase ASE		PostgreSQL
1	DELETE [FROM] tab …	Delete rows	DELETE FROM tab …	FROM keyword is required

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

	Sybase ASE		PostgreSQL
1	IDENTITY	Identity column (no start and increment allowed)	GENERATED ALWAYS AS IDENTITY (START WITH start INCREMENT BY increment)
2	IDENTITY can be defined on DECIMAL, NUMERIC columns with 0 scale		Integer columns must be used

Temporary tables:

	Sybase ASE		PostgreSQL
1	CREATE TABLE #name	Temporary table name starts with #	CREATE TEMPORARY TABLE name

CREATE PROCEDURE Statement

Converting stored procedures from Sybase ASE to PostgreSQL:

	Sybase ASE		PostgreSQL
1	CREATE PROCEDURE name		CREATE OR REPLACE PROCEDURE name
2	@param [AS] datatype = default OUT \| OUTPUT		p_param IN \| INOUT datatype = default
3	Optional () for procedure parameters		() required
4	AS		Changed AS $$
5	Declarations inside BEGIN block		DECLARE block is between AS and BEGIN clauses
6	END	End of procedure block	END; $$ LANGUAGE plpgsql;

Transact-SQL Statements

Converting procedural Transact-SQL statements used in stored procedures, functions and triggers from Sybase ASE to PostgreSQL:

Variable declaration and assignment:

	Sybase ASE		PostgreSQL
1	DECLARE @var [AS] datatype(len) [= default]	Variable declaration	var datatype(len) [:= default];

Flow-of-control statements:

	Sybase ASE		PostgreSQL
1	IF condition BEGIN … END	IF statement	IF condition THEN … END IF;
2	WHILE condition BEGIN … END	WHILE loop	WHILE condition LOOP … END LOOP;

Cursors operations and attributes:

	Sybase ASE		PostgreSQL
1	CLOSE cur DEALLOCATE [CURSOR] cur	Close a cursor	CLOSE cur;
2	@@SQLSTATUS = 0	Fetch was successful	FOUND
	@@SQLSTATUS != 2

Stored procedure calls:

	Sybase ASE		PostgreSQL
1	EXEC sp_name @param1 = value1,…	Execute a procedure	CALL sp_name(p_param1 => value1,…)

System Procedures

Converting system stored procedures from Sybase ASE to PostgreSQL:

	Sybase ASE		PostgreSQL
1	sp_addtype name, "basetype(len)", "not null"	Create a user-defined type	CREATE DOMAIN name AS basetype(len) NOT NULL
2	sp_bindrule name, "table.column"	Assign a rule to a table column	ALTER TABLE table ADD CHECK rule_condition

SQL Statements

Converting SQL statements from Sybase ASE to PostgreSQL:

	Sybase ASE		PostgreSQL
1	CREATE RULE name AS condition	Create a domain of acceptable values	Converted to a CHECK constraint
2	USE name	Change the database	SET SCHEMA 'name'	If databases are mapped to schemas

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

SQL in Applications