Re: [GENERAL] filter records by substring match of an postgresql array column

t; On Nov 8, 2017, at 4:02 PM, Arup Rakshit <aruprakshit1...@outlook.com> wrote: > > Hi, > > I do have a videos table, and it has a column called `tags` of type array. I > would like to select all videos where any string inside tag column matches a > given sub

[GENERAL] filter records by substring match of an postgresql array column

Hi, I do have a videos table, and it has a column called `tags` of type array. I would like to select all videos where any string inside tag column matches a given substring. What method should I use? The *Contains `@>` operator* will do full string comparisons as far as I understood. --

[GENERAL] Exclude posts which was from blacklisted users Sql help

Hello, Below is my table structure: musedb_dev=# \d kudosposts Table "public.kudosposts" Column|Type |Modifiers

Re: [GENERAL] Left join help

t; wrote: On 06/24/2017 08:01 AM, Arup Rakshit wrote: Hi, Thanks everyone for taking time to explain this. I tried to add a case statement and getting errors. Can you tell me how should I add a column to mark which mission is completed and which is not. My try is not working. Instead of the new CA

Re: [GENERAL] Left join help

Hi, Thanks everyone for taking time to explain this. I tried to add a case statement and getting errors. Can you tell me how should I add a column to mark which mission is completed and which is not. My try is not working. Instead of the new CASE expression, the query works as expected.

Re: [GENERAL] Left join help

t;> wrote: On Friday, June 23, 2017, Arup Rakshit <aruprakshit1...@outlook.com<mailto:aruprakshit1...@outlook.com>> wrote: FROM "missions" LEFT JOIN submissions ON submissions.mission_id = missions.id<http://missions.id/> INNER JOIN members ON members.id<http://membe

[GENERAL] Left join help

Hi, I have this relationship Track has many missions. Missions has many submissions. Each Submission has one member and one mission. Say I have track id 7. I want to find out which missions under track 7 are submitted by John ( a user id say 3 ) and which are not yet. I tried a query, but it

Re: [GENERAL] Question regarding the output of postgresql *explain* command

Hi Tom, Thanks, I’ll read this page. > On May 28, 2017, at 8:36 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > Arup Rakshit <aruprakshit1...@outlook.com> writes: >> I was reading to day how indexing works. And I was trying some query, for >> example below on

[GENERAL] Question regarding the output of postgresql *explain* command

Hi, I was reading to day how indexing works. And I was trying some query, for example below one. What the range basically means (cost=0.28..8.30 ? I don’t understand this. ——— arup@ror ~/part-time-projects/entrylvl (add_index_to_job_sources)$ rails db psql (9.5.0) Type "help" for

[GENERAL] SQL query problem of a Quiz program

t work out. Regards, Arup Rakshit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Delete from table conditionally

is "Arup". Now from this row, I want to delete all next rows where the content is "Arup". How should I achieve this? Regards, Arup Rakshit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Random order by but first 3

Hi, Suppose I have a column t1 for a table. Now t1 holds some numerice value for each row. Say R1 to R5 records has values for the column t1 as : t1(2,5,8,10,32) I want the result to be printed as (10, 32, 8, 2, 5) means - Big, Biggest, small , then any random order Regards, Arup Rakshit

[GENERAL] Postgresql 9.4 upgrade openSUSE13.1

@music_track (master)]$ -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- Sent via pgsql

[GENERAL] JSONB matching element count

Hi, This says if matched found or not against the input array : '{a:1, b:2, c:3}'::jsonb ?| array['b', 'c'] But how would I determine how many matched ? Like for the above example, I see only 2 matched found. -- Regards, Arup Rakshit Debugging is twice

[GENERAL] Between with a list of ranges possible ?

; -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- Sent via pgsql-general mailing list (pgsql

Re: [GENERAL] How to skip duplicate records while copying from CSV to table in Postgresql using COPY

On Sunday, May 24, 2015 07:24:41 AM you wrote: On 05/24/2015 04:55 AM, Arup Rakshit wrote: On Sunday, May 24, 2015 02:52:47 PM you wrote: On Sun, 2015-05-24 at 16:56 +0630, Arup Rakshit wrote: Hi, I am copying the data from a CSV file to a Table using COPY command. But one thing

[GENERAL] How to skip duplicate records while copying from CSV to table in Postgresql using COPY

in advance! -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- Sent via pgsql-general

Re: [GENERAL] How to skip duplicate records while copying from CSV to table in Postgresql using COPY

On Sunday, May 24, 2015 02:52:47 PM you wrote: On Sun, 2015-05-24 at 16:56 +0630, Arup Rakshit wrote: Hi, I am copying the data from a CSV file to a Table using COPY command. But one thing that I got stuck, is how to skip duplicate records while copying from CSV to tables. By looking

Re: [GENERAL] How to skip duplicate records while copying from CSV to table in Postgresql using COPY

, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] How to recover or resent the password for the user 'postgres'

