sql - 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

Answer 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)
);