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

[Arup Rakshit]

I enabled the extension `pg_trgm`.

I thought to query like:

SELECT
   "videos".* 
FROM
   "videos" 
WHERE
   (
  array_to_string(tags, ', ') ilike '%web shorts%'
   )
   AND EXISTS
   (
  SELECT
  FROM
 unnest(tags) AS u(val) 
  WHERE
 u.val ILIKE '%web shorts%'
   )
;

And to do I wanted to add an index like:

CREATE INDEX trgm_idx_video_tags ON videos USING gist ((array_to_string(tags, 
', ')) gist_trgm_ops)

But on running this I get an error as:

ERROR:  functions in index expression must be marked IMMUTABLE

How can I fix this?



> 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 substring. What method should I use? The *Contains `@>` operator* will 
> do full string comparisons as far as I understood.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

Hello,

Below is my table structure:

musedb_dev=# \d kudosposts
  Table "public.kudosposts"
Column|Type |Modifiers
--+-+-
 id   | integer | not null default 
nextval('kudosposts_id_seq'::regclass)
 content  | text|
 user_id  | integer |
 created_at   | timestamp without time zone | not null
 updated_at   | timestamp without time zone | not null
 pix  | character varying   |
 giphy_id | integer |
 destroyed_at | timestamp without time zone |
TABLE "user_posts" CONSTRAINT "fk_rails_3b5b08eb72" FOREIGN KEY 
(kudospost_id) REFERENCES kudosposts(id)
TABLE "comments" CONSTRAINT "fk_rails_bc8176e8bc" FOREIGN KEY 
(kudospost_id) REFERENCES kudosposts(id)

musedb_dev=# \d user_posts
  Table "public.user_posts"
Column|Type |Modifiers
--+-+-
 id   | integer | not null default 
nextval('user_posts_id_seq'::regclass)
 user_id  | integer |
 kudospost_id | integer |
 created_at   | timestamp without time zone | not null
 updated_at   | timestamp without time zone | not null
Foreign-key constraints:
"fk_rails_3b5b08eb72" FOREIGN KEY (kudospost_id) REFERENCES kudosposts(id)
"fk_rails_6c6a346128" FOREIGN KEY (user_id) REFERENCES users(id)

musedb_dev=# \d users
   Table "public.users"
 Column |Type | 
Modifiers
+-+
 id | integer | not null default 
nextval('users_id_seq'::regclass)
 email  | character varying   | not null default 
''::character varying
 
 black_list_user_ids| integer[]   | default '{}'::integer[]

Referenced by:
TABLE "comments" CONSTRAINT "fk_rails_03de2dc08c" FOREIGN KEY (user_id) 
REFERENCES users(id)
TABLE "settings" CONSTRAINT "fk_rails_5676777bf1" FOREIGN KEY (user_id) 
REFERENCES users(id)
TABLE "user_posts" CONSTRAINT "fk_rails_6c6a346128" FOREIGN KEY (user_id) 
REFERENCES users(id)
TABLE "kudosposts" CONSTRAINT "fk_rails_ba6b4c6f54" FOREIGN KEY (user_id) 
REFERENCES users(id)
TABLE "favorites" CONSTRAINT "fk_rails_d15744e438" FOREIGN KEY (user_id) 
REFERENCES users(id)
TABLE "user_kudos_milestones" CONSTRAINT "fk_rails_e5a78b2bce" FOREIGN KEY 
(user_id) REFERENCES users(id)

musedb_dev=#

I am trying list posts whose owner is not in black lists users column of the 
post receiver. But my sql still selecting the backlisted user posts.

Below is the SQL I tried:

SELECT
   "kudosposts".* 
FROM
   "kudosposts" 
   INNER JOIN
  "user_posts" "user_posts_kudosposts_join" 
  ON "user_posts_kudosposts_join"."kudospost_id" = "kudosposts"."id" 
   INNER JOIN
  "users" 
  ON "users"."id" = "user_posts_kudosposts_join"."user_id" 
  AND "users"."destroyed_at" IS NULL 
   INNER JOIN
  "user_posts" 
  ON "kudosposts"."id" = "user_posts"."kudospost_id" 
WHERE
   "kudosposts"."destroyed_at" IS NULL 
   AND "user_posts"."user_id" = 5 
   AND 
   (
  kudosposts.user_id != all (users.black_list_user_ids)
   )
ORDER BY
   "kudosposts"."created_at” DESC

Could you help me to find out where I am wrong?
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Left join help

[Arup Rakshit]

Hello Adrian,

The way John taught me this query with CTE and then how that can be using with 
JOIN really helped me to understand. The query below is from John and it worked.

SELECT *
FROM missions
LEFT JOIN submissions
INNER JOIN members
ON submissions.member_id = members.id<http://members.id> AND 
members.id<http://members.id> = 1
ON missions.id<http://missions.id> = submissions.mission_id
WHERE missions.track_id = 7

Result is attached.

When I added CASE expression to the above query it was giving me the error as I 
mentioned in previous email. I managed the CASE expression query with CTE as 
below:


with member_submissions as (
SELECT submissions.*
FROM submissions
INNER JOIN members ON submissions.member_id = members.id
AND members.id = 1
)
SELECT *, CASE WHEN member_submissions.id IS NULL THEN 'incompleted' ELSE 
'completed' END AS mission_status
FROM missions
LEFT JOIN member_submissions ON missions.id = member_submissions.mission_id
WHERE missions.track_id = 7

But still would like to know why it didn’t work with JOIN.

Thanks,
- A


On Jun 24, 2017, at 10:30 PM, Adrian Klaver 
<adrian.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com>> 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 CASE expression, the query works as expected.

Can you show what works?

SELECT missions.*, CASE WHEN submissions.id IS NULL THEN 'incompleted' ELSE 
'completed' END AS mission_status
FROM missions
LEFT JOIN submissions
INNER JOIN members
ON submissions.member_id = members.id AND members.id = 1
ON missions.id = submissions.mission_id
WHERE missions.track_id = 7

I am not how Postgres would determine which ON refers to which JOIN here?

To get back to your original request would the below work?:

SELECT
missions.*, CASE WHEN sub.id IS NULL THEN 'incompleted' ELSE 'completed' END AS 
mission_status
FROM
missions
LEFT JOIN
(SELECT * FROM submissions WHERE member_id = 1) AS sub
ON
missions.mission_id = sub.mission_id
WHERE
missions.track_id = 7


ERROR: missing FROM-clause entry for table "missions"
LINE 1: SELECT missions.*, CASE WHEN submissions.id IS NULL THEN 'in...
On Jun 24, 2017, at 4:53 AM, John W Higgins 
<wish...@gmail.com<mailto:wish...@gmail.com> <mailto:wish...@gmail.com>> wrote:

SELECT *
FROM missions
LEFT JOIN submissions
INNER JOIN members
ON submissions.member_id =members.id <http://members.id/>ANDmembers.id 
<http://members.id/>= 1
ONmissions.id <http://missions.id/>= submissions.mission_id
WHERE missions.track_id = 7


--
Adrian Klaver
adrian.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com>

