How can I set a size limit for an "int" datatype in PostgreSQL 9.5

Question

I am experimenting with PostgreSQL coming from SQL using MySQL and I simply wish to create a table with this piece of code which is valid SQL:

CREATE TABLE flat_10
(
  pk_flat_id INT(30) DEFAULT 1,
  rooms      INT(10) UNSIGNED NOT NULL,
  room_label CHAR(1) NOT NULL,

  PRIMARY KEY (flat_id)
);

I get the error

ERROR:    syntax error at or near "("
LINE 3:   pk_flat_id integer(30) DEFAULT 1,

I have conducted searches on the web and found no answer and I cant seem to find an answer in the PostgreSQL manual. What am I doing wrong?

I explicitly want to set a limit to the number of digits that can be inserted into the "pk_flat_id" field

Solution 1

I explicitly want to set a limit to the number of digits that can be inserted into the "pk_flat_id" field

Your current table definition does not impose a "size limit" in any way. In MySQL the parameter for the intdata type is only a hint for applications on the display width of the column when displaying it.

You can store the value 2147483647 in an int(1) without any problems.

If you want to limit the values to be stored in an integer column you can use a check constraint:

CREATE TABLE flat_10
(
  pk_flat_id bigint DEFAULT 1,
  rooms      integer NOT NULL,
  room_label CHAR(1) NOT NULL,

  PRIMARY KEY (flat_id), 
  constraint valid_number 
      check (pk_flat_id <= 999999999)
);

Solution 2

The answer is that you use numeric or decimal types. These are documented here.

Note that these types can take an optional precision argument, but you don't want that. So:

CREATE TABLE flat_10
(
  pk_flat_id DECIMAL(30) DEFAULT 1,
  rooms      DECIMAL(10) NOT NULL,
  room_label CHAR(1) NOT NULL,

  PRIMARY KEY (pk_flat_id)
);

Here is a SQL Fiddle.

I don't think that Postgres supports unsigned decimals. And, it seems like you really want serial types for your keys and the long number of digits is superfluous.

Comments

[FlashspeedIfe]

Why did you specify a size (Or whatever the CHAR(n) stands for )for the CHAR datatype on the room_label column?

[a_horse_with_no_name]

@FlashspeedIfe: because you did that in your original table as well. And for character data types, the size does specify a limit.