Create array of data from JSONB in PG 9.5

cd-be5a-4225-a2db-b549105e4172'; When I ran the same query to production I get error: ERROR: set-valued function called in context that cannot accept a set Thanks, Arup Rakshit a...@zeit.io

Re: Extend inner join to fetch not yet connected rows also

> On 22-Sep-2019, at 6:55 PM, Jan Kohnert > wrote: > > Hi Arup, > > Am Sonntag, 22. September 2019, 14:48:20 CEST schrieb Arup Rakshit: >> Hi Jan, >> >>> On 22-Sep-2019, at 5:38 PM, Jan Kohnert >>> wrote: >>> maybe something like

Re: Extend inner join to fetch not yet connected rows also

Hi Jan, > On 22-Sep-2019, at 5:38 PM, Jan Kohnert > wrote: > > Hey, > > Am Sonntag, 22. September 2019, 13:21:46 CEST schrieb Arup Rakshit: >> I have craftsmanships table which has (id, name) and users table (id, email, >> ..). When a user has some craftsmanshi

Re: Extend inner join to fetch not yet connected rows also

OR contractor_skills.user_id IS NULL) ORDER BY "craftsmanships"."id”; But after I read yours I found mine is doing lot of unnecessary joins. Thank you. Thanks, Arup Rakshit a...@zeit.io > On 22-Sep-2019, at 5:38 PM, Jan Kohnert > wrote: > > Hey, > &

Extend inner join to fetch not yet connected rows also

---+-+- 1 | paint | t 2 | drywall | t (2 rows) But I want to list all craftsmanships and has column should have `t` when user_id #8 has it, else `f`. How can I extend thisquery? Thanks, Arup Rakshit a...@zeit.io

Re: duplicate key value violates unique constraint "chinese_price_infos_pkey"

company_id | uuid| | | Indexes: "chinese_price_infos_pkey" PRIMARY KEY, btree (id) "index_chinese_price_infos_on_company_id" btree (company_id) Thanks, Arup Rakshit a...@zeit.io > On 06-May-2019, at 4:38 PM, Ray O'Donnell wrote: > > On 06/05/2019 12:05, A

duplicate key value violates unique constraint "chinese_price_infos_pkey"

dy exists. Then I found: docking_dev=# SELECT MAX(id) FROM chinese_price_infos; max 128520 (1 row) docking_dev=# SELECT nextval('chinese_price_infos_id_seq'); nextval - 71164 (1 row) Not sure how it is out of sync. How can I fix this permanently. I ran vacuum analyze verbose; still same error. Thanks, Arup Rakshit a...@zeit.io

Re: SQl help to build a result with custom aliased bool column

tables I created above in different schema to ask question with sample data and test the query output. So they are little inconsistent, yes you are right. Thanks, Arup Rakshit a...@zeit.io > On 08-Apr-2019, at 4:36 PM, mariusz wrote: > > bear in mind that with a large number of comp

Re: SQl help to build a result with custom aliased bool column

| t 2 | f2 | t 3 | f3 | f 3 | f3 | t 4 | f4 | f (5 rows) I should get in the results only 3, as total number of features are 3. Thanks, Arup Rakshit a...@zeit.io > On 08-Apr-2019, at 3:28 PM, Szymon Lipiński wrote: > > Hey, > you could just use > >

Re: SQl help to build a result with custom aliased bool column

| 4|f4 | | Thanks, Arup Rakshit a...@zeit.io > On 08-Apr-2019, at 3:28 PM, Szymon Lipiński wrote: > > Hey, > you could just use > > SELECT > features.id, > features.name, > company_features.company_id = 1 as active > > reg

SQl help to build a result with custom aliased bool column

= features.id It works. But is there any better way to achieve this? Thanks, Arup Rakshit a...@zeit.io

ERROR: type "myrowtype" does not exist