"id","locked_title","locked_content","date","created_at","updated_at","locked_cta","locked_placeholder","locked_attachment_url","track_id","locked_attachment_name","locked_attachment_mime_type","locked_deadline_time","locked_character_minimum","promo_bg_url","promo_color_bg","promo_color_highlight","locked_character_minimum_message","locked_character_minimum_title","promo_title","promo_headline","promo_prize_title","promo_email_bg_url","feedback_headline","feedback_content","feedback_attachment_image_url","feedback_attachment_audio_url","feedback_attachment_video_url","feedback_audio_headline","promo_order"
5,"What's wrong with this advertising?","https://s27.postimg.org/ea9kcfr4j/Best_NYT.png"; width=""484"" height=""401"" />
For reference, here are the other 2 we looked at in this campaign:
Just one question for today's mission:
Why don't you have a subscription to the NYT?
Have you ever considered the benefit, value, or future desired state?
If not, what friction do you think is getting in the way?
*Notice theirCTA (button) improving as well","2017-06-09","2016-12-07 15:20:44.43092","2017-02-27 13:35:57.841557","Locked cta","Locked placeholder","",7,"Locked attachment name","image",,,"https://content.linkedin.com/content/dam/business/sales-solutions/global/en_US/blog/2017/01/Wolf-of-Wall-Street.jpg","#82FFA3","#FF722B",,,"promo_title","promo_headline","promo_prize_title","https://ucarecdn.com/a59f992c-2049-47ce-9123-8ea104a4a146/","THIS IS FEEDBACK","Below are two sample Facebook advertisements fromhttp://www.ashandanvil.com";>Ash  Anv

Re: [GENERAL] Left join help

[Arup Rakshit]

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.

SELECT missions.*, CASE WHEN submissions.id IS NULL THEN 'incompleted' ELSE 
'completed' END AS mission_status
FROM missions
LEFT JOIN submissions
INNER JOIN members
ON submissions.member_id = members.id AND members.id = 1
ON missions.id = submissions.mission_id
WHERE missions.track_id = 7



ERROR: missing FROM-clause entry for table "missions"
LINE 1: SELECT missions.*, CASE WHEN submissions.id IS NULL THEN 'in...


On Jun 24, 2017, at 4:53 AM, John W Higgins 
> wrote:

SELECT *
FROM missions
LEFT JOIN submissions
INNER JOIN members
ON submissions.member_id = members.id AND 
members.id = 1
ON missions.id = submissions.mission_id
WHERE missions.track_id = 7

Re: [GENERAL] Left join help

[Arup Rakshit]

Hi David,

Can you tell me how can I add ordering between LEFT and INNER JOIN. I think 
also that is where I am wrong, but not sure how to correct the ordering.

Thanks,
A


On Jun 24, 2017, at 3:18 AM, David G. Johnston 
<david.g.johns...@gmail.com<mailto:david.g.johns...@gmail.com>> 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://members.id/> = submissions.member_id

Quick look here but having submissions as part of the inner join with members 
defeats the attempt at left joining it with missions.  You might need to add 
parentheses to put the joins in the correct order, or something.  Why it gives 
zero results we cannot say without a self-contained example.

David J.

[GENERAL] Left join help

[Arup Rakshit]

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 is not giving me 
any result. Can anyone help me pls?

SELECT missions.*,
  CASE WHEN submissions.id IS NULL THEN 'incompleted'
  ELSE 'completed' END AS mission_status
FROM "missions" LEFT JOIN submissions ON submissions.mission_id = missions.id
INNER JOIN members ON members.id = submissions.member_id
WHERE (members.id = 1 AND missions.track_id = 7)

The above query really not showing any output. Any help to solve this?

Tables are posted here: http://dpaste.com/1EXF4KQ


Thanks,
A

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

[Arup Rakshit]

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 one. What the range basically means (cost=0.28..8.30 ? I don’t 
>> understand this.
> 
> https://www.postgresql.org/docs/current/static/using-explain.html
> 
>   regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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 help.

entrylvl_dev=# \d job_sources;
   Table "public.job_sources"
Column |Type |Modifiers
---+-+--
 id| bigint  | not null default 
nextval('job_sources_id_seq'::regclass)
 job_id| bigint  |
 source| character varying   |
 job_source_id | character varying   |
 created_at| timestamp without time zone | not null
 updated_at| timestamp without time zone | not null
Indexes:
"job_sources_pkey" PRIMARY KEY, btree (id)
"index_job_sources_on_source_and_job_source_id" UNIQUE, btree (source, 
job_source_id)
"index_job_sources_on_job_id" btree (job_id)
Foreign-key constraints:
"fk_rails_f45da00eca" FOREIGN KEY (job_id) REFERENCES jobs(id)

entrylvl_dev=# explain select * from job_sources where job_source_id = 
'p_1208b146c9eb8905' AND source = 'indeed';
QUERY PLAN
--
 Index Scan using index_job_sources_on_source_and_job_source_id on job_sources  
(cost=0.28..8.30 rows=1 width=58)
   Index Cond: (((source)::text = 'indeed'::text) AND ((job_source_id)::text = 
'p_1208b146c9eb8905'::text))
(2 rows)

entrylvl_dev=#

—


Thanks,
Arup

[GENERAL] SQL query problem of a Quiz program

[Arup Rakshit]

Hi,

Here is a sample data from table "quiz_results":

id | question_id |  user_id
+-+
  2 |  25 | 5142670086
  3 |  26 |
  4 |  26 |
  5 |  27 |
  6 |  25 | 5142670086
  7 |  25 | 5142670086
  8 |  25 | 5142670086
  9 |  26 |
 10 | 40 | 5142670086
 11 |  29 | 5142670086
 

As you see above question id 25 appeared more than once. This is basically a 
quiz result table where for users as they answered. question_id 25 always the 
first questions. Any user can go though the quiz N number of time. So, I want 
to find the last occurrence of the question_id 25 for any specific user in the 
table, and select that and all answers the users gave after this till the end 
of the quiz. Any idea how to solve it in a single efficient query. My all try 
didn'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

[Arup Rakshit]

Hi,

I am using Postgresql 9.5.

Below is the sample data taken from the mail table with only required columns:

id | question_id | answer_id | content
+-+---+--
2 |  25 |   270 | Arup
3 |  26 |   276 | Kajal
4 |  26 |   276 | Arup
5 |  27 |   280 | Kajal
6 |  25 |   270 | Arup
7 |  25 |   270 | Lalu
8 |  25 |   270 | Arup
9 |  26 |   274 |  Dilip
10 |  25 |   270 | doli
11 |  25 |   270 | Arup
(10 rows)


Say, I know the id 6 where content 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

[Arup Rakshit]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgresql 9.4 upgrade openSUSE13.1

[Arup Rakshit]

Hi,

I have installed long back postgresql92, and I want to upgrade it to postgresql 
9.4. But not seeing any package for that. Any help ?

[arup@music_track (master)]$ zypper info postgresql94
Loading repository data...
Reading installed packages...


package 'postgresql94' not found.
[arup@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 mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] JSONB matching element count

[Arup Rakshit]

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 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)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Between with a list of ranges possible ?

[Arup Rakshit]

Hi,

Can I do the below 3 queries in a single query  ?

select * from table where number * 3  between start_value1 and end_value2;
select * from table where number * 3  between start_value2 and end_value2;
select * from table where number * 3  between start_value3 and end_value3;


