Re: [SQL] "record" datatype - plpgsql
I attempted the same thing in pl/pgsql but was unable to find a satisfactory
solution using it. I eventually started using tcl as the procedural language
to get this type of effect. Tcl casts NEW and OLD into arrays in a manner
that makes it possible.
Original post:
Subject: PL/Pgsql Trigger fcuntion issue..
This is suppose to pull all the columns of the table that initiated the
trigger func from the sys catalogs, loop through them and put everything
that has changed between OLD and NEW into a comma delimited string for
input into a log like table for future analysis via middleware (php,perl..,etc).
Here is the problem, OLD.A results in 'old does not have field A', which
is true. I cant get the OLD and NEW record objects to realize that I want
OLD. for the column name instead of an explicit
A as the column name. The only way I can find to make this work is by using
TCL for the procedural language because of the way it casts the OLD and
NEW into an associative array instead of a RECORD object, but by using TCL
I will lose functionallity in the "complete" version of the following function
which has been stripped to show my specific problem so using TCL is currently
not in my list of options. Any insight will be greatly appreciated.
create or replace function hmm() returns TRIGGER as '
DECLARE
table_cols RECORD;
attribs VARCHAR;
A VARCHAR;
BEGIN
IF TG_OP = ''UPDATE'' THEN
FOR table_cols IN select attname from pg_attribute where attrelid =
TG_RELID and attnum > -1 LOOP
A := table_cols.attname;
IF OLD.A != NEW.A THEN --Begin problem
IF attribs != THEN
attribs := attribs || '','' || table_cols.attname || ''='' || OLD.A
|| ''->'' || NEW.A;
ELSE
attribs := table_cols.attname || ''='' || OLD.A || ''->'' || NEW.A;
END IF;
END IF;
END LOOP;
END IF;
RAISE EXCEPTION ''%'', attribs;
RETURN NULL;
END;
' Language 'plpgsql';
1 of my TCL functions (note: a person with good TCL experience may be able
to make this shorter or faster, first thing I ever really did in TCL but it
works fast and reliable on a large database):
create function touch_loggerINSUPD() returns OPAQUE as '
spi_exec "select current_user as tguser"
spi_exec "select relname as tgname from pg_class where relfilenode = $TG_relid"
if {[string equal -nocase $tguser audit] } {return OK }
if {[string equal -nocase $TG_op INSERT] } {
set forins ""
foreach i $TG_relatts {
set forins "$forins^[array get NEW $i]"
}
set themid [lindex [split [array get NEW acid] " "] 1]
spi_exec -array C "INSERT INTO TOUCHLOG (username,tablename,recid,modtime,action,attributes)
values (''$tguser'',''$tgname'',''$themid'',''now'',''$TG_op'',''$forins'')"
}
if {[string equal -nocase $TG_op UPDATE] } {
set forins ""
set toadd ""
foreach i $TG_relatts {
if {[string equal -nocase [array get NEW $i] [array get OLD $i]] == 0}
{
set toadd "[array get OLD $i]-[array get NEW $i]"
set forins "$forins^[string trim $toadd \']"
}
}
set themid [lindex [split [array get NEW acid] " "] 1]
spi_exec -array C "INSERT INTO TOUCHLOG (username,tablename,recid,modtime,action,attributes)
values (''$tguser'',''$tgname'',''$themid'',''now'',''$TG_op'',''$forins'')"
}
return OK
' LANGUAGE 'pltcl';
Brian Knox wrote:
That's not what I was asking. I know I can access the values of each
column using NEW.columnname. What I was asking is if there's anyway to use
the NEW record to get a list of the columnnames in it without knowing them
beforehand.
Brian Knox
On Thu, 29 May 2003, George Weaver wrote:
Hi Brian;
Assuming "NEW" has been declared as foo%rowtype, you can access the
columns thus
NEW.xxx where xxx is the column name
HTH.
George
- Original Message -
From: "Brian Knox" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, May 29, 2003 11:11 AM
Subject: [SQL] "record" datatype - plpgsql
Given a variable of the "record" data type in pl/pgsql, is it possible to
get the names of the columns ( attributes ) of that record?
eg, given record "NEW" for table "foo", is there a way to get information
concerning the columns that make up that record?
Brian Knox
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] return %ROWTYPE from function
hi Anton, works fine if you write: create or replace function get_rec (in p_id test.id%TYPE) returns test as $$ -- regards, jr. (j...@tailorware.org.uk) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] safely exchanging primary keys?
hi Louis-David,
tmp := nextval('cabin_type_id_cabin_type_seq');
seems to me you're adding a newly created key value (for which there
isn't a record yet).
--
regards, jr. (j...@tailorware.org.uk)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: [GENERAL] How to store gif or jpeg? Thanks!
Hello! When you store data with PostgreSQL as BLOBs it doesn't matter if it's an image, sound or whatever - everything works in the same way. So you should use the image type that better represents your images. You can find some information about how to use it below: psql: (see the commands \lo_[something] ) http://www.postgresql.org/devel-corner/docs/postgres/app-psql.html client interfaces: http://www.postgresql.org/devel-corner/docs/postgres/programmer-client.html Nelson. Maurizio Ortolan wrote: > Hello! > > In your opinion, which is the best > way to store images (gif o jpg) > with PostgreSQL? > > Where can I find an easy example? > > Many thanks to all of you! > CIAO! > MAURIZIO > > *** > ** Happy surfing on THE NET !! ** > ** Ciao by ** > ** C R I X 98 ** > *** > AntiSpam: rimuovere il trattino basso > dall'indirizzo per scrivermi... > (delete the underscore from the e-mail address to reply) > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Insert/Update Perl Function involving two tables needing to by 'in sync'
I am trying to write a Perl Function for one of the databases I'm building a web application for. This function is triggered to occur BEFORE INSERT OR UPDATE. This function is complex in that it involves fields in two different tables which need to be updated, where the updates one receives depend on and must actually also reflect the same data entered into one of the tables explicitly by the user. I basically want to make sure that certain Same fields in two tables are automatically updated with the exact Same information. This is redundant but it is the design our engineers came up with and management is forcing me to work with this redundant design. I've been trying to I've been trying to tweak this function using "new." & "old." prefixes for the fields that I'm manipulating but it doesn't work. I've attached my function below. Any input on how this logic is wrong is appreciated. See the code below. CREATE FUNCTION classdata_scheduleid_sync() -- the purpose of this function is to make sure that when a schedule_id -- is updated in the registration_and_attendance table, that the -- class_id start_date, & end date fields in this table are -- automatically updated with the corresponding class_id, start_date, -- end_date from the schedules table RETURNS trigger AS 'DECLARE schedule_info RECORD; BEGIN IF length(new.schedule_id) = 0 -- IS THIS AN INSERT OR UPDATE? THEN -- IS AN UPDATE FOR existing schedule_id IS NULL SELECT INTO schedule_info * FROM schedules WHERE id = old.schedule_id; UPDATE registration_and_attendance SET class_id = schedule_info.class_id, start_date = schedule_info.start_date, end_date = schedule_info.end_date WHERE id = old.id; ELSE-- must have been an INSERT SELECT INTO schedule_info * FROM schedules WHERE id = new.schedule_id; INSERT INTO registration_and_attendance(class_id, start_date, end_date) VALUES (schedule_info.class_id, schedule_info.start_date, schedule_info.end_date) WHERE schedule_id = new.schedule_id; END IF; RETURN new; END;' LANGUAGE 'plpgsql'; ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] UPDATE/INSERT on multiple co-dependent tables
Is it possible for an UPDATE/INSERT query string to function in such a way that it requires two like fields in different tables to be equal to/'in sync with' one another: Example: I have two tables: registration & schedules they both record a class_id, start_date, end_date... I want to make sure that if the schedule_id field is updated in the registration table; that class_id, start_date & end_date fields automatically change to match the schedules.id record in the schedules table I've devised a function to handle this but pgsql recognizes the query to be 'infinitely recursive: CREATE RULE registration_update AS ON UPDATE TO registration DO UPDATE registration SET class_id = schedules.class_id WHERE new.schedule_id = (SELECT schedules.id FROM schedules WHERE id = new.schedule_id); What I'm doing is kind of redundant but necessary for backwards-compatibility ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] UPDATE/INSERT on multiple co-dependent tables
Is it possible for an UPDATE/INSERT query string to function in such a way that it requires two like fields in different tables to be equal to/'in sync with' one another: Example: I have two tables: registration & schedules they both record a class_id, start_date, end_date... I want to make sure that if the schedule_id field is updated in the registration table; that class_id, start_date & end_date fields automatically change to match the schedules.id record in the schedules table I've devised a function to handle this but pgsql recognizes the query to be 'infinitely recursive: CREATE RULE registration_update AS ON UPDATE TO registration DO UPDATE registration SET class_id = schedules.class_id WHERE new.schedule_id = (SELECT schedules.id FROM schedules WHERE id = new.schedule_id); What I'm doing is kind of redundant but necessary for backwards-compatibility -- www.sleekcollar.com Ferindo Middleton, Jr. Chief Architect Sleekcollar Internet Application & Artistic Visualizations [EMAIL PROTECTED]
[SQL] incorrect syntax for 'plpgsql' function to test boolean values
I'm trying to write a function and trigger to validate that user data entry for boolean values makes sense before being inserted or updated into my database. I have the following trigger: CREATE TRIGGER trigger_registration_and_attendance BEFORE INSERT OR UPDATE ON registration_and_attendance FOR EACH ROW EXECUTE PROCEDURE trigger_insert_update_registration_and_attendance(); Here is the problem: Below is the first part of the function called from the above trigger 1: CREATE FUNCTION trigger_insert_update_registration_and_attendance() 2: RETURNS opaque 3: AS 'DECLARE 4:schedules_record RECORD; 5:BEGIN 6: 7:/* To ensure the integrity of boolean variables this database stores to identify the 8: status of a registration */ 9: IF ((new.enrolled == true) && (new.waitlisted == true)) 10: THEN RAISE EXCEPTION ''Participant cannot be Enrolled AND Waitlisted at the same time.''; 11: END IF; 12: IF ((new.enrolled == true) && (new.cancelled == true)) 13: THEN RAISE EXCEPTION ''Participant cannot be Enrolled and Cancelled at the same time.''; 14: END IF; I get he following error message when I try inserting a record: ERROR: operator does not exist: boolean == boolean HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. CONTEXT: SQL statement "SELECT (( $1 == true) && ( $2 == true))" PL/pgSQL function "trigger_insert_update_registration_and_attendance" line 13 at if What is wrong with my syntax above? Ferindo -- Ferindo Middleton Chief Architect Sleekcollar.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] incorrect syntax for 'plpgsql' function to test boolean
Yeah, I guess so. I just didn't want the compiler to think I was trying to assign the value. And I also figured out that instead of the &&, I needed to just say AND Thanks. Ferindo John DeSoi wrote: On Aug 6, 2005, at 10:52 PM, Ferindo Middleton Jr wrote: ERROR: operator does not exist: boolean == boolean HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. CONTEXT: SQL statement "SELECT (( $1 == true) && ( $2 == true))" PL/pgSQL function "trigger_insert_update_registration_and_attendance" line 13 at if What is wrong with my syntax above? Too much C programming :). You just want a single equal sign. select true = true; ?column? -- t (1 row) John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL -- Ferindo Middleton Chief Architect Sleekcollar.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to secure PostgreSQL Data for distribute?
On 8/17/05, Premsun Choltanwanich <[EMAIL PROTECTED]> wrote: Dear All, I need to distribute my application that use PostgreSQL as database to my customer. But I still have some questions in my mind on database security. I understand that everybody who get my application database will be have a full control permission on my database in case that PostgreSQL already installed on their computer and they are an administrator on PostgreSQL. So that mean data, structure and any ideas contain in database will does not secure on this point. Is my understanding correct? What is the good way to make it all secure? Please advise. If it is "your" database, then I would not give them the database, but merely offer the information in the database as a "service." This, of course, can be implemented through the internet. That way, the rules that govern which customers can access and see which pieces of data can be implemented in the application itself so you wouldn't have give them all the data and structure the customer doesn't need to see if the rules governing how they access the database from the application are built into the application itself, with possibly authentication credentials stored in the database and the athentication mechanism implemented in the application. The only other way I can imagine where you can get beyond, atleast not showing the data they don't need to see, is querying the database to filter out the data 'belonging' to the customer, and export that filtered data to isolated tables comprising a new database that could then be given to the customer, watered down to what's relevant to them. If it's "your" database then, hey, don't give it to them. If it's really "their" database then you wouldn't really have a problem with giving them their data... But it's apparently not really "their" database so keep it to yourself and offer access to the data as a service. Ferindo -- Ferindo Middleton Chief Architect Sleekcollar.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] showing multiple reference details from single query
I have a table (table_one) with two columns, both of which are integers which reference the same column (on a one-to-many relationship) row back at another table (table_two) which has more detailed info on these integer columns: table_one has the following columns: id (SERIAL), column_one (INTEGER REFERENCES table_two(id)), column_two (INTEGER REFERENCES table_two(id)) table_two has the following columns: id (SERIAL), more_detailed_info (TEXT) How can I write a single query which will can uniquely identify in the result the separated values of the more_detailed_info field of table_two? For instance, if I write the following query: SELECT * FROM table_one WHERE id = 4 AND table_one.column_one = table_two.id; I'll get rows which will list the more_detailed_info fields one time for each match but table_two has more_detailed_info for both column_one and column_two. However the query above will only be able to show the more_detailed_info field for column_one. How can I show the more_detailed_info field for column_two as well on the same row, simultaneously with that of column_one Sorry if this is confusing. I don't know of all the technical jargon which involves my question above. Ferindo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] showing multiple REFERENCE details of id fields in single query that share the same table
I have a table which has two id fields which REFERENCE data back at another table. It's setup like this: class_prerequisite_bindings(id SERIAL, class_id INTEGER REFERENCES classes(id), prerequisiteINTEGER REFERENCES classes(id)) The classes table is like this: classes(idSERIAL, course_titleTEXT, course_codeTEXT) I have the following query: SELECT * FROM class_prerequisite_bindings, classes WHERE class_prerequisite_bindings.class_id = 64 AND class_prerequisite_bindings.class_id = classes.id; If I run the query above, the result will only give me info about the class_id field matching id 64 back in the classes table. PROBLEM: I want this query to also show the info about the prerequisite field which would also have info at the classes table. This query will only show the course_title and course_code of the class_id but I need this for the prerequisite field as well. I think I need to do a JOIN, but I don't understand how. How can I do this? Ferindo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] redundancy in CHECK CONSTRAINTs
I have the following table: CREATE TABLE gyuktnine ( id SERIAL, intsystem INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT int_cannot_equal_ext CHECK (intsystem != extsystem), extsystem INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT ext_cannot_equal_int CHECK (extsystem != intsystem), PRIMARY KEY (intsystem, extsystem) ); the intsystem and extsystem fields both have a check constraint on them which preventing any one record from having values in which they are equal. There is also a primary key. Is this redundant? Do only one of them really need this constraint? Or does it not really matter. I'm concerned about using constraints like this and have redundant checks built in slowing down my db. Ferindo ---(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
Re: [SQL] redundancy in CHECK CONSTRAINTs
Thank you for your advice, Tom. I've re-done the table in my db using the schema you describe below. The is a need for the id field. Other tables in my applications use it to refer to any one intsystem/extsystem relationship and be able to provide users with one simple number to use to refer to them. Thank you. Ferindo Tom Lane wrote: Ferindo Middleton Jr <[EMAIL PROTECTED]> writes: I have the following table: CREATE TABLE gyuktnine ( id SERIAL, intsystem INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT int_cannot_equal_ext CHECK (intsystem != extsystem), extsystem INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT ext_cannot_equal_int CHECK (extsystem != intsystem), PRIMARY KEY (intsystem, extsystem) ); Is this redundant? Yes. I think it's poor style too: a constraint referencing multiple columns should be written as a table constraint not a column constraint. That is, you ought to write CREATE TABLE gyuktnine ( idSERIAL, intsystem INTEGER NOT NULL REFERENCES yuksystems(id), extsystem INTEGER NOT NULL REFERENCES yuksystems(id), PRIMARY KEY (intsystem, extsystem), CONSTRAINT int_cannot_equal_ext CHECK (intsystem != extsystem) ); At least in the earlier versions of the SQL standard, it was actually illegal for a column constraint to reference any other columns. I'm not sure if that's still true in the latest spec. Postgres treats column constraints and table constraints alike, but other SQL databases are likely to be pickier. BTW, is there any actual need for the "id" column here, seeing that you have a natural primary key? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
Is there some reason why the SERIAL data type doesn't automatically have a UNIQUE CONSTRAINT. It seems that the main reason for using it is so that the value for this field keeps changing automatically and is never null so any one record can be identified using it- So why not imply that it is always be UNIQUE anyway. I mean, if you were to force another value on a SERIAL field that already had that same value, the would through the sequence tracking the the fields current value off any way, so it just makes sense to me to not let a serial field be duplicated. Let's take a poll. Is there anyone out there who actually uses the SERIAL data type who would not want it to be UNIQUE? Ferindo ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Why doesn't the SERIAL data type automatically have a UNIQUE
You're right, Tom. I'm sure someone has a use for a serial field that isn't unique. I just assumed that it was. I guess I didn't read the documentation closely enough. At any rate, I had a table using a serial field that I had to restore to a previous date when I noticed that I forgot to set the sequence to the most recent value... user continued adding data to this table and it started causing some problems. It just seems like most situations would want it unique... to ensure integrity. But I guess you need to choose constraint for built-in data types that follow more of a one-size-fits-all philosophy. And hey, how hard can it be to add the word UNIQUE when I'm creating tables? Ferindo Tom Lane wrote: Ferindo Middleton Jr <[EMAIL PROTECTED]> writes: Is there some reason why the SERIAL data type doesn't automatically have a UNIQUE CONSTRAINT. It used to, and then we decoupled it. I don't think "I have no use for one without the other" translates to an argument that no one has a use for it ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] changing a column's position in table, how do you do that
Is there a way to change the position attribute of a column in a table? I have data that I need to import into various tables in my db on a consistent basis... I usually us e the COPY ... FROM query but I can't control the -order- of the fields my client dumps the data so I would like to be able to change the position the columns in my table to be able to better align the data to be imported with the format of my table. I was thinking I could do something like "ALTER TABLE ALTER COLUMN ... " or something like that to change the columns position in the table but I can't figure out how. Ferindo ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Why doesn't the SERIAL data type automatically have a
Jim C. Nasby wrote: On Tue, Sep 27, 2005 at 10:33:14AM -0500, Scott Marlowe wrote: On Mon, 2005-09-26 at 20:03, Tom Lane wrote: Ferindo Middleton Jr <[EMAIL PROTECTED]> writes: Is there some reason why the SERIAL data type doesn't automatically have a UNIQUE CONSTRAINT. It used to, and then we decoupled it. I don't think "I have no use for one without the other" translates to an argument that no one has a use for it ... I have to admit, right after the change was made, I was of the opinion that no one would ever need that. Then, a few months later, it was exactly what I needed for some project... :) Arguably it would have been better to make the default case add either UNIQUE or PRIMARY KEY with a way to over-ride. If newbies are getting burned maybe it would be useful to toss a NOTICE or maybe even WARNING when a serial is created without a unique constraint of some kind? Based on the feedback I received after I made that original post, it seemed most people don't use SERIAL with a unique constraint or primary key and I was blasted for making such a suggestion. I'm sorry... It only seemed logical to me to do so and I thought other's would think the same. After giving it further thought to it and thinking about the broader scope that the developers would need to employ to the overall body of people using this database, it does now make more sense to me to just not include it at all and leave it to the admin to deploy it using what ever schema he/she sees fit... I don't think a NOTICE or a WARNING is necessary. People can read documentation. You should probably just stress more so that they actually read the docs rather than putting warnings and the like in place. When I first wrote the article I was a little falsely alarmed because I had thought that I didn't read the documentation and deployed a bunch of table using a serial without constraining them to some kind of UNIQUE property... but I later realized it was just this one table that I didn't do it with and had accidentally duplicated the fields integer value during manual INSERTS/RESTORES/BACKUPS etc. and the like to my db. Ferindo ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Why doesn't the SERIAL data type automatically have a
Richard Huxton wrote: Jim C. Nasby wrote: Is there some reason why the SERIAL data type doesn't automatically have a UNIQUE CONSTRAINT. It used to, and then we decoupled it. [snip] Arguably it would have been better to make the default case add either UNIQUE or PRIMARY KEY with a way to over-ride. Arguably SERIAL shouldn't be a type at all since it's nothing to do with defining a set of values. If you were being clean about it you'd have to have something like "mycol INTEGER SERIAL UNIQUE", then wire SERIAL to a generator function for the type in question. If newbies are getting burned maybe it would be useful to toss a NOTICE or maybe even WARNING when a serial is created without a unique constraint of some kind? Don't forget the NOT NULL too. Perhaps simpler to have a PGIDENT pseudo-type that implies "UNIQUE NOT NULL" and then explain the difference in the docs. -- Richard Huxton Archonet Ltd I like Richard's idea. That seems to be the best way to go. Ferindo Sleekcollar ---(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] UPDATE Trigger on multiple tables
Is it possible to have a single trigger on multiple tables simultaneously? Example: CREATE TRIGGER emp_cust_stamp BEFORE INSERT OR UPDATE ON employees, customers FOR EACH ROW EXECUTE PROCEDURE last_updated_stamp(); I tried something like the above but get an error message at the comma. I tried using the keyword AND as well. I couldn't find anything on this in the docs. I have many different tables in my databases which all have a "last_updated" field and it seems less tedious to be able to enforce updating this trigger database-wide using just one trigger. Is it possible? Ferindo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] query to return hourly snapshot
I orginally sent this email to the [novice] list but did not get any response.
I am look for help in developing a query that will return the nearest process
record that was
logged at or after each hour in a day (i.e. hourly snapshot).
Below are typical sample data. Also, I've included a query used to return the
average of the
process value for each hour to give an example for the format that I am trying
to achieve.
Thanks for the help.
Regards,
Richard
select * from process limit 10;
tstamp| process
-+-
2005-10-25 21:10:41 | 3.56513
2005-10-25 21:10:42 | 3.56503
2005-10-25 21:10:43 | 3.56494
2005-10-25 21:10:44 | 3.56484
2005-10-25 21:10:45 | 3.56475
2005-10-25 21:10:46 | 3.56465
2005-10-25 21:10:47 | 3.56455
2005-10-25 21:10:48 | 3.56446
2005-10-25 21:10:49 | 3.56436
2005-10-25 21:10:50 | 3.56427
(10 rows)
select avg(process),
date_trunc('hour',tstamp) as date
from process
wheredate_trunc('day', tstamp) = '2005-10-26'
group by date_trunc('hour', tstamp)
order by date_trunc('hour', tstamp);
avg|date
--+-
9.79195118032606 | 2005-10-26 05:00:00
10.0249767947376 | 2005-10-26 06:00:00
8.88596018049452 | 2005-10-26 07:00:00
7.95090951088542 | 2005-10-26 08:00:00
8.10741349776586 | 2005-10-26 09:00:00
7.30079822791947 | 2005-10-26 10:00:00
7.10586501293712 | 2005-10-26 11:00:00
8.15196838166979 | 2005-10-26 12:00:00
8.26183129151662 | 2005-10-26 13:00:00
8.95141531440947 | 2005-10-26 14:00:00
10.562882253329 | 2005-10-26 15:00:00
10.863490825 | 2005-10-26 16:00:00
11.4077104069976 | 2005-10-26 17:00:00
12.4702264580744 | 2005-10-26 18:00:00
11.9155618293134 | 2005-10-26 19:00:00
11.5622152555012 | 2005-10-26 20:00:00
11.6527367563489 | 2005-10-26 21:00:00
10.3170960432442 | 2005-10-26 22:00:00
9.56747980806563 | 2005-10-26 23:00:00
(19 rows)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] query to return hourly snapshot
Yes! Thanks you very much!
--- Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Tue, Apr 04, 2006 at 05:58:26PM -0700, Richard Broersma Jr wrote:
> > I am look for help in developing a query that will return the nearest
> > process record that was logged at or after each hour in a day (i.e.
> > hourly snapshot).
>
> Are you looking for something like this?
>
> SELECT p.process, date_trunc('hour', p.tstamp) AS hour
> FROM process AS p
> JOIN (
> SELECT date_trunc('hour', tstamp), min(tstamp)
> FROM process
> WHERE date_trunc('day', tstamp) = '2005-10-26'
> GROUP BY date_trunc('hour', tstamp)
> ) AS s ON s.min = p.tstamp
> ORDER BY hour;
>
> Or, using PostgreSQL's non-standard DISTINCT ON clause:
>
> SELECT DISTINCT ON (date_trunc('hour', tstamp))
>process, date_trunc('hour', tstamp) AS hour
> FROM process
> WHERE date_trunc('day', tstamp) = '2005-10-26'
> ORDER BY date_trunc('hour', tstamp), tstamp;
>
> --
> Michael Fuhr
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] how to solve this problem
Just a thought, Could you achieve that same result using the binary representation of an integer? Regards, Richard --- ivan marchesini <[EMAIL PROTECTED]> wrote: > Dear users, > I have this problem > > I have a table where there are 20 columns named > vinc1, vinc2, vinc3, vinc4, etc > > the values contained into each column are simply 1 or 0 (each column is > dichotomic) > 1 means presence > 0 means absence > > I would obtain a column (new_column) containg the name of the columns, > comma separated, where the value is = 1 > > for example: > > vinc1 vinc2 vinc3 vinc4 new_column > 1 0 1 0 vinc1,vinc3 > 0 0 0 1 vinc4 > 0 1 1 1 vinc2,vinc3,vinc4 > > can someone help me to find the best way to obtain this result??? > thank you very much > > Ivan > > > > > > > > -- > Ivan Marchesini > Department of Civil and Environmental Engineering > University of Perugia > Via G. Duranti 93/a > 06125 > Perugia (Italy) > e-mail: [EMAIL PROTECTED] > [EMAIL PROTECTED] > tel: +39(0)755853760 > fax: +39(0)755853756 > jabber: [EMAIL PROTECTED] > > > > > ---(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 > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How To Exclude True Values
> > how can i exclude true values for this query? > > > > http://www.rafb.net/paste/results/obtkGz26.html > > > > if i uncomment out > > > > --AND t_inspect_result.inspect_result_pass = 'f' > > > > it looks for prior falses within an inspect_id and > > returns it. i want the original result set minus > > the > > trues, if possible. > > > > tia... > > this SQL appears to do the trick... > > http://www.rafb.net/paste/results/zZKIjH80.html I have one idea that hopefully wont complicate you query but it could simplfy your query by getting rid of the query nexting. Also, I haven't tested it. Basically, Replace the DISTINCT ON (t_inspect.inspect_id) construct with GROUP BY t_inspect.inspect_id HAVING t_inspect_result.inspect_result_pass = 'f' Regards, Richard Broersma Jr. ---(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
Re: [SQL] Advanced Query
> Personally: I think your posts are getting annoying. This isn't SQLCentral. > Learn to write your own damn queries or even better - buy a book on SQL... Personally: (being a newbie with an interest in developing a strong rdms skillset) I've enjoyed following threads like these. Even when the questions (to some) seems overly simplistic, the courteous respondents often share insightful solutions or nuances that are not found in an "off the self" SQL book. However, if questions like these are *really* off-topic for the pgsql-sql I would be interested in knowing what kind of threads are acceptable and on-topic for this list. Also, if there are other mailing lists (pg or other) that are better suited for threads like this, I would appreciate learning of them. Regards, Richard Broersma ---(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
Re: [SQL] How To Exclude True Values
> inspect_id, inspect_result_id, inspect_result_pass, > inspect_result_timestamp > 3, 5, f, 2006-06-05 05:00:00 > 3, 6, t, 2006-06-05 06:00:00 > 4, 7, f, 2006-06-05 07:00:00 > *4, 8, f, 2006-06-05 08:00:00* > the query linked in this post will return 3 lines... > > 1, 2, f > 3, 5, f > *4, 8, f* -- the only one i really want >From your sample it seems to me that you are really only looking for the most >recient occuring record that have produced a false test regardless of which Inspect_id or inspect_result_id it came from. Is this correct? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How To Exclude True Values
> an inspection node (each row in t_inspect is an > inspection node) that has passed can't have a new > defect added - since it has already passed. > > therefore, in the defect entry form, i only want to > display those inspection nodes that don't have a true > value. by definition, a true value will always be the > last (by time) inspect_result. > > therefore, i need all the inspect nodes that don't yet > have a true value (iow, a true value in the last (by > time) inspect_result_pass row). > > an inspection node can have multiple inspection > results, hence, the t_inspection_results table. > > this might seem counter-intuitive at first, but it > makes sense since it may take 5 tries to eventually > pass a particular inspection node (f, f, f, f, t) for > fucntional test, for example. one node, five tests to > pass it. here is a test I did. bye the way, I did this is access as it is the only source available to me at the moment. table = test id_iir_id teststamp 1 1 No 6/5/2006 1:00:00 AM 1 2 No 6/5/2006 2:00:00 AM 1 3 Yes 6/5/2006 4:00:00 AM 2 4 Yes 6/5/2006 4:00:00 AM 3 5 No 6/5/2006 5:00:00 AM 3 6 Yes 6/5/2006 6:00:00 AM 4 7 No 6/5/2006 7:00:00 AM 4 8 No 6/5/2006 8:00:00 AM query -- SELECT a.id_i, a.ir_id, a.test, max(a.stamp) as mstamp -- PostgreSQL might not require an aggregate with the group by. FROM test a INNER JOIN ( SELECT Max(stamp) AS tmax, id_i FROM test group by id_i ) b ON a.stamp = b.tmax group by a.id_i, a.test, a.ir_id, a.stamp having a.test = false ORDER BY a.id_i,a.ir_id, a.test ; results -- id_iir_id testmstamp 4 8 No 6/5/2006 8:00:00 AM Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How To Exclude True Values
> Richard, that is the result i would need given that > data set. i have to digest this version, though. > > should this query be more efficient than the subquery > version as the table starts to get large? My experience is that Distinct On queries do not preform as well as their group by counter parts. I believe that others have also commented to the same effect. To speed the query you could apply indexes on the group by fields and the join columns. Also, I produced a second query using PostgreSQL: select a.id_i, a.ir_id, a.test, a.stamp from test a join ( select max(stamp) as mstamp, id_i from test group by id_i ) b on a.stamp = b.mstamp where a.test = false ; -- result id_i | ir_id | test |stamp --+---+--+- 4 | 8 | f| 2006-06-05 08:00:00 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How To Exclude True Values
> yields the following error: > > ERROR: schema "a" does not exist > > i tried to interpret you query and apply it to my > case, but, apparently, i didn't too good of a job. > > do you see the error? Hmmm... That is strange. but according to the PostgreSQL documentation on select you can add the option [AS] keyword between the table_name and alias. http://www.postgresql.org/docs/8.1/interactive/sql-select.html [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] select a.inspect_id, a.inspect_result_id, a.inspect_result_pass, a.inspect_result_timestamp from t_inspect_result AS a join ( select max(t_inspect_result.inspect_result_timestamp) as mstamp, t_inspect_result.inspect_id from t_inspect_result group by t_inspect_result.inspect_id ) AS b on a.inspect_result_timestamp = b.mstamp ; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How To Exclude True Values
> Richard, > > i got the expected rewsults! now i just have to study > the query to figure out what it does. -lol- > > i tried using AS, but i only did it in one place - > doh! the error message just moved to the next place i > didn't do it. i'll know better next time. > > thanks for the help. I am surprised that the query did not work the first time without the optional AS keyword. I would be interested in knowing why your server requires the AS and mine doesn't. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How To Exclude True Values
> i think i've figured out the gist of the thought > process behind the SQL query... > > 1. select everything from t_inspect_result as table a > 2. select max timestamp value entries in > t_inspect_result as table b > 3. choose only choose those rows where the max > timestamps of table a and b are equal. > > is that about it? The one problem with query would be if there is a possibility that two differnt tests will have the exact same time stamp. If that is a possibility, then you could use the exact same query structure but replace Max(timestamp) with max(inspect_result_id) and then join on inspect_result_id instead. Regards, Richard Broersma ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Query to return modified results at runtime?
> IDColor > --- --- > 1 Blue > 2 Red > 3 Green > 4 Orange > > How would I rewrite the query to return results where the colors are > replaced by letters to give the following results? > > IDColor > --- --- > 1 A > 2 D > 3 B > 4 C http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html this is probably the easiest to implement but hard to mangage over time. Another solution would be to create color_code table that is referenced by your test table. Then when you can create a query as: select a.ID, b.code from test as a join color_code as b on a.color = b.color; There are additional solutions to this also. But these two are probably the easiest. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Finding multiple events of the same kind
> On Sunday 11. June 2006 15:27, Frank Bax wrote: > >SELECT participants.person_fk, count(participants.person_fk) FROM > > events, participants > > WHERE events.event_id = participants.event_fk > > AND events.tag_fk in (2,62,1035) > > GROUP BY participants.person_fk HAVING > > count(participants.person_fk) > 1 > > That worked like a charm! Thank you very much! > -- Also, you could create a unique column constraint that would prevent multiply instances of the same person in the participants table. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] COPY to table with array columns (Longish)
> On Mon, 2006-06-12 at 21:58 -0500, Aaron Bono wrote: > > I agree with Tom. Personally I cannot think of a time I would use an > > array column over a child table. Maybe someone can enlighten me on > > when an array column would be a good choice. > > Arrays are a good choice when the data comes naturally segmented. > Also, for a case and point, some of the postgresql system tables use arrays. I suppose that these would be examples were the core develops felt arrays were a good fit. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Repetitive code
> AFAIK PostgreSQL does not support materialized views but it's > interesting that you mention that because in essence the query is used > to materialize a view, i.e., it's part of an INSERT / SELECT into a > table which is then joined back to the other tables to construct a web > page as well as an RSS feed. This thread: http://archives.postgresql.org/pgsql-performance/2006-06/msg00324.php mentions the use of materialized views in postgresql. See: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html However, I am not sure if this link shows how postgresql supports materialized views or if it just shows how to simulate a materialized view with procedural code. Either way, I thought it might be of interest to you. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] any additional date_time functions?
I am working with the date_trunc() function with great success especially in the group by clause for aggregates. However, it is limited to returning "WHOLE" time units. i.e. years, months, days, hours, minutes, seconds. Are there any functions similar to date_trunc that can return variable increments i.e.: 5, 10, or 15 minutes increments, 3, 4, 6 hour increments, 1, 2 weekly increments, I imagine that the returned values would have to either be the "floor" or "ceiling" of the actual time stamps. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] any additional date_time functions?
> > Are there any functions similar to date_trunc that can return variable > > increments i.e.: > > > > 5, 10, or 15 minutes increments, > > 3, 4, 6 hour increments, > > 1, 2 weekly increments, > You might be able to extract the time since the epoch and divide it by the > appropiate number of seconds (the length of your interval) and truncate > the result. This might have unexpected results for you when you span > daylight savings time changes. > > Another option is to not use timestamp, but rather just store an integer that > represents some number of your intervals offset from an epoch. Bruno, Thanks for the suggestions. I am going to "toy" around with them to see what I can get to work. Thanks for the help. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to get a result in one row
> >I'd like to get the result in only one row: > >id | nick > >--+-- > >22192 | A,T > This question is in the archives (probably more than once). The answer > is...> > Read the online docs about aggregate functions. There is an example that > does (almost) exactly what you are asking. Where you referring to the tread regarding the LTREE contrib module for postgresql? http://archives.postgresql.org/pgsql-general/2006-06/msg00745.php I know I've seen this done using cursors in PL-PGSQL, but I would be interested if there was a solution with pre-existing aggregates. Regards, Richard Broersma Jr. ---(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
Re: Fwd: [SQL] Start up question about triggers
> I did some research and can't even find a way to get meta data in a trigger. > > In a trigger, is there a way to inspect OLD and NEW to see what columns are > there and see what has changed? If so, you may not be able to grab the > actual query but you could create a generic trigger that reconstructs a > possible update/insert/delete for any table in your database. > > Does anyone know of a good place to go get information about using meta data > in a stored procedure or trigger? yes. See the section "User Comments" at the very bottom of Chapter 33 after "Writing Trigger Functions in C". It is odd that a PL_PGSQL example is given at the end a chapter for triggers written in C. http://www.postgresql.org/docs/8.1/interactive/trigger-example.html Also see the entire chapter 36.10 "Trigger Procedures" http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html Hope this is what you are looking for. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Fwd: [SQL] Start up question about triggers
> Im really interested in the part where you say "generic trigger" can you > give me some tips? As to how I will go about that? I had already read the > links that Richard gave, I new I could get the values like that. So right > now I will have to create a trigger for each of my tables to create the > necessary queries, or I could do it "generically" :-) Sorry, I guess I haven't kept up to speed with this thread. However, from chapter 36.10 http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html Notice the variables that you have to work with in a trigger function: TG_WHEN Data type text; a string of either BEFORE or AFTER depending on the trigger's definition. TG_RELNAME = Data type name; the name of the table that caused the trigger invocation. TG_OP = Data type text; a string of INSERT, UPDATE, or DELETE telling for which operation the trigger was fired. 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. Also, notice chapter 9.19 http://www.postgresql.org/docs/8.1/interactive/functions-info.html current_user = user name of current execution context So with this information couldn't one (from a trigger function) insert a record in to a history table with the following columns?: Then for each column of the affect table if old.tbl_col1 != new.tbl_col1 then add a record to the history as follows. TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_col1 TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_col2 TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_col3 TG_WHEN : TG_RELNAME : current_user : TG_OP : old.tbl_coln is this something like what you had in mind? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Fwd: [SQL] Start up question about triggers
> The problem is not tracking WHAT changed, this can be done, as we have > discussed in this thread, the problem is how to replicate the necessary > commands that will alter a mirror database to reflect what has been changed, > sequencially and in the order that it has occurred. If your finial goal is just to achieve db server replication, wouldn't slony achieve what you want? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Fwd: [SQL] Start up question about triggers
> > Actually man I do need to be able to write to both databases, and keep them > > synchronized, and all this because of the recurring xenofobia for technology > > Then sorry, but this can't be done out of the box by anything. You > have all manner of race conditions here. Doesn't PGcluster allow for multiple master databases that are kept synchronized? http://pgfoundry.org/projects/pgcluster/ I thought that Mammoth replicator might support synchronous masters but it appears to be an Asynchronous system like Slony. http://www.commandprompt.com/products/mammothreplicator Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] i need solution to this problem
> > I have tables like 1) emp_table (personal_no integer (foreign key), > > cdacno varchar (primary key),name varchar); > > > 2) Rank_table (rank_id varchar (primary key), rank_name varchar); > > > 3) Rank_date_table (rank_id (foreign key), rank_date date); > > > 4) Unit_table (unit_id varchar (primarykey), unit_name varchar); > > > 5) Personal_table (per_no varchar (primary key), pername varchar); > > > My query is â¦.if I give cdacno I have to get per_no from > > personal_table.. With this I have to display rank_name from > > rank_table ,name from emp_table, unit_name from unit_master.. > > it is not clear what the relationships are between the tables. for > example what is the foreign key to unit_table? > > how does the rank connect to emp_table or personal_table? yes. in addition to this, it seems that emp_table references personal_table on personal_no = per_no. But it is not clear how this is the case when personal_no is an integer and per_no is a varchar. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SELECT Aggregate
> SELECT trans_no,
> customer,
> date_placed,
> date_complete,
> date_printed,
> ord_type,
> ord_status,
select (
SUM(sell_price)
from soh_product
where sales_orders.trans_no = soh_product.soh_num
) as transact_sum,
> customer_reference,
> salesman,
> parent_order,
> child_order,
> order_number
> FROMsales_orders
> WHERE (trans_no Like '8%' AND order_number Like '8%')
> OR (trans_no Like '9%' AND order_number Like '8%')
> OR (trans_no Like '8%' AND order_number Like '9%')
> OR (trans_no Like '9%' AND order_number Like '9%')
> AND(warehouse='M')
> AND(date_placed > (current_date + ('12 months ago'::interval)))
> ORDER BY trans_no DESC
I am pretty new to SQL. But while reading a book written by an author
recommended on this list,I
can suggest a possible solution that I've seen. It might work for your
problem. Of course, I
haven't tested anything like this and don't know if PostgreSQL supports it.
Just be sure that trans_no is unique in the returned query.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] SELECT Aggregate
> > SELECT trans_no,
> > customer,
> > date_placed,
> > date_complete,
> > date_printed,
> > ord_type,
> > ord_status,
(select SUM(sell_price) -- this syntax working for me. see
below
fromsoh_product
where sales_orders.trans_no = soh_product.soh_num
) as transact_sum,
> > customer_reference,
> > salesman,
> > parent_order,
> > child_order,
> > order_number
> > FROMsales_orders
> > WHERE (trans_no Like '8%' AND order_number Like '8%')
> > OR (trans_no Like '9%' AND order_number Like '8%')
> > OR (trans_no Like '8%' AND order_number Like '9%')
> > OR (trans_no Like '9%' AND order_number Like '9%')
> > AND(warehouse='M')
> > AND(date_placed > (current_date + ('12 months ago'::interval)))
> > ORDER BY trans_no DESC
>
>
> I am pretty new to SQL. But while reading a book written by an author
> recommended on this
> list,I
> can suggest a possible solution that I've seen. It might work for your
> problem. Of course, I
> haven't tested anything like this and don't know if PostgreSQL supports it.
>
> Just be sure that trans_no is unique in the returned query.
select
f1.fiscalyear,
(select f2.startdate
from fiscalyeartable2 as f2
where f1.fiscalyear = f2.fiscalyear
) as start2date,
f1.enddate
from
fiscalyeartable1 as f1;
fiscalyear | start2date | enddate
++
1995 | 1994-10-01 | 1995-09-30
1996 | 1995-10-01 | 1996-08-30
1997 | 1996-10-01 | 1997-09-30
1998 | 1997-10-01 | 1998-09-30
QUERY PLAN
--
Seq Scan on fiscalyeartable1 f1 (cost=0.00..6.83 rows=1 width=6)
(actual time=0.044..0.067 rows=4 loops=1)
SubPlan
-> Index Scan using fiscalyeartable2_pkey on fiscalyeartable2 f2
(cost=0.00..5.82 rows=1 width=4)
(actual time=0.008..0.009 rows=1 loops=4)
Index Cond: ($0 = fiscalyear)
Total runtime: 0.138 ms
(5 rows)
it works, and check out the nifty query plan.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Using In Clause For a Large Text Matching Query
> We can assume a certain portion of the text is included in the DB table, > so I want to be able to do a substring match on "brown" and "green" and > in this case return both "brown kitty", and "green doggy". However the > problem is, if I run the query on each of my 300 rows to scan 200,000 > rows in my DB is entirely too slow. So I was hoping to use the IN clause > to create an IN group of about 300 items to scan the DB once. You can probably do it. However, you will have to pick a substring from your text field to compare against. In this case you seem to be choosing the first word, i.e. "brown" and "green". so maybe: select t1.col1 from table1 as t1, ( select distinct split_part( tblFileDump.Col1, ' ', 1 ) as samplecol from tblFileDump ) as fd1 where t1.col1 like '%' || fd1.samplecol || '%' ; This is just an idea. I've never used split_part or developed a sudo join this way. But it may work provided you and jump your text files into a temp table. Notice: http://www.postgresql.org/docs/8.1/interactive/functions-string.html for the syntax for split_part(). Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Using In Clause For a Large Text Matching Query
> Well, there is also: href="http://www.postgresql.org/docs/8.1/interactive/functions-comparisons.html#AEN13377";> > > /expression/ /operator/ ANY (/array expression/). So, if you have a way > to preprocess you > input text fields that you want matched > you could build a regex for each and feed them in an array to an '~ ANY' > expression like so (or, > use ~* for case > insensitive matching): > > SELECT col1 > FROM table > WHERE col1 ~ ANY (ARRAY['regex1', 'regex2', ...]); Good point, But don't forget to include the list in your response. :-) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECT Aggregate
> I've tried Aaron's suggestion of the GROUP BY and I don't know much about > it, but it ran for around 17 hours and still going (it had a dedicated Dual > Xeon 3.0GHz box under RHEL4 running it!) Maybe, this query that you are trying to run is a good candidate for a "Materialize View". http://archives.postgresql.org/pgsql-performance/2004-02/msg00279.php Also before you run your query you might want to see the explain plan is. Perhap it is using a sequencial scan in a place where an index can improve query preformance. ---(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
Re: [SQL] SELECT Aggregate
> Well whatdyaknow?? Being a Postgres newbie I hadn't even played with indexes > yet. They're awesome!! > Using Richard's suggestion of the Sub-Select in the COLUMN list, combined > with adding some indexes, I can now return this in under 5 seconds! Also, another way to improve preformance will be to analyze the affected tables. Analyze will ensure that the query planner has accurate statics by which it will use in picking fastest possible query. If you ever plan on updating or deleting records. You will also need to vacuum the table. And an additional measure of maintance would be to re-index the database. All of this is listing in the postgresql manual. If you really want to ensure the best possible speeds, it will be an important step to take. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Alternative to Select in table check constraint
I am practicing with SQL examples comming from the book:
JOE CELKO'S
SQL
PUZZLES
& ANSWERS
The following codes doesn't work on PostgreSQL 8.1.4 but according to the book
does conform to
SQL-92. Is there any other solutions that would result in the same effect? Or
is this an example
of a contraint that should be avoided at all costs?
CREATE TABLE BADGES
(
BADGENO SERIAL NOT NULL PRIMARY KEY,
EMPNO INTEGER NOT NULL REFERENCES SECEMPLOYEES (EMPNO),
ISSUEDATE DATE NOT NULL,
STATUS CHAR(1) NOT NULL CHECK ( STATUS IN ('A', 'I')),
CHECK ( 1 = ALL ( SELECT COUNT(STATUS)
FROM BADGES
WHERE STATUS = 'A'
GROUP BY EMPNO))
);
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] How To Exclude True Values
> Also, I produced a second query using PostgreSQL: > select a.id_i, a.ir_id, a.test, a.stamp > from test a > join > ( > select max(stamp) as mstamp, id_i > from test > group by id_i > ) b > on a.stamp = b.mstamp > where a.test = false > ; > -- result > id_i | ir_id | test |stamp > --+---+--+- > 4 | 8 | f| 2006-06-05 08:00:00 I found this query produced the same result. It is a list slower than the first with my small dataset. but maybe it will improve for larger datasets? select t1.id_i, t1.ir_id, t1.test, t1.stamp, t1.inttest from test as t1 where t1.stamp = ( select max(T2.stamp) from test as t2 where t2.id_i = t1.id_i) and t1.test = 'f'; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Alternative to Select in table check constraint
> > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > FROM BADGES > > WHERE STATUS = 'A' > > GROUP BY EMPNO)) > > From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ > interactive/sql-createtable.html) > > CREATE UNIQUE INDEX one_a_badge_per_employee_idx > ON badges (empno) > WHERE status = 'A'; > http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html > http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html Michael, Partial indexs seem to be "what the doctor ordered!" And your suggest is right on, the idea of the constraint is to allow only one active badge status at a time. But now that I think about it, using the authors suggestion (if it actually worked), how would would it be possible to change the active status from one badge to another? Oh well, partial index are obvious the superior solution since the entire table doesn't not have to be scanned to determine if the new badge can be set to active. Once again thanks for the insight. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Alternative to Select in table check constraint
> > The following codes doesn't work on PostgreSQL 8.1.4 but according to > > the book does conform to SQL-92. > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > FROM BADGES > > WHERE STATUS = 'A' > > GROUP BY EMPNO)) > > Yeah, the spec does allow sub-SELECTs in CHECK constraints but PG > doesn't implement that. The problem with it is that there's no clear > way to make it perform reasonably, because the CHECK doesn't simply > implicate the row you're currently inserting/updating --- every other > row is potentially referenced by the sub-SELECT, and so changing row > X might make the CHECK condition fail at row Y. A brute-force > implementation would be that every update of any sort to BADGES causes > us to re-evaluate the CHECK constraint (hence, re-scan BADGES entirely > to compute the sub-SELECT) for every row of BADGES (hence, O(N^2) work > if there are N rows in the table). That is certainly unworkable :-(. > A bright person can think of ways to optimize particular cases but > it's not easy to see how the machine might figure it out for arbitrary > SELECTs. > > The unique-index hack that Michael suggested amounts to hand-optimizing > the sub-SELECT constraint into something that's efficiently checkable. > > regards, tom lane Ah. Thanks for the clarification. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Left join?
> In the following table, codsol, codate and codfec are foreign keys > referencing table func and I need some help to codify a SELECT command that > produces the following result set but instead of codsol, codate and codfec I > need the respectives names (column nome from table func). > > postgres=# select * from reqtran; > codreq | codsol | codate | codfec > +++ > 1 ||| > 2 | 1 || > 3 | 1 | 1 | > 4 | 1 | 1 | 1 > postgres=# \d func > Table "public.func" > Column | Type | Modifiers > +-+--- > codfun | integer | not null > nome | text| > Indexes: > "reqtran_codate_fkey" FOREIGN KEY (codate) REFERENCES func(codfun) > "reqtran_codfec_fkey" FOREIGN KEY (codfec) REFERENCES func(codfun) > "reqtran_codsol_fkey" FOREIGN KEY (codsol) REFERENCES func(codfun) Would this do what you need? select R1.codreq, CS.nome, CD.nome, CF.nome from rectran as R1 left join func as CS on (R1.codsol=CS.codefun) left join func as CD on (R1.codate=CD.codefun) left join func as CF on (R1.codfec=CF.codefun) ; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Alternative to Select in table check constraint
> On Fri, 2006-06-30 at 18:41 -0700, Richard Broersma Jr wrote: > > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > > > FROM BADGES > > > > WHERE STATUS = 'A' > > > > GROUP BY EMPNO)) > > > > > > From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ > > > interactive/sql-createtable.html) > > > > > > CREATE UNIQUE INDEX one_a_badge_per_employee_idx > > > ON badges (empno) > > > WHERE status = 'A'; > > > http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html > > > http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html > > > > Michael, > > > > Partial indexs seem to be "what the doctor ordered!" And your suggest is > > right on, the idea > of > > the constraint is to allow only one active badge status at a time. > > > > But now that I think about it, using the authors suggestion (if it actually > > worked), how would > > would it be possible to change the active status from one badge to another? > > Unset the status first then set on the new one. Same transaction of > course. > > You may find this type of constraint is more workable with a TRIGGER > deferred until commit time than a unique constraint which cannot (at > this time in PostgreSQL) be deferred. Thanks for the Input Rod. I will try implementing a trigger as a way to constrain the input data to see how it works. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Alternative to Select in table check constraint
> This is more of an implementation option, but when I worry about what is > active/inactive I put start/end dates on the tables. Then you don't need > active indicators. You just look for the record where now() is >= start > date and now() <= end date or end date is null. You can even > activate/deactivate a badge on a future date. Of course, this complicates > the data integrity - you will need some kind of specialized trigger that > checks the data and makes sure there are no date overlaps to ensure you > don't have two badges active at the same time. But is also gives you a > history of badges and their activities. Good point. I take it that this type of solution stems from temporal schema design. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Can't drop table
> I have created a table called experimenters with the following query: > CREATE TABLE experimenters ( > experimenter_id INTEGER CONSTRAINT firstkey PRIMARY KEY, > first_name CHAR(20), > last_nameCHAR(25), > address CHAR(30), > phone_numCHAR(15) > ); > The query was successful. But when I try to drop or alter the table, it just > hangs without > echoing any error message. I have to cancel the query to get out. > I ran the following query to drop the table > DROP TABLE experimenters; > It also hangs when I try to alter the table. > Just don't understand the problem here. > Any help will be highly appreciated. You might want to post this on the PG_General Mailing list of you do not get many responses. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] hi i need to connect database from my system to another system
> I am using postgresql database server... in my system.. if I want to > connect to this database server from other system..Or how can I use this > database server in other machine .. > > How can I do this..if any one have idea about this please give me brief > description You need to start the postgresql server with the "-i" option to allow TCP/IP connections. Then you need to configure your pg_hba.conf file to set the permissions about which users can connect to which databases. Notice the pg_ctl section for starting postgresql. http://www.postgresql.org/docs/8.1/interactive/app-pg-ctl.html Notice the -o "options" section that can be set. (in this case you want -i) http://www.postgresql.org/docs/8.1/interactive/app-postmaster.html Finally notice, the section on the pg_hba.conf file. http://www.postgresql.org/docs/8.1/interactive/client-authentication.html#AUTH-PG-HBA-CONF Also, secure shell also works well when connecting from another system. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] hi i need to connect database from my system to another system
> If u don't mind can u please tell me clearly.. where I have start -I or > how to start postgresql with -I > > I am using postgresql database server... in my system.. if I want to > > connect to this database server from other system..Or how can I use > this > > database server in other machine .. > > > > How can I do this..if any one have idea about this please give me > brief > > description > > You need to start the postgresql server with the "-i" option to allow > TCP/IP connections. Then > you need to configure your pg_hba.conf file to set the permissions about > which users can connect > to which databases. > > Notice the pg_ctl section for starting postgresql. > http://www.postgresql.org/docs/8.1/interactive/app-pg-ctl.html > > Notice the -o "options" section that can be set. (in this case you want > -i) > http://www.postgresql.org/docs/8.1/interactive/app-postmaster.html > > Finally notice, the section on the pg_hba.conf file. > http://www.postgresql.org/docs/8.1/interactive/client-authentication.htm > l#AUTH-PG-HBA-CONF > > Also, secure shell also works well when connecting from another system. Don't forget to include the pgsql-sql list in your reply's. :-) The individuals on this list have varying degree of experience and knowledge of postgresql. So if you only respond to me, you would be limiting yourself to my meager experience and knowledge. ;-) To start off with, it would be helpful to know which OS version of PostgreSQL you are using. The windows versions is pre-configured to start with the "-i" option for allowing TCP/IP connections. Secondly, how are you trying to connect to your postgresql server from another system? What error messages are you getting. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] hi i need to connect database from my system to another
> I am using windows xp operating system... > I want to know how to connect remote postgresql from my system.. I need > brief description from beginning.. > Now I am using pg_HBF conf file to connect remote postgresql in that I > am giving host name , ip-address,mask and trust... but it is showing > error like > > Could not connect to server: connection refused(0x274d\10061) > Is the server running on host 127.0.0.1 and accepting TCP\IP connection > on port 5432 ? Actually, your PostgreSQL server is running on the IP address that is assigned to that computer. The 127.0.0.1 in the pg_hba.conf file is the initial accepted client IP address that your postgresql server will accepted connections from. 127.0.0.1 is basically means localhost. So, your pg_hba.conf file is initial configured to only allow client connections from itself. So in this case, if you want to connect from an IP address from a different computer, you will need to specify that ip address in your pg_hba.conf file as well as the permissions. But just to get started, you could try editing a line in your pg_hba.conf to this: #hostall all 127.0.0.1/32md5 hostall all/24 trust hostall all 127.0.0.1/32trust To understand all of this, you will need to read the introduction in your pg_hba.conf file. Also, keep in mind that the above change is basically an "open door" to everything in your DB server. So after you've had a change to get familiar with everything, you will want to tighten up on how,what, and how access is granted to you system. "Last but not least", you will need to restart your postgresql service so these changes will take effect. This should get you connected. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] i have a problem of privilages
> I am not an expert in postgres, may this work for you. > REVOKE INSERT,UPDATE,DELETE ON ALL from PUBLIC; > it will take the INSERT,UPDATE,DELETE permmission from all users for all > objects in the database. > If you want to give permission for a specific user on all objects > GRANT ALL on ALL to ; > Please make a backup before doing this , good for always > I am not sure is this is want you want. > > On 7/4/06, Penchalaiah P. <[EMAIL PROTECTED]> wrote: > > > > I have 290 tables .. to these tables one schema is there .. that name is > > public .so I don't know how to create permission to these tables.. either I > > have to give permissions to individual table.. or I have to give permissions > > to schema or schema name if I give permission to schema it has to > > applicable to all tables.. > > I created one user with password then finally I don't know how to > > allocate permission to him.. please tell me very briefly step by step.. then > > I can understand > > Please tell me if any one knows about this .. One way to limit which users can access specific database is with the pg_hba.conf file. You really should spend some time to read it. http://www.postgresql.org/docs/8.1/interactive/client-authentication.html#AUTH-PG-HBA-CONF In PostgreSQL 8.1 the concept of "Roles" were added. A "Role" can be given specific privileges to the database. Once this step is complete, database uses can be assigned to this role. This will essentially inherit all role privileges to these users. There is also an important document that should be read in order to understand it. http://www.postgresql.org/docs/8.1/interactive/user-manag.html The SQL syntax for assigning privileges is found here: http://www.postgresql.org/docs/8.1/interactive/ddl-priv.html Also finding these topic is very easy also. Simple scan over the table of contents of the manual: http://www.postgresql.org/docs/8.1/interactive/index.html And of-course depending on what version of PostgreSQL you have: http://www.postgresql.org/docs/manuals/ Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] create aggregate function 'count_bool( column_name, boolean )'
> so now I can create the statement > select distict product_id, count_bool(purchased, true), > count_bool(was_selected, true) from some_table group by product_id; > > instead of breaking the query into 3 seperate queries > > select distict product_id from some_table; > select count(purchased) from product_id where purchased = true; > select count(was_selected) from some_table where was_selected = true; > Am I missing a detail with SQL based aggregate function development? > Any help would be appreciated. how about: select product_id, ( select count(purchased) from some_table as A2 where purchased=true and A1.product_id=A2.product_id ) as TP, ( select count(selected) from some_table as A3 where purchased=true and A1.product_id=A3.product_id ) as TS from some_table as A1 group by product_id; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SELECT substring with regex
> valid entries would be: > "28mm F2.8" (prime lens) > "30-70mm F4" (zoom lens) > "30-70mm F2.8" (zoom lens) > "30-100mm F4.5-5.6" (zoom lens with variable speed) > > > In the WHERE clause I have specified all those NAMEs, which follow that > pattern but have some gubbins appended: > > WHERE NAME ~ > '^((\\d+(-\\d+)?)mm F((\\d+(\.\\d+)?)+(\.(\\d+(\.\\d+)?)+)?))\\D+$' > > > which gives me a listing of those candidates that need to be amended - > manually or otherwise. > > Next, I wanted to produce a result which splits NAME into what it should > be (BASE) and attributes (SUFFIX). Maybe I can generate some SQL from > that to tidy up the data. would this give you the results you want? It admit that it doesn't look to elegant. name: substr(your_string, 0, strpos(your_string, ' ')+1) suffix: substr(your_string, length(your_string)-strpos(your_string, ' '), length(your_string)) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Celko's Puzzle Number 5
No matter how I try it, I can't getting the book's answer for this puzzle to work. Does anyone know of a solution that will work for this problem. The Problem is: "How do you ensure that a column will have a single alphabetic character string in it? (That means no spaces, no numbers, and no special characters.)" The book's solution is as follows: CREATE TABLE Foobar (alpha_only VARCHAR(6) CHECK ((UPPER(TRIM(alpha_only)) || 'A') BETWEEN 'AA' AND 'ZZ') ); However, this check constraint only prevents numerics beginning with 'A'. So the constraint works by preventing following strings that begin with these kinds of characters: !, 4, A!..., A4... But the constraint fails to prevent non-alphabetic characters when the string starts with a character > A. Thus B thru Z can be follow on not alphabetic characters. I compared the result from PostgreSQL with sqlite and access. They returned the same result. According to the text, this solution "could" have been generalized to work with more complicated strings as well. For example string "masks" could be used to enforce a kind of tagging convention like 'AA4', 'BB5'. Has anyone seen or done anything like this before? I am interested to hear what kind of solutions there are. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] table joining duplicate records
> --query results-- > survey :category :question :answer > survey1:category1:question1:answer1 > survey1:category1:question1:answer2 > > how can i elimate duplicates on my query results? > an also am i using the right 'table joining' or table design for my > survey app? What duplicates? You have two unique answers for question1. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Celko's Puzzle Number 5
> http://www.postgresql.org/docs/8.1/interactive/functions-matching.html > > Any of these CHECK expressions should work: > > CHECK (alpha_only SIMILAR TO '[A-Za-z]+') > CHECK (alpha_only ~ '^[A-Za-z]+$') > CHECK (alpha_only ~* '^[a-z]+$') > > Unfortunately, even though SIMILAR TO has been standard SQL for > several years, not all databases implement it. Many databases > do support regular expressions but generally via a non-standard > syntax (as PostgreSQL does with its ~, ~*, !*, and !~* operators). Thanks for the link! Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] table joining duplicate records
> can i have a result set. just like the outer join returns. > but on my case. return null on duplicates. > like this one. > > survey :category :question :answer > --- > survey1:category1:question1:answer1 >: : :answer2 hmmm.. I am no sure that you are going to get what you want from a simple select query. A reporting software could do this easily however. also be sure to include the list in your replies :-). Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] MS-SQL<->Postgres sync
> On 7/10/06, Kevin Bednar <[EMAIL PROTECTED]> wrote: > > > > Thanks Aron. What I'm actually trying to do is this: > > > > Postgress in physical store, being used by POS system as the back end. > > MS-SQL being used on web server by ecommerce system. > > > > Table structures are different of course, but some common fields. What I > > want to do is when an item is sold in the store, update the quantity field > > for that sku number on the web site and vice versa. Only 2 fields basically > > need to be updated on each side, the SKU number and quantity. This is to > > keep the product table in sync and try to avoid selling product that isnt in > > stock and setting a flag on the web system stating such. Thanks for your > > help. > > > > > For something this simple you are probably better off doing some custom > coding. > > If you have the ability to modify the databases, I would recommend putting a > trigger on each database so when there is a product sold, that sale is > recorded in a temp table (which serves as a queue of data that needs to be > synched). Then have a process read from these temp tables and feed the data > back to the other database. Of course, I am assuming you have full control > to change the databases - some vendors do not allow that. > > You may be able to connect the databases - MS SQL Server will definitely > allow you to connect via ODBC to another database and feed data back and > forth. I think there are add on modules for PostgreSQL but I have not tried > to have PostgreSQL talk to other databases before. I am not sure if this applys directly to the problem here, but this link my be useful also. http://archives.postgresql.org/pgsql-general/2006-07/msg00298.php Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to find entries missing in 2nd table?
> Hi, > I realize I probably lost my marbles but I've been having a god > awful time with a single query: > > control: > > > controller_id pk; > > > datapack: > > controller_id fk; > > > > > I need to get all entries from the table control that are not listed in > datapack. SELECT C.CONTROLLER_ID FROM CONTROL AS C LEFT JOIN DATAPACK AS D ON ( C.CONTROLLER_ID = D.CONTROLLER_ID) WHERE D.CONTROLLER_ID IS NULL; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to find entries missing in 2nd table?
--- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > > >> I need to get all entries from the table control that are not listed in > > >> datapack. > > > > > > SELECT C.CONTROLLER_ID > > > > > > FROM CONTROL AS C > > > LEFT JOIN DATAPACK AS D ON ( C.CONTROLLER_ID = D.CONTROLLER_ID) > > > > > > WHERE D.CONTROLLER_ID IS NULL; > > > > > > > > > Or > > (SELECT controller_id FROM control) > > EXCEPT > > Good point! But don't forget to include the list. :-) > > Regards, > > Richard Broersma Jr. > > (SELECT controller_id FROM datapack) > > ? > > > > -- > > Milen A. Radev > > > > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to find entries missing in 2nd table?
> > SELECT controller_id FROM control > > WHERE controller_id NOT IN > > (SELECT DISTINCT controller_id FROM datapack); > The DISTINCT is not necessary. I have heard with Oracle that DISTINCT is a > huge performance problem. Is that true on PostgreSQL also? >From my experience, it does not preform as well as the standard group by >clause. I noticed a ~20% increase in query run times. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to find entries missing in 2nd table?
> > > > SELECT controller_id FROM control > > > > WHERE controller_id NOT IN > > > > (SELECT DISTINCT controller_id FROM datapack); > > > The DISTINCT is not necessary. I have heard with Oracle that DISTINCT > > is a > > > huge performance problem. Is that true on PostgreSQL also? > > > > From my experience, it does not preform as well as the standard group by > > clause. I noticed a ~20% > > increase in query run times. > > > > So in that case this would be better: > > SELECT controller_id FROM control > WHERE controller_id NOT IN > (SELECT controller_id FROM datapack); > > or > > SELECT controller_id FROM control > WHERE controller_id NOT IN > (SELECT controller_id FROM datapack GROUP BY controller_id); Well in this case, the group by or distinct is simple not needed for the query to preform correctly. The additional group by clause in the second query could cause it to preform additional processing which "may" cause it to preform slower. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Table Join (Maybe?)
> I have two tables: > 1. Sales figures by date and customer. > 2. Customer details - including their Geographic State > I need to extract a report from the first table (I can do that!), and in > that report order by their State (I can do that too!), but I also need a > summary of all the customers in each state, below the end of each state, and > have a grand total at the bottom. > Eg: > Customer 1 State 1 $100.00 > Customer 2 State 1 $100.00 > State 1 $200.00 > Customer 3 State 2 $100.00 > Customer 4 State 2 $100.00 > State 2 $200.00 > Grand Total $400.00 > Does anyone have any magic pointers for me? I've been playing with SELECT > INTO as 2 queries (the individual customers, then the summary figures added > to the temp table) but I end up with ROWS IN FIRST QUERY * ROWS IN SECOND > QUERY instead of them all sorted together nicely :-( I do not believe that a single "query" will give you what you want here. YOU could have 3 seperate queries: 1 for customer detail by state. 1 for state total 1 for grand total However, if you use a reporting tool like crystal or others, they have the ability to generate summaries exactly as you are referring to here. you would only need to pass it the query on total per customer. The reporting utility has a groupby feature where it would group the customers by state for your. In the group by summary it would automaticly display state total. Then if the report footer you could add a grand total summary for all of the records you passed to the report. The following link show some of the reporting programs that you can use. http://www.postgresql.org/community/survey.43 Regards, Richard Broersma Jr. ---(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
Re: [SQL] User Permission
> > Dear group, > >i created a user named 'dataviewer' and grant only select permission to > > that user, > >but now the user could able to create tables. how to restrict this, > >i want to give permission to create views and do selects on tables and > > views. > >how to do it? > >plz help. > Have your checked > http://www.postgresql.org/docs/8.1/interactive/sql-grant.html > http://www.postgresql.org/docs/8.1/interactive/sql-revoke.html > I would start by creating a role: > http://www.postgresql.org/docs/8.1/interactive/user-manag.html > And revoke all on it. Then add only the permissions it needs and assign the > role to the user. Also, one additional point would be to revoke all from public as mentioned in the following thread: http://archives.postgresql.org/pgsql-general/2006-07/msg00148.php Apparently, whatever privileges 'pubic' has are extended to the privileges of the individual users. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Error when trying to use a FOR loop
> Sorry if this is in the archives, but I've done a search and couldn't find > anything relevant. > I'm running HP's precompiled version of 8.1.3.1 as part of their Internet > Express offering, and > I can't seem to run a for loop. Here's what I'm seeing: > xp512-0715-0716=# FOR LV in 1..10 LOOP > xp512-0715-0716-# select * from ldevrg; > ERROR: syntax error at or near "FOR" at character 1 > LINE 1: FOR LV in 1..10 LOOP The following is give examples of control structures. However, you need to be sure that your version of postgresql has a procedural language installed. If not, you will need to install it. http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS Use can use createlang to add different languages to postgresql. http://www.postgresql.org/docs/8.1/interactive/sql-createlanguage.html The following link gives a shot list of languages that you can use. There are others however: http://www.postgresql.org/docs/8.1/interactive/server-programming.html see Server-side Procedural Languages from: http://www.postgresql.org/download/ Hope this help. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Help with privilages please
REVOKE ALL on TABLE suppliers FROM hilary; now login as hilary SELECT * from suppliers; and I get all the records!!! If I create a **new** table though and then do the above, the permissionswork I get a polite message telling me "no go". Thissounds to me like a problem with earlier compatibility. Is there away I can overcome this. A simple dump/restore does not solve theproblem. You might also have to revoke all from public: Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] simple problem???
> A|B|C > 1|2|20 > 1|3|10 > 1|4|21 > 2|3|12 > 2|4|22 > 3|4|23 > 1|3|10 > 2|3|12 > 3|4|23 > -select all records where A=1, > -find, into this selection, the record where there's the minimum value > of the field C > -print all the fields for this record. maybe this will work; select a,b,c from table as T1 join (select a, min(c) as minc from table group by a) as T2 on (t1.a = t2.a) and (t1.c = t2.c) ; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to use table from one database to another
> I have 2 databases namee PAO and CAS. > PAO contains 3 schemas named Public,pao,sts CAS contains 4 schemas > named Public,cao,sts,reports > Now i am in PAO database..now i want access table 'activity' in > schema 'cas' in CAS database. > How it is posible. > 2nd thing is... > i have 2 servers access i.e local and mainserver. > How access table from one server to another server? > please tel me...because we need this one > Please give me full details with examples... because I am new to > Postgres but I know oracle very well For this functionality to work you will need the db-link add on for postgresql. http://pgfoundry.org/projects/snapshot/ Also for other useful addons check out the project tree. http://pgfoundry.org/softwaremap/trove_list.php Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Query response time
> I am using PostgresSQL 7.4 and having some serious performance issues. > Trawling through the archives and previous posts the only visible advice > I could see was either by running vacuum or setting the fsynch flag to > false. > > I am using tables that only contain approx 2GB of data. However > performing a number of simple conditional select statements takes a > great deal of time. Putting limits on the data obviously reduces the > time, but there is still a delay. (Note: on one particular query I set > the limit to 538 and the query returns in under 2mins if the limit > becomes 539 the query loops indefinitely!) > From previous experience I know these delays are longer than both > Informix and MySql. In some instances it takes so long I end up having > to kill the query. > > The install was performed by yum onto a RAID server using Centos. I am > sure there is something fundamentally wrong for I can't believe that > postgres would have the reputation it has based on the statistics I'm > getting. Does anyone have any advice? > > The data I am using was imported from an Informix system as part of a > migration strategy. > I know this is long shot but I hope someone can shed some light. Are the Update/Insert queries slow or is it the select queries that are taking awhile? For select queries, an explain analyze of the offending query would be helpful. Also, in addition to vacuuming you may want to reindex you db in order to clean all of the dead tuples from your indexs. For heavy insert/update queries check your postgres logs to see if any messages suggest increasing your check-point-segments. If this is the case, try increasing you check_point_segments and try moving your pg_xlog to a different spindle. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Best way to do this query..
> I have the following: > > name effective tstamp rate > John 01-01-2006 2005-12-07 13:39:07.614945115.00 > John 01-16-2006 2006-01-07 13:39:07.614945125.00 > John 01-16-2006 2006-01-09 15:13:04.4169351885.00 > > I want the output to be: > name effective end_daterate > John 01-01-2006 01-15-2006115.00 > John 01-16-2006 1885.00 > > What is the best way to do this? This is on a huge table and what I > have right now is quite slow. Any ideas? I would assume that your data does not change after it is logged. If this is the case, maybe this part of your data model would actually be a good canidate for an OLAP data model. If this is not the direction your are enterested in moving, you could also reduce your query processing time by limiting the data ranges for your existing query to something for reasonable. Regards, Richard Broersma Jr. ---(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
Re: [SQL] Best way to do this query..
> Yes the data does not change once it is logged. > I am quite new to this whole thing, do you mind elaborating more > about the OLAP data model you mentioned about? Well just to give a generalization using an OLAP as your data model, the data is stored in a denormalized yet defined model. Data integrety is maintained because by definition all records inserted will remain static. Here are some additional links that can be used for getting started: http://en.wikipedia.org/wiki/OLAP http://www.amazon.com/gp/product/0123695120/sr=8-4/qid=1156546075/ref=pd_bbs_4/002-4041472-4877644?ie=UTF8 Another alternative is to create a materialized view that will update itself only when records are inserted or updated. http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Create Assertion -- Question from a newbie
Sorry if my question is a little off topic. I am reading my new "SQL for Smarties" book side by side with the PostgreSQL 8.1 manual. I noticed that this particular feature is not included in PostgreSQL. Some of the achieve threads mostly discuss that this feature is currently not supported. My understanding is that Assertions place constraints upon data spanning multiple related tables. Is the Assertion feature slated to be added in the future? (Perhaps rolled up in a more generalized "TO-DO" item?) Would this feature add functionality that can not be achieved by other means? (i.e. alternative schema definitions or triggers?) Or does it merely provide a redundant means to constrain data, and thereby not warrant addition into the features of PostgreSQL? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Create Assertion -- Question from a newbie
> You can accomplish what assertions do using triggers. > I think the issue is generating triggers for general assertions that don't > totally suck performancewise. Ah, I see. So the points is that checking the integrity between two complete data sets can become a preformace killer. Thanks for the feed back. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Fastest way to get max tstamp
> name | program | effective | tstamp | rate > --+-+++-- > jdoe | AAA | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 > jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 > jdoe | AAA | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20 > jdoe | BBB | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20 > jdoe | AAA | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 > jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 > jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 > jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 > > I want to get: > name | program | effective | tstamp | rate > --+-+++-- > jdoe | AAA | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 > jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20 > jdoe | AAA | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 > jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20 > jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 > jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20 > > Basically, for effective='08-16-2006', it only gets the latest inserted > record (using tstamp) for that effective date, which is 2006-08-25 11:57: > 17.394854. > > So what is the quickest way to do this? > I can always do: > Select * from Table t where tstamp=(select max(tstamp) from Table t2 where > t2.name=t.name and t2.effective=t.effective) > but it takes so long since this is a huge table. > > Any suggestions? SELECT name, program, effective, tstamp, rate FROM TABLE AS T1 JOIN ( SELECT max(tstamp) as maxtstamp FROM Table WHERE tstamp between current_timestamp - interval '7 days' and current_timestamp GROUP BY name, program, effective ) AS T2 ON (T1.tstamp = T2.maxtstamp) ; A smaller date range on a large table will really speed up your query also. If you really need to see the results of the same table over and over again, a materialized view(i.e. push the query results into a table and then add incremental updates over time) would probably work better for you. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] where clause subqueries vs multiple rows results
> I've a query which I'd have liked to word akin to: > > SELECT guid FROM child WHERE the_fkey = > ( SELECT id FROM parent WHERE name ~ 'some_regex' ) > ORDER BY the_fkey, my_pkey; > > I got around it by doing the SELECT id first, and then doing a SELECT > guid for each row returned, appending the results together. > > Can that be done in a single query, insead of 1+n queries? select guid from child C join parent P on (C.the_fkey = P.di) Where P.name ~ 'some_regex' order by C.the_fkey, P.my_pkey; Perhaps this might work. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to autoincrement a primary key...
> I am sure this is simple, but I don't get it. I am new to PGSQL, coming from
> MySQL - in mysql, you can autoincrement the primary key; in postgre, I am
> not sure how to do this. I have read the documentation, and tried "nextval"
> as the default - I have searched for the datatype SERIAL, but I am using
> navicat and this datatype is not supported. Can someone tell me how to do
> this - I just want the integer value for a primary key to autoincrement by
> one.
CREATE TABLE bar (idSERIAL PRIMARY KEY);
Is just shorthand notation for:
CREATE SEQUENCE foo START 1;
CREATE TABLE bar (id integer PRIMARY KEY DEFAULT nextval('bar'));
Also see:
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
http://www.postgresql.org/docs/8.1/interactive/sql-createsequence.html
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] conversion of numeric field from MSSQL to postgres
> It seems that you've already asked for this question last Sunday, and > because your question is somewhat deterministic, the answers are more likely > to be the same. Check your previous e-mails. actually I am get duplicate emails like this one across the various PG lists. Perhaps someone else is resending these email? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] conversion of numeric field from MSSQL to postgres
> This could be fixed if someone wanted to improve the moderation software > to auto-approve pending messages from someone who's just subscribed, but > perhaps that's a lot of work. I haven't looked at that code, so I'm not > volunteering ... Thats not a problem. I was surprised to see your name in a man pages for other command line utilities un-related to postgresql. So I would expect that you have enough to do in the mean time besides altering the moderation software. :-) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [HACKERS] Bug?
> Then how do we clear the values of a serial column(is it done only by > dropping the column?)? If you look at the following link, you will see that serial is not really a true data-type. It is merely a short-cut to get the desired results: http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL Also, if you are interested in resetting your columns "serial" value back to 1 or 0 or -1, you can do it using the setval() function for the following link: http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] delete on cascade
> Hi all, > I guess this is an already asked question, but I didn't found an answer, so > apologize me. Imagine I've got two tables: > skill(id,description) // primary key => id > family(id,description)// primary key => id > and I want to associate skills to families: > ass_sf(id_skill,id_family)// appropriate foreign keys > > Tables are already created and the database is running. Now I'd like to > implement a delete cascade, thus when I delete a skill also its association > with the family must be deleted. I gues I should have declared the skill > table as follows: > CREATE TABLE skill > ( > id varchar(20) on delete cascade, > description varchar(50), > primary key(id) > ); > > right? The problem is: how can I alter the table to add the column constraint > now, without redeclaring the table? > Thanks very much for helping me. This link has one line that is very similar to what you want to do. You will probably have to start a transaction, drop the foriegn key contraint and then add a new foriegn key contraint with the on drop cascade . http://archives.postgresql.org/pgsql-general/2006-10/msg00467.php Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] delete on cascade
i guess my first attempt to send a reply failed. --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > > Hi all, > > I guess this is an already asked question, but I didn't found an answer, so > > apologize me. Imagine I've got two tables: > > skill(id,description) // primary key => id > > family(id,description) // primary key => id > > and I want to associate skills to families: > > ass_sf(id_skill,id_family) // appropriate foreign keys > > > > Tables are already created and the database is running. Now I'd like to > > implement a delete cascade, thus when I delete a skill also its association > > with the family must be deleted. I gues I should have declared the skill > > table as follows: > > CREATE TABLE skill > > ( > > id varchar(20) on delete cascade, > > description varchar(50), > > primary key(id) > > ); > > > > right? The problem is: how can I alter the table to add the column > > constraint > > now, without redeclaring the table? > > Thanks very much for helping me. > > This link has one line that is very similar to what you want to do. You will > probably have to > start a transaction, drop the foriegn key contraint and then add a new > foriegn key contraint > with > the on drop cascade . > > http://archives.postgresql.org/pgsql-general/2006-10/msg00467.php > > Regards, > > Richard Broersma Jr. > > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Add calculated fields from one table to other table
> I have two tables. Tick table has fields like ticker, time, price & volume
> and Timeseries
> table has fields like ticker, time, avg_price, avg_volume.
>
> The time field in Timeseries table is different from time in tick table,
> its the timeseries
> for every minute. Now I want to calculate the average price & volume from
> tick table for each
> ticker and for every minute and add those fields to timeseries table. Can
> anyone please help me
> out with the sql query.
>
> Note: The ticker in the tick table also has duplicate values, so i am not
> able to create
> relation between two tables.
Here is my guess how it can be done:
insert into Timeseries ( tiker, time, avg_price, avg_volume ) select ...
where select would be
selecttick,
date_trunc('minute', time) as minute,
avg(price) as avg_price,
avg(volume) as avg_volume
from ticker
where time between 'yourstartdate' and 'yourenddate'
group by tick, minute;
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to query information schema from shell script
> In my original email i forgot to mentioned i need to know if the > database exists or not from the shell script. If it doesn't exist i > would then create it. Currently i was just creating the db everytime > our db script is run (since it doesn't hurt the db) but this generates > the 'db already exists' log and I now have the requirement that the > users not see that log. ie, don't run createdb if it already exists. > > In looking through the postgres docs i can see how to check if a table > exists but not how a db exists. > > Again, thanks for the help. psql -l Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Case Preservation disregarding case sensitivity?
> Case was preserved. Now lets add the foreign key just as we did before (note
> that the case in the table definition and the ALTER TABLE query is the same):
>
> ALTER TABLE user_profile ADD CONSTRAINT fk_uproftype FOREIGN KEY
> (userProfileTypeId) REFERENCES user_profile_type (userProfileTypeId);
> ^^
> ERROR: column "userprofiletypeid" referenced in foreign key constraint does
> not exist
When ever you defince a column with quotes, all references to it must also
contain quotes. Try:
ALTER TABLE user_profile ADD CONSTRAINT fk_uproftype FOREIGN KEY
("userProfileTypeId") REFERENCES user_profile_type ("userProfileTypeId");
> OK, another query (perfectly valid SQL):
>
> insert into user_profile_type
> (userProfileTypeId,userProfileType) VALUES(1,'ABNORMAL');
> ^ ^^^
> ERROR: column "userprofiletypeid" of relation "user_profile_type" does not
> exist
Try:
insert into user_profile_type
("userProfileTypeId","userProfileType") VALUES(1,'ABNORMAL');
>
> I am hoping that there is an easy way to obtain case-preservation with
> case-insensitivity, or at the very least, case-preservation and complete
> case-sensitivity, or case-preservation and a consistant case-conversion
> strategy.
>
> Again, I am looking for a way (magic, patches, whiskey, etc) that will give
> me
> case-preservation with EITHER case-sensitivity OR case-insensitivity, but not
> both as I am seeing.
Perhaps in your queries or views you use the AS keywork to respecify the column
name with
upper/lower cases.
i.e.
mydb=# select id as "Id" from foo;
Id
-
goodbye
(1 row)
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Case Preservation disregarding case sensitivity?
> Again, I am looking for a way (magic, patches, whiskey, etc) that will give > me > case-preservation with EITHER case-sensitivity OR case-insensitivity, but not > both as I am seeing. > > Thanks in advance. I am hoping to find a solution to this so I can actually > convert one of our databases to use Postgres. And I can say that little > issues like this are precisely why Postgres was never used in this > organization before, even though several of the other database developers > like the features, stability and performance of Postgres. Here is an explination from the postgresql manual: http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS ... Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.) ... Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Add calculated fields from one table to other table
> Thanks a lot for your help. The query does work, but now I have a problem. > The query goes like > this: > > select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price), > sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) > from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and > tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by > tm.timeseries_time, > tk.ric order by tk.ric, tm.timeseries_time > > The problem is, if there is no row for certain minute, then I want the > count to be displayed > as zero and other coulmns like avg to be null. In this query, it just omits > those minutes which > doesnt have any row for a particular minute. You have to use an outer join. You will need a table or sequence that has every minute in a range that you are interested in and outer join that to your actual table. This will give you a count of zero. i.e. select S.minute, count(W.minute) as minutecnt from Series_of_Minutes S left join Working_table W on S.minute = W.minute ; hope this helps. REgards, Richard Broersma jr. ps. sorry that my query suggestion didn't work :0) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Add calculated fields from one table to other table
> select foo.ric, tm.times_time, count(tk.*), avg(tk.price), > sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select distinct > ric from ticks) as > foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and > tk.tick_time < > (tm.times_time + '1 minute' :: interval)::time and tk.ric = foo.ric group by > tm.times_time, > foo.ric order by tm.times_time; > > I get a error message like this: > > ERROR: invalid reference to FROM-clause entry for table "foo" > HINT: There is an entry for table "foo", but it cannot be referenced from > this part of the > query. > > Can you help me with this? I will try, but to start with, to help us, when you have a difficult query to solve, you should simplify your query as much a possible. This way we can more quickly see what you are intending verses the problem you are having. 1 tip: (select distinct ric from ticks) I think that you will find that: (select ric from ticks group by ric) is much faster than using the distinct. The error in the query that I see is that you are using foo as a criteria in the ON syntax. This will not work. To illistrate: A,B join C ON (B.id = C.id) --ON syntax only works with joins AND (B.id2 < C.id) --The And is still part of the ON syntax --you can not reference A since it is not joined Where A.id = B.id --you can only specify a non-joined tables contrainst AND A.id2 < C.id2 ; --in the where clause I hope this helps. Regards, Richard Broersma JR. ---(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
Re: [SQL] Add calculated fields from one table to other table
> Thanks for your help. That does make sense, but I am not able to get the
> result what I wanted
> exactly. Let me explain you.
>
> I have ticks table in which I have columns like ric, tick_time, price &
> volume. The times
> table has just one column with times_time which has time data for each minute
> ie.)
>
> Ticks
> ric | tick_time | price | volume
> A | 12:00:01 | 23.00 | 12
> A | 12:00:02 | 26.00 | 7
> B | 12: 00:02 | 8.00 | 2
> B | 12:01:01 | 45.00 | 6
>
> Times
> times_time
> 12:00
> 12:01
> 12:02
>
> Now I want the timeseries for each minute for all ric in the tick table. So
> my query goes like
> this for a particular ric say for example ric 'A'
>
> select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum
> (tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select ric from
> ticks where ric = 'A'
> group by ric) as foo, times tm left join ticks tk on tk.tick_time >=
> tm.times_time and
> tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and tk.ric =
> 'A' group by
> tm.times_time, foo.ric order by tm.times_time;
>
> I get the result as I expect, but i am not able to derive a query for all
> rics in the tick
> table.
>
How about:
SELECT
foo.ric,
date_trunc('minute', tm.times_time) as minute,
count(tk.*),
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Add calculated fields from one table to other table
> Hi Richard,
>
> Thanks a lot. I still am not able to get the result for all the rics in the
> ticks table but I
> am able to get the result for a particular ric.
>
> Can you help me with getting the result for all the rics in the ticks table
>
> Thanks
> Roopa
Could you send create table statements for the tables you are working on, and a
few insert
statements for each table to have sample data. then show what you want the
query results to look
like.
But from what you stated in your previous emails here is what I gather: maybe
it might work?
selecttk.ric as ric,
tm.times_time as minute, --timestamps by minutes
count(tk.*) as ,
...
from times tm
left join ticks tk
on (tm.times_time = date_trunc('minutes', tk.time))
group by ric, minute
order by minute;
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Add calculated fields from one table to other table
> I am sending you the create statement of tables & few insert statements as
> well. Hope this
> helps to solve the problem.
where are the insert statements? ;)
>
> CREATE TABLE ticks
> (
> tick_id int8 NOT NULL DEFAULT nextval(('ticks_s'::text)::regclass),
> ric varchar(30) NOT NULL,
> tick_date date NOT NULL,
> tick_time time NOT NULL,
> price float8,
> volume int4,
> CONSTRAINT ticks_pkey PRIMARY KEY (tick_id),
> )
> WITHOUT OIDS;
>
> CREATE TABLE times
> (
> times_time time NOT NULL,
> count int4,
> CONSTRAINT times_pkey PRIMARY KEY (times_time)
> )
>
> selct statement of ticks table
>ric | tick_date | tick_time | price
> -++--+---
> A | 2006-04-04 | 00:00:55.023 | 4.05
> AA | 2006-04-04 | 00:00:55.023 | 9.05
> A | 2006-04-04 | 00:00:59.023 | 6.05
> A | 2006-04-04 | 00:01:00.023 | 5.05
> ABC | 2006-04-04 | 00:01:00.509 |12.00
> ABI | 2006-04-04 | 00:01:03.511 |13.00
> AA | 2006-04-04 | 00:01:08.023 | 6.05
> ABT | 2006-04-04 | 00:01:08.518 | 3.06
> ABT | 2006-04-04 | 00:01:09.518 | 7.06
>
> select statement of times table
> times_time
> ---
> 00:00:00
> 00:01:00
> 00:02:00
>
>
> I want the query result to look
> ric | times_time | count | avg_price
> ++---+---
> A | 00:00:00 | 2 | 5.05
> AA | 00:00:00 | 1 | 9.05
> ABC | 00:00:00 | 0 |
> ABI | 00:00:00 | 0 |
> ABT | 00:00:00 | 0 |
> A | 00:01:00 | 1 | 5.05
> AA | 00:01:00 | 1 | 6.05
> ABC | 00:01:00 | 1 |12.00
> ABI | 00:01:00 | 1 |13.00
> ABT | 00:01:00 | 2 | 5.06
Here is what I got:
ric | minute | count |avg_price
-+--+---+--
ABC | 00:00:00 | 0 |0
ABT | 00:00:00 | 0 |0
AA | 00:00:00 | 2 | 9.05
ABI | 00:00:00 | 0 |0
A | 00:00:00 | 6 | 5.05
A | 00:01:00 | 3 | 5.05
ABI | 00:01:00 | 1 | 13
AA | 00:01:00 | 2 | 6.05
ABT | 00:01:00 | 9 | 5.726667
ABC | 00:01:00 | 1 | 12
A | 00:02:00 | 0 |0
AA | 00:02:00 | 0 |0
ABI | 00:02:00 | 0 |0
ABC | 00:02:00 | 0 |0
ABT | 00:02:00 | 0 |0
(15 rows)
And here is how I got it:
SELECT
A.ric,
A.minute,
count(B.*) as count,
COALESCE(avg(B.price),0) as avg_price
FROM
(
SELECT T.ric,
M.times_time as minute
FROM
ticks T
CROSS JOIN
times M
WHERE
M.times_time
BETWEEN
'00:00:00'
AND
'00:03:00'
) A
LEFT JOIN
ticks B
ON
A.ric = B.ric
AND
A.minute = date_trunc('minute', B.tick_time)
GROUP BY
A.ric,
A.minute
ORDER BY
A.minute
;
Hope this is what you were looking for. This is the first time I've ever had
to employ a cross
join get what I wanted. Just realize that this query will explode with a very
large number to
records returned as the times table grows. You should expect a quantity of
results like (total
ticks * total times)
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
