Postgresql – the purpose of the keyword “ID” in PostgreSQL

featurespostgresqlpostgresql-9.1reserved-wordsyntax

I'm new to PostgreSQL and I ran into problems using the following query:

SELECT * FROM table1 WHERE ID = 1

ERROR: column "id" does not exist
LINE 2: WHERE ID = 1

where ID is a column I added via a GUI-tool.

I then found out that ID is a keyword in my current version of PSQL and should be quoted. But I can't recognize what is this ID for.

On the linked page there is the following notice:

It is important to understand before studying Table C-1 that the fact that a key word is not reserved in PostgreSQL (==true) does not mean that the feature related to the word is not implemented.

So, ID is not marked as a PostgreSQL keyword and there seems to be a feature connected to it, but I can't find out which. SELECT ID itself turns into the same error message as posted above.

Best Answer

In PostgreSQL, inserting a column named

"ID"

is one thing. Inserting a column named

ID

is another.

create table test (
  "ID" integer not null
);

insert into test values (1);

select *
from test
where ID = 1;

ERROR: column "id" does not exist

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case.

Using `pg-bignum`

The pg-bignum library does what you want. I just patched it to accept hex input.

Download the library. Run

make
sudo make install

Now, run

CREATE EXTENSION bignum;

Now you can take a 256 bit number like this

59 51 a1 20 2e 65 62 26 ab f3 af e6 93 16 db 1c 
3b 37 24 47 75 32 72 95 79 e9 d7 fa 38 12 c0 0e

And feed it to bn_hex_in(),

SELECT bn_in_hex('5951a1202e656226abf3afe69316db1c3b3724477532729579e9d7fa3812c00e');
                                   bn_in_hex                                   
-------------------------------------------------------------------------------
 40400070184923956455012790534029088594440775740385060328118871212830923866126
(1 row)

You can confirm that with Wolfram Alpha

Size advantages

Numeric is pretty big, from the docs

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 above example in numeric is 46 bytes (you can check with ::text::numeric), as a bignum it's 36.

Postgresql – why is the name `loop` treated differently in PostgreSQL

The word LOOP is a reserved word in PL/pgSQL. If you want to use it as a column name, you need to quote it.

Best Answer

Related Solutions

Postgresql – Adding unsigned 256 bit integers in PostgreSQL

Using pg-bignum

Size advantages

Postgresql – why is the name `loop` treated differently in PostgreSQL

Related Question

Using `pg-bignum`