Postgres: How can I fix this create query?

Question

I am learning Postgres and trying a create command. But it is giving error which I am unable to understand which is making error. The command is :

CREATE TABLE Package (
    id smallint NOT NULL primary key,
    package_name varchar ( 45 ) not null,
    updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    count integer(2) default null,
    pkg_desc varchar ( 45 ) not null,
    bucket_name varchar ( 45 ) not null,
    active bit(1),
    status INT(2) default null,
    metadata JSONB,
    path varchar ( 45 ) default null,
    created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    run_status INT(5) 
)

And the error:

ERROR: syntax error at or near "("
  Position: 186

you cannot specify size for integer or int data types. just remove that and you should be good. — Adil Khalil, Apr 13 '21 at 07:02
CREATE TABLE Package ( id smallint NOT NULL primary key, package_name varchar ( 45 ) not null, updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP, count integer(2) default null, pkg_desc varchar ( 45 ) not null, bucket_name varchar ( 45 ) not null, active bit(1), status INT(2) default null, metadata JSONB, path varchar ( 45 ) default null, created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP, run_status INT(5) ) — Aditya Sinha, Apr 13 '21 at 07:02
There is nothing like INT(2) in Postgresql. Integer (or int) is legal but other numeric type might suit you. You can find the available types here: https://www.postgresql.org/docs/12/datatype.html — Bjarni Ragnarsson, Apr 13 '21 at 07:03
The reason I used both is I was just trying ...the use case is it should be a interger with specific limit. — Aditya Sinha, Apr 13 '21 at 07:04
@AdilKhalil what if there is a limit like int(5) ,which datatype has to be used? — Aditya Sinha, Apr 13 '21 at 07:06
What do you expect from an INT(5) ? What numbers should it support? When you have that answer, you can pick the right PostgreSQL datatype — Frank Heikens, Apr 13 '21 at 07:07
Does this answer your question? [How can I set a size limit for an "int" datatype in PostgreSQL 9.5](https://stackoverflow.com/questions/34883306/how-can-i-set-a-size-limit-for-an-int-datatype-in-postgresql-9-5) — HrkBrkkl, Apr 13 '21 at 07:09

score 1 · Answer 1 · answered Apr 13 '21 at 07:06

You use integer(2) and INT(2) to create a column. You must not add the number. Integers are fixed size. Also you should decide on one way to write it Try this:

CREATE TABLE Package (
    id smallint NOT NULL primary key,
    package_name varchar ( 45 ) not null,
    updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    count INT default null,
    pkg_desc varchar ( 45 ) not null,
    bucket_name varchar ( 45 ) not null,
    active bit(1),
    status INT default null,
    metadata JSONB,
    path varchar ( 45 ) default null,
    created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    run_status INT
)

score 0 · Answer 2 · answered Apr 13 '21 at 07:10

integer and int types don't accept arguments. You can't write it like integer(2), which gives syntax error.

create table Package (
  id smallint not null primary key
  , package_name varchar(45) not null
  , updated_at timestamp null default current_timestamp
  , count integer default null
  , pkg_desc varchar(45) not null
  , bucket_name varchar(45) not null
  , active bit(1)
  , status int default null
  , metadata jsonb
  , path varchar(45) default null
  , created_at timestamp null default current_timestamp
  , run_status int
)

The official document should be helpful: https://www.postgresql.org/docs/13/datatype.html

Postgres: How can I fix this create query?

2 Answers2