Menu
I'd like to store a simple map of key-value strings as a field in my PostgreSQL table. I intend to treat the map as a whole; i.e, always select the entire map, and never query by its keys nor values.
I've read articles comparing between hstore, json and jsonb, but those didn't help me choose which data-type is most fitting for my requirements, which are:
null.What data-type would best fit my use case?
909
The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed.
PostgreSQL jsonb Json preserves the original formatting like the whitespaces as well as the ordering of keys. Jsonb does not preserve the original formatting of text like the whitespaces and the ordering of keys. Json processes input faster than jsonb as there is no conversion involved in this.
The JSONB data type stores JSON (JavaScript Object Notation) data as a binary representation of the JSONB value, which eliminates whitespace, duplicate keys, and key ordering. JSONB supports GIN indexes.
The type names serial and serial4 are equivalent: both create integer columns. The type names bigserial and serial8 work just the same way, except that they create a bigint column.
UUID is an ideal Data type for primary keys. Postgre also accepts alternative forms of UUID inputs like all capital case, no hyphens, braces, etc. PostgreSQL allows you to store XML data in a data type, but it is nothing more than an extension to a text data type.
PostgreSQL supports character data types for storing text values. PostgreSQL builds character data types off of the same internal structures. PostgreSQL offers three character data types: CHAR(n), VARCHAR(n), and TEXT.
This data type is used to store date-time data. PostgreSQL has 5 temporal data type: DATE is used to store the dates only. TIME is used to stores the time of day values. TIMESTAMP is used to stores both date and time values. TIMESTAMPTZ is used to store a timezone-aware timestamp data type.
Well, mostly, as long as you remember what the PostgreSQL data types used in the table columns are, and use the correct PostgreSQL to Java mappings. As an experiment I tried a few data types including int2/int4/int8 (short, int, long), char/text (String), numeric (BigDecimal).
you could use hstore which is a keymap, however I personally would us jsonb. It's a little overkill, however, most languages can convert json natively without having to decode it yourself.
In json, I'd just store a simple object or array for the info you're trying to store.
Both support indexes and are efficiently stored.
Hstore in text is a custom type format that your language may be unaware of and thus require processing the data to insert or query.
92
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
