How do I convert a T-SQL query to PostgreSQL that concatenates substrings?-CodePudding

This is my query:

SELECT
    *,
    CONCAT(
        RIGHT( account_no, 4),
        RIGHT( customer_id, 5 )
    ) AS "password for my diplomo"
FROM
    account_info;

But I get this error:

Error: function left(bigint, integer) does not exist;

My table is:

CREATE TABLE account_info (
    account_no  bigint       NOT NULL PRIMARY KEY,
    customer_id varchar(...)
)

CodePudding user response：

You seem to be using a reference for T-SQL or JET Red SQL (for MS SQL Server and MS Access respectively) when you're actually using PostgreSQL which uses completely different functions (and syntax) for string/text processing.

CodePudding user response：

Postgres functions left and right expect their first argument be text. So first cast account_no to type text and your query (a bit simplified) will work.

SELECT *,
       right(account_no::text, 4) || right(customer_id, 5) as pfmd
FROM account_info;

Unrelated but the best practice under Postgres is to use type text instead of char or varchar.