Creating a String in Postgresql

Asked

Viewed 301 times

1

So, I have this sequence in a column of a table where every time I do an insertion its value is defined by nextval ('ptable_pr_codigo_seq' :: regclass)

CREATE SEQUENCE public.ptable_pr_codigo_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 103
  CACHE 1;
ALTER TABLE public.ptable_pr_codigo_seq
  OWNER TO postgres;

Now, how can I make a new sequence so that every time I make an insertion, the value is not an auto-numeric number, but a character in an interval [A ~ ZZZ] (so that it advances the sequence to each insertion)?.


Exemplo: Primeiro insert           = A (valor da coluna)
         Segundo                   = B
         Terceiro                  = C
         27th                      = AA
         ...
         ?Th                       = ZZZ
sql postgresql
  • 2

    Welcome to Sopt. Please translate the question into English.

  • RT in @8bit comment

  • From the documentation [https://www.postgresql.org/docs/current/sql-createsequence.html]: you can optionally specify a data type and the valid types are: smallint, integer and bigint, (default bigint) so it is not possible to specify a string.

  • But remember that Postgresql is free software and therefore nothing prevents you to create and implement your own set of sequence handling routines and do it the way you want. The source code is available at: https://doxygen.postgresql.org/

  • Basically it would be a "Function" that would call the Sequence and do a 'base Conversion' an Imaginary base 26, A B C D ... AA / Basically it would be a "Function" that would call Quence and make a 'base conversion' an imaginary base 26 , A B C D ... AA...

  • and then young ? solved his problem ?

  • 1

    solved the problem

Show 2 more comments

1 answer

2


I do not know what the objective is, and what could be advantages and disadvantages of this; I had never thought about it until I saw your question and through curiosity I elaborated the answer. I made the following function in postgresql:

CREATE OR REPLACE FUNCTION public.to_char_sequence (bigint)
RETURNS varchar AS
$body$
declare 

retorno varchar DEFAULT 'A';
letra varchar DEFAULT '';
xaux integer := $1;
currnum integer;

BEGIN

while (xaux > 0) LOOP

  currnum := (xaux - 1) % 26;
  letra := chr(currnum+65);
  retorno := retorno || letra;
  xaux = (xaux - (xaux + currnum+1))/26;

END LOOP;

return retorno;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

When performing the function: Select to_char_sequence(27)

'AA'

Now, create a sequence normally:

CREATE SEQUENCE public.teste_serie_id_seq
  INCREMENT 1 MINVALUE 1
  MAXVALUE 9223372036854775807 START 1
  CACHE 1;

And in the column that wants the increment, set the default value to:

to_char_sequence(nextval('teste_serie_id_seq'::regclass))

Okay, let’s see the result:

Postgresql incremendo varchar

Extra: To be documented, the reverse function code:

CREATE OR REPLACE FUNCTION public.from_char_sequence (varchar)
RETURNS bigint AS
$body$
declare 

retval bigint DEFAULT 0;
col varchar := upper($1);
colnum integer;
letra varchar;
len integer;
val integer;

BEGIN
retval := 0;
len := length(col);

FOR i IN REVERSE len..1 LOOP
    letra := substring(col from i for 1);
    colnum := ascii(letra)-64;
    val := colnum * ( pow(26 , (len - (i))))::integer;
    retval := retval + val;
END LOOP;

return retval;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.