On Monday, March 30, 2015 06:27:19 AM Adrian Klaver wrote: On 03/30/2015 01:09 AM, Arup Rakshit wrote: Hi, I am trying to follow what has been mentioned below **Setting Up Postgres** (https://www.digitalocean.com/community/tutorials/how-to-setup-ruby-on-rails-with-postgres

[GENERAL] How to recover or resent the password for the user 'postgres'

(add_postgres_addapter)]$ It is asking me the password, whereas I don't know the password of the user `postgres`. How would I create a new role with a password in this case ? I am using OS X version 10.8.2 Regards, Arup Rakshit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

[GENERAL] JSON merge in postgresql

, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

[GENERAL] What is the alternate of FILTER below Postgresql 9.4 ?

version ? -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- Sent via pgsql-general

Re: [GENERAL] What is the alternate of FILTER below Postgresql 9.4?

On Tuesday, February 24, 2015 06:14:53 PM Andreas Kretschmer wrote: Arup Rakshit arupraks...@rocketmail.com wrote: This *FILTER* method is available from 9.4, How can I get the same output below 9.4 version ? case when ... see: http://www.cybertec.at/postgresql-9-4-aggregation

Re: [GENERAL] What is the alternate of FILTER below Postgresql 9.4 ?

On Tuesday, February 24, 2015 06:13:21 PM Pavel Stehule wrote: Hi 2015-02-24 17:02 GMT+01:00 Arup Rakshit arupraks...@rocketmail.com: Hi, Please look at my query : [shreyas@rails_app_test (master)]$ rails db psql (9.4.1) Type help for help. app_development=# select id

Re: [GENERAL] What is the alternate of FILTER below Postgresql 9.4?

On Tuesday, February 24, 2015 06:14:53 PM Andreas Kretschmer wrote: Arup Rakshit arupraks...@rocketmail.com wrote: This *FILTER* method is available from 9.4, How can I get the same output below 9.4 version ? case when ... see: http://www.cybertec.at/postgresql-9-4-aggregation

[GENERAL] Query optimization to select rows instead of too many or conditions

wired. Any better way to get it done ? -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan

Re: [GENERAL] Query optimization to select rows instead of too many or conditions

On Saturday, February 21, 2015 12:42:03 PM Alban Hertroys wrote: On 21 Feb 2015, at 9:34, Arup Rakshit arupraks...@rocketmail.com wrote: Select * from Emp where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14and attr2 = val15 and attr3 = val16); Now

[GENERAL] HINT: Perhaps you need a different datestyle setting - postgresql

: invalid value for parameter DateStyle: ISO, BIS IS 7900:2001 DETAIL: List syntax is invalid. prac_db=# My Data style inside the CSV exactly like - http://en.wikipedia.org/wiki/Date_and_time_notation_in_India. How to fix this problem? -- Regards, Arup Rakshit

Re: [GENERAL] HINT: Perhaps you need a different datestyle setting - postgresql

| integer| Indexes: orders_pkey PRIMARY KEY, btree (order_id) \d: extra argument ; ignored prac_db=# -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code

Re: [GENERAL] HINT: Perhaps you need a different datestyle setting - postgresql

On Sunday, December 28, 2014 01:24:00 PM you wrote: On 28 Dec 2014, at 12:06, Arup Rakshit arupraks...@rocketmail.com wrote: Now I have another problem: prac_db=# SET datestyle = SQL, DMY; SET prac_db=# copy orders from '/home/arup/postgresql/order.csv' with CSV DELIMITER

[GENERAL] Uninstall Postgresql in openSUSE 13.1

I have to remove? Or is there any other safe approach or not ? -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough

Re: [GENERAL] Uninstall Postgresql in openSUSE 13.1

On Saturday, December 20, 2014 12:40:08 AM Arup Rakshit wrote: Hi, I need to uninstall postgresql. I downloaded using http://www.enterprisedb.com/products-services-training/pgdownload . But now I want to uninstall it. As there are no uninstaller, I am thinking to install it manually

Re: [GENERAL] Uninstall Postgresql in openSUSE 13.1