uot;: 4, "e": "a b c"}}'); ERROR: type "myrowtype" does not exist LINE 1: select * from json_populate_record(null::myrowtype, '{"a": 1… What does null::extype, null::myrowtype mean here ? Thanks, Arup Rakshit a...@zeit.io

Re: Why my query not using index to sort?

'26987b95-8652-491c-b950-4fb3128f4714', 'c4f55044-3f4e-439e-a586-8b6978c7b406',

Re: How to improve sql query to achieve the better plan

; btree (project_id) "index_workitems_on_standard_workitem_id" btree (standard_workitem_id) "index_workitems_on_workitem_category_id" btree (workitem_category_id) "patrial_index_workitems_200_1" btree (project_id) WHERE deleted_at IS NULL Thanks, Arup Ra

How to improve sql query to achieve the better plan

; btree (parent_workitem_id) "index_workitems_on_project_id" btree (project_id) "index_workitems_on_standard_workitem_id" btree (standard_workitem_id) "index_workitems_on_workitem_category_id" btree (workitem_category_id) Thanks, Arup Rakshit a...@zeit.io

Re: Why my query not using index to sort?

'6fed40b7-fdd7-4efb-a163-c2b42e6486ae'::uuid) Planning time: 0.191 ms Execution time: 1.210 ms I have no idea why PG thinks in memory sort will be cheaper.. Thanks, Arup Rakshit a...@zeit.io > On 29-Sep-2018, at 9:40 PM, Arup Rakshit wrote: > > Hello Tom, > > I hav

Re: Why my query not using index to sort?

items_on_item_code" btree (item_code) "index_standard_workitems_on_workitem_category_id" btree (workitem_category_id) "standard_workitems_partial_index_idx_1_1" btree (company_id, item_code) WHERE deleted_at IS NULL Thanks, Arup Rakshit a...@zeit.io > On 28-Se

Re: Why my query not using index to sort?

Hello Adrian, I used to consider this time as the sql execution time approximately. I’ll do some research and get back to you. Thanks for mentioning it. Thanks, Arup Rakshit a...@zeit.io > On 29-Sep-2018, at 2:50 AM, Adrian Klaver wrote: > > On 9/28/18 12:14 PM, Arup Raks

Re: Why my query not using index to sort?

Forgot to mention in my previous email, it was a quick send click. Sorry for that. In the gist you need to see all the line with Vessel Load(.. . I load the page multiple times to catch the different times, so you will the line multiple times there in the log file. Thanks, Arup Rakshit a

Re: Why my query not using index to sort?

4ad6 <https://gist.github.com/aruprakshit/a6bd7ca221c9a13cd583e0827aa24ad6> Thanks, Arup Rakshit a...@zeit.io > On 29-Sep-2018, at 12:28 AM, Adrian Klaver wrote: > > On 9/28/18 11:51 AM, Arup Rakshit wrote: >> Yes, I have shown the explain plan output. But in my applica

Re: Why my query not using index to sort?

Yes, I have shown the explain plan output. But in my application log the sql query prints 1.7 to 1.9 ms. Thanks, Arup Rakshit a...@zeit.io > On 29-Sep-2018, at 12:17 AM, Adrian Klaver wrote: > > On 9/28/18 11:32 AM, Arup Rakshit wrote: >> Yes. But I thought I could improve

Re: Why my query not using index to sort?

Yes. But I thought I could improve it, so that it can be finished in 0.xx ms. It takes now between 1.7 to 1.9 ms in production. Thanks, Arup Rakshit a...@zeit.io > On 28-Sep-2018, at 9:46 PM, Andreas Kretschmer > wrote: > > > > Am 28.09.2018 um 18:03 schrieb Arup

Re: Why my query not using index to sort?

Also I meatn the execution time is less than I had before with enable_sort = off. Why 6 rows was a side question. :) Thanks, Arup Rakshit a...@zeit.io > On 28-Sep-2018, at 9:29 PM, Andreas Kretschmer > wrote: > > > > Am 28.09.2018 um 16:49 schrieb Arup Rakshit: >&g

