Question 255743 in PostgreSQL Question posted in
The official documentation can be found here.

How can I transform integer column results into strings in Postgresql?

I need to write a function that will transform the results of a column called "store_id" in a table. I want to transform the integer 1 into "Store 1" and 2 into "Store 2", but I get errors no matter what I enter.

I tried:

CREATE OR REPLACE FUNCTION transform_store_id()
RETURNS VOID AS $$
BEGIN
  UPDATE detailed_sales
  SET store_id = CASE store_id
                    WHEN 1 THEN 'store 1'
                    WHEN 2 THEN 'store 2'
                    ELSE store_id::text
                  END;
END;
$$
 LANGUAGE plpgsql;

But I get "ERROR: column "store_id" is of type integer but expression is of type text"
So, I tried incorporating CAST and TO_CHAR with varying errors.

Tags:

3

Answers


  1. 0

    You need first change the column type

    CREATE TABLE detailed_sales(store_id  int)

    CREATE TABLE

    INSERT INTO detailed_sales VALUEs (1),(2), (3)

    INSERT 0 3

    ALTER TABLE detailed_sales 
ALTER COLUMN  store_id TYPE   TEXT;

    ALTER TABLE

      UPDATE detailed_sales
  SET store_id = CASE store_id
                    WHEN '1' THEN 'store 1'
                    WHEN '2' THEN 'store 2'
                    ELSE store_id::text
                  END;

    UPDATE 3

    fiddle

    Login or Signup to reply.
  2. 0

    You can add a new column with the text data type and update it with the required values in order to change the "store_id" column in your table. try the following code. In this code the original "store_id" column is preserved while a new "store_name" column is created and updated.

    CREATE OR REPLACE FUNCTION transform_store_id()
RETURNS VOID AS $$
BEGIN
  -- Add a new column called "store_name" with text data type
  ALTER TABLE detailed_sales ADD COLUMN store_name TEXT;
  
  -- Update the "store_name" column based on the "store_id" values
  UPDATE detailed_sales
  SET store_name = CASE store_id
                    WHEN 1 THEN 'Store 1'
                    WHEN 2 THEN 'Store 2'
                    ELSE store_id::text
                  END;
END;
$$
LANGUAGE plpgsql;

    Hope it works 🙂

    Login or Signup to reply.
  3. 0

    Other answers have demonstrated that there is no need for a function at all.

    Another approach might be to use GENERATED (manual) – aka COMPUTED, DERIVED and also confusingly, VIRTUAL, columns (all of the code below is available on the fiddle here).

    CREATE TABLE detailed_sales(store_id  int)

    Populate:

    INSERT INTO detailed_sales VALUEs (1),(2), (3)

    Add our GENERATED column:

    ALTER TABLE detailed_sales 
ADD COLUMN store TEXT 
GENERATED ALWAYS AS 
(
  CASE store_id
    WHEN 1 THEN 'store 1'
    WHEN 2 THEN 'store 2'
    ELSE store_id::TEXT
  END
) STORED;

    And, without further ado, we run:

    SELECT 
   store_id,
   store,
   PG_TYPEOF(store_id),
   PG_TYPEOF(store)
FROM 
   detailed_sales;

    Result:

    store_id   store  pg_typeof  pg_typeof
       1 store 1    integer       text
       2 store 2    integer       text
       3       3    integer       text

    And our store 1, store 2, &c. "magically" appear!

    The PG_TYPEOF() (manual) function is, of course, not necessary – just for demonstration purposes!

    Note that the STORED keyword is compulsory – most servers have two storage modes for GENERATED columns – STORED (on disk) and VIRTUAL (computed on the fly – i.e. when requested) – hence the remark about calling the column itself VIRTUAL being confusing.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search