Переход на postgreSQL с MS SQL

Введение

Здесь разберем на что обратить внимание при переходе от TSQL (SQL Server) к Postgre SQL.  

Язык 

Точки запятой в конце операций. После SELECT, INSERT, DELETE, UPDATE запросов, после END IF, после присвоения значений и т.д. 

В кавычки названия с большими буквами. Все поля таблиц с большими буквами надо использовать в кавычках. 

Для новых объектах лучше именование делать в нижнем регистре + разделитесь подчеркивание some_object. 

Параметры хранимок также лучше ставить в lower case

Конфликт имен в процедурах. Если есть параметр процедуры code и у таблицы  колонка code, то при обращении к колонке или параметру процедуры надо использовать префикс таблицы или названия процедуры (например, sp1.code и as_crud_tables.code).

Объявление переменых в одном месте - для этого в функции, SP используется раздел declare и там объявляем все переменные. 

Вместо print используем RAISE NOTICE (%  задает номер по порядку что вывести). 

 RAISE NOTICE '%,%,$', x1,x2,x3;

Нельзя просто писать какой-то код без объявления функции, SP или блока. Нужно оформить блок DO, чтобы отправить блок  из нескольких команд на выполнение. 

Пример: 

DO $$
        DECLARE id INT;
        declare res text;
BEGIN
        CALL as_forms_getForm(res, 'code1', '', 1);
        RAISE NOTICE '%', res;
END $$

Конкатенация строк через || вместо +

Приведенние типов -  вместо cast используется ::  Пример ss::integer

Сравнение дат

where f.scheduled_departure::date = bookings.now()::date - INTERVAL '2 day' 

Поиск вхождения подстроки, аналог indexOf

select	position(' ' IN passenger_name)

Конструкция if

 IF condition1 THEN
        statements1;
    ELSIF condition2 THEN
        statements2;
    ELSIF condition3 THEN
        statements3;
    ELSE
        else_statements;
    END IF;

SQL операции и работа с таблицами

Аналог scope identity после insert. Возврат значения из insert через returnind id into somevar.

DO
$$
DECLARE id1 INT;
BEGIN
insert into as_users (username)
select 'user1'
returning id
into id1;
raise notice 'id = %', id1;

END
$$

Про операцию DO https://postgrespro.ru/docs/postgresql/9.5/sql-do

Как изменить колонку id на identity и primary key

ALTER TABLE "as_userSettings"  ALTER COLUMN id
ADD GENERATED BY DEFAULT AS IDENTITY;

ALTER TABLE "as_userSettings"   ADD PRIMARY KEY (id);

Установка primary key и создание столбцов таблицы через  pgAdmin

Правой кнопкой на таблице и Properties и дальше Columns

Создание внешних ключей для таблицы

В pgAdmin База / Properties / Constraints / Foreign Keys

Пагинация для SELECT (есть пример в GetItems процедур таблицы Falcon Space):

Postgre:
LIMIT pagesize OFFSET (page - 1) * pagesize;

SQL Server:
OFFSET @pageSize * (@page - 1) ROWS
FETCH NEXT @pageSize ROWS ONLY;
        

Аналог ltrim, rtrim

Удаление символа «#» из начала строки «#postgres»:
SELECT TRIM(LEADING '#' FROM '# Postgres' ) result;. Результат — «Postgres».
Удаление символа «.» из конца строки «pgtutorial.com.»:
SELECT TRIM(TRAILING '.' FROM 'pgtutorial.com.' ) result;. Результат — «pgtutorial.com».
Удаление пробелов с обоих концов строки «pgtutorial.com»:
SELECT TRIM( BOTH ' ' FROM 'pgtutorial.com ' ) result;. Результат — «pgtutorial.com».

Аналог try_cast() 

В FSPG добавлена своя функция try_cast(a,x) где x задает тип поля к которому мы приводим. 

Аналог Like без учета регистра

LIKE с учетом регистра

ILIKE без учета регистра

Аналог getdate() для получени текущей даты 

Есть функция now(), но также добавлена своя функция getdate() для упрощения перехода. 

Добавление и вычитание из даты

Как вычесть 7 дней из даты    

minDate date = NOW() - INTERVAL '7 DAY; 

SQL для создания таблицы

CREATE TABLE color (
id INT GENERATED ALWAYS AS IDENTITY,
color_name VARCHAR NOT NULL
);

Создание временных таблиц

CREATE TEMP TABLE my_fist_temp_table 
ON COMMIT DROP -- удаляем таблицу при завершении транзакции
AS 
SELECT 1 AS id, CAST ('какие-то значения' AS TEXT) AS val;

Вместо MS Management Studio  используется pgAdmin.

Типы данных

Оказываем тип TSQL и аналог в Postgre SQL:

• nvarchar() = text (длина никогда не будет ограничена)    
• nvarchar = varchar    
• datetime = timestamp    
• datetimeoffset = timestamp with time zone    
• int = integer
• bit = boolean (вместо 0 и 1 используются false и true, и чтобы проверить значение, используется не case when test = 1 then…, а case when test then… Вместо case when test = 0 будет case when not test. Аналогично для if)    

Cравнение типов данных https://www.convert-in.com/docs/mss2pgs/types-mapping.htm    

Про типы данных: https://postgrespro.ru/docs/postgrespro/current/datatype