Re: Why my query not using index to sort?

Oh I see. That makes sense, I was reading too much into that line.. :) Thanks, Arup Rakshit a...@zeit.io > On 28-Sep-2018, at 9:29 PM, Andreas Kretschmer > wrote: > > > > Am 28.09.2018 um 16:49 schrieb Arup Rakshit: >> Planning time: 1.867 ms >> Execu

Re: Why my query not using index to sort?

-> Bitmap Index Scan on inspector_tool_idx20_1 (cost=0.00..4.88 rows=81 width=0) (actual time=0.059..0.059 rows=81 loops=1) Index Cond: (company_id = '86529964-6e9b-4bfa-ba9e-62bd24eaa954'::uuid) Planning time: 1.743 ms Execution time: 0.954 ms (12 rows) Th

Re: Why my query not using index to sort?

Hi Ravi, Yes you are right. It seems it is doing sorting in memory by name. But I have `name` column in index. Thanks, Arup Rakshit a...@zeit.io > On 28-Sep-2018, at 6:38 PM, Ravi Krishna wrote: > > >> Is there anyway, I can improve the sorting so that it can use the i

Why my query not using index to sort?

t; Bitmap Index Scan on inspector_tool_idx20_1 (cost=0.00..4.88 rows=81 width=0) (actual time=0.025..0.025 rows=81 loops=1) Index Cond: (company_id = '86529964-6e9b-4bfa-ba9e-62bd24eaa954'::uuid) Planning time: 0.168 ms Execution time: 0.533 ms Is there anyway, I can improve the sorting so that it can use the index ? Thanks, Arup Rakshit a...@zeit.io

Re: Help to understand Actual Rows vs Plan Rows from the query planner output

ires some experience to master, but this section attempts to cover the basics..” Lets see how can I get there. :) I probably need to ask a lot of questions. Thanks for sharing those points. I made a note about them. Thanks, Arup Rakshit a...@zeit.io > On 25-Sep-2018, at 11:45 AM, Laurenz

Re: Help to understand Actual Rows vs Plan Rows from the query planner output

Rakshit a...@zeit.io > On 24-Sep-2018, at 4:11 AM, Tomas Vondra wrote: > > > > On 09/23/2018 10:21 PM, Arup Rakshit wrote: >> Hello I have some questions related to the query plan output about the >> planned and actual rows. In the following example: >>

Help to understand Actual Rows vs Plan Rows from the query planner output

apAnd”, I see again the actual rows 1, then why on the final plan i.e. Node Type: "Bitmap Heap Scan” again planner estimated rows 1? How does it counts these? What does the Loops count says us about the query? Thanks, Arup Rakshit a...@zeit.io

Bitmap Heap Scan and Bitmap Index Scan

e cost is higher in Heap scan than index scan? Thanks, Arup Rakshit a...@zeit.io

Bitmap Heap Scan and Bitmap Index Scan

e cost is higher in Heap scan than index scan? Thanks, Arup Rakshit a...@zeit.io

Re: Can I add Index to make a query faster which involves joins on unnest ?

Hello Rob, So how do I improve this query speed? Thanks, Arup Rakshit a...@zeit.io > On 14-Sep-2018, at 12:27 AM, Rob Sargent wrote: > > > >> On Sep 13, 2018, at 12:17 PM, Arup Rakshit > <mailto:a...@zeit.io>> wrote: >> >> The below query bas

Can I add Index to make a query faster which involves joins on unnest ?

) (actual time=0.044..25.941 rows=6 loops=1) -> Sort (cost=4.32..4.57 rows=100 width=32) (actual time=0.108..3.946 rows=53289 loops=1) Sort Key: t.size Sort Method: quicksort Memory: 25kB -> Function Scan on unnest t (cost=0.00..1.00 rows=100 width=32) (actual time=0.030..0.033 rows=9 loops=1) Planning time: 0.667 ms Execution time: 62.846 ms Thanks, Arup Rakshit a...@zeit.io