-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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 that I got stuck, is how to skip duplicate records while
  copying from CSV to tables. By looking at the documentation, it seems,
  Postgresql don't have any inbuilt too to handle this with copy
  command. By doing Google I got below 1 idea to use temp table.
 
  http://stackoverflow.com/questions/13947327/to-ignore-duplicate-keys-during-copy-from-in-postgresql
 
  I am also thinking what if I let the records get inserted, and then
  delete the duplicate records from table as this post suggested -
  http://www.postgresql.org/message-id/37013500.dff0a...@manhattanproject.com.
 
  Both of the solution looks like doing double work. But I am not sure
  which is the best solution here. Can anybody suggest which approach
  should I adopt ? Or if any better ideas you guys have on this task,
  please share.
 
  Assuming you are using Unix, or can install Unix tools, run the input
  files through
 
 sort -u
 
  before passing them to COPY.
 
  Oliver Elphick
 
 
  I think I need to ask more specific way. I have a table say `table1`, where 
  I feed data from different CSV files. Now suppose I have inserted N records 
  to my table `table1` from csv file `c1`. This is ok, next time when again I 
  am importing from a different CSV file say `c2` to `table1`, I just don't 
  want reinsert any record from this new CSV file to table `table1`, if the 
  current CSV data already table has.
 
  How to do this?
 
 As others have pointed out this depends on what you are considering a 
 duplicate.
 
 Is it if the entire row is duplicated?

It is entire row.

 Or if some portion of the row(a 'primary key') is duplicated?
 
 
My SO link is not a solution to my problem I see now.
 
 
 
 

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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 at the documentation, it seems, Postgresql don't have any 
inbuilt too to handle this with copy command. By doing Google I got below 1 
idea to use temp table.

http://stackoverflow.com/questions/13947327/to-ignore-duplicate-keys-during-copy-from-in-postgresql

I am also thinking what if I let the records get inserted, and then delete the 
duplicate records from table as this post suggested - 
http://www.postgresql.org/message-id/37013500.dff0a...@manhattanproject.com.

Both of the solution looks like doing double work. But I am not sure which is 
the best solution here. Can anybody suggest which approach should I adopt ? Or 
if any better ideas you guys have on this task, please share.

Thanks 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 mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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 at the documentation, it seems,
  Postgresql don't have any inbuilt too to handle this with copy
  command. By doing Google I got below 1 idea to use temp table.
  
  http://stackoverflow.com/questions/13947327/to-ignore-duplicate-keys-during-copy-from-in-postgresql
  
  I am also thinking what if I let the records get inserted, and then
  delete the duplicate records from table as this post suggested -
  http://www.postgresql.org/message-id/37013500.dff0a...@manhattanproject.com.
  
  Both of the solution looks like doing double work. But I am not sure
  which is the best solution here. Can anybody suggest which approach
  should I adopt ? Or if any better ideas you guys have on this task,
  please share.
 
 Assuming you are using Unix, or can install Unix tools, run the input
 files through
 
   sort -u
 
 before passing them to COPY.
 
 Oliver Elphick
 

I think I need to ask more specific way. I have a table say `table1`, where I 
feed data from different CSV files. Now suppose I have inserted N records to my 
table `table1` from csv file `c1`. This is ok, next time when again I am 
importing from a different CSV file say `c2` to `table1`, I just don't want 
reinsert any record from this new CSV file to table `table1`, if the current 
CSV data already table has.

How to do this?

 My SO link is not a solution to my problem I see now.

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

On Sunday, May 24, 2015 07:52:43 AM you wrote:
 
  Is it if the entire row is duplicated?
 
  It is entire row.
 
 So, Olivers second solution.
 

I have done this : 

columns_t1 = self.singleton_class.fields.map { |f| t1.#{f} }.join(,)
columns_t2 = self.singleton_class.fields.map { |f| t2.#{f} }.join(,)
ActiveRecord::Base.transaction do
  conn = ActiveRecord::Base.connection
  conn.execute CREATE TEMP TABLE tmp_table AS SELECT * FROM #{table.strip}; 
  conn.execute(COPY tmp_table ( #{self.singleton_class.fields.join(',') } ) 
FROM '#{source_file}' CSV HEADER DELIMITER '\t' QUOTE '|' ;)
  conn.execute INSERT INTO #{table.strip} ( 
#{self.singleton_class.fields.join(',')} ) SELECT DISTINCT #{columns_t1} FROM 
tmp_table t1 WHERE NOT EXISTS ( SELECT 1 FROM #{table.strip} t2 WHERE 
(#{columns_t2}) IS NOT DISTINCT FROM (#{columns_t1}) );
  conn.execute DROP TABLE IF EXISTS tmp_table;
End

The SQL wrapped inside the ActiveRecord ORM as you see above. But I hope you 
got the idea. But I am not sure, if it is the correct way to do it or how it 
will hit the performance.

The Application can run on different OS. So I am helpless to use Unix commands.

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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).
 
  But no luck!
 
  [shreyas@myapp(add_postgres_addapter)]$ su - postgres
  Password:
  su: Sorry
  [shreyas@myapp (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 ?
 
 How did you install Postgres?

I didn't install. I joined a company as an Application developer, where the 
machine I have been assigned had PostgreSql already installed. I changed the 
configuration of the pg_hba.config as *trust*, and it worked.

Thanks for the help to all of you.

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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).

But no luck!

[shreyas@myapp(add_postgres_addapter)]$ su - postgres
Password:
su: Sorry
[shreyas@myapp (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 changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] JSON merge in postgresql

[Arup Rakshit]

I have a simple table having column `data` which is a JSON type. Sample data I 
took from -- http://schinckel.net/2014/05/25/querying-json-in-postgres/

[arup@pg_food_mgmt (master)]$ rails db
psql (9.2.7)
Type help for help.

pg_foo_development=# SELECT * FROM json_test;
 id | data
+--
  1 | {}
  2 | {a: 1}
  3 | {a: 2, b: [c, d]}
  4 | {a: 1, b: {c: d, e: true}}
  5 | {b: 2}
(5 rows)

Now suppose I want to update the record#1 and # 2 as {a: 12} and {a: 2, 
b: [c, d]} .. What is the way to update this ?

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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, location, name from people;
 id | location | name
+--+--
  2 | X| foo
  3 | X| foo
  4 | Y| foo
(3 rows)

app_development=# SELECT COUNT(id) FILTER(WHERE lower(location) != 'x') AS 
Non_X_loc, COUNT(id) FILTER (WHERE lower(location) = 'x') AS X_loc FROM 
people;
 non_x_loc | x_loc
---+---
 1 | 2
(1 row)

This *FILTER* method is available from 9.4, How can I get the same output below 
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 mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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-filters-they-do-pay-off/
 
 Andreas
 

Humm.. That's correct. I used the same with group by, that's why got 2 rows 
output. 

Anway thanks for your help! :-)

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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, location, name from people;
   id | location | name
  +--+--
2 | X| foo
3 | X| foo
4 | Y| foo
  (3 rows)
 
  app_development=# SELECT COUNT(id) FILTER(WHERE lower(location) != 'x') AS
  Non_X_loc, COUNT(id) FILTER (WHERE lower(location) = 'x') AS X_loc FROM
  people;
   non_x_loc | x_loc
  ---+---
   1 | 2
  (1 row)
 
  This *FILTER* method is available from 9.4, How can I get the same output
  below 9.4 version ?
 
 
 use SQL CASE
 
 SELECT COUNT(CASE lower(location)  'x' THEN 1 END), ...
 
 attention: lower function is slow - so don't use it if it is not necessary
 
 Regards
 
 Pavel Stehule

Pavel,

I tried, but it is not giving the output exactly like *FILTER*.

app_development=# select CASE WHEN lower(location) = 'x' THEN COUNT(id) ELSE 0 
END AS X_loc, CASE WHEN lower(location) != 'x' THEN COUNT(id) ELSE 0 END AS 
Non_X_loc from people group by lower(location);
 x_loc | non_x_loc
---+---
 0 | 1
 2 | 0
(2 rows)
app_development=# select count(CASE WHEN lower(location) = 'x' THEN 1 END) AS 
X_loc, count(CASE WHEN lower(location) != 'x' THEN 1 END) AS Non_X_loc from 
people group by lower(location);
 x_loc | non_x_loc
---+---
 0 | 1
 2 | 0
(2 rows)

It is 2 rows output.

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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-filters-they-do-pay-off/
 
 Andreas
 

Humm. It is the one I wanted. But I used it, with group by, which is why I got 
2 rows output.

Thanks!!

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

Hi,

Suppose I want to select the rows from a table, say Emp, as below :

Select * from Emp 
where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14and 
attr2 = val15 and attr3 = val16);

Now suppose I got (x1, x2, x3) and (y1, y2, y3). Then I need to rewrite my 
query as :

Select * from Emp 
where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14 and 
attr2 = val15 and attr3 = val16) or
 (attr1 = x1 and attr2 = x2 and attr3 = x3) or (attr1 = y1 and attr2 = y2 and 
attr3 = y3);

So for each new set I need to add one more `or` conditions. It seems 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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 suppose I got (x1, x2, x3) and (y1, y2, y3). Then I need to rewrite my 
  query as :
  
  Select * from Emp 
  where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14 
  and attr2 = val15 and attr3 = val16) or
  (attr1 = x1 and attr2 = x2 and attr3 = x3) or (attr1 = y1 and attr2 = y2 
  and attr3 = y3);
  
  So for each new set I need to add one more `or` conditions. It seems wired. 
  
  Any better way to get it done ?
 
 If the number of attributes to compare is always the same, you can write:
 
 select * from Emp where (attr1, attr2, attr3) in ((val11, val12, val13), 
 (val14, val15, val16), (x1, x2, x3), (y1, y2, y3));
 
 Is that the sort of thing you're after?
 
 Alban Hertroys

Thanks for the reply, Can the comparisons be done using Array ? I tried, but 
got some syntax error. 

psql (9.2.7)
Type help for help.

app_development=# select * from pets where ARRAY[id, animals] IN 
'{{1,2,3},{4,5,6},{7,8,9}}';
ERROR:  syntax error at or near '{{1,2,3},{4,5,6},{7,8,9}}'
LINE 1: select * from pets where ARRAY[id, animals] IN '{{1,2,3},{4,...
   ^
app_development=# select * from pets where ARRAY[id, animals] IN '{{6, 1}, {5, 
1}}';
ERROR:  syntax error at or near '{{6, 1}, {5, 1}}'
LINE 1: select * from pets where ARRAY[id, animals] IN '{{6, 1}, {5,...
   ^
