[SQL] Using UPDATE FROM
I have a sql query which hits 5 database tables. I'll cut a bit out of the results and just show the important values reps_goal reps_actual repsvalue 10 10 1 33 5 1 10 12 1 10 12 1 10 10 1 11 11 1 What I'm trying to do is a single UPDATE statement which will take the values from the reps_actual column, and put them into the repsvalue column. These two columns come from different tables or obviously you could to a simple regular UPDATE. Here is the UPDATE statement: UPDATE programactivitysets SET repsvalue = reps_actual FROM workouts w, workoutactivities wa, workoutactivitysets was, programactivities pa, programactivitysets pas WHERE wa.workout_id = w.workout_id AND was.workoutactivity_id = wa.workoutactivity_id AND pa.programactivity_id = wa.programactivity_id AND pas.programactivity_id = pa.programactivity_id AND pas.set = was.set AND w.workout_id = 6036; After I run that and do a select, these are the results: reps_goal reps_actual repsvalue 10 10 5 33 5 5 10 12 5 10 12 5 10 10 5 11 11 5 For some reason, repsvalue is assigned 5 which is the reps_actual value for the 2nd row. This isn't right. Am I doing something incorrectly or can postgres not handle this kind of UPDATE? Regards, Collin Peters ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Text->Date conversion in a WHERE clause
I have a table that has some columns which store 'custom' fields so the content varies according to the user that the row belongs to. For one of the groups of users the field is a date (the type of the field is 'text' though). I'm trying to perform a query where it only returns values in a certain date range so in the WHERE clause I have WHERE cust3 <> '' AND cust3::text::timestamp > CURRENT_DATE - interval '1 month' This results in the error 'ERROR: date/time field value out of range: "052-44-5863"'. Now that is obviously not a valid date but there is actually more to the where clause and the first part of it excludes all rows where the user is not even the correct type, so the row which includes the field '052-44-5863' should really not even be checked. My main confusion lies in the assumption I made that the offending row would not even be included as it should have already been discarded. Is this not the case?How can I overcome this problem? There appears to be no isDate() function in postgresql like there is in sql server. Regards, Collin Peters ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Help with simple query
I have a simple table called notes which contains notes for users. The table has 4 columns: note_id (auto-incrementing primary key), user_id (foreign key to a users table), note (varchar), and modified_date (timestamp). Is there a nice simple query I can run that will return me a list of all the *latest* notes for all users (users can have many notes in the table)? I'm trying to figure out a simple way of doing it but seem to be having some mental block or there is no easy way to do it. The following query will return me all the latest dates, but I can't return the note_id or subject with it. SELECT n.user_id, max(n.modified_date) FROM notes n GROUP by n.user_id ORDER BY n.user_id Is this simpler than I am making it? Regards, Collin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Am I crazy or is this SQL not possible
I am having some serious mental block here. Here is the abstract version of my problem. I have a table like this: unique_id (PK) broadcast_id date_sent status 1 1 2005-04-0430 2 1 2005-04-01 30 3 1 2005-05-20 10 4 2 2005-05-29 30 So it is a table that stores broadcasts including the broadcast_id, the date sent, and the status of the broadcast. What I would like to do is simply get the last date_sent and it's status for every broadcast. I can't do a GROUP BY because I can't put an aggregate on the status column. SELECT MAX(date_sent), status FROM broadcast_history GROUP BY broadcast_id How do I get the status for the most recent date_sent using GROUP BY? DISTINCT also doesn't work SELECT DISTINCT ON (email_broadcast_id) * FROM email_broadcast_history ORDER BY date_sent As you have to have the DISTINCT fields matching the ORDER BY fields. I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent I keep thinking am I missing something. Does anybody have any ideas? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Multi-table insert using RULE - how to handle id?
I am learning about how to use rules to handle a multi-table insert.
Right now I have a user_activity table which tracks history and a
user_activity_users table which tracks what users are associated with
a row in user_activity (one to many relationship).
I created a rule (and a view called user_activity_single) which is to
simplify the case of inserting a row in user_activity in which there
is only one user in user_activity_users.
CREATE OR REPLACE RULE user_activity_single_insert AS
ON INSERT TO user_activity_single
DO INSTEAD (
INSERT INTO user_activity(
user_activity_id,
description,
...
)
VALUES (
NEW.user_activity_id,
NEW.description,
...
);
INSERT INTO user_activity_users (
user_activity_id,
user_id
)
VALUES (
NEW.user_activity_id,
NEW.user_id
);
);
This works well by itself, but the problem is that I have to manually
pass in the user_activity_id which is the primary key. I do this by
calling nextval to get the next ID in the sequence.
Is there any way to have the rule handle the primary key so I don't
have to pass it in? It seems you can't use pgsql inside the rule at
all. What I'm looking for is something like:
CREATE OR REPLACE RULE user_activity_single_insert AS
ON INSERT TO user_activity_single
DO INSTEAD (
SELECT nextval('user_activity_user_activity_id_seq') INTO next_id;
INSERT INTO user_activity(
user_activity_id,
description,
...
)
VALUES (
next_id,
NEW.description,
...
);
INSERT INTO user_activity_users (
user_activity_id,
user_id
)
VALUES (
next_id,
NEW.user_id
);
);
Note the sequence stored in next_id. This doesn't work as it
complains about next_id in the INSERT statements. Any way to do
something like this? I suppose I could create a function and then
have the rule call the function but this seems like overkill.
Regards,
Collin
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] Question about "AT TIME ZONE"
The following is taken from section 9.9.3 of the help docs === Examples (supposing that the local time zone is PST8PDT): SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST'; Result: 2001-02-16 19:38:40-08 SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST'; Result: 2001-02-16 18:38:40 The first example takes a time stamp without time zone and interprets it as MST time (UTC-7), which is then converted to PST (UTC-8) for display. The second example takes a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7). === In the first example it says it is converted to PST "for display". In the second example it is not converted to PST for display. Does this mean that if a timestamp *with* a timezone is specified, and it also includes "AT TIME ZONE", that it is not converted to PST "for display" at the end? I just want to make sure that these two examples perform completely different tasks. Essentially the first item: SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST'; And this: SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-07' AT TIME ZONE 'PST'; are the exact same thing. Kind of confusing. Regards, Collin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Dynamic prepare possible in plpgsql?
Is it possible to have a dynamic PREPARE statement in plpgsql? Something like PREPARE users_plan ( || 'text, text' || ) AS INSERT INTO pp_users( || 'col1, col2' || ) VALUES($1, $2); Regards, Collin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] "is not distinct from" syntax error?
From: http://www.postgresql.org/docs/8.2/static/functions-comparison.html " The ordinary comparison operators yield null (signifying "unknown") when either input is null. Another way to do comparisons is with the IS [ NOT ] DISTINCT FROM construct: expression IS DISTINCT FROM expression expression IS NOT DISTINCT FROM expression For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, when both inputs are null it will return false, and when just one input is null it will return true. Similarly, IS NOT DISTINCT FROM is identical to = for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these constructs effectively act as though null were a normal data value, rather than "unknown"." However, when I do: SELECT 1 IS NOT DISTINCT FROM 2 I get "ERROR: syntax error at or near "DISTINCT"" I can do: SELECT NOT (1 IS DISTINCT FROM 2) What is the problem here? Regards, Collin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Dynamic prepare possible in plpgsql?
I have a plpgsql function which is doing a loop over one table of user data and then inserting that data in various tables. Example: loop over user table (temp data) insert into users1 table insert into users2 table etc end loop Is it faster to use PREPARE for the various INSERT statements inside a plpgsql function? Perhaps I am wrong and it does its PREPARE work when the function is parsed. Collin On 4/30/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Collin Peters" <[EMAIL PROTECTED]> writes: > Is it possible to have a dynamic PREPARE statement in plpgsql? Well, you could use plpgsql's EXECUTE to run SQL PREPARE and EXECUTE commands, but it seems awfully brute-force. What do you really need to accomplish here? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Dynamic prepare possible in plpgsql?
So if I have an INSERT inside a LOOP in a plpgsql function, it is only prepared once? Regards, Collin On 5/1/07, Jonah H. Harris <[EMAIL PROTECTED]> wrote: On 5/1/07, Collin Peters <[EMAIL PROTECTED]> wrote: > Is it faster to use PREPARE for the various INSERT statements inside a > plpgsql function? Perhaps I am wrong and it does its PREPARE work > when the function is parsed. IIRC, PLpgSQL automagically prepares each statement behind the scenes on the first use. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Possible to access value in RECORD without knowing column name?
In plpgsl, if I have a RECORD variable that is populated via some dynamic SQL, is it possible to access the columns in that RECORD object without knowing the column names? I.e. Can I grab whatever value is the 3rd column? random_colname = 'foobar'; sql = 'SELECT col1, col2, ' || random_colname || ' FROM table'; FOR mviews IN EXECUTE sql LOOP --possible to access 3rd column of mviews? something like mviews[3]? END LOOP; Regards, Collin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Insert into VIEW using RULE. Not possible to use nextval()?
I am having the same problem that is documented elsewhere in the
archives. Namely when you have a INSERT RULE on a VIEW the nextval()
function doesn't behave properly (or it doesn't behave how I'd like it
to).
http://archives.postgresql.org/pgsql-sql/2003-07/msg00333.php
I'm wondering if anything has changed for this in regards to best
practices? Suggested solutions are to change to a trigger or use
currval() for your secondary INSERTS inside the RULE.
A trigger does not apply to my case as I am basically using this as a
shortcut to manually doing two INSERTs. Is there any problems with
using the currval() approach? If I use that within the same call is
there any chance of that not returning the correct value? (e.g. if
this INSERT RULE is being called a 1000 times at once, is it
guaranteed to be correct?
Another option I see is to place the INSERT inside a LOOP. For
example instead of:
INSERT INTO user_activity_single(user_activity_id, activity_date,
user_activity_type_id, user_activity_action_id, user_id, div1)
SELECT nextval('user_activity_user_activity_id_seq'), etc
have:
FOR mviews IN
SELECT nextval('user_activity_user_activity_id_seq') as id,
CURRENT_DATE, 1, 2, 27, 'foo'
LOOP
INSERT INTO user_activity_single(mviews.id, etc...)
END LOOP;
Performance wise this doesn't seem as good. In my case the SELECT
statement would be around 4000 records.
Any tips for me?
Regards,
Collin Peters
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Insert into VIEW using RULE. Not possible to use nextval()?
The exact problem is that you can't use nextval() with an INSERTable VIEW Problem is the same as that in this post: http://archives.postgresql.org/pgsql-sql/2003-07/msg00333.php On 5/3/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: --- Collin Peters <[EMAIL PROTECTED]> wrote: > I am having the same problem that is documented elsewhere in the > archives. Namely when you have a INSERT RULE on a VIEW the nextval() > function doesn't behave properly (or it doesn't behave how I'd like it > to). > > http://archives.postgresql.org/pgsql-sql/2003-07/msg00333.php > Is your problem that you can't insert more than one record at a time into your INSERTable VIEW? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Insert into VIEW using RULE. Not possible to use nextval()?
Hi Richard,
Your situation is the same as mine. The only difference is that I
actually had a call to nextval() in the actual call to the VIEW. In
your case this would be something like "INSERT INTO
Vschematic_insert(...". So if you were to try "INSERT INTO
Vschematic_insert(nextval('foo_sql'), ..." and then try to use that
value inside the RULE it will fail.
Your solution works great assuming two things:
1. You don't need to use the ID for anything else (which is true in
your case and actually in mine as well)
2. currval() is guaranteed to be correct within the RULE. This was my
second question. If I call "INSERT INTO Vschematic_insert(..."
simultaneously 1000 times is it guaranteed to be correct?
On 5/3/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
--- Collin Peters <[EMAIL PROTECTED]> wrote:
> The exact problem is that you can't use nextval() with an INSERTable VIEW
I apoligize if I am not understanding your problem correctly. I am unsure as
to intent behind
using nextval() as opposed to currval().
I do not know if the following DDL will help but I can show you how I preform
inserts across
multiple tables using the rule system:
CREATE RULE Vschematic_insert AS ON INSERT TO Docs.Vschematic
DO INSTEAD (
INSERT INTO Docs.Document ( did, doccode, docnum, docdisc)
VALUES ( DEFAULT, 'schematic', New.docnum, New.docdisc);
INSERT INTO Docs.Drawing ( did, doccode, title1, title2, title3)
VALUES ( Currval('Docs.Document_did_seq'), 'schematic', New.title1,
New.title2, New.title3);
INSERT INTO Docs.Schematic ( did, doccode)
VALUES ( Currval('Docs.Document_did_seq'), 'schematic')
);
For reference the table DDL follows:
CREATE TABLE docs.document (
did serial PRIMARY KEY,
doccode varchar(30) not null,
docnum varchar(30) unique not null,
docdisc textnot null default '',
constraint
document_doccode_chk
check ( doccode in ( 'cpf', 'logicsystem', 'processdetail',
'electricaldetail', 'locationplan', 'logicdiagram',
'loopdiagram', 'schematic', 'wiringdiagram', 'pid',
'isometric', 'airsupplydetail', 'mountingdetail',
'pnuematicdetail', 'functionaldiscription',
'datasheet', 'processmaterialspec',
'loopfoldermiscellaneous', 'loopfolderorficeplate',
'loopfolderinstallation', 'loopfolderswitch',
'loopfolderxmtrctrlind', 'loopfoldercontrolvalve',
'loopfolderanalyzer', 'loopfolderworkscope',
'loopfolderdocumentation')));
CREATE TABLE docs.drawing
(
did integer primary key references
docs.document(did) on delete cascade,
doccode varchar(30) not null,
title1 varchar(50) not null,
title2 varchar(50) not null,
title3 varchar(50) not null,
constraint
drawing_doccode_chk
check ( doccode in ( 'processdetail', 'electricaldetail',
'locationplan',
'logicdiagram', 'loopdiagram', 'schematic',
'pid',
'isometric', 'airsupplydetail',
'mountingdetail',
'pnuematicdetail'))) ;
CREATE TABLE docs.schematic
(
did integer primary key references
docs.drawing(did) on delete cascade,
doccode varchar(30) not null,
cid integer references equ.lcp(cid),
constraint
schematic_doccode_chk
check ( doccode = 'schematic')) ;
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Convert serial column to regular integer
I have a need to convert an incorrectly typed serial column to a regular integer column. Basically this just involves removing the sequence. I am able to successfully remove the default value (DROP DEFAULT) (which seems to use nextval) and now pgadmin does show the column as an integer, but I cannot remove the sequence as it says it is still in use. If I look at the column in pgadmin the sequence field is still filled in with the sequence but it is grayed out. Is there any way to remove the sequence fully? Regards, Collin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Convert serial column to regular integer
Anything pre-8.2? On 5/11/07, Rodrigo De León <[EMAIL PROTECTED]> wrote: CREATE TABLE dtab (i SERIAL); ALTER TABLE dtab ALTER COLUMN i DROP DEFAULT; ALTER SEQUENCE dtab_i_seq OWNED BY NONE; DROP SEQUENCE dtab_i_seq; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