Re: Select rows when all all ids of its children records matches

Hi Rob, I figured it out. thanks. It is giving correct data. > Aren't you looking for cte.tags = array[1,2]? posts_tags_cte has tags column, so I am using it. Thanks, Arup Rakshit a...@zeit.io > On 12-Sep-2018, at 9:47 PM, Rob Sargent wrote: > > > > On 09/12/2018

Re: Select rows when all all ids of its children records matches

, 2]::int8[] But it gives me all the posts. Thanks, Arup Rakshit a...@zeit.io > On 12-Sep-2018, at 9:14 PM, Arup Rakshit wrote: > > Can you show me the SQL construction? Do I need to use `WITH`? > > > Thanks, > > Arup Rakshit > a...@zeit.io <mailto:a...@zeit.io&

Re: Select rows when all all ids of its children records matches

Can you show me the SQL construction? Do I need to use `WITH`? Thanks, Arup Rakshit a...@zeit.io > On 12-Sep-2018, at 9:13 PM, David G. Johnston > wrote: > > On Wednesday, September 12, 2018, Arup Rakshit <mailto:a...@zeit.io>> wrote: > IN is OR, I want the AND l

Re: Select rows when all all ids of its children records matches

IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 ( tag ids ) Thanks, Arup Rakshit a...@zeit.io > On 12-Sep-2018, at 8:58 PM, Ron wrote: > > Maybe this: > select p.id, p.name > from posts p, > posts_tags pt, > tags t > where t.id i

Select rows when all all ids of its children records matches

I have a table posts(id, name), posts_tags(post_id, tag_id) and tags (id, name) ... I want to get all posts which has tag id 1, 2 and 3 for example. How should I do this? I tried ALL, but it didn’t work. Those tag ids comes from UI by users, so I am looking for generic approach. Thanks, Arup

Why order by column not using index with distinct keyword in select clause?

99 loops=1) Planning time: 0.213 ms Execution time: 9.086 ms (3 rows) aruprakshit=# Thanks, Arup Rakshit a...@zeit.io

Re: Why my query not doing index only scan

Hello Stephen, I would like to ask one more question related to this topic. When I take a dump from production, and restore it to development DB, what are the commands I generally need to run to dev deb quack close to production? Thanks, Arup Rakshit a...@zeit.io > On 10-Sep-2018, at 4

Re: Why my query not doing index only scan

044..0.626 rows=1232 loops=1) Index Cond: (company_id = '7881ff2e-0557-4734-9da8-2d33072ff1ef'::uuid) Heap Fetches: 0 Planning time: 0.190 ms Execution time: 0.778 ms (5 rows) Thanks, Arup Rakshit a...@zeit.io > On 10-Sep-2018, at 4:58 PM, Stephen Frost wrote: > > VACUUM

Why my query not doing index only scan

st=0.00..45.61 rows=1226 width=0) (actual time=0.347..0.347 rows=1232 loops=1) Index Cond: (company_id = '7881ff2e-0557-4734-9da8-2d33072ff1ef'::uuid) Planning time: 0.207 ms Execution time: 1.358 ms (7 rows) Thanks, Arup Rakshit a...@zeit.io

Re: help to query json column

Thanks Charles. that worked. > On Mar 7, 2018, at 12:27 PM, Charles Clavadetscher > wrote: > > Hello > >> -Original Message- >> From: Arup Rakshit [mailto:aruprakshit1...@outlook.com] >> Sent: Mittwoch, 7. März 2018 05:41 >> To: pgsql-general@l

help to query json column

Hi, I would like to select only rows where signature has a non null value. My json looks like : { "carInspection": { "damages": [ { "x": 68.670309653916, "y": 44.08014571949, "errors": [ { "code": "BR", "description": "Gebroch