Re: [GENERAL] Help needed creating a view
Quoth David Johnston pol...@yahoo.com:
A) SELECT user_id, CASE WHEN course_name = 'Maths' THEN completed ELSE false
END math_cmp, CASE WHEN course_name = 'English' THEN completed ELSE false
END AS english_cmp FROM applications
a) Expand to multiple columns and store either the default false or the
value of completed into the value for the corresponding column
B) SELECT user_id, CASE WHEN bool_or(math_cmp) THEN true ELSE false END AS
did_math, CASE WHEN bool_or(english_cmp) THEN true ELSE false END AS
did_english FROM A GROUP BY user_id
b) Then determine whether the user_id has at least one true in the given
column by using the bool_or function
Dynamic columns are difficult to code in SQL. You should probably also
include some kind of OTHER COMPLETED DISCIPLINES column to catch when you
add an previously unidentified course - course_name NOT IN
('Maths','English','...')
Also concerned with the fact that, as coded, a single complete course
triggers the given flag. What happens when you want to specify that they
have only completed 3 of 4 courses? Also, instead of hard-coding the
course_name targets you may want to do something like CASE WHEN
course_name IN (SELECT course_name FROM courses WHERE course_type =
'Maths').
Many thanks David for a clear and comprehensive reply, although I haven't
completely grokked your use of bool_or.
No matter though, because 'CASE WHEN ... THEN column_name END' is precisely
the idiom I was looking for.
My view definition now looks something like this:
CREATE VIEW alumni AS
SELECT * FROM (
-- query includes every user_id in applications
SELECT user_id,
CASE WHEN course_name='Maths' THEN completed END AS
maths_alumni,
CASE WHEN course_name='English' THEN completed END AS
english_alumni,
...
...
FROM applications ) AS foo
-- so we need to exclude user_ids who did not complete *any* courses
WHERE maths_alumni IS TRUE
OR english_alumni IS TRUE
...
...;
Thanks again.
Sebastian
--
Emacs' AlsaPlayer - Music Without Jolts
Lightweight, full-featured and mindful of your idyllic happiness.
http://home.gna.org/eap
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help needed creating a view
Hi list, Given an 'applications' table for a static set of courses:: user_id (integer) course_name (text) completed (boolean) how best should I go about creating an 'alumni' view with columns: user_id (integer) maths (boolean) english (boolean) . . . . . . where each of the columns (apart from user_id) is a boolean value representing whether or not user_id completed each course? Sebastian -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- 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] Best database model for canvassing (and analysing) opinion
Quoth Sam Mason s...@samason.me.uk: [...] The only table that's really needed to solve your original problem would be the last one, but the others provide all the checks that the data is actually going in correctly and may or may not be useful depending on your problem. The main thing to notice is lots of tables with few columns, the reason being is that the database normally takes care of the rows and you, the DBA/programmer, take care of the columns. Thus the more work you can give to the database the better. [...] Hope that gives you some ideas! More than enough ideas. Thank you _very_ much. Presenting this kind of 'distributed' data in a useful way is more difficult (at least for me) but I can see now that this is what _relational_ databses are all about, and that once you've grasped how to do this, the advantages are legion. Many thanks once again. Sebastian -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Best database model for canvassing (and analysing) opinion
Hi all, A school wants to offer a number of short courses on a number of different dates. Students apply online for a single course at a time and choose one or more dates (from a list) which would suit them. Once the application period is over which course is taught when is decided soley on the basis of maximising the number of students that can attend. Perhaps the simplest model is a database table 'application_forms' which includes two text columns; 'course' and 'preferred_dates' with entries that look like this: course: Drama prefered_dates: Sat_22Aug09, Tue_25Aug09, Tue_08Sep09 The data can then be usefully presented in a series of SELECT statements (one for each date): AS SELECT count(*), course FROM application_forms WHERE preferred_dates like '%Sat_22Aug09%' GROUP BY course ORDER BY count DESC; count | course ---+- 7 | Drama 3 | Readers 1 | Self-study but clearly this method doesn't scale very well as the number of dates increases. A single table of results looking something like this would be far better, but how? date | course_suiting_most_applicants | num_applicants - --++--- Sat_22Aug09 | Drama | 7 Tue_25Aug09 | Readers| 4 Any advice/tips/pointers/suggestions for a database design newbie very much appreciated. Regards, Sebastian -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- 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] running postgres
Quoth Kusuma Pabba kusu...@ncoretech.com: /usr/local/pgsql/bin/psql test test=# sudo su postgres -c psql template1 template=# why is the path different in both cases? Type: $ which psql and $ sudo which psql The answer is the same, yes? $ /usr/local/pgsql/bin/psql test |___| | absolute path $ cd /usr/local/pgsql $ bin/psql test |__| | relative path When you provide an absolute path (or a relative path), your shell does not search your $PATH environment variable for the program - it just runs the program in the directory you specified. When you don't provide a path of any sort, your shell looks for the program in the directories specified in your $PATH environment variable. Check the value of your $PATH by typing: $ echo $PATH If there is only one executable file called 'psql' in the directories in your PATH, it makes no difference whether you specify a path, or no path at all. Note that different users may have different directories in their $PATH. Your can alter your $PATH variable (if you need to) in your ~/.profile. what is the difference between the above two and, 'sudo command' is a command for running a single command with superuser privileges, i.e., 'as root'. 'su user' (without the -c switch) is a command for 'becoming' user until you type 'exit'. 'su user -c command' is similar to 'sudo' in that the single command command is run as user. If you type: $ su postgres -c psql template1 you will be asked for a password, and if you haven't setup any user accounts and passwords in postgres you won't know what the password is, and therefore you won't be able to connect. This is why you need to type 'sudo su postgres -c psql template1'. sudo temporarily makes you root, and root is never asked for passwords (root is God in the UNIX world) so you are able to connect as user 'postgres'. how can i create a user in test or template? when i give create user it is asking for create role , how should i create role? Others with a better understanding of users and roles should answer this question. Sebastian -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recovery mode
Hi there, Just noticed this in my webapp logs: ERROR: FATAL: the database system is in recovery mode Only one instance, so I'm not too concerned, but why, how often, how long for, etc. Am I negelecting to do some important database maintenace? Could it be related to the backup cron performs hourly: pg_dump --format=custom --file=file db Seb -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Building the PostgreSQL manual in Info format on Debian Lenny
Hello, Here's a step-by-step guide to building the PostgreSQL manual in Info format on a Debian Lenny box: First of all, you may made need to install a few extra packages: $ sudo apt-get install bzip2 docbook-dsssl docbook2x opensp Next, download the PostgreSQL source (using apt), unpack it and configure: $ cd ~/workspace/src $ apt-get source postgresql $ cd postgresql-8.3-8.3.5 $ bunzip2 postgresql-8.3-8.3.5.tar.bz2 $ tar -xf postgresql-8.3-8.3.5.tar ## this next operations is not strictly necessary but... $ sudo chown -R sebyte:sebyte postgresql-8.3.5 # change user/group name to suit $ cd postgresql-8.3.5 $ ./configure [...] Now we need to tell a program called 'osx' to ignore any errors by editing the relevant Makefile: $ cd doc/src/sgml $ emacs -Q Makefile ## change line 227 to read: ## ## $(OSX) -E0 -x lower $ | \ ## ^ ## |___ add this switch While you are about it, you may also decide to edit the Makefile so that the resulting Info manual utilises the full width of your screen. (This greatly improves the look of tables within the manual, of which there are many). ## change line 263 to read: ## ## $(MAEKINFO) --fill-column 184 --enable-encoding --no-split --no-validate $ -o $@ ##^ ## specify number of columns _| That's it. You're ready to roll. $ make postgres.info [...] $ ls -l postgres.info -rw-rw-r-- 1 sebyte sebyte 4939332 Jan 24 15:33 postgres.info Job done :) 'osx' generates 108,484 errors, all of which we ignored, and there are 14 other warnings in the output, but the resulting Info manual is fine. I wouldn't be surprised if there's a way of bypassing 'osx' altogether but I've no idea how. In fact, I've no idea what 'osx' even does :) Any tips anyone? HTH, Sebastian -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Ordering returned rows according to a list
Hi all, I'd like to make a single query that returns a number of rows using a 'WHERE id IN (list-of-ids)' condition, but I'd like the rows to be returned in the order in which the ids are given in the list. Is this possible? Sebastian -- 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] Ordering returned rows according to a list
Quoth Adam Rich ada...@sbcglobal.net: I'd like to make a single query that returns a number of rows using a 'WHERE id IN (list-of-ids)' condition, but I'd like the rows to be returned in the order in which the ids are given in the list. Depending on how many IDs you have in your list, you can accomplish this with a CASE statement: SELECT * FROM MYTABLE WHERE id IN (6, 9, 3) ORDER BY CASE id WHEN 6 then 1 WHEN 9 then 2 WHEN 3 then 3 END Thanks for this suggestion Adam. You say depending on how many IDs there are in the list. The query is constructed programatically so unless there's a limit on the number of conditions a CASE clause can handle, this is the way I'll go about it. Sebastian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unique constaint violated without being violated
Hi all,
Here's an odd one:
itidb= \d joblist;
Table public.joblist
Column | Type | Modifiers
---+--+---
full_name | character varying(64)| not null
email_address | character varying(64)| not null
username | character varying(12)|
password | character varying(12)|
recruiter | boolean | not null
subscribed| boolean | not null
verified | boolean | not null
created_at| timestamp with time zone | not null
updated_at| timestamp with time zone | not null
verification_code | character varying(24)|
alumni| boolean |
Indexes:
joblist_pkey PRIMARY KEY, btree (email_address)
joblist_username_key UNIQUE, btree (username)
itidb= update joblist set (full_name, email_address, recruiter,
itidb( subscribed, verified, created_at, updated_at) =
itidb- ('[name hidden]', '[email address hidden]', false, true
itidb( true, current_timestamp(0), current_timestamp(0));
ERROR: duplicate key value violates unique constraint joblist_pkey
itidb= select * from joblist where
itidb- email_address='[email address hidden]';
(No rows)
email_address is the primary key of this table (because the manual says
every table should have one :-) and the unique aspect of this primary
key is being violated when I try to enter the (hidden) email address
above.
But the email address hasn't already been entered into this table, as
shown by the output of the select command... so why the error?
Is my database corrupted somehow, or am I just losing my mind?
What course of action do you suggest I follow?
Sebastian
P.S. I've checked three times now, and I'm definitely using the same
email address in the update command and the select command, i.e., a
typo is not what's causing this.
--
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] Resp.: Automatic insert statement generator?
Hi Osvaldo,
Your list_fields function looked interesting to me so I tried it out and
it only worked for one of the five or so tables in the database I was
connected to at the time.
More concerning is the fact that I can't seem to drop it. I'm told it
doesn't exist, and then I use it to prove (to myself) that it does.
Here's it not working:
itidb= select list_fields('joblistings');
-[ RECORD 1 ]-
list_fields |
Here's it working:
itidb= select list_fields('joblist');
-[ RECORD 1 ]--
list_fields | full_name,username,password,recruiter,subscribed,...
Here's me trying to drop it, only to be told it doesn't exist:
itidb= drop function list_fields();
ERROR: function list_fields() does not exist
And here's it working again!
itidb= select list_fields('joblist');
-[ RECORD 1 ]--
list_fields | full_name,username,password,recruiter,subscribed,...
I'm noticing some very strange behaviour this evening (see thread
'Unique constaint violated without being violated').
Is my database corrupted or are there some vital database maintenance
tasks I've neglected to do?
I'm starting to get worried now.
Sebastian
--
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] Unique constaint violated without being violated
Quoth Merlin Moncure [EMAIL PROTECTED]: It looks to me like you are setting the whole table to the same address in the update statement (no where clause)...so of course you'd get the error. Maybe you want to do an insert statement? merlin Doh! Thanks Merlin. I'm so glad it's just my mind that's going! :-) Sebastian -- 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] Resp.: Automatic insert statement generator?
Quoth Raymond O'Donnell [EMAIL PROTECTED]:
On 06/12/2008 16:02, Sebastian Tennant wrote:
Here's it working:
itidb= select list_fields('joblist');
snip
Here's me trying to drop it, only to be told it doesn't exist:
itidb= drop function list_fields();
ERROR: function list_fields() does not exist
You need to specify the argument types as well, so this -
drop function list_fields(varchar); -- or whatever it is
- ought to work.
Ray.
Man, am I'm feeling geriatric tonight! Thanks for clearing that up for
me Ray.
I guess this is what comes of working under pressure on a Saturday night
when I should be out having a quiet drink with a few friends.
Sebastian
--
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] Detecting changes to certain fields in 'before update' trigger functions
Quoth Alvaro Herrera [EMAIL PROTECTED]: Sebastian Tennant wrote: Quoth Alvaro Herrera [EMAIL PROTECTED]: Sebastian Tennant wrote: P.S. Emacs users of PostgreSQL might like to know that there's a texinfo version of the manual (version 8.3.3) available for download from here: http://www.emacswiki.org/PostGreSQL Hmm, we did have a patch to add a texinfo target to the docs Makefile ... apparently it was never applied. Maybe that's a good idea? It's a very good idea IMHO. Hmm, actually now that I look closer, it is there (make postgres.info does the trick). The build process throws a worrying number of warnings though. Warnings are better than errors :-) I'll download the source and have a go myself. Many thanks Alvaro. Sebastian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Detecting changes to certain fields in 'before update' trigger functions
Hi list, First steps in trigger functions and PL/pgSQL so please bear with me... How can one detect changes to certain fields in before update trigger functions? IF (NEW.column-name != OLD.column-name) THEN ... doesn't work, so obviously my understanding of the values of the varriables NEW and OLD in before update trigger functions is wrong; I had thought that OLD holds the record as it was before the update, and that NEW holds the record as it is since the update (but before the update has been committed)? How should one go about detecting changes to certain fields in before update trigger functions? Any help/advice much appreciated. Sebastian -- 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] Detecting changes to certain fields in 'before update' trigger functions
Quoth Adrian Klaver [EMAIL PROTECTED]: On Monday 01 December 2008 7:18:51 am Sebastian Tennant wrote: I had thought that OLD holds the record as it was before the update, and that NEW holds the record as it is since the update (but before the update has been committed)? '42.10 Trigger Procedures' seems to confirm this: `NEW' Data type `RECORD'; variable holding the new database row for `INSERT'/`UPDATE' operations in row-level triggers. This variable is `NULL' in statement-level triggers. `OLD' Data type `RECORD'; variable holding the old database row for `UPDATE'/`DELETE' operations in row-level triggers. This variable is `NULL' in statement-level triggers. It works here. Can you be more specific? Full function code, table schema,etc. Of course. timestamper.sql starts here -- \i ./timestamper.sql DROP TABLE IF EXISTS tt; CREATE TEMP TABLE tt (username character varying(12), delisted boolean, created_at timestamp(0) without time zone, updated_at timestamp(0) without time zone, delisted_at timestamp(0) without time zone); CREATE OR REPLACE FUNCTION timestamper() RETURNS TRIGGER AS $$ BEGIN IF (TG_OP = 'INSERT') THEN NEW.created_at := current_timestamp(0); END IF; IF (TG_OP = 'UPDATE') THEN NEW.updated_at := current_timestamp(0); IF ((NEW.delisted = true) AND (NEW.delisted != OLD.delisted)) THEN NEW.delisted_at := current_timestamp(0); END IF; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER timestamper_before_insert BEFORE INSERT ON tt FOR EACH ROW EXECUTE PROCEDURE timestamper(); CREATE TRIGGER timestamper_before_update BEFORE UPDATE ON tt FOR EACH ROW EXECUTE PROCEDURE timestamper(); -- DROP FUNCTION timestamper() CASCADE; -- no need to drop temporary tables timesatmper.sql ends here testdb= \i ./timestamper.sql DROP TABLE CREATE TABLE CREATE FUNCTION CREATE TRIGGER CREATE TRIGGER testdb= insert into tt values (foo'); INSERT 0 1 testdb= select * from tt; -[ RECORD 1 ] username| foo delisted| created_at | 2008-12-01 16:17:37 updated_at | delisted_at | testdb= update tt set username=bar'; UPDATE 1 testdb= select * from tt; -[ RECORD 1 ] username| bar delisted| created_at | 2008-12-01 16:17:37 updated_at | 2008-12-01 16:18:27 delisted_at | testdb= update tt set delisted=true where username='bar'; UPDATE 1 testdb= select * from tt; -[ RECORD 1 ] username| bar delisted| t created_at | 2008-12-01 16:17:37 updated_at | 2008-12-01 16:19:01 delisted_at | The triggers for the initial insert and the first update do what I want them to, but the second update (that marks 'foo' as delisted) fails to update the delisted_at timestamp. Sebastian -- 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] Detecting changes to certain fields in 'before update' trigger functions
Quoth Richard Broersma [EMAIL PROTECTED]: On Mon, Dec 1, 2008 at 7:18 AM, Sebastian Tennant [EMAIL PROTECTED] wrote: IF (NEW.column-name != OLD.column-name) THEN ... The != operator doesn't work the way you might think when nulls are thrown into the mix. I asked a similar question a while back and was kindly pointed to the following syntax: IF( NEW.* IS DISTINCT FROM OLD.* ) THEN ... That's it! Thanks very much Richard. I sometimes think this kind of gotcha is purposely buried, or not addressed at all, in order to force users to read the manual. I wasn't planning on spending four hours doing just that, but now I suppose I'm almost glad I did. Sebastian -- 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] Detecting changes to certain fields in 'before update' trigger functions
Quoth Richard Broersma [EMAIL PROTECTED]: On Mon, Dec 1, 2008 at 8:35 AM, Sebastian Tennant [EMAIL PROTECTED] wrote: IF (NEW.column-name != OLD.column-name) THEN ... IF( NEW.* IS DISTINCT FROM OLD.* ) THEN ... I sometimes think this kind of gotcha is purposely buried, or not addressed at all, in order to force users to read the manual. I wouldn't say it is intentionally buried. I would say that the PostgreSQL manual focuses primarily is on What are the PG features. While the manual may at times document some of the good/best practices to use by combining various PG features, I wouldn't say that its intention isn't to be an authoritative source on How to use PG features. On the other hand, there are many ANSI-SQL books that focus on good practices. For example, the need for the IS DISTINCT FROM when dealing with nulls would be discussed in an SQL book. Once you have the theory down, you can turn to the PostgreSQL manual to find out how PostgreSQL implements this functionality. That's sound advice and I take your point about the manual focussing on Postgre features rather than SQL per se. I have read one or two SQL books but I'm very much a learn by doing person... and the fact is, I haven't done much doing, until now. May I wriggle out a little by saying that I didn't really mean what I said, or rather, I failed to say what I really meant; that it sometimes feels as if a gotcha has been buried in order to make you read the manual. Sebastian -- 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] Detecting changes to certain fields in 'before update' trigger functions
Quoth Tom Lane [EMAIL PROTECTED]: Sebastian Tennant [EMAIL PROTECTED] writes: I sometimes think this kind of gotcha is purposely buried, or not addressed at all, in order to force users to read the manual. Where exactly do you think we should document it, if not in the manual? I clearly didn't express myself very well. Let me set the record straight by saying that my experience with PostgreSQL over the past three months or so has been fantastic, thanks in no small part to the clear and comprehensive accompanying manual. All I meant was that it sometimes _feels_ as if a vital piece of information has been buried in the manual in order to make you read it. (I wasn't making a serious point and I didn't expect it to be taken literally). Sebastian P.S. Emacs users of PostgreSQL might like to know that there's a texinfo version of the manual (version 8.3.3) available for download from here: http://www.emacswiki.org/PostGreSQL -- 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] Detecting changes to certain fields in 'before update' trigger functions
Quoth Alvaro Herrera [EMAIL PROTECTED]: Sebastian Tennant wrote: P.S. Emacs users of PostgreSQL might like to know that there's a texinfo version of the manual (version 8.3.3) available for download from here: http://www.emacswiki.org/PostGreSQL Hmm, we did have a patch to add a texinfo target to the docs Makefile ... apparently it was never applied. Maybe that's a good idea? It's a very good idea IMHO. I would love to see a texinfo target in the docs Makefile. Nothing beats Info for convenience. Sebastian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
