PostgreSQL field type to store string, int, or json

Question

I want to store a table for variables with table definition like below

variable
--------
id           int
var_type     int  0: number, 1: string, 2: json
var_name     int
var_value    ??? varchar or jsonb?

If I use varchar, how to store the json type variable and if, I am using jsonb how to store the int and string?

The example json value that is stored,

[{"name": "Andy", "email" : "[email protected]"},{"name": "Cindy", "email" : "[email protected]"}]

TIA

Beny

Variable types aren't really a thing relational schemas are good at. Try to avoid it if possible. Why not simply having a name and an email column with appropriate types each? — sticky bit
– sticky bit, Commented Feb 7, 2021 at 5:26
the idea is to have a variable that can be number, string or json (array). the example of name and email aboce is just an example. it can be anything, [{'first_name' : 'Andy', 'last_name' : 'Robertson'}, {'first_name' : 'Cindy', 'last_name' : 'McGill'}]. — Beny Setiawan
– Beny Setiawan, Commented Feb 7, 2021 at 7:08

Schwern · Accepted Answer · 2021-02-07 05:45:29Z

1

When you have data and you don't know the structure, use a single jsonb column. JSON can handle strings, numbers, and more JSON.

{
  "string": "basset hounds got long ears",
  "number": 23.42,
  "json": [1,2,3,4,5]
}

Don't try to cram them all into a single array. Put them in separate rows.

One row: {"name": "Andy", "email" : "[email protected]"}
Another row: {"name": "Cindy", "email" : "[email protected]"}

However, your example feels like its avoiding designing a schema. JSONB is useful, but overusing it defeats the point of a relational database.

create table people (
  id bigserial primary key,

  // Columns for known keys which can have constraints.
  name text not null,
  email text not null,

  // JSONB for extra keys you can't predict.
  data jsonb
)

Use the JSON operators to query individual pairs.

select
  name, email, data->>'favorite dog breed'
from some_table

edited Feb 7, 2021 at 5:45

answered Feb 7, 2021 at 5:37

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Beny Setiawan Over a year ago

thank you for your explanation. The thing is that name and email are there not because they are permanently there. Another JSON type variable is [{'first_name' : 'Andy', 'last_name' : 'Robertson'}, {'first_name' : 'Cindy', 'last_name' : 'McGill'}].

Beny Setiawan Over a year ago

if I stored as varchar, How can I cast this varchar to this json where var_type is json?

Schwern Over a year ago

@BenySetiawan Don't store it as varchar, use a single jsonb column for everything.

Schwern Over a year ago

@BenySetiawan Here's an example. Again, I advise you consider a more traditional table structure before slapping everything into jsonb.

Beny Setiawan Over a year ago

Thanks a lot @Schwern. I follow your suggestion that I move the important thing to a field and only leave unstructured fields in the jsonb field.

Collectives™ on Stack Overflow

PostgreSQL field type to store string, int, or json

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related