app_development=#


-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

Hi,

I am actually trying to copy data from my csv file to DB. But I am getting the 
DateStyle error.

I tried different ways to make it workable. But no luck!

prac_db=# copy orders from '/home/arup/postgresql/order.csv' with CSV 
DELIMITER ',' HEADER ;
ERROR:  date/time field value out of range:  19/08//2014
HINT:  Perhaps you need a different datestyle setting.
CONTEXT:  COPY orders, line 2, column delivery_date:  19/08//2014
prac_db=# SET datestyle = ISO;
SET
prac_db=# copy orders from '/home/arup/postgresql/order.csv' with CSV 
DELIMITER ',' HEADER ;
ERROR:  date/time field value out of range:  19/08//2014
HINT:  Perhaps you need a different datestyle setting.
CONTEXT:  COPY orders, line 2, column delivery_date:  19/08//2014
prac_db=# SET datestyle = ISO, IS;
ERROR:  invalid value for parameter DateStyle: ISO, IS
DETAIL:  Unrecognized key word: is.
prac_db=# SET datestyle = ISO, IS 7900:2001;
ERROR:  invalid value for parameter DateStyle: ISO, IS 7900:2001
DETAIL:  List syntax is invalid.
prac_db=# SET datestyle = ISO, BIS IS;
ERROR:  invalid value for parameter DateStyle: ISO, BIS IS
DETAIL:  List syntax is invalid.
prac_db=# SET datestyle = ISO, BIS IS 7900:2001;
ERROR:  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

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)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

On Sunday, December 28, 2014 12:54:30 PM Pavel Stehule wrote:
 Hi
 
 try
 
 postgres=# set datestyle to DMY;
 SET
 postgres=# SELECT '19/08/2014'::date;
 date
 
  2014-08-19
 (1 row)
 
 Postgres supports following styles only:
 
 DEFAULT  EUROPEAN ISO  NONEUROPEAN  SQL
 YMD
 DMY  GERMAN   MDY  POSTGRES US
 
 or more exactly:
 
 postgres=# set datestyle to SQL,DMY;
 SET
 postgres=# SELECT '19/08/2014'::date;
 date
 
  19/08/2014
 (1 row)
 
 
 http://www.postgresql.org/docs/9.4/static/datatype-datetime.html

Thanks for the link. I was following this - 
http://dba.stackexchange.com/questions/19679/how-to-set-postgresql-database-to-see-date-as-mdy-permanently.
 This actually lead me to those combinations 
that I had already tried.

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 ',' HEADER ;
ERROR:  time zone displacement out of range:  9-25 AM
CONTEXT:  COPY orders, line 2, column delivery_time:  9-25 AM
prac_db=#

===
ERROR:  time zone displacement out of range:  9-25 AM
===

How to fix this ?

prac_db=# \d orders ;
 Table public.orders
  Column  |  Type  | Modifiers
--++---
 order_id | integer| not null default 
nextval('orders_order_id_seq'::regclass)
 delivery_address | text   |
 order_date   | date   |
 delivery_date| date   |
 status   | text   |
 delivery_time| time without time zone |
 courier_id   | 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 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)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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 ',' HEADER ;
  ERROR:  time zone displacement out of range:  9-25 AM
  CONTEXT:  COPY orders, line 2, column delivery_time:  9-25 AM
  prac_db=#
  
  ===
  ERROR:  time zone displacement out of range:  9-25 AM
  ===
  
  How to fix this ?
 
 Shouldn’t that read 9:25 AM”, with a colon instead of a dash? If that’s the 
 case, fix your data before feeding it to your orders table.
Data came that way.
 
 If not, what is “9-25 AM supposed to mean? A timezone offset (which is what 
 +/- means in a time) of more than +/-12 hours makes no sense on this planet. 
 Neither does a time-range, especially not with the AM addition.
No it is not a range. It is 9 hours 25 minutes( morning). :)
 Alban Hertroys


-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Uninstall Postgresql in openSUSE 13.1

[Arup Rakshit]

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.

