CODEWITHSUNDEEP
sqlpostgresql
Kevin
Kevin

Reputation: 1

Statement works in MySQL but not PostgreSQL

This basically converts a numerical value into a unique identifier we use in our system. It works fine in our MySql database but when running on Postres it's not working and i'm not quite sure why

CONCAT( 
  (240000000000 + AA.id),   
 (SUBSTRING((10 - ((((SUBSTRING((240000000000 + AA.id) FROM 2 FOR 1) + SUBSTRING((240000000000 + AA.id) FROM 4 FOR 1) + SUBSTRING((240000000000 + AA.id) FROM 6 FOR 1) + SUBSTRING((240000000000 + AA.id) FROM 8 FOR 1) + SUBSTRING((240000000000 + AA.id) FROM 10 FOR 1) + SUBSTRING((240000000000 + AA.id) FROM 12 FOR 1))*3) + (SUBSTRING((240000000000 + AA.id) FROM 1 FOR 1) + SUBSTRING((240000000000 + AA.id) FROM 3 FOR 1) + SUBSTRING((240000000000 + AA.id) FROM 5 FOR 1) + SUBSTRING((240000000000 + AA.id) FROM 7 FOR 1) + SUBSTRING((240000000000 + AA.id) FROM 9 FOR 1) + SUBSTRING((240000000000 + AA.id) FROM 11 FOR 1))) MOD 10)) FROM -1 FOR 1)) 
  ) AS 'BI_1_240_SKU'

This is error i'm getting in postgres 

SET
psql:C:/Users/kevin.jeung/Desktop/sql/test2:103: ERROR:  syntax error at or near "MOD"
LINE 5: ...SUBSTRING((240000000000 + AA.id) FROM 11 FOR 1))) MOD 10)) F...

I'm sure it's an easy syntax change for someone who knows

Upvotes: 0

Views: 91

Answers (2)

LongBeard_Boldy
LongBeard_Boldy

Reputation: 812

do you generate checkdigit for something like ean13 barcode?

SELECT
    CONCAT((240000000000 + AA.id)::text

, (SUBSTRING((10 
              - ((
                        ((SUBSTRING((240000000000 + AA.id) ::text
FROM 2 FOR 1)::integer + SUBSTRING((240000000000 + AA.id)::text
FROM 4 FOR 1)::integer + SUBSTRING((240000000000 + AA.id)::text
FROM 6 FOR 1)::integer + SUBSTRING((240000000000 + AA.id)::text
FROM 8 FOR 1)::integer + SUBSTRING((240000000000 + AA.id)::text
FROM 10 FOR 1)::integer + SUBSTRING((240000000000 + AA.id)::text
FROM 12 FOR 1)::integer) * 3)  
 +                     (SUBSTRING((240000000000 + AA.id)::text
FROM 1 FOR 1)::integer + SUBSTRING((240000000000 + AA.id)::text
FROM 3 FOR 1)::integer + SUBSTRING((240000000000 + AA.id)::text
FROM 5 FOR 1)::integer + SUBSTRING((240000000000 + AA.id)::text
FROM 7 FOR 1)::integer + SUBSTRING((240000000000 + AA.id)::text
FROM 9 FOR 1)::integer + SUBSTRING((240000000000 + AA.id)::text
FROM 11 FOR 1)::integer  ))
% 10))::text
FROM 1 FOR 1    
            ))) AS "BI_1_240_SKU"
            from ( select 2569825 as id) aa

bit more readable and simpler would be something like this:

SELECT
     (
        SELECT ean || ((10 - (sum(substr(ean, length(ean) - num + 1, 1)::integer * CASE WHEN num % 2 = 1 THEN 3 ELSE  1 END) % 10)) % 10 )::text AS ean_plus_checkdigit
        FROM (
            SELECT num
                  ,substring(240000000000::text FROM 1 FOR length(240000000000::text) - length(aa.id::text)) || aa.id::text AS ean
             FROM generate_series(1, 12) num
            ) AS main
       WHERE num <= length(ean)
       GROUP BY ean
    ) AS "BI_1_240_SKU"
FROM ( SELECT 2569825 AS id) aa;

Upvotes: 0

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

Your error is quite informative. PostgreSQL does not recognize MOD function.

If you'd look at documentation, to MOD, you have to use % sign.

╔══════════╦═══════════════════════════════════════════════╦═══════════╦════════╗
║ Operator ║                  Description                  ║  Example  ║ Result ║
╠══════════╬═══════════════════════════════════════════════╬═══════════╬════════╣
║ +        ║ addition                                      ║ 2 + 3     ║      5 ║
║ -        ║ subtraction                                   ║ 2 - 3     ║     -1 ║
║ *        ║ multiplication                                ║ 2 * 3     ║      6 ║
║ /        ║ division (integer division truncates results) ║ 4 / 2     ║      2 ║
║ %        ║ modulo (remainder)                            ║ 5 % 4     ║      1 ║
║ ^        ║ exponentiation                                ║ 2.0 ^ 3.0 ║      8 ║
║ |/       ║ square root                                   ║ |/ 25.0   ║      5 ║
║ ||/      ║ cube root                                     ║ ||/ 27.0  ║      3 ║
║ !        ║ factorial                                     ║ 5 !       ║    120 ║
║ !!       ║ factorial (prefix operator)                   ║ !! 5      ║    120 ║
║ @        ║ absolute value                                ║ @ -5.0    ║      5 ║
║ &        ║ bitwise AND                                   ║ 91 & 15   ║     11 ║
║ |        ║ bitwise OR                                    ║ 32 | 3    ║     35 ║
║ #        ║ bitwise XOR                                   ║ 17 # 5    ║     20 ║
║ ~        ║ bitwise NOT                                   ║ ~1        ║     -2 ║
║ <<       ║ bitwise shift left                            ║ 1 << 4    ║     16 ║
║ >>       ║ bitwise shift right                           ║ 8 >> 2    ║      2 ║
╚══════════╩═══════════════════════════════════════════════╩═══════════╩════════╝

Upvotes: 4

Related Questions