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.
Databases:
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 |
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:
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' | |
DATEADD(dd, exp, 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:
NULL handling functions:
Sybase ASE | PostgreSQL | |||
1 | ISNULL(exp, replacement) | Replace NULL with the specified value | COALESCE(exp, replacement) |
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 |
Converting DELETE statement:
Sybase ASE | PostgreSQL | |||
1 | DELETE [FROM] tab … | Delete rows | DELETE FROM tab … | FROM keyword is required |
Converting CREATE TABLE statement keywords and clauses:
Temporary tables:
Sybase ASE | PostgreSQL | |||
1 | CREATE TABLE #name | Temporary table name starts with # | CREATE TEMPORARY TABLE name |
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; |
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,…) |
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 |
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 |