0

I am fairly new to SQL but have been working hard to learn. I am currently stuck on an issue with setting a primary key to have 8 digits no matter what.

I tried using INT(8) but that didn't work. Also AUTO_INCREMENT doesn't work in PostgreSQL but I saw there were a couple of data types that auto increment but I still have the issue of the keys not being long enough.

Basically I want to have numbers represent User IDs, starting at 10000000 and moving up. 00000001 and up would work too, it doesn't matter to me.

I saw an answer that was close to this, but it didn't apply to PostgreSQL unfortunately.

Hopefully my question makes sense, if not I'll try to clarify.

My code (which I am using from a website to try and make my own forum for a practice project) is:

CREATE Table users (
user_id     INT(8) NOT NULL AUTO_INCREMENT,
user_name   VARCHAR(30) NOT NULL,
user_pass   VARCHAR(255) NOT NULL,
user_email  VARCHAR(255) NOT NULL,
user_date   DATETIME NOT NULL,
user_level  INT(8) NOT NULL,
UNIQUE INDEX user_name_unique (user_name),
PRIMARY KEY (user_id)
) TYPE=INNODB;

It doesn't work in PostgreSQL (9.4 Windows x64 version). What do I do?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
a8d9add11bff124c0fbddd7a6607
  • 53
  • 1
  • 8
  • I was able to create the table using the code that I'll post below, but like I said it doesn't force the number of digits to 8 for the Primary Key (user ID) and won't automatically increase for each new user created: `CREATE TABLE users ( user_id INT NOT NULL, user_name VARCHAR(30) NOT NULL, user_pass VARCHAR(255) NOT NULL, user_email VARCHAR(255) NOT NULL, user_date DATE NOT NULL, user_level INT NOT NULL, PRIMARY KEY (user_id) );` – a8d9add11bff124c0fbddd7a6607 Apr 11 '15 at 18:25
  • See http://stackoverflow.com/questions/20781111/postgresql-9-1-primary-key-autoincrement for auto increment columns in PostgreSQL. – JP Moresmau Apr 11 '15 at 18:27
  • 3
    Your other requirement is a bit weird. Why do you care how many digits the primary key has? If 1 as you say is a valid value, then keep an integer column, and format the value with 8 digits when you show it in a GUI. – JP Moresmau Apr 11 '15 at 18:28
  • Hey JP Moresmau, the reason I had asked for 8 digits is because I didn't want it to show up as 1, 2, 7, etc I'd prefer many digits. To be honest I didn't realize I can just leave them as small digits then fill in with zeroes like you said at the GUI level (like I said I am pretty new at SQL so I haven't worked with the frontend of things yet and how they look to users). – a8d9add11bff124c0fbddd7a6607 Apr 11 '15 at 18:50
  • Unrelated but: `INT(8)` does ***not*** limit the values to 8 digits in MySQL. It's merely a _hint_ for a GUI application on how many digits the application should _display_. It does in no way constrain the data you can put into it. You can store the same range of values (up to a value of 2147483647) into a `int(1)` or `int(10)` column in MySQL –  Apr 11 '15 at 19:52

3 Answers3

3

You are mixing two aspects:

  • the data type allowing certain values for your PK column
  • the format you chose for display

AUTO_INCREMENT is a non-standard concept of MySQL, SQL Server uses IDENTITY(1,1), etc.
Use a serial column in Postgres:

CREATE TABLE users (
  user_id serial PRIMARY KEY
, ...
)

That's a pseudo-type implemented as integer data type with a column default drawing from an attached SEQUENCE. integer is easily big enough for your case (-2147483648 to +2147483647).

If you really need to enforce numbers with a maximum of 8 decimal digits, add a CHECK constraint:

CONSTRAINT id_max_8_digits CHECK (user_id BETWEEN 0 AND < 99999999)

To display the number in any fashion you desire - 0-padded to 8 digits, for your case, use to_char():

SELECT to_char(user_id, '00000000') AS user_id_8digit
FROM   users;

That's very fast. Note that the output is text now, not integer.
SQL Fiddle.

A couple of other things are MySQL-specific in your code:

  • int(8): use int.
  • datetime: use timestamp.
  • TYPE=INNODB: just drop that.
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

You could make user_id a serial type column and set the seed of this sequence to 10000000.

TavoloPerUno
  • 549
  • 2
  • 7
  • I looked up what you said and I was able to figure it out! Here is my code that I used, is there a better way to go about it? I added two entries to the database without inputting the User ID # and it did it automatically! I would show a screenshot but I don't think I can link here so here is my code: `CREATE TABLE users ( user_id SERIAL PRIMARY KEY NOT NULL, user_name VARCHAR(30) NOT NULL, user_pass VARCHAR(255) NOT NULL, user_email VARCHAR(255) NOT NULL, user_date DATE NOT NULL, user_level INT NOT NULL ); ALTER SEQUENCE users_user_id_seq RESTART WITH 10000000 INCREMENT BY 1;` – a8d9add11bff124c0fbddd7a6607 Apr 11 '15 at 18:46
  • Here is how it looks in the database (hopefully I am allowed to link things) [link](http://i.imgur.com/vVjdrst.png) – a8d9add11bff124c0fbddd7a6607 Apr 11 '15 at 18:47
  • Yes, it looks good. All the best. Do keep in mind that you will have to reseed the sequence to max(id) + 1 every time you restore the table from other database or input the id column manually. [This](https://wiki.postgresql.org/wiki/Fixing_Sequences) page has more information. – TavoloPerUno Apr 11 '15 at 19:49
  • @TavoloPerUno: I don't think you need to reseed sequences in any currently supported version of PostgreSQL. I know *I* don't need to. – Mike Sherrill 'Cat Recall' Apr 13 '15 at 13:01
  • I run postgres 9.4.1 too and I still have to fix the sequences after every insert operation that inputs values for any of these sequence columns, failing which the DB would greet me with a duplicate key value error on the next insert with no value for an id column. – TavoloPerUno Apr 13 '15 at 13:10
0

Why?

int(8) in mysql doesn't actually only store 8 digits, it only displays 8 digits

Postgres supports check constraints. You could use something like this:

create table foo (
  bar_id int primary key check ( 9999999 < bar_id and bar_id < 100000000 )
);

If this is for numbering important documents like invoices that shouldn't have gaps, then you shouldn't be using sequences / auto_increment

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152