[root@arup]# find / -name postgresql
find: ‘/var/run/user/1000/gvfs’: Permission denied
/etc/init.d/postgresql
/etc/sysconfig/SuSEfirewall2.d/services/postgresql
/etc/sysconfig/postgresql
/opt/PostgreSQL/9.3/include/postgresql
/opt/PostgreSQL/9.3/lib/postgresql
/opt/PostgreSQL/9.3/doc/postgresql
/opt/PostgreSQL/9.3/share/postgresql

Can you tell me which of those files 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 to debug it.

--Brian Kernighan


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Uninstall Postgresql in openSUSE 13.1

[Arup Rakshit]

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.
 
 [root@arup]# find / -name postgresql
 find: ‘/var/run/user/1000/gvfs’: Permission denied
 /etc/init.d/postgresql
 /etc/sysconfig/SuSEfirewall2.d/services/postgresql
 /etc/sysconfig/postgresql
 /opt/PostgreSQL/9.3/include/postgresql
 /opt/PostgreSQL/9.3/lib/postgresql
 /opt/PostgreSQL/9.3/doc/postgresql
 /opt/PostgreSQL/9.3/share/postgresql
 
 Can you tell me which of those files I have to remove? Or is there any other
 safe approach or not ?

I found this link - 
http://www.enterprisedb.com/docs/en/9.3/pginstguide/PostgreSQL_Installation_Guide-13.htm#P413_27929

And tried - 

[root@arup]# uninstall-postgresql
If 'uninstall-postgresql' is not a typo you can use command-not-found to 
lookup the package that contains it, like this:
cnf uninstall-postgresql
[root@arup]# ls /opt/PostgresPlus/
ls: cannot access /opt/PostgresPlus/: No such file or directory
[root@arup]#

Nothing worked.

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Uninstall Postgresql in openSUSE 13.1

[Arup Rakshit]

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 it. As there are no uninstaller, I am thinking to
  install it manually.
  
  [root@arup]# find / -name postgresql
  find: ‘/var/run/user/1000/gvfs’: Permission denied
  /etc/init.d/postgresql
  /etc/sysconfig/SuSEfirewall2.d/services/postgresql
  /etc/sysconfig/postgresql
  /opt/PostgreSQL/9.3/include/postgresql
  /opt/PostgreSQL/9.3/lib/postgresql
  /opt/PostgreSQL/9.3/doc/postgresql
  /opt/PostgreSQL/9.3/share/postgresql
  
  Can you tell me which of those files I have to remove? Or is there any
  other safe approach or not ?
 
 I found this link -
 http://www.enterprisedb.com/docs/en/9.3/pginstguide/PostgreSQL_Installation_
 Guide-13.htm#P413_27929
 
 And tried -
 
 [root@arup]# uninstall-postgresql
 If 'uninstall-postgresql' is not a typo you can use command-not-found to
 lookup the package that contains it, like this:
 cnf uninstall-postgresql
 [root@arup]# ls /opt/PostgresPlus/
 ls: cannot access /opt/PostgresPlus/: No such file or directory
 [root@arup]#
 
 Nothing worked.

I uninstalled finally - 

[root@arup]#  find /opt -name uninstall-postgresql
/opt/PostgreSQL/9.3/uninstall-postgresql
[root@arup]# /opt/PostgreSQL/9.3/uninstall-postgresql

And it got uninstalled. Then I deleted the postgres user 

[root@arup]#  userdel postgres
no crontab for postgres

and manually removed some file :

[root@arup]# rm -rf /opt/PostgreSQL/
[root@arup]# ls /opt/

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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 ?

[Arup Rakshit]

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

[Arup Rakshit]

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, answer1_avg FROM ...) ans1 USING (gender)
LEFT JOIN (SELECT gender, answer2_avg FROM ...) ans2 USING (gender)

You could also try learning crosstab from the tablefunc extension:

http://www.postgresql.org/docs/9.3/interactive/tablefunc.html

I do not see how a single participant count column will provide a
meaningful piece of data...


Finally I wrote 

SELECT users.gender,count(*) as participant,
case when daily_action_answers.measure_id = 1 then 
avg(daily_action_answers.value) end as cpd,
case when daily_action_answers.measure_id = 2 then 
avg(daily_action_answers.value) end as other 
FROM users INNER JOIN daily_action_answers ON daily_action_answers.user_id = 
users.id
INNER JOIN measures ON measures.id = daily_action_answers.measure_id
WHERE (((daily_action_answers.day between '2014-07-03' and '2014-12-31')and 
daily_action_answers.daily_action_id = 1)) 
GROUP BY users.gender, daily_action_answers.measure_id

gender | participant | cpd |other 

Female, 2 , 8.5, 0.0
Female, 1 , 0.0, 8.0
None, 2, 6.5, 0.0
None, 1, 0.0, 5.0
Male, 1, 4.0, 0.0, 
Male, 2, 0.0, 10.0

Problem is.. I am not able to merge those pair rows into one... :-(

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

[Arup Rakshit]

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 = Measure.find_by(option: 'CPD').id
    other_id = Measure.find_by(option: 'Others').id
   
    User.select(view.gender, sum(view.participant) as participant, sum(cpd) as 
cpd, sum(other) as other).from(User.joins(daily_action_answers: 
[:measure]).group(users.gender, daily_action_answers.measure_id)
                                                
.where(((daily_action_answers.day between ? and ?) and
                                                         
daily_action_answers.daily_action_id = ?),
                                                         Date.today, 
Date.today.end_of_year,
                                                         question_id
                                                       )
                                                .select(users.gender, count(*) 
as participant,
                                                         case when 
daily_action_answers.measure_id = #{cpd_id} then 
avg(daily_action_answers.value) end as cpd,
                                                         case when 
daily_action_answers.measure_id = #{other_id} then 
avg(daily_action_answers.value) end as other
                                                       ), 
:view).group(view.gender)
  end

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

[Arup Rakshit]

Hi,

I am working on web development project. There I am using this awesome DB. Let 
me tell you first the schema that I am having associated the problem.

I am having a table *users* - which has many fields. Out of them, the one I 
need here is *gender*. This column can have value f/m/n.

I have a table called *measures*. This table contains all possible answers of 
questions lies in the table called *daily_actions*. It has a foreign key 
columns as *daily_action_id*.

I have a table called *daily_actions*. It has a field *question* and several 
other fields too.

I have a table called *daily_action_answers*. It has  foreign keys called 
user_id, daily_action_id and measure_id. Another field is *value* and 
day. *day* is a _date_ field.



SELECT users.gender,count(*) as participant,avg(daily_action_answers.value) as 
value 
FROM users INNER JOIN daily_action_answers ON 
daily_action_answers.user_id = users.id 
INNER JOIN measures ON measures.id = daily_action_answers.measure_id 
WHERE (((daily_action_answers.day between now() and last_date_of_year) and 
daily_action_answers.daily_action_id = 1)) 
GROUP BY users.gender, measures.option

This is producing the below 

gender  |participants  |   value  
   n   2  12
   n   1  3
   m  1   4
   m  4  12
   f3  23
   f   4  15

Here n.m,f it comes 2 times, because the possible answer is 2. That's the 
problem with my current query. I don't understand which average value for 
which answer.

Can we make the output as below ?

genderparticipants   answer1_avg   answer2_avg
n  3 12  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 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)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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 ?

[Arup Rakshit]

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

