Re: [GENERAL] How can I replace the year of the created_at column with the current year dynamically ?
On Wed, Jul 2, 2014 at 3: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
> 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
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
> 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] Very high latency, low bandwidth replication
On 30 June 2014 15:05, Bob Jolliffe wrote: > What are people's thoughts about a more optimal solution? I would like to > use a more incremental approach to replication. This does not have to be a > "live" replication .. asynchronously triggering once every 24 hours is > sufficient. Also there are only a subset of tables which are required (the > rest consist of data which is generated). WAL shipping is probably best here. Configure an archive_command on the master to compress and push logs to cloud storage, and configure a hot standby on site to pull and decompress the logs. The wal-e tool may make things simpler pushing to cloud storage, or just follow the PostgreSQL documentation to archive the WAL files to a filesystem. If that isn't good enough, you can look at more esoteric approaches (eg. nightly plaintext dumps to a git repository, pushing changes to disk on site). -- Stuart Bishop http://www.stuartbishop.net/ -- 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 replace the year of the created_at column with the current year dynamically ?
On 07/02/2014 12:48 AM, Arup Rakshit wrote:
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;
Maybe simplify it a bit:
select to_char('2011-01-01'::timestamp,'DD/MM/' || to_char(now(),''));
or per Michaels suggestion:
select to_char('2011-01-01'::timestamp,'DD/MM/' || date_part('year',
now()));
--
Adrian Klaver
adrian.kla...@aklaver.com
--
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 About Roles
My highest priority has just changed to developing a multiuser database application. The backend is postgres, of course, and everything else will be written in Python, SQLAlchemy, and wxPython. This application is at least one order of magnitude more complicated/sophisticated than any I have developed in the past. Ergo, there will be many questions, despite my reading the docs. My first questions regard roles (postgres-9.3.4 is what's currently installed here). I've read Chapter 20 (Database Roles) and completely understand what it says; but I'm missing how to apply it to my developing application. Here's the context: There are three groups of users (now all lumped together in roles), plus the local SysAdmin whose role is to add and remove users and be the local maintainer of the application and the database. For one client, this is the head IT at the mine site. The other roles are 'executive,' 'manager,' and 'technician.' Executives are allowed to generate and view reports, retrieve and view stored documents (such as permits), but not enter or modify data. Managers have authority to enter, modify, or delete data, create reports, and do other tasks required by their responsibilities. Technicians are allowed to only enter data (and correct user errors within a short time window). After that, corrections must be made by a manager. In the .sql file I'm creating, following the CREATE DATABASE command, I have these commands: -- Set user roles CREATE ROLE admin LOGIN; CREATE ROLE executive; CREATE ROLE manager; CREATE ROLE technician; and my question is what do I need to do so when a user is added to the system the local administrator and the group specified during the createuser process, she or he can be granted that group's privileges? Or, is that automatic if the role is included in the Users table? Rich -- 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] Question About Roles
On 07/02/2014 06:17 AM, Rich Shepard wrote: -- Set user roles CREATE ROLE admin LOGIN; CREATE ROLE executive; CREATE ROLE manager; CREATE ROLE technician; and my question is what do I need to do so when a user is added to the system the local administrator and the group specified during the createuser process, she or he can be granted that group's privileges? I am not following. Can you show an example of what you are talking about? Or, is that automatic if the role is included in the Users table? Assuming Users is your own table, Postgres has no way of knowing that relates to user permissions. It stores that information in the system catalogs. Rich -- Adrian Klaver adrian.kla...@aklaver.com -- 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] Question About Roles
Rich Shepard writes: > ... my question is what do I need to do so when a user is added to the > system the local administrator and the group specified during the createuser > process, she or he can be granted that group's privileges? I might be misunderstanding, but I think you're looking for GRANT/REVOKE: GRANT rolename TO newuser; 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
Re: [GENERAL] Question About Roles [ANSWERED]
On Wed, 2 Jul 2014, Tom Lane wrote: I might be misunderstanding, but I think you're looking for GRANT/REVOKE: GRANT rolename TO newuser; tom, You correctly understand my question. That's the syntax I need and did not see. Thank you, Rich -- 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] Question About Roles
Tom Lane-2 wrote > Rich Shepard < > rshepard@ > > writes: >> ... my question is what do I need to do so when a user is added to the >> system the local administrator and the group specified during the >> createuser >> process, she or he can be granted that group's privileges? > > I might be misunderstanding, but I think you're looking for GRANT/REVOKE: > > GRANT rolename TO newuser; or if you want to do it as part of creating a new user: CREATE ROLE new_management_user [other stuff here] IN ROLE management; http://www.postgresql.org/docs/9.2/interactive/sql-createrole.html -- View this message in context: http://postgresql.1045698.n5.nabble.com/Question-About-Roles-tp5810176p5810189.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: How can I replace the year of the created_at column with the current year dynamically ?
Adrian Klaver-4 wrote
> On 07/02/2014 12:48 AM, Arup Rakshit wrote:
>>
>
>>
>> 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;
>
>
> Maybe simplify it a bit:
>
> select to_char('2011-01-01'::timestamp,'DD/MM/' || to_char(now(),''));
>
> or per Michaels suggestion:
>
> select to_char('2011-01-01'::timestamp,'DD/MM/' || date_part('year',
> now()));
[not syntactically correct]
ALTER TABLE ... ADD COLUMN created_at_monthday_prefix text --stores 'MM/DD/'
CREATE FUNCTION current_year() RETURNS text AS ...; --return
SELECT created_at_monthday_prefix || current_year();
OR even
CREATE FUNCTION day_in_current_year(source_date date) RETURNING date/text...
SELECT day_in_current_year(created_at);
The only way to actually calculate the new date is to, at some point, break
apart the existing date and then join the m/d component back with today's
year - which has multiple likely nearly identical solutions. My suggestions
is to wrap that in user functions and, in the first case, cache the result
of pulling out the m/d component so you do not have to do so repeatedly.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-can-I-replace-the-year-of-the-created-at-column-with-the-current-year-dynamically-tp5810122p5810192.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
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] Question About Roles
On 07/02/2014 06:17 AM, Rich Shepard wrote: Or, is that automatic if the role is included in the Users table? To follow up on my previous post. If you have your own user table, having a user in that table with the same name as one of the Postgres role does not mean they pick up the Postgres role permissions. The permissions apply to the role that the user connects as in the connection parameters(or is changed to once connected). This is why something like Django has it owns permissions system. The framework connects to the database as a single role(which has sufficient Postgres permissions) and when users log in they are tracked by the Django permissions not by the Postgres system. Rich -- Adrian Klaver adrian.kla...@aklaver.com -- 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] Question About Roles
On Wed, 2 Jul 2014, David G Johnston wrote: or if you want to do it as part of creating a new user: CREATE ROLE new_management_user [other stuff here] IN ROLE management; http://www.postgresql.org/docs/9.2/interactive/sql-createrole.html David, I'll have to think deeply about what this is doing. Initially, I read it as assigning a new user's role to an existing group's role; that's the opposite of what I want. Thanks, Rich -- 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] Question About Roles
On Wed, 2 Jul 2014, Adrian Klaver wrote: To follow up on my previous post. If you have your own user table, having a user in that table with the same name as one of the Postgres role does not mean they pick up the Postgres role permissions. Adrian, OK. Now I see the issue. What I have created as roles are what would be considered group roles in versions < 8.1. Usernames in the Users table are individuals; e.g., Fred Flintstone, George Gamov, Issac Azimov, etc. What I want to do, when the user is being added to the system, is specify the group to which this new user should be assigned so that the group's permissions are granted to him. The permissions apply to the role that the user connects as in the connection parameters(or is changed to once connected). This is why something like Django has it owns permissions system. The framework connects to the database as a single role(which has sufficient Postgres permissions) and when users log in they are tracked by the Django permissions not by the Postgres system. I considered making this a Web-based application using django, but I've no experience with this approach, the django learning curve is rather steep, and it would add the cost and time of setting up and maintaining an in-house (or colo) httpd server. That's not my expertise, interest, or business. So, a stand-alone application is the route I've chosen. Thanks, Rich -- 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] Question About Roles
Rich Shepard wrote > On Wed, 2 Jul 2014, David G Johnston wrote: > >> or if you want to do it as part of creating a new user: >> >> CREATE ROLE new_management_user >> [other stuff here] >> IN ROLE management; >> >> http://www.postgresql.org/docs/9.2/interactive/sql-createrole.html > > David, > >I'll have to think deeply about what this is doing. Initially, I read > it > as assigning a new user's role to an existing group's role; that's the > opposite of what I want. Its the exact same outcome Tom provided, just via a different mechanism... You want to assign all permissions to the standard group-roles and then have new users inherit the appropriate permissions via their membership in the appropriate group-role. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Question-About-Roles-tp5810176p5810198.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Question About Roles
On Wed, Jul 2, 2014 at 7:50 AM, Rich Shepard wrote: > On Wed, 2 Jul 2014, David G Johnston wrote: > > or if you want to do it as part of creating a new user: >> >> CREATE ROLE new_management_user >> [other stuff here] >> IN ROLE management; >> >> http://www.postgresql.org/docs/9.2/interactive/sql-createrole.html >> > > David, > > I'll have to think deeply about what this is doing. Initially, I read it > as assigning a new user's role to an existing group's role; that's the > opposite of what I want. > > Thanks, > > Rich > > If it made you feel better, remember that CREATE USER is an alias for CREATE ROLE that includes LOGIN by default. So if you simply swap one word in your command, the context becomes a little more clear for what you want: CREATE USER new_management_user [other stuff here] IN ROLE management; -Greg Haase
Re: [GENERAL] How can I replace the year of the created_at column with the current year dynamically ?
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
Re: [GENERAL] Migration error " invalid byte sequence for encoding "UTF8": 0xff " from mysql 5.5 to postgresql 9.1
I‘v changed cmd (in win8) to encoding utf8 through chcp 65001, but error still occurs. And i use the following cmd to dump mysql data: mysql> select Picture from personpicture where id = 'F2931306D1EE44ca82394CD3BC2404D4' into outfile "d:\\1.txt" ; I got the ansi file, and use Ultraedit to see first 16 bytes: FF D8 FF E0 5C 30 10 4A 46 49 46 5C 30 01 01 5C It's different from mysql workbench to see: FF D8 FF E0 00 10 4a 46 49 46 00 01 01 00 00 01 peng On Tue, Jul 1, 2014 at 9:18 PM, Kevin Grittner wrote: > sunpeng wrote: > > > load data to postgresql in cmd(encoding is GBK) is WIN8: > > > psql -h localhost -d test -U postgres < dbdata.sql > > > > I got the error: > > ERROR: invalid byte sequence for encoding "UTF8": 0xff > > If the encoding is GBK then you will get errors (or incorrect > characters) if it is read as UTF8. Try setting the environment > variable PGCLIENTENCODING. > > http://www.postgresql.org/docs/9.1/static/app-psql.html > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
Re: [GENERAL] Question About Roles
On 07/02/2014 07:50 AM, Rich Shepard wrote: On Wed, 2 Jul 2014, David G Johnston wrote: or if you want to do it as part of creating a new user: CREATE ROLE new_management_user [other stuff here] IN ROLE management; http://www.postgresql.org/docs/9.2/interactive/sql-createrole.html David, I'll have to think deeply about what this is doing. Initially, I read it as assigning a new user's role to an existing group's role; that's the opposite of what I want. If you want opposite then: http://www.postgresql.org/docs/9.2/interactive/sql-createrole.html ROLE role_name The ROLE clause lists one or more existing roles which are automatically added as members of the new role. (This in effect makes the new role a "group".) Thanks, Rich -- Adrian Klaver adrian.kla...@aklaver.com -- 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 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/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
Re: [GENERAL] Question About Roles
On Wed, 2 Jul 2014, David G Johnston wrote: Its the exact same outcome Tom provided, just via a different mechanism... You want to assign all permissions to the standard group-roles and then have new users inherit the appropriate permissions via their membership in the appropriate group-role. David, Ah, so! I'll read the docs to better understand this approach. Thanks for clarifying, Rich -- 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
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 ) 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] Question About Roles
On Wed, 2 Jul 2014, Gregory Haase wrote: If it made you feel better, remember that CREATE USER is an alias for CREATE ROLE that includes LOGIN by default. Greg, Yes, I read that in the docs. So if you simply swap one word in your command, the context becomes a little more clear for what you want: CREATE USER new_management_user [other stuff here] IN ROLE management; Got it. Thanks, Rich -- 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
you have: GROUP BY users.gender, measures.option instead try: GROUP BY users On Wed, Jul 2, 2014 at 12:55 PM, Arup Rakshit wrote: > 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 ) > 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
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
On Wed, Jul 2, 2014 at 1:44 PM, Arup Rakshit 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
Re: [GENERAL] Not able to understand how to write group by
On 7/2/2014 10:44 AM, Arup Rakshit 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. I believe he meant group by users.gender -- john r pierce 37N 122W somewhere on the middle of the left coast
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 > > 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
On 07/02/2014 09:55 AM, Arup Rakshit wrote: 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 ) 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 As mentioned by jared, the problem is the additional group by measures.option which needs to be eliminated. To better understand what is happening, just add measures.option to your list of output columns. Right now the grouping is hidden because you aren't showing that column. Cheers, Steve -- 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
Steve Crawford wrote > On 07/02/2014 09:55 AM, Arup Rakshit wrote: >> 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 > > ) 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 >> >> >> > As mentioned by jared, the problem is the additional group by > measures.option which needs to be eliminated. To better understand what > is happening, just add measures.option to your list of output columns. > Right now the grouping is hidden because you aren't showing that column. 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. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Not-able-to-understand-how-to-write-group-by-tp5810250p5810283.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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
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 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] Two-way encryption
Toby Corkindale wrote: > From: "Patrick Simcoe" >> I have a question regarding two-way encryption data for specific columns. >> >> Does anyone have a technique or recommendation for two-way encryption which >> somehow obfuscates the decrypt key so that it isn't easily retrievable from >> the database or the application source code? We've already considered (a) >> letting users hold the decrypt key and (b) obfuscating the decrypt key with >> the user's own (one-way encrypted) password, but neither of these >> approaches are viable for us. > > If you want the application to be able to decrypt the data automatically, then > it has to hold the decryption key somewhere. There's really no way around > that. That is the tricky bit; if you store it on the machine with the database, you haven't really protected yourself against anything. The first thing to do when designing a security mechanism like this is to be clear what the threat model is that you are trying to protect against. In a previous job I had to protect a few sensitive columns on physically distributed machines against theft of the server or of its drive array. The design we developed was to use the pgcrypto extension with symmetric encryption using random leading salt. A view was placed in front of protected tables, which would decrypt the column upon selection and use a trigger to encrypt the column when written. None of that was too hard -- the hard part was how to supply, store, and retrieve the encryption keys. These distributed machines were all on a WAN, and the central site had a secure server room with a special locked cage for high-security machines. A machine in this cage had the keys, and would only supply them to properly authenticated servers (with the right fingerprints from the right IP addresses). We wrote a special C loadable module which would request the keys on cluster startup, and store them in private RAM, providing them only to the database superuser. Security definer functions (owned by the database superuser) did the encryption and decryption. In the event of a WAN outage during cluster startup, the encrypted columns were not available until the WAN came back and the superuser manually ran the function to load the passwords. This design allowed any database user with rights to select from such a column of the table to see the encrypted data rather transparently, but it should be pretty effective in protecting the data should someone walk off with the hardware. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 create multiple databases running in different dirs
> how would an instance of your program know what to connect to, or which > previous instance its 'predecessor' was ? > normally, you have ONE database for a given set of applications, and all > the applications share the same database tables and such. That's the problem, is there some way to tell pgsql "Go to dir X, open your data base Y and prepare for connections at adress Z and port P"? And could pgsql accept multiple connections on the same address and port? I was thinking of using my pID but that would change and if I used a user created string then if I started only on a single instace of pgsql and pointed it to it's databases the user might get the strings duplicated and that would be no good. I also thought of naming each database with a name generated by using the uuid library but I'm not sure which would be best. If I choose to have multiple servers running on the same machine how would my program start them? > Is each runtime instance of your application to have a new, empty > database? No data from any other run of the application? No, the dynamically generated content is to be dropped (drop table dynamic_content;) but the rest is to be preserved. The idea is to create a database of a file system but the files contain no data, I only want their metadata and I will add a few additional metadta values to each file. Thanks, David
Re: [GENERAL] how to create multiple databases running in different dirs
Afternoon Frank, I believe what you might wish to look at is a single database with a set of schemas[1] which would separate your data in a logical way. You could have a single connection url and then each individual connection could create a schema (or reuse if you wish), set the search path (first example here [2]) and populate the schema (if needed) within your single database That would give you your desired isolation but within a simple framework that is PostgreSQL friendly. John [1] - http://www.postgresql.org/docs/9.3/static/ddl-schemas.html [2] - http://www.postgresql.org/docs/9.3/static/sql-set.html On Wed, Jul 2, 2014 at 4:24 PM, frank ernest wrote: > > how would an instance of your program know what to connect to, or which > > previous instance its 'predecessor' was ? > > normally, you have ONE database for a given set of applications, and all > > the applications share the same database tables and such. > > That's the problem, is there some way to tell pgsql "Go to dir X, open > your data base Y and prepare for connections at adress Z and port P"? And > could pgsql accept multiple connections on the same address and port? I was > thinking of using my pID but that would change and if I used a user created > string then if I started only on a single instace of pgsql and pointed it > to it's databases the user might get the strings duplicated and that would > be no good. I also thought of naming each database with a name generated by > using the uuid library but I'm not sure which would be best. > > If I choose to have multiple servers running on the same machine how would > my program start them? > > > Is each runtime instance of your application to have a new, empty > > database? No data from any other run of the application? > > No, the dynamically generated content is to be dropped (drop table > dynamic_content;) but the rest is to be preserved. The idea is to create a > database of a file system but the files contain no data, I only want their > metadata and I will add a few additional metadta values to each file. > > Thanks, David > >
Re: [GENERAL] how to create multiple databases running in different dirs
> how would an instance of your program know what to connect to, or which > previous instance its 'predecessor' was ? > normally, you have ONE database for a given set of applications, and all > the applications share the same database tables and such. That's the problem, is there some way to tell pgsql "Go to dir X, open your data base Y and prepare for connections at adress Z and port P"? And could pgsql accept multiple connections on the same address and port? I was thinking of using my pID but that would change and if I used a user created string then if I started only on a single instace of pgsql and pointed it to it's databases the user might get the strings duplicated and that would be no good. I also thought of naming each database with a name generated by using the uuid library but I'm not sure which would be best. why directory, address and port? why not just 'open database X' ? this still doesn't answer my question, HOW do you determine which database a given instance of your program is supposed to connect to? yes, postgres supports many different connections to the same address and port, either to the same database or different databases. relational databases are totally built around concurrent transactional operations. there's no such thing as 'directory', all database tables are stored in the database server's private storage, the client app has no need to know where this is. There /is/ a concept of 'tablespaces', these are used when you want to put different tables on different file systems, typically used for very large scale databases, and/or when you have different performance tiers of storage. No, the dynamically generated content is to be dropped (drop table dynamic_content;) but the rest is to be preserved. The idea is to create a database of a file system but the files contain no data, I only want their metadata and I will add a few additional metadta values to each file. using TRUNCATE SOME_TABLE would likely make more sense, that deletes all the data in table SOME_TABLE, without actually deleting the table definition, so you can then proceed to insert new data into it. I really don't understand what you mean by 'a database of a file system'. I'm beginning to suspect you don't understand how SQL databases work. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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
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 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
afonit wrote >> genderparticipants answer1_avg answer2_avg >> n 3 12 3 >> m 5 4 12 >> f 71523 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. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Not-able-to-understand-how-to-write-group-by-tp5810250p5810279.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Windows releases - Bundle OpenSSL includes and .libs in the installer?
Hi I've just noticed that the EDB-produced installer for Pg omits a number of components required to build things that're fully compatible with libpq if they also use some of the same libs as libpq, like libintl, openssl, etc. The specific case of interest to me is psqlODBC, which I've been doing some work on recently. I just realised that a crash I've been encountering is caused by linking to the OpenSSL binaries from http://slproweb.com/products/Win32OpenSSL.html when libpq is using its own different OpenSSL. libeay32.lib is not in the Pg binary distribution for Windows so it's not possible to link directly to the same libeay32.dll that libpq uses. Ideally, every .dll that's shipped should be accompanied by the corresponding .lib, so people compiling against PostgreSQL can use the same OpenSSL, libintl, etc that were used when compiling Pg. The headers for those libraries should really also be bundled, e.g. have an include\openssl dir. That might add a little weight to the installer package, but probably not much - and that's clearly not a huge concern given that it already includes some fat .pdb debug symbols. If trimming the installer is a concern, it'd be reasonable to split the .lib files, includes, and pdb files into a separate "PostgreSQL development" component. The biggest issue - the missing libintl.h - was fixed a while ago, so it's possible to compile simple programs against libpq and have them work properly. Now it's only an issue if your app also needs to use OpenSSL, gettext, etc its self. Thoughts? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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
On 02 Jul 2014, at 18:55, Arup Rakshit wrote: > 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 ) and > daily_action_answers.daily_action_id = 1)) > GROUP BY users.gender, measures.option > Can we make the output as below ? > > genderparticipants answer1_avg answer2_avg > n 3 12 3 > m 5 4 12 > f 71523 Following the discussion, if this is really only about a fixed number of measures you can solve that by using the CASE statement for each measure involved and the fact that aggregate functions skip NULL-values, like so: SELECT users.gender,count(*) as participant, avg(CASE WHEN measures.id = 1 THEN daily_action_answers.value ELSE NULL END) as value1, avg(CASE WHEN measures.id = 2 THEN daily_action_answers.value ELSE NULL END) as value2 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 ) and daily_action_answers.daily_action_id = 1)) GROUP BY users.gender BTW, I noticed you are mixing how you quote the same identifiers. Quoting identifiers makes them case-sensitive, so either always quote them or never quote them, but don’t mix or you’ll get into trouble if you ever end up in a database(-version) where identifiers are case-folded to upper case (which is pretty much any database different from PG). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
