How to Set a Size Limit for an "Int" Datatype in Postgresql 9.5

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

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

setting the length of an integer column in Postgres

On PostgreSQL an integer type can hold values between -2147483648 and +2147483647 so if the value to insert is "greater than" there is no way to store it on database, only by modifying the data type for example to bigint you will be able to store greater values (bigint can hold values between -9223372036854775808 to +9223372036854775807).

Please check https://www.postgresql.org/docs/current/static/datatype-numeric.html

How to specify the size of a database parameters with schema on PostgreSQL?

If I'm not wrong (and I'm pretty sure), postgresSQL doesn't accept int sizes but NUMERIC.
You can do columnName NUMERIC(11)
Or with a constraint, forcing to being between ranges

Postgresql: optimizing columns size for numeric fields

From the manual:

Numeric values are physically stored without any extra leading or trailing
zeroes. Thus, the declared precision and scale of a column are maximums,
not fixed allocations. (In this sense the numeric type is more akin to
varchar(n) than to char(n).) The actual storage requirement is two bytes
for each group of four decimal digits, plus three to eight bytes overhead.

The result of the pg_total_relation_size function includes indexes. The correct column size for each of the values you are inserting is:

select pg_column_size(a)
from (values
    (999999999999999.62::numeric(17,2)),
    (999999999999999.6250::numeric(19,4)),
    (999999999999999.625000000000000000::numeric(35,18)),
    (16512.67::numeric(16,2)),
    (99.36::numeric(4,2))
) s(a)
;
 pg_column_size 
----------------
             16
             16
             16
             12
             10

So if you want to let the user to have a maximum of n decimals just define it as numeric(35, n). It will only use the space up to the number of existing decimals as trailing zeroes are not stored.

Postgres 9.4 how can i do a max constraint on an int field

Use CHECK constraint for that.

When creating the schema, just type:

CREATE TABLE sometable (
  ...
  friends int CONSTRAINT high_friends CHECK (friends <= 30)
)

Why does creating a table with a serial column create a sequence with a max val of a bigint?

As stated in the PostgreSQL 9.4 docs for Data Type SERIAL a sequence is implicitly created using CREATE SEQUENCE tablename_colname_seq without any additional parameters.

The documentation for CREATE SEQUENCE says:

The optional clause MAXVALUE maxvalue determines the maximum value for the sequence. If this clause is not supplied or NO MAXVALUE is specified, then default values will be used. The defaults are 2^63-1 and -1 for ascending and descending sequences, respectively.

So it is documented behaviour that the sequence is created with the max value you are seing.

How to specify min and max digits for a bank account number?

A bank account number is not an integer by nature. 26 decimal digits are too much for integer or bigint anyway.

A bank account number is not a numeric value at all, really, even if we could use the type numeric for storage. It can handle 26 decimal digits easily. But it also allows fractional digits (and other decorators, like @klin commented). You can restrict to numeric(26), which is short for numeric(26,0), to remove fractional digits from storage. But that still allows fractional digits on input, which are then rounded off. And other decorators. All of these seem undesirable for a bank account number:

SELECT numeric(26) '12345678901234567890123456'
     , numeric(26) '12345678901234567890123456.4'  -- rounded down
     , numeric(26) '12345678901234567890123456.5'  -- rounded up
     , numeric(26) '1e25'
     , numeric(26) '1.2345e25'
     , numeric(26) '+12345678901234567890123456.5'

SELECT numeric(26) '99999999999999999999999999.5'  -- error after rounding up

A bank account number is more like text by nature, so data type text seems more appropriate (like @klin provided), even if that occupies a bit more space on disk (like @a_horse mentioned). 27 bytes vs. 17 bytes for numeric - or 30 vs. 20 bytes in RAM. See:

• What is the overhead for varchar(n)?

However, you would not want to apply collation rules to bank account numbers. That happens with collatable types like text or varchar if your DB cluster runs with a non-C locale. Would be a void effort for only digits to begin with. But you still get slower sorting and slower indexes etc. Notably, the "abbreviated keys" feature in Postgres 9.5 or later is currently (incl. Postgres 10) disabled for non-C locales.

Putting everything together, I suggest:

CREATE TABLE bank_account (
   bank_account_id serial  PRIMARY KEY
-- bank_account_id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY  -- in Postgres 10+
 , number_account  text COLLATE "C" NOT NULL  -- disable collation rules
 , CONSTRAINT number_account_has_26_digits CHECK (number_account ~ '^\d{26}$')
);

Asides:

• Consider an IDENTITY column instead of the serial in in Postgres 10+. Details:

  • https://blog.2ndquadrant.com/postgresql-10-identity-columns/

• INTEGER(26) is not valid syntax in Postgres, where the integer data type has no modifiers. You can chose from int2, int4 (default integer) and int8, though - the dangling number signifying occupied bytes, not the number of digits allowed.

What is the maximum number of columns in a PostgreSQL select query

According to PostgreSQL Limits it's "250 - 1600 depending on column types". See note under the table. The column types affect it because in PostgreSQL rows may be at most 8kb (one page) wide, they cannot span pages. Big values in columns are OK because TOAST handles that, but there's a limit to how many columns you can fit in that depends on how wide the un-TOASTed data types used are.

(Strictly this refers to columns that can be stored in on-disk rows; queries might be able to use wider column sets than this. I don't recommend relying on it.)

If you're even thinking about approaching the column limits you're probably going to have issues.

Mapping spreadsheets to relational databases seems like the simplest thing in the world - map columns to columns, rows to rows, and go. Right? In reality, spreadsheets are huge freeform monsters that enforce no structure and can be really unweildy. Relational databases are designed to handle lots more rows, but at a cost; in the case of PostgreSQL part of that cost is a limitation to how wide it likes those rows to be. When facing spreadsheets created by Joe User this can be a real problem.

One "solution" is to decompose them into EAV, but that's unspeakably slow and ugly to work with. Better solutions are using arrays where possible, composite types, hstore, json, xml, etc.

In the end, though, sometimes the best answer is to analyse the spreadsheet using a spreadsheet.

---

Related Topics