CODEWITHSUNDEEP
databasepostgresqlrdbms
RoCkStUnNeRs
RoCkStUnNeRs

Reputation: 319

how to store the alphanumeric value in integer type in postgres?

I need to store the id of the person in Database.But the id should contain one alpha in beginning for that ,I have followed following thing ,

for id column I set the default value like 

create table alphanumeric (id int default ('f'||nextval('seq_test'))::int) ;

So now table was created like 

default (('f'::text || nextval('seq_test'::regclass)))::integer

After creating the table I insert the values its showing the error like 

INSERT INTO alpha VALUES (default) ;
ERROR:  invalid input syntax for integer: "f50"

I understood the error but I need this type of storing .....!

Notes : I don't want to use function or triggers .

Upvotes: 1

Views: 3397

Answers (2)

Daniel Lyons
Daniel Lyons

Reputation: 22803

Just to add a couple more cents to @muistooshort's answer. If you are certain the IDs you want will always conform to a certain regular expression, you can enforce that with a CHECK constraint:

CREATE TABLE alphanumeric (
  id VARCHAR DEFAULT ('f' || nextval('seqtest') PRIMARY KEY,
  ...
  CHECK(id ~ '^[A-Za-z][0-9]+')
);

Of course, I'm making a gross assumption about the nature of your identifiers, you will have to apply your own judgement about whether or not your identifiers constitute a regular language.

Secondly, the sort order @muistooshort is talking about is sometimes (confusingly) called 'natural sort' and you can get a PostgreSQL function to assist with this.

Upvotes: 3

mu is too short
mu is too short

Reputation: 434685

You want to use a string for your ids so use a text column for your id:

create table alphanumeric (
    id text default ('f' || nextval('seq_test'))
)

If you're only use seq_test for that column then you probably want it to be owned by that column:

alter sequence seq_test owned by alphanumeric.id

That way the sequence will be dropped if you drop the table and you won't have an unused sequence cluttering up your database.

One thing you might want to note about this id scheme is that they won't sort the way a human would sort them; 'f100' < 'f2', for example, will be true and that might have side effects that you'll need to work around.

Upvotes: 2

Related Questions