[Arup Rakshit]

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 brain not able to 
produce any straight forward one. Not so good in sql recently.. :(

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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, answer1_avg FROM ...) ans1 USING (gender)
LEFT JOIN (SELECT gender, answer2_avg FROM ...) ans2 USING (gender)

You could also try learning crosstab from the tablefunc extension:

http://www.postgresql.org/docs/9.3/interactive/tablefunc.html

I do not see how a single participant count column will provide a
meaningful piece of data...

David J.


Finally I wrote 

SELECT users.gender,count(*) as participant,
case when daily_action_answers.measure_id = 1 then 
avg(daily_action_answers.value) end as cpd,
case when daily_action_answers.measure_id = 2 then 
avg(daily_action_answers.value) end as other 
FROM users INNER JOIN daily_action_answers ON daily_action_answers.user_id = 
users.id
INNER JOIN measures ON measures.id = daily_action_answers.measure_id
WHERE (((daily_action_answers.day between '2014-07-03' and '2014-12-31')and 
daily_action_answers.daily_action_id = 1)) 
GROUP BY users.gender, daily_action_answers.measure_id

gender | participant | cpd |other 

Female, 2 , 8.5, 0.0
Female, 1 , 0.0, 8.0
None, 2, 6.5, 0.0
None, 1, 0.0, 5.0
Male, 1, 4.0, 0.0, 
Male, 2, 0.0, 10.0

Problem is.. I am not able to merge those pair rows into one... :-(

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

[Arup Rakshit]

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 brain not able to 
produce any straight forward one. Not so good in sql recently.. :(

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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 forward one. Not so good in sql recently..
 
 I was referring to the ORM syntax in whatever language that was. to me,
 thats just more layers of obfuscation.

Ok Got it.. Yes it is. This is ActiveRecord ORM, which ships with Ruby on 
Rails.. :-) 

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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
 20/02/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 ?

[Arup Rakshit]

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
 20/02/2014
 20/06/2014
 20/02/2014
(15 rows)


Can the same be done using any other clever trick ? 
What is the data at your disposal when trying to select the current year? If it 
is a timestamp, simply use date_part:
=# select date_part('year', now());
 date_part 

---
  2014
(1 row)
-- 
Michael

It is *datetime*. Now my users are created at different date...

say -

user1   24/02/1997
user2  28/02/2011
user3  02/03/2001
user4  01/03/2003
.

But I have some requirment, where date/month part will be as it is... but as 
per the current year, I will replace the actual year with the current year, 
while I will be displaying it. To meet this need, I am currently doing as 

  select  to_char(created_at,'DD/MM') || '/' || to_char(now(),'') as when 
from users;

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

[Arup Rakshit]

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/02/2014
  
  (15 rows)
  
  Can the same be done using any other clever trick ?
 
 No tricks are springing to mind but a warning is. The above will produce
 illegal dates whenever you are an inconvenient number of years past
 February 29. I think this will fix that issue:
 
 select created_at + ((extract(year from now()) - extract(year from
 created_at)) * '1 year'::interval);
 
 Note that the above returns a date (assuming that created_at is a date).
 You may need to apply to_char to format to your desired specification.
 
 Cheers,
 Steve

Thanks Steve. Your warning is 100% valid. *created_at* is a *datetime* data 
type.

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

Hi,

I am working on web development project. There I am using this awesome DB. Let 
me tell you first the schema that I am having associated the problem.

I am having a table *users* - which has many fields. Out of them, the one I 
need here is *gender*. This column can have value f/m/n.

I have a table called *measures*. This table contains all possible answers of 
questions lies in the table called *daily_actions*. It has a foreign key 
columns as *daily_action_id*.

I have a table called *daily_actions*. It has a field *question* and several 
other fields too.

I have a table called *daily_action_answers*. It has  foreign keys called 
user_id, daily_action_id and measure_id. Another field is *value* and 
day. *day* is a _date_ field.



SELECT users.gender,count(*) as participant,avg(daily_action_answers.value) as 
value 
FROM users INNER JOIN daily_action_answers ON 
daily_action_answers.user_id = users.id 
INNER JOIN measures ON measures.id = daily_action_answers.measure_id 
WHERE (((daily_action_answers.day between now() and last_date_of_year) and 
daily_action_answers.daily_action_id = 1)) 
GROUP BY users.gender, measures.option

This is producing the below 

gender  |participants  |   value  
   n   2  12
   n   1  3
   m  1   4
   m  4  12
   f3  23
   f   4  15

Here n.m,f it comes 2 times, because the possible answer is 2. That's the 
problem with my current query. I don't understand which average value for 
which answer.

Can we make the output as below ?

genderparticipants   answer1_avg   answer2_avg
n  3 12  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 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)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

On Wednesday, July 02, 2014 02:38:36 PM jared wrote:
 you have:
 GROUP BY users.gender, measures.option
 
 instead try:
 GROUP BY users
 
 

*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 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 Kernighan


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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 to
  get
  the data as per the format I am looking for.
 
 Arup,
 I meant:
 GROUP BY users.gender

That makes sense. How then calculate the average value for 2 different answers 
of the given *daily_action_id* ? As I said *answer1* and *answer2*

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

On Wednesday, July 02, 2014 12:28:04 PM David G Johnston wrote:
 Steve Crawford wrote
 

 
 Are you sure this is what you want?
 
 Since there are two columns you will have to either use a CASE or a 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, answer1_avg FROM ...) ans1 USING (gender)
 LEFT JOIN (SELECT gender, answer2_avg FROM ...) ans2 USING (gender)
 
 You could also try learning crosstab from the tablefunc extension:
 
 http://www.postgresql.org/docs/9.3/interactive/tablefunc.html
 
 I do not see how a single participant count column will provide a
 meaningful piece of data...
 
 David J.
 

This is a summary report of a specific questions answers gender wise.

Q is How much you learned today?

how many female participants in answering the question Q. If they answers, 
then average of A1 and average of A2 ( A1. A2 means two types of answers). 
They put the numbers in those 2 types. They are allowed to choose either of 
the answer type, but not the both. So, if any female F1 provides 80 to A2, in 
that day, she wouldn't be allowed to answer 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 it.

--Brian Kernighan


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

On Wednesday, July 02, 2014 12:28:04 PM David G Johnston wrote:
 Steve Crawford wrote
 

 
 Are you sure this is what you want?
 
 Since there are two columns you will have to either use a CASE or a 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, answer1_avg FROM ...) ans1 USING (gender)
 LEFT JOIN (SELECT gender, answer2_avg FROM ...) ans2 USING (gender)
 
 You could also try learning crosstab from the tablefunc extension:
 
 http://www.postgresql.org/docs/9.3/interactive/tablefunc.html
 
 I do not see how a single participant count column will provide a
 meaningful piece of data...
 
 David J.
 

This is a summary report of a specific questions answers gender wise.

Q is How much you learned today?

how many female participants in answering the question Q. If they answers, 
then average of A1 and average of A2 ( A1. A2 means two types of answers). 
They put the numbers in those 2 types. They are allowed to choose either of 
the answer type, but not the both. So, if any female F1 provides 80 to A2, in 
that day, she wouldn't be allowed to answer 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 it.

--Brian Kernighan


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

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

[Arup Rakshit]

Suppose I have users table. I have a joining_date. Now I only want those users 
who completed 5 years, 10 years, and in which date they completed. 

Example:

