How to get the count of the field in postgres?

Question

I have a table orders whose structure is like this:

create table orders (id serial primary key, created_at timestamp, updated_at timestamp, product_id varchar[]);

I for the product_id I am inserting data like this:

insert into orders values (1,now(),now(),'{"1","2"}');

the product_id holds the different products that were ordered in a particular order. There is a entry in products table for each of the product_id.

Now I want to get the counts of the product_id for a particular order.

Example: For the above insertion I made, I should get something like:

Id    Count
1     2

How is it possible is postgresql?

Edit: Sorry for messing with the create table query. It's a varchar[] instead of varchar(255)

Normalize the schema and use a linking table to link products to orders along with the quantity. (Read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|192.9954).) Then this is easy. And BTW 9.3 isn't supported any longer, consider upgrading. — sticky bit, May 18 '20 at 10:58
Show us you've tried something and I'll fix it if there you have issues. — hd1, May 18 '20 at 10:59
@stickybit I am doing this as a hobby project to learn the backend development. I too found out that this is a bad practice. I'm thinking of having a third table to link the products and orders instead of writing this as a list of production. Stil wanted to know if this is possible in any way. — Sulove Bista, May 18 '20 at 11:03
@a_horse_with_no_name oh sorry. it's a `varchar[]`. have updated the question — Sulove Bista, May 18 '20 at 11:07
You should nevertheless upgrade to a supported and maintained Postgres version as soon as possible. — , May 18 '20 at 11:28

score 1 · Accepted Answer · edited May 18 '20 at 11:26

1

You can convert the value to an array and measure the number of values:

select o.*, cardinality(product_id)
from orders o

edited May 18 '20 at 11:26

answered May 18 '20 at 11:09

Gordon Linoff

1,242,037
58
646
786

it doesn't work. I get the error saying `string_to_array function` doesn't exist – Sulove Bista May 18 '20 at 11:18
`select o.*, cardinality(product_id) from orders o;` this works though – Sulove Bista May 18 '20 at 11:21
Another solution I found is by using the array_length() function `SELECT coalesce(array_length(product_id, 1), 0) as size FROM orders;` – Sulove Bista May 18 '20 at 12:38

score 0 · Answer 2 · answered May 18 '20 at 11:21

0

DDL:

CREATE TABLE tbl1 (id integer, count json);

INSERT INTO tbl1 (id, count) values (1, '["1", "2"]'::json), (2, '["3", "45"]');

Query:

 SELECT id, sum(json_array_length(count)) as count from tbl1 group by id order by id;

Result:

 id | count 

----+-------

  1 |     2

  2 |     2

I do hope it helps

answered May 18 '20 at 11:21

hd1

33,938
5
80
91

for this it would require me to change the table structure and migrate the data. For the existing table structure, I did find a couple of ways to do but not sure if it is correct. Will update in a while – Sulove Bista May 18 '20 at 11:23
You didn't give your DDL statements, at least initially, so I had to improvise @SuloveBista – hd1 May 18 '20 at 11:30

How to get the count of the field in postgres?

2 Answers2