CODEWITHSUNDEEP
sqlpostgresqldatabase-designsqldatatypes
FlashspeedIfe
FlashspeedIfe

Reputation: 378

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

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

Upvotes: 7

Views: 30661

Answers (3)

rashmi shree
rashmi shree

Reputation: 1

Changing integer to numeric works.

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

Upvotes: 0

user330315
user330315

Reputation:

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

Upvotes: 13

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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.

Upvotes: 4

Related Questions