Out of all users, suppose below users only -

Foo_1 on 24/1/2014 completed 10 years
Foo_2 on 2/2/2014 completed  10 years
Foo_3 on 14/3/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

[Arup Rakshit]

On Monday, June 30, 2014 04:52:32 PM you wrote:
 Hi Arup,
 
 Two ways come to mind for me. They're pretty much the same as Szymon's,
 just minus the sample table creation. I would suggest creating a view
 instead, so you can just select from it whenever you please.
 
 
  create view vw_employee as
select * from employees
where ((age(joining_date::date) like '5 years%') or
 (age(joining_date::date) like '10 years%') )
 

But I am using Ruby on Rails framework to develop web application. Here I use 
basically query. If no way, then I go for view. It seems I can use this as a 
select 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

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)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

On Monday, June 30, 2014 04:52:32 PM Rebecca Clarke wrote:
 Hi Arup,
 
 Two ways come to mind for me. They're pretty much the same as Szymon's,
 just minus the sample table creation. I would suggest creating a view
 instead, so you can just select from it whenever you please.
 
 
  create view vw_employee as
select * from employees
where ((age(joining_date::date) like '5 years%') or
 (age(joining_date::date) like '10 years%') )
 
 or
 
  create view vw_employee as
select * from employees
where
   ((to_char(joining_date, '-MM') = to_char((now() - interval '5
 years'), '-MM') )
or
   (to_char(joining_date, '-MM') = to_char((now() - interval '10
 years'), '-MM')))
 


Can this query be set up like :-

Consider the below scenarios :

Ram completed 5 years on 12/04/2014
Shyam completed 5 years on 21/04/2014
Ayan completed 10 years on 12/04/2014
and so on...

Now consider the current month is *march*. I have 12 employees. Out of which 
above only completed 5 and 10 years. Thus my output should come as

Name  milestoneswhen
Ram 512/04/2014
Shyam  5   21/04/2014
Ayan10  12/04/2014

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

Look the below 2 scenarios :-

yelloday-staging::YELLOW= select id, dob from users;
 id |         dob         
+-
 13 | 
  9 | 1967-02-13 14:00:00
 10 | 
 11 | 
  8 | 1977-06-05 14:00:00
 15 | 
 21 | 
 14 | 2014-05-25 14:00:00
 37 | 
 22 | 
 26 | 2014-05-06 14:00:00
 32 | 
 35 | 
  7 | 
  6 | 2014-05-10 14:00:00
(15 rows)

yelloday-staging::YELLOW= select date_trunc('year', age(dob)) as age, 
count(id) from users group by dob ;
   age    | count 
+---
               |    10
 00:00:00 |     1
 47 years |     1
 00:00:00 |     1
 37 years |     1
 00:00: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 ?

[Arup Rakshit]

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 ?

[Arup Rakshit]

Thanks for your answer. How to get the first day date of last 6 months from now 
then will be :

yelloday_development=# select date_trunc('month', now()) - interval '5 month' 
as first_month;
        first_month        
---
 2014-01-01 00:00:00+05:30
(1 row)

Is it correct ? 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 day of previous
 month can be found using the answer -
 http://stackoverflow.com/a/8945281/2767755
Here is how to get the first day date of the previous month:
=# select date_trunc('month', now()) - interval '1 month' as last_month;
       last_month

2014-05-01 00:00:00+09
(1 row)
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

On Friday, June 20, 2014 08:12:14 AM you wrote:

 Welcome. And yes, it is awesome. 

I agree.

Being new to the DB 

No. I worked on Oracle DB earlier days(approx 2 years). But pgsql just 1 
month.

and mailing list,
 please note that the convention on all PostgreSQL mailing lists is to
 post your 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

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)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Arup Rakshit]

I have a below table :

yelloday_development=# select id,workplace_ids,team_ids  from reporting_groups 
;                                                                               
                                                                                
                      
 id | workplace_ids | team_ids 
+---+--
  2 | {}            | {}
  3 | {}            | {}
  1 | {}            | {}
  4 | {1}           | {1}
  5 | {2}           | {2,3,4}
(5 rows)

yelloday_development=# select id from reporting_groups where 2 = ANY (team_ids)
yelloday_development-# ;
 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 ?

[Arup Rakshit]

Hi,

Suppose I have a table

 CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
);

Now I have a query -

select * from sal_emp where pay_by_quarter  some_var

Now some var some times is fully empty, then I am getting error as 

PG::UndefinedFunction: ERROR:  operator does not exist: text[]  integer[]
       LINE 1: ...ps.* FROM reporting_groups  WHERE (ARRAY[NULL]  
workpla...

                                                                    ^

How to fix this ? The error is taken from production and the example I gave 
above is the same as per our current 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
+--++---
  1 | sample post  | lorem ipsum    | {apple,orange}
  2 | another post | fruits are bad | {apple,hamburger}
  3 | third post   | foo bar            | { 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 Rakshit
Perhaps the  (overlaps) operator will work best for you.
Check out the available operators at:
http://www.postgresql.org/docs/current/static/functions-array.html

Cheers,
Steve

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

[Arup Rakshit]

Just to help you guys the error - here is the full error stack . I am using an 
ORM with Rails

2.1.0 :001  ReportingGroup.where(ARRAY[?]  workplace_ids, 
Workplace.select(:id))
   (0.4ms)  SELECT COUNT(workplaces.id) FROM workplaces
D, [2014-06-04T12:00:23.479024 #14429] DEBUG -- :    (0.4ms)  SELECT 
COUNT(workplaces.id) FROM workplaces
  Workplace Load (0.2ms)  SELECT id FROM workplaces
D, [2014-06-04T12:00:23.479496 #14429] DEBUG -- :   Workplace Load (0.2ms)  
SELECT id FROM workplaces
  ReportingGroup Load (0.9ms)  SELECT reporting_groups.* FROM 
reporting_groups WHERE (ARRAY[1,2]  workplace_ids)
D, [2014-06-04T12:00:23.560772 #14429] DEBUG -- :   ReportingGroup Load (0.9ms) 
 SELECT reporting_groups.* FROM reporting_groups WHERE (ARRAY[1,2]  
workplace_ids)
 = #ActiveRecord::Relation [#ReportingGroup id: 5, created_at: 2014-06-03 
12:06:40, updated_at: 2014-06-03 12:06:40, company_id: nil, name: test123, 
owner_id: nil, logo_file_name: nil, logo_content_type: nil, logo_file_size: 
nil, logo_updated_at: nil, cover_image_file_name: nil, 
cover_image_content_type: nil, cover_image_file_size: nil, 
cover_image_updated_at: nil, team_ids: [], reporting_group_ids: [], 
workplace_ids: [2], #ReportingGroup id: 4, created_at: 2014-06-02 11:55:03, 
updated_at: 2014-06-04 05:56:20, company_id: nil, name: biz, owner_id: nil, 
logo_file_name: nil, logo_content_type: nil, logo_file_size: nil, 
logo_updated_at: nil, cover_image_file_name: nil, cover_image_content_type: 
nil, cover_image_file_size: nil, cover_image_updated_at: nil, team_ids: [1], 
reporting_group_ids: [], workplace_ids: [1]] 
2.1.0 :002  ReportingGroup.where(ARRAY[?]  workplace_ids, [])
  ReportingGroup Load (0.6ms)  SELECT reporting_groups.* FROM 
reporting_groups WHERE (ARRAY[NULL]  workplace_ids)
D, [2014-06-04T12:00:30.264431 #14429] DEBUG -- :   ReportingGroup Load (0.6ms) 
 SELECT reporting_groups.* FROM reporting_groups WHERE (ARRAY[NULL]  
workplace_ids)
PG::UndefinedFunction: ERROR:  operator does not exist: text[]  integer[]
LINE 1: ...ps.* FROM reporting_groups  WHERE (ARRAY[NULL]  workpla...
                                                             ^
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)
E, [2014-06-04T12:00:30.264546 #14429] ERROR -- : PG::UndefinedFunction: ERROR: 
 operator does not exist: text[]  integer[]
LINE 1: ...ps.* FROM reporting_groups  WHERE (ARRAY[NULL]  workpla...
                                                             ^
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)
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does 
not exist: text[]  integer[]
LINE 1: ...ps.* FROM reporting_groups  WHERE (ARRAY[NULL]  workpla...
                                                             ^
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:55 AM, Arup Rakshit arupraks...@rocketmail.com 
wrote:
 


Hi,

Suppose I have a table

 CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
);

Now I have a query -

select * from sal_emp where pay_by_quarter  some_var

Now some var some times is fully empty, then I am getting error as 

PG::UndefinedFunction: ERROR:  operator does not exist: text[]  integer[]
       LINE 1: ...ps.* FROM reporting_groups  WHERE (ARRAY[NULL]  
workpla...

                                                                    ^

How to fix this ? The error is taken from production and the example I gave 
above is the same as per our current 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
+--++---
  1 | sample post  | lorem ipsum    | {apple,orange}
  2 | another post | fruits are bad | {apple,hamburger}
  3 | third post   | foo bar            | { 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 Rakshit
Perhaps the  (overlaps) operator will work best for you.
Check out the available operators at:
http://www.postgresql.org/docs/current/static/functions-array.html

Cheers,
Steve

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

[Arup Rakshit]

I just figured it out :-

I just figured it out :-

yelloday_development=# select id, workplace_ids from reporting_groups ;
 id | workplace_ids 
+---
  2 | {}
  3 | {}
  1 | {}
  5 | {2}
  4 | {1}
(5 rows)

yelloday_development=# select id, workplace_ids from reporting_groups where 
workplace_ids  ARRAY[2,4];
 id | workplace_ids 
+---
  5 | {2}
(1 row)

yelloday_development=# select id, workplace_ids from reporting_groups where 
workplace_ids  ARRAY[];
ERROR:  cannot determine type of empty array
LINE 1: ...ce_ids from reporting_groups where workplace_ids  ARRAY[];
                                                               ^
HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].
yelloday_development=# select id, workplace_ids from reporting_groups where 
workplace_ids  ARRAY[]::integer[];
 id | workplace_ids 
+---
(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 PM, Arup Rakshit arupraks...@rocketmail.com 
wrote:
 


Just to help you guys the error - here is the full error stack . I am using an 
ORM with Rails

2.1.0 :001  ReportingGroup.where(ARRAY[?]  workplace_ids, 
Workplace.select(:id))
   (0.4ms)  SELECT COUNT(workplaces.id) FROM workplaces
D, [2014-06-04T12:00:23.479024 #14429] DEBUG -- :    (0.4ms)  SELECT 
COUNT(workplaces.id) FROM workplaces
  Workplace Load (0.2ms)  SELECT id FROM workplaces
D, [2014-06-04T12:00:23.479496 #14429] DEBUG -- :   Workplace Load (0.2ms)  
SELECT id FROM workplaces
  ReportingGroup Load (0.9ms)  SELECT reporting_groups.* FROM 
reporting_groups WHERE (ARRAY[1,2]  workplace_ids)
D, [2014-06-04T12:00:23.560772 #14429] DEBUG -- :   ReportingGroup Load (0.9ms) 
 SELECT reporting_groups.* FROM reporting_groups WHERE (ARRAY[1,2]  
workplace_ids)
 = #ActiveRecord::Relation [#ReportingGroup id: 5, created_at: 2014-06-03 
12:06:40, updated_at: 2014-06-03 12:06:40, company_id: nil, name: test123, 
owner_id: nil, logo_file_name: nil, logo_content_type: nil, logo_file_size: 
nil, logo_updated_at: nil, cover_image_file_name: nil, 
cover_image_content_type: nil, cover_image_file_size: nil, 
cover_image_updated_at: nil, team_ids: [], reporting_group_ids: [], 
workplace_ids: [2], #ReportingGroup id: 4, created_at: 2014-06-02 11:55:03, 
updated_at: 2014-06-04 05:56:20, company_id: nil, name: biz, owner_id: nil, 
logo_file_name: nil, logo_content_type: nil, logo_file_size: nil, 
logo_updated_at: nil, cover_image_file_name: nil, cover_image_content_type: 
nil, cover_image_file_size: nil, cover_image_updated_at: nil, team_ids: [1], 
reporting_group_ids: [], workplace_ids: [1]] 
2.1.0 :002  ReportingGroup.where(ARRAY[?]  workplace_ids, [])
  ReportingGroup Load (0.6ms)  SELECT reporting_groups.* FROM 
reporting_groups WHERE (ARRAY[NULL]  workplace_ids)
D, [2014-06-04T12:00:30.264431 #14429] DEBUG -- :   ReportingGroup Load (0.6ms) 
 SELECT reporting_groups.* FROM reporting_groups WHERE (ARRAY[NULL]  
workplace_ids)
PG::UndefinedFunction: ERROR:  operator does not exist: text[]  integer[]
LINE 1: ...ps.* FROM reporting_groups  WHERE (ARRAY[NULL]  workpla...
                                                             ^
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)
E, [2014-06-04T12:00:30.264546 #14429] ERROR -- : PG::UndefinedFunction: ERROR: 
 operator does not exist: text[]  integer[]
LINE 1: ...ps.* FROM reporting_groups  WHERE (ARRAY[NULL]  workpla...
                                                             ^
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)
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does 
not exist: text[]  integer[]
LINE 1: ...ps.* FROM reporting_groups  WHERE (ARRAY[NULL]  workpla...
                                                             ^
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:55 AM, Arup Rakshit arupraks...@rocketmail.com 
wrote:
 


Hi,

Suppose I have a table

 CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
);

Now I have a query -

select * from sal_emp where pay_by_quarter  some_var

Now some var some times is fully empty, then I am getting error as 

PG::UndefinedFunction: ERROR:  operator does not exist: text[]  integer[]
       LINE 1: ...ps.* FROM reporting_groups  WHERE

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

[Arup Rakshit]

Hi,

Suppose, I have a table as below :-

id |    title     |    content     |       tags
+--++---
  1 | sample post  | lorem ipsum    | {apple,orange}
  2 | another post | fruits are bad | {apple,hamburger}
  3 | third post   | foo bar            | { 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 Rakshit

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

[Arup Rakshit]

 You can use the array overlap operator, something like this:
 
   SELECT  WHERE tags  ARRAY['apple', 'banana'];
 
 See here:
 
   http://www.postgresql.org/docs/9.3/static/functions-array.html
 
 Ray.

Yes. It is the one I need really. It worked. Great DB it is. Lots of utility 
methods. 

-- 

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general