On Saturday, December 20, 2014 12:53:44 AM Arup Rakshit wrote: On Saturday, December 20, 2014 12:40:08 AM Arup Rakshit wrote: Hi, I need to uninstall postgresql. I downloaded using http://www.enterprisedb.com/products-services-training/pgdownload . But now I want to uninstall

[GENERAL] How can I group all children by their parent ?

How can I group all children by their parent ?  id email parent_id 1 t...@test.com nil 2 te...@test.com 1 3 email 1 4 email 2 5 email nil 6 email 3   Regards, Arup Rakshit

Re: [GENERAL] How can I group all children by their parent ?

ORDER BY parent_id, id ? François Beausoleil parent_id .. But why order_by.. I thought I need to group by parent child email  1      2        te...@test.com                 3        email

Re: [GENERAL] Not able to understand how to write group by

Are you sure this is what you want? Since there are two columns you will have to either use a CASE or a sub-select to facilitate calculating the values for each of the columns. SELECT gender, answer1_avg, answer2_avg FROM (SELECT DISTINCT gender FROM ...) gn LEFT JOIN (SELECT gender,

Re: [GENERAL] Not able to understand how to write group by

Without commenting on the rest of it...to combine what you show here just GROUP BY gender and SUM() everything else (i.e., turn the above into a subquery and then do this) David J. Exactly.. I am done. Here is the ORM query :-   def self.employee_learning_by_gender(question_id)     cpd_id

[GENERAL] Not able to understand how to write group by

3 m 5 4 12 f 71523 Please let me know if you need any more information on this ? Regards, Arup Rakshit Debugging is twice

[GENERAL] converting a N rows table to a 1 row table ?

Hi, One query is producing the below table :- answer |  count  |  avg   a1       3         14   a2       2         10 How to convert this to a single row table ? count | avg_a1  | avg_a2   5       14       10   Regards, Arup Rakshit

Re: [GENERAL] converting a N rows table to a 1 row table ?

Hi, Could you have multiple row with same answer ? If I understand you one row and N + 1 column where N is the number of answer ? Regards You are right. Current it is fixed 2 answer. It means N = 2.

Re: [GENERAL] Not able to understand how to write group by

On Thursday, July 03, 2014 09:04:36 AM John R Pierce wrote: On 7/3/2014 4:01 AM, Arup Rakshit wrote: Exactly.. I am done. Here is the ORM query :- OT, but it boggles my mind that anyone thinks thats 'better' than the straight SQL I would like to see your idea. Could you please ? My thick

Re: [GENERAL] Not able to understand how to write group by

Are you sure this is what you want? Since there are two columns you will have to either use a CASE or a sub-select to facilitate calculating the values for each of the columns. SELECT gender, answer1_avg, answer2_avg FROM (SELECT DISTINCT gender FROM ...) gn LEFT JOIN (SELECT gender,

Re: [GENERAL] Not able to understand how to write group by

On Thursday, July 03, 2014 09:04:36 AM John R Pierce wrote: On 7/3/2014 4:01 AM, Arup Rakshit wrote: Exactly.. I am done. Here is the ORM query :- OT, but it boggles my mind that anyone thinks thats 'better' than the straight SQL I would like to see your idea. Could you please ? My thick

Re: [GENERAL] Not able to understand how to write group by

On Thursday, July 03, 2014 11:49:04 AM John R Pierce wrote: On 7/3/2014 8:24 AM, Arup Rakshit wrote: OT, but it boggles my mind that anyone thinks thats 'better' than the straight SQL I would like to see your idea. Could you please ? My thick brain not able to produce any straight

[GENERAL] How can I replace the year of the created_at column with the current year dynamically ?

/2014  20/06/2014  20/02/2014 (15 rows) Can the same be done using any other clever trick ?  Regards, Arup Rakshit

Re: [GENERAL] How can I replace the year of the created_at column with the current year dynamically ?

Here is my try : staging::= select  to_char(created_at,'DD/MM') || '/' || to_char(now(),'') as when from users;     when      24/02/2014  28/02/2014  02/03/2014  01/03/2014  04/03/2014  02/03/2014  06/03/2014  07/05/2014  02/06/2014  06/06/2014  20/02/2014  20/02/2014  

Re: [GENERAL] How can I replace the year of the created_at column with the current year dynamically ?

On Wednesday, July 02, 2014 08:42:43 AM Steve Crawford wrote: On 07/01/2014 11:27 PM, Arup Rakshit wrote: Here is my try : staging::= select to_char(created_at,'DD/MM') || '/' || to_char(now(),'') as when from users; when 24/02/2014 ... 20

[GENERAL] Not able to understand how to write group by

3 m 5 4 12 f 71523 Please let me know if you need any more information on this ? Regards, Arup Rakshit Debugging is twice

Re: [GENERAL] Not able to understand how to write group by

to get the data as per the format I am looking for. -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian

Re: [GENERAL] Not able to understand how to write group by

On Wednesday, July 02, 2014 02:49:54 PM you wrote: On Wed, Jul 2, 2014 at 1:44 PM, Arup Rakshit arupraks...@rocketmail.com wrote: *group by* on full table(*users*). I am away from our production DB. Could you tell me how this little change will solve the whole problem and help me

Re: [GENERAL] Not able to understand how to write group by

for A1. Same stands for male and non-gender also. -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug

Re: [GENERAL] Not able to understand how to write group by

for A1. Same stands for male and non-gender also. -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug

[GENERAL] collecting employees who completed 5 and 10 years in the current month

I have employee table. Where I have a column joining_date. Now I am looking for a way to get all employee, who completed 5 years, 10 years current month. How to do so ? I am not able to figure this out.   Regards, Arup Rakshit

[GENERAL] select users as per their years completion date current year

/2014 completed 15 years Foo_4 on 21/3/2014 completed 5 years They should come in the output    Regards, Arup Rakshit

Re: [GENERAL] collecting employees who completed 5 and 10 years in the current month

query. But view of course a good idea.In our web app, we will show this data as a report. A user can run it whenever he/she feel. All query seems like current day query. But I really need current month. Again it sometimes feel like ok, sometimes not. :-) -- Regards, Arup Rakshit

Re: [GENERAL] collecting employees who completed 5 and 10 years in the current month

-- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- Sent via pgsql-general mailing list (pgsql

[GENERAL] pgsql age function showing 00:00:00 with year argument

:00 |     1 (6 rows) Question - (a) How to convert age to 1 year when age will come as 00:00:00 or calculate the age in years in with rounding like 0.4, 0.5, 47.3 years like that ? (b) Why group by didn't group all '00:00:00' ?   Regards, Arup Rakshit

[GENERAL] How can I get first day date of the previous month ?

How can I get first day date of the previous month. Last day of previous month can be found using the answer - http://stackoverflow.com/a/8945281/2767755   Regards, Arup Rakshit

Re: [GENERAL] How can I get first day date of the previous month ?

? I am new pgdql DB :-) Awesome DB it is...    Regards, Arup Rakshit On Friday, 20 June 2014 12:22 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Fri, Jun 20, 2014 at 3:42 PM, Arup Rakshit arupraks...@rocketmail.com wrote: How can I get first day date of the previous month. Last

Re: [GENERAL] How can I get first day date of the previous month?

reply at the bottom and not to top-post. Yes, you are correct. Otherwise it is very hard to follow. One suggestion I need from you. Would it be a good to start straight from doco, or should I start from a book ? Again thanks for writing . -- Regards, Arup Rakshit

[GENERAL] How to select rows for which column has empty array ?

-# ;  id    5 (1 row) How would I select rows which has empty array for the field team_ids ?   Regards, Arup Rakshit

Re: [GENERAL] How can I select rows by comparing an array data type column with multiple values ?

query in production.   Regards, Arup Rakshit On Monday, 2 June 2014 9:04 PM, Steve Crawford scrawf...@pinpointresearch.com wrote: On 06/02/2014 03:54 AM, Arup Rakshit wrote: Hi, Suppose, I have a table as below :- id |    title     |    content     |       tags

Re: [GENERAL] How can I select rows by comparing an array data type column with multiple values ?

...                                                              ^ HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. : SELECT reporting_groups.* FROM reporting_groups  WHERE (ARRAY[NULL] workplace_ids)   Regards, Arup Rakshit On Wednesday, 4 June 2014 11

Re: [GENERAL] How can I select rows by comparing an array data type column with multiple values ?

  +--- (0 rows)                                                      yelloday_development=# select id, workplace_ids from reporting_groups where workplace_ids ARRAY[1,4,5]::integer[];  id | workplace_ids  +---   4 | {1} (1 row)   Regards, Arup Rakshit On Wednesday, 4 June 2014 12:05

[GENERAL] How can I select rows by comparing an array data type column with multiple values ?

            | { banana, lemon }   4 | third post   | foo baz           | { watermelon, lemon } Now I want to select all rows, for which tags will having either one or all value from the this array [apple,banana] ? how should I write the query using such a set ? output should select 1,2,3.   Regards, Arup

Re: [GENERAL] How can I select rows by comparing an array data type column with multiple values ?

. -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- Sent via pgsql-general mailing list