[SQL] How to set autocommit on/off
As titled, is autocommit of Postgresql is default to be "ON", how to turn it off then? Thnaks, Kevin -- - Kevin LAM, System Analyst Crown Development Ltd. A Shun Tak Group Company Tel: (852) 2283-2132 Fax:(852) 2283-2727 -
Re: [SQL] SQL help (Informix outer to EnterpriseDB outer)
I could probably work this out for you but I have no time today. However, as a 'plan b' maybe try this... 1- create a temp table based on all tables & conditions in the query except for the outer table (i.e. user, ascpDef, address, invention, and user) 2- do an outer join on the above temp table and the inv_contracts table (inv_contracts as the outer) > Hi all, > I have been working on converting our Informix DB to PostgreSQL. There are > some differences with SQL syntax. > > I have done many outer conversion so far, but all has either one outer or > simple > one. But this one I do not know how to do it. I have searched but could not > find > similar to what I need. > > This is the one works on InformixDB (OUTER inv_contracts ) connects to > three different tables (1. inv_contracts.inv_id = invention.id AND > 2. inv_contracts.con_id = con.id AND > 3. inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id > = 2)) > > -informix outer --- > select count(user.id) > FROM user, ascpDef AS stateDef, address, invention, user as con > , OUTER inv_contracts > WHERE > address.type = 'User' AND > address_id = 1 AND > user.id = address.type_id AND > state_id = stateDef.id AND > invention.user_id = user.id AND > invention.inv_number = '1994376-A' AND > inv_contracts.inv_id = invention.id AND > inv_contracts.con_id = con.id AND > inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = > 2); -- > > If there were only one table connection (say 1. inv_contracts.inv_id = > invention.id AND ) > I would have done it as > (,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = > invention.id) > > but I can do same or similar conversion for 2. and 3. > > I have attempted to do as below but not giving correct count. > > > postgres-- > select count(dbuser.id) > FROM dbuser, ascpDef AS stateDef, address > --, invention > --, dbuser as con > --, OUTER inv_contracts > --1 > --,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = > invention.id > --,dbuser as con LEFT OUTER JOIN inv_contracts ON inv_contracts.con_id = > con.id --,contractDef LEFT OUTER JOIN inv_contracts ON > inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = > 2) > --1 gives ERROR: table name "inv_contracts" specified more than once > -- > --2 > --,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = > invention.id > --,dbuser as con LEFT OUTER JOIN inv_contracts as inv2 ON inv2.con_id = > con.id --,contractDef LEFT OUTER JOIN inv_contracts as inv3 ON > inv3.contract_id IN (select id FROM contractDef WHERE phase_id = 2) > --2 takes very long (cancelled/stoped by me) (informix returns 306229 > within 10sec) > --Cancel request sent > --ERROR: canceling statement due to user request > -- > --3 > --,inv_contracts LEFT OUTER JOIN invention ON inv_contracts.inv_id = > invention.id > --,inv_contracts as inv2 LEFT OUTER JOIN dbuser as con ON inv2.con_id = > con.id --,inv_contracts as inv3 LEFT OUTER JOIN contractDef ON > inv3.contract_id IN (select id FROM contractDef WHERE phase_id = 2) > --3 takes very long (cancelled/stoped by me) (informix returns 306229 > within 10sec) > --Cancel request sent > --ERROR: canceling statement due to user request > -- > --4 > ,inv_contracts LEFT OUTER JOIN invention ON inv_contracts.inv_id = > invention.id LEFT OUTER JOIN dbuser as con ON inv_contracts.con_id = con.id > LEFT OUTER JOIN contractDef ON inv_contracts.contract_id IN (select id FROM > contractDef WHERE phase_id = 2) > --4 returns 1 but (informix returns 306229 within 10sec) > WHERE > address.type = 'User' AND > address_id = 1 AND > dbuser.id = address.type_id AND > state_id = stateDef.id AND > invention.user_id = dbuser.id AND > invention.inv_number = '1994376-A'; > --- > > Thanks for help. > > - > This mail sent through IMP: www.resolution.com > > ---(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 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] Speed of SQL statements
Does anyone have any performance numbers regarding SQL statements, specifically SELECT, UPDATE, DELETE, and INSERT? For instance, on average how long does a typical SELECT (UPDATE, DELETE, INSERT) statement take to execute? Thank you, Kevin
[SQL] Estimation of SQL statements
Are there any good techniques that estimate the time it will take to execute an SQL statement, specifically an INSERT, SELECT, UPDATE, or DELETE? What factors are important to consider when estimating the execution time of these types of SQL statements? Thank you, Kevin
[SQL] SELECT DISTINCT problems
Hi there,
I'm having a lot of trouble with one sql statement and I wonder can you
help.
I My problem is the following...there are two field name in the the table
named LecturerName and Projectcode. Each ProjectCode vulue is unique within
the table but there can be many entries in the table with the same
LecturerName.
I would like to pull each distinct value of LecturerName with any one
corresponding ProjectCode value, it does not matter what the ProjectCode
value is, but all attemps at this have failed so far.
I have tried this but it does not work:
Set rsLecturers = Server.CreateObject("ADODB.Recordset")
sqlLect = "Select LecturerName, MIN(ProjectCode) from tblSuggestions WHERE
LecturerName IN ( SELECT DISTINCT LecturerName FROM tblSuggestions)"
rsLecturers.Open sqlLect, Conn, 3, 3
I get this error when I try to run this:
[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that
does not include the specified expression 'LecturerName' as part of an
aggregate function.
I am trying to put the results of this query into a recordset and I am using
an accessdatabase
Thanks in advance,
Kevin.
[SQL] table inheritance and foreign key troubles
I'm having a little trouble with some inherited tables and a foreign key. Here's a simplified case, to show the trouble. CREATE TABLE node ( node_id SERIAL NOT NULL, nameTEXT NOT NULL, PRIMARY KEY (node_id) ); -- works just fine CREATE TABLE users ( email TEXT NOT NULL ) INHERITS (node); -- so far so good CREATE TABLE item ( reason TEXT NOT NULL, author_id INT NOT NULL REFERENCES users (node_id) ) INHERITS (node); ERROR: UNIQUE constraint matching given keys for referenced table "users" not found Does this operation just require differing syntax, because the referenced field is inherited from another table, or is this not possible? Kevin Way ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] trigger trouble -- procedure not found
I'm having trouble creating a trigger. First i'm creating a function, add_to_search in PL/pgSQL. \df verifies the existance of this function: smallint | add_to_search | text, text, text, integer but when I call: CREATE TRIGGER item_insert_search_add AFTER INSERT ON item FOR EACH ROW EXECUTE PROCEDURE add_to_search (name, description, reason, node_id); I get: ERROR: CreateTrigger: function add_to_search() does not exist What am I missing here? It seems to me that I'm missing something very simple, but I can't figure out what it is for the life of me. Kevin Way ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] trigger trouble -- procedure not found
Thank you. Later checking showed that these requirements were listed in the first sentence of the relevant page. Everything works like a champ now. I've made a small donation to the EFF and to the Red Cross as a minor thanks for your prompt help. Kevin Way ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Bug?: Update on ancestor for a row of a child
> And you think that Oracle is entirely free of bugs? ;-) Yes, but they'd be exciting technology-oriented e-business enabled bugs! > Still, I understand your frustration. Thanks... It's just frustrating that the bug is on something so basic, which makes it both hard to code around and hard for me to delve into the postgres source and fix. I spent a few hours tracing source before finally conceding the point that it takes more than a few hours to understand postgres internals well enough to fix a major bug. For development purposes I've just removed all the CHECK constraints from my child tables, and I'm hoping some genius will solve the problem by the time I'm looking to deploy. -Kevin Way ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] aggregate functions, COUNT
* Tom Lane <[EMAIL PROTECTED]> [02-10-01 18:02]: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > I think you could use EXISTS for that, > > select EXISTS (); should give a true/false on whether the > > query returned any rows. I'm not sure if it stops after one row > > or not, but if it doesn't you can add a limit 1 to the query. > > Yes it does stop after one row; and furthermore, the planner knows to > generate a fast-start plan for it. (Or at least it's supposed to > ... hmm, this seems to be broken in current sources ...) Anyway, > there's no need for LIMIT 1 inside an EXISTS, because the planner > assumes that automatically. Thank you muchly, I did some profiling and SELECT EXISTS is indeed exactly what I wanted. -Kevin Way -- Kevin Way <[EMAIL PROTECTED]> http://www.overtone.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Server crash caused by CHECK on child
> -- I don't think that I only found a minor bug compared to > -- the other you wrote in your last letter: the backend crash > -- is caused by the same CHECK constraint in the child table. Oooh, my bad. I should run your scripts before assuming I know how they fail. > -- However, for you without time to analyzing Kevin's huge > -- scheme, here is the very simplified, crash-causing script. Thank you so much for finding this simplified method of crashing Postgres. Hopefully somebody can find a fix now. > -- I am hunting it, but I have to learn all what this query-executing > -- about, so probably it takes uncomparable longer for me than for > -- a developer. That's my problem as well, though your example is vastly easier to trace than mine. -Kevin Way ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] confounding, incorrect constraint error
--
-- vote totalling rules
-- vote insertion
CREATE RULE itemvote_insert_item_inc AS
ON INSERT TO itemvote DO
SELECT mod_node_vote_count(NEW.target_id, NEW.nays, 1);
CREATE RULE uservote_insert_item_inc AS
ON INSERT TO uservote DO
SELECT mod_node_vote_count(NEW.target_id, NEW.nays, 1);
-- vote deletion
CREATE RULE itemvote_delete_item_dec AS
ON DELETE TO itemvote DO
SELECT mod_node_vote_count(OLD.target_id, OLD.nays, 0);
CREATE RULE uservote_delete_item_dec AS
ON DELETE TO uservote DO
SELECT mod_node_vote_count(OLD.target_id, OLD.nays, 0);
-- vote updates
CREATE RULE itemvote_update_item_mod AS
ON UPDATE TO itemvote WHERE OLD.nays != NEW.nays DO
(SELECT mod_node_vote_count(OLD.target_id, OLD.nays, 1);
SELECT mod_node_vote_count(NEW.target_id, NEW.nays, 0););
CREATE RULE uservote_update_item_mod AS
ON UPDATE TO uservote WHERE OLD.nays != NEW.nays DO
(SELECT mod_node_vote_count(OLD.target_id, OLD.nays, 1);
SELECT mod_node_vote_count(NEW.target_id, NEW.nays, 0););
-- users
INSERT INTO users (name, pass_hash, realname, email) VALUES ('mosch',
'dafe001b7733b0f3236aa95e00f8ed88', 'Kevin', '[EMAIL PROTECTED]');
INSERT INTO users (name, pass_hash, realname, email) VALUES ('Wakko',
'c6ef90fcf92bf703c3cc79a679c192a3', 'Alex', '[EMAIL PROTECTED]');
-- items
INSERT INTO item (name, creator_id, reason) VALUES ('slashdot.org', 2, 'Because it\'s
a pile of turd.');
INSERT INTO item (name, creator_id, reason) VALUES ('Yahoo!', 2, 'Because it\'s
ugly.');
INSERT INTO item (name, creator_id, reason) VALUES ('memepool', 1, 'Because it\'s phat
phat phat phat phat.');
INSERT INTO item (name, creator_id, reason) VALUES ('blow!!??!!', 1, 'this record nays
nays nays');
-- item votes
INSERT INTO itemvote (target_id, user_id, nays) VALUES (3, 1, 1);
INSERT INTO itemvote (target_id, user_id, nays) VALUES (4, 1, 0);
INSERT INTO itemvote (target_id, user_id, nays) VALUES (5, 2, 1);
-- user votes
INSERT INTO uservote (target_id, user_id, nays) VALUES (2, 1, 0);
INSERT INTO uservote (target_id, user_id, nays) VALUES (1, 2, 1);
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] confounding, incorrect constraint error
> > Below is a significantly simplified version of my schema, which > > exhibits > > the above problem. > > Unfortunately, even a simplified version of your schema would take me > some hours to understand. As your rule-setting is quite complex, my > first instinct would be to hunt for circular procedural logic in your > rules. Try to pursue, step by step, everything that happens from the > moment you send the insert command to uservotes. You may find that the > logic cascades back to the beginning. I've done this to myself on > occasion, causing the DB to hang on a seemingly simple request. I'm fairly certain that there's no circular procedural logic. The errors can be turned on/off by turning on/off the uservote_ series of rules, which are attached to the uservote table. These rules call mod_node_vote_count which only touches the node table. There are no rules or triggers associated with the node table, so there is no circular logic there. Additional strangeness is that the itemvote_ series of rules works perfectly despite the fact that the only difference between uservote_ and itemvote_ rules is the table that triggers them, they both call the same procedure on the nodes table. My current thinking is that something is stomping on some memory, because you can vary the effect of the error from being an incorrectly failed CHECK constraint, to crashing the database, by varying the number of columns in the tables in question. I'm unemployed at the moment and this is a pet project, so I can't offer much in the way of financial compensation, but I'll start the bidding at $50 donation in your name to your choice of the EFF, the Red Cross, or the American Cancer Society, in return for a fix. (If none of these charities are acceptable, surely one can be found later that is acceptable to both parties). Again, I greatly appreciate any help, and I apologize that my test case is still fairly sizeable, despite being about 10% the size of the original code. -Kevin Way msg06097/pgp0.pgp Description: PGP signature ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Bug?: Update on ancestor for a row of a child
> The problem is: when updating a row in an ancestor table, > which is really belongs to a child, there's something wrong > with the CHECK system. Well, I believe you found one minor problem. The bigger one is still lurking in the shadows though. To duplicate it, take my previous schema, and add lastlog TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, to the users table, between pass_hash and logged_in. After doing so, you'll find that postgres actually crashes when you try to insert a vote into the uservote table. That's the one that has me looking at the costs involved with migrating to Oracle. -Kevin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] aggregate functions, COUNT
I'm currently using a SELECT count(*) when all I really want to know is if 1 or more records exist. Is there a standard way to just find out if a record exists? If not, is there a way to avoid iterating over all the records by writing an aggregate function? Given what I've read of how they work, I don't see how to make the function return before parsing all the results anyway, am I wrong here? Thanks for any help, or 2x4s, Kevin Way -- Kevin Way <[EMAIL PROTECTED]> http://www.overtone.org/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] is it easy to change the create sequence algorithm?
I see in the docs that when I create a column that is of type SERIAL, the engine automatically creates the sequence for me, named TABLE_COLUMN_seq. That's great until the table name + column name lengths are > 27 chars, then it starts chopping, and you guessed it, I have multiple table/column combinations that don't differ until after that length. Is there a way to influence the "create sequence" generator with a directive, hint, set value, whatever, to be something else? (e.g. COLUMN_seq if I guarantee all the columns are unique) Yes I know that I could create the sequence myself, but the engine does such a good job. :-) Thanks, Kevin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] bit field changes in 7.2.1
I'm on a Linux RH 7.2 system, which came with Pg 7.1.2 (I think). When
there, I prototyped some code that worked well, and looked like:
create table ref_sp
(
name varchar(10),
sname char(1),
bitmask bit(6)
);
insert into ref_sp values ('one', '1', b'01');
insert into ref_sp values ('two', '2', b'10');
insert into ref_sp values ('four', '4', b'000100');
insert into ref_sp values ('eight', '8', b'001000');
insert into ref_sp values ('sixteen', 's', b'01');
insert into ref_sp values ('thirtytwo', 't', b'10');
create table emp
(
id int,
name varchar(30),
specialties bit(6)
);
insert into emp values (1, 'mary_124', b'000111');
insert into emp values (2, 'joe_14st', b'110101');
Which allowed me to find out who had what bit (specialty) set with:
select sp.name
from emp s, ref_sp sp
where s.specialties & sp.bitmask != b'0'::bit(6)
and s.name = 'joe_14st' ;
EXCEPT that now fails in 7.2.1 (I just upgraded this afternoon). It
forces me to use "b'00'" instead of "b'0'::bit(6)". Searching thru
the docs, I find a note that says:
---
Note: Prior to PostgreSQL 7.2, BIT type data was zero-padded on the
right. This was changed to comply with the SQL standard. To implement
zero-padded bit strings, a combination of the concatenation operator and
the substring function can be used.
---
Obviously the source of my problem. However, whoever wrote that note
didn't say how to do it (examples are *SO* useful), and I can't imagine
the solution.
* Can anyone clue me into how I can do that, in SELECT / INSERT / UPDATE
statements?
* Or must I resort to doing the equivalent in Perl as I create the SQL?
* Or is there a backward-compatibility flag?
Thanks!
Kevin
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] bit field changes in 7.2.1
Peter Eisentraut wrote:
> Kevin Brannen writes:
>
>
>>EXCEPT that now fails in 7.2.1 (I just upgraded this afternoon). It
>>forces me to use "b'00'" instead of "b'0'::bit(6)".
>
>
> Which is a problem why?
Because in the real system, it will be b'0'::bit(64) or b'001::bit(64)
or etc. A bit nasty to type isn't it. :-) And of course that's for 1
table, in another place it be 40 bits wide, and in yet a third 96 bits
wide. So a simple mechanism to make it the correct size (for
comparisons) is very useful (to maintain my sanity. :-)
...
>
>
> In your case the solution is to type the six zeroes.
>
> The comment referred to cases where the results of computations needed to
> be forced to the right length, in which case you could use something like
>
> substring(computation() || b'00' for 6)
>
> The question whether the constant should go before or after the
> computation, and whether it should be zeros or ones is a matter of taste,
> which is why an example has been omitted.
>
I'm still not sure I see the problem, but that doesn't really matter.
If that's the explanation, then *that* should have been included with
the note, IMO. OTOH, I still don't see where that helps me. How does
it give me a string of bits like b'0'::64 does, or b'1'::64 does? And
something like:
select substring(1 || b'00' for 6);
assuming some computation returns a "1", tells me:
ERROR: Unable to identify an operator '||' for types 'integer' and
'bit' You will have to retype this query using an explicit cast
In an email, Chris suggested using rpad(), which looks good but doesn't
quite work, unless I'm overlooking something (if so, please point it
out!). When I try it in my query I get:
select ... from ...
where sp.bitmask != cast(rpad('0',6,'0') as bit(6));
ERROR: Cannot cast type 'text' to 'bit'
I guess my issue is that when some feature is taken out, there needs to
be another way to do it, AND it needs to be documented. Or just leave
it in and flag it as an extension.
;-)
I guess I'll just have to read the bits in from the DB reference table,
then store and manipulate them all in Perl...sigh...
Thanks for the effort though!
Kevin
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] is there a way to get hh:mm:ss given seconds
Tod McQuillin wrote: > On Mon, 8 Jul 2002, Narendra A wrote: > > >>Is there a way in sql such that if I give seconds it should me return me >>hours:mins:seconds >> >>Eg. Seconds hh:mm:ss >>422 1:01:02 > > > foo=# select '422 seconds'::interval; > interval > -- > 00:07:02 > (1 row) That works on inserts into a "time" field too, cool tip. THANKS! Kevin ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
datatype matrix (was: Re: [SQL] Sorry..)
Josh Berkus wrote: > Christopher, > > >>In the bad old days when we couldn't distinguish explicit from implicit >>cast functions, I was wary of adding new cast pathways. Too many >>implicit casts and you have no type system at all. But in 7.3 there >>should be no reason to object to an explicit-only cast from numeric >>to text or vice versa. > > > I'd suggest making the explicit cast of numeric to text be the exact > equivalent of: > > SELECT btrim(to_char(numeric, '999,999,999,999.99')) > or similar. > In this vain, is there someplace in the docs that has a type conversion table (matrix) that shows what datatype can be cast into what other datatype (both implicitly and explicitly)? I haven't seen one and it would be helpful for us newbies. Thanks! Kevin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Retrieving the new "nextval" for primary keys....
Greg Patnude wrote:
> I am using postgreSQL with Perl::CGI and Perl::DBI::Pg... I would like to be
> able to insert a row from my Perl script [$SQL->exec();] and have postgreSQL
> return the id of the newly inserted record (new.id) directly to the Perl
> script for further processing... Anyone with a solution / idea ???
>
> Nearly EVERY table I create in postgreSQL (7.2) has the following minimum
> structure:
>
> create table "tblName" (
>
> id int4 primary key nextval ("tblName_id_seq"),
>
> ..field...
> )
You can either do it in 2 statements, something like:
$dbh->do("insert into tblName ...");
my ($id) = $dbh->selectrow_array("select currval('tblName_id_seq')");
Or you could create a function which takes the insert statement, and
ends with doing a select on the currval (as above) and returning that.
As I do the 2 statement approach above, I haven't done a function, but
it doesn't look like it would be that hard to do.
HTH,
Kevin
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Changing Column Type
Peter Atkins wrote: > All, > > Is there a way to easily change the type of column? Or do I have to drop > and create again. > > From: > assignment_notes | character varying(255) > > To: > assignment_notes | text Do that kind of change will require creating a new table, copying the data, dropping the old table, renaming the new to the old. If you were changing from varchar(20) to varchar(255), you could hack the system tables and be OK (or so I've read). Going smaller is possible too, as long as your 110% sure you don't have any data bigger then the new smaller size (or you're taking your life into your own hands so to speak. :-) HTH, Kevin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Dublicates pairs in a table.
Ries van Twisk wrote:
> I have a small question which I could not clearly find in the postgreSQL
> manual.
>
> if I create this table and index
> CRAEATE TABLE test (
> id SERIAL,
> c1 VARCHAR(32),
> c2 VARCHAR(32),
> c3 VARCHAR(32)
> );
>
> CREATE UNIQUE INDEX test_idx ON test(id, c1,c2);
>
> what I try to archive here is that I don't want duplicate pais in my table:
> example
>
> INSET INTO test (c1,c2) VALUES('a', 'a'); -- Allowed
> INSET INTO test (c1,c2) VALUES('b', 'b'); -- Allowed
> INSET INTO test (c1,c2) VALUES('b', 'c'); -- Allowed
> INSET INTO test (c1,c2) VALUES('a', 'a'); -- Not allowed since we already
> have a duplicate ('a', 'a') pair
> INSET INTO test (c1,c2) VALUES('b', 'c'); -- Not allowed since we already
> have a duplicate ('b', 'c') pair
>
> etc. etc. I think you get the idea...
I have the same issue with a table that currently holds well
over 600,000 rows. The case you left out is this:
INSERT INTO test (c1,c2) VALUES('a','c');
INSERT INTO test (c1,c2) VALUES('c','a');
I want that to fail, but I haven't been able to get it to fail
using unique indexes. I presume ordering is significant. Instead,
I am doing a SELECT prior to insert to insure the pair doesn't
already exist. If you've been able to get order-independent
pairs restricted to being unique using indexes, I'd like to know
about it. :-)
Kevin
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Help tuning query
All; Can anyone please help with the tuning of this query? With 77000 rows in the operator_messages database the query is taking almost 15 seconds to return. Preference woul dbe under 5 seconds if possible. System load on a dual processor P3 with 1.5GB of memory remains under .4 during the query. The query and explain are noted below as well as description of the tables; Note both ANALYZE and VACUUM have been run numerous times. any help would be appreciated. -Kev virgin=# explain SELECT opr.msg_id, opr.status, opc.std_time virgin-# FROM op_msg_folder opc, operator_messages opr virgin-# WHERE opr.username = 'khp' virgin-# AND opr.foldername = 'inbox' virgin-# and opr.msg_id = opc.msg_id; NOTICE: QUERY PLAN: Merge Join (cost=25037.29..27675.47 rows=47958 width=54) -> Index Scan using opmf_i on op_msg_folder opc (cost=0.00..1797.37 rows=48579 width=32) -> Sort (cost=25037.29..25037.29 rows=47958 width=22) -> Seq Scan on operator_messages opr (cost=0.00..20722.26 rows=47958 width=22) virgin=# \d operator_messages Table "operator_messages" Column | Type | Modifiers +--+--- msg_id | numeric | username | text | foldername | text | status | character(1) | Indexes: op_msgs_i, opr_msgs_foldername_i, opr_msgs_username_i virgin=# \d op_msgs_i Index "op_msgs_i" Column | Type +- msg_id | numeric btree virgin=# \d opr_msgs_foldername_i Index "opr_msgs_foldername_i" Column | Type +-- foldername | text btree virgin=# \d opr_msgs_username_i Index "opr_msgs_username_i" Column | Type --+-- username | text btree virgin=# \d op_msg_folder Table "op_msg_folder" Column | Type | Modifiers +--+--- msg_id | numeric | status | character(1) | std_time | text | julian_time| text | smi| character(3) | description| text | type | text | flight | text | tail | text | dep_station| text | dest_station | text | op_description | text | Unique keys: opmf_i virgin=# \d opmf_i; Index "opmf_i" Column | Type +- msg_id | numeric unique btree ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Case Sensitive "WHERE" Clauses?
Jordan Reiter wrote: > Are string comparisons in postgresql case sensitive? > > I keep on having this response: > > SELECT * > FROM People > WHERE first_name='jordan' > > Result: 0 records > > SELECT * > FROM People > WHERE first_name='Jordan' > > Result: 1 record It's case-sensitive. You can do this: SELECT * FROM People WHERE lower(first_name) = 'jordon' Kevin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Can Postgres cache a table in memory?
Hello all; I'm trying to speed up a query which returns the majority of a table so and index isn't helpful. I've got more than enough RAM to hold my table so, can anyone tell me if there is there a way to force Postgres to cache a table in RAM? Any help would be appreciated. Thanks; -Kevin Traub ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Database Design tool
Dan, I use Xpga Java Postgresql client and it is awesome. It's at http://www.kazak.ws/xpg/ Kevin On Wed, 2002-10-30 at 10:30, Dan Hrabarchuk wrote: > gASQL is a gnome-db client that looks like it has a lot of promise. The > only problem is I've never been able to get the application to run > properly. I'm using RedHat 8.0 on my desktop. The last official version > does not install properly. If I grab a CVS copy, I go through dependency > hell. Has anyone ever actually gotten gASQL to work? > > Dan > > On Wed, 2002-10-30 at 06:35, Johannes Lochmann wrote: > > On Wed, 2002-10-30 at 07:45, Viacheslav N Tararin wrote: > > > > Hi, > > > > (which list should this go to? I guess it is OT on both...) > > > > > Can anybody take me reference on Database design tool with PostgreSQL > > > support. > > > > Dia and dia2sql (or something similar...) Google knows more :-) > > > > HTH > > > > Johannes Lochmann > > > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Kevin Old <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Subtracting time fields
Hello all, I have two fields in my database access_time and release_time.I would like to calculate the "duration" between these two values, but can't figure out what to do. I've tried something like this but it doesn't work: select access_time, release_time, time(access_time) - time(release_time) as duration from mastertbl limit 10 Any suggestions? Thanks, Kevin -- Kevin Old <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQL to determine Tablenames, Primarykeys & Foreignkeys]
Thanks to previous help I am using: $sql = "SELECT " . "ic.relname AS index_name, " . "bc.relname AS tab_name, " . "ta.attname AS column_name, " . "i.indisunique AS unique_key, " . "i.indisprimary AS primary_key " . "FROM " . "pg_class bc, " . "pg_class ic, " . "pg_index i, " . "pg_attribute ta, " . "pg_attribute ia " . "WHERE " . "bc.oid = i.indrelid " . "AND ic.oid = i.indexrelid " . "AND ia.attrelid = i.indexrelid " . "AND ta.attrelid = bc.oid " . "AND bc.relname = '" . $tablename . "' " . "AND ta.attrelid = i.indrelid " . "AND ta.attnum = i.indkey[ia.attnum-1] " . "ORDER BY " . "index_name, tab_name, column_name"; which provides primary keys 100%. I have written the following to obtain tablenames: $sql = "SELECT " . "ic.relname " . "FROM " . "pg_class ic " . "WHERE " . "ic.relname not like 'pg%' " . "AND ic.relname not like '%pk' " . "AND ic.relname not like '%idx' "; which I am not certain is complete but appears to work. Could anyone help me with the SQL to retrieve Foreign Keys for a particular Table? Much appreciated. Kevin Gordon ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] mergejoin error message executing in 7.2
hi there, I have a problem trying to execute a complex query that was designed in 7.3.2 to a machine running 7.2.x this is the error message FULL JOIN is only supported with mergejoinable join conditions this is the source string for a jdbc prepared statement. (it has ordering clause added at the end using string concatenation) this string works fine in 7.3.2 but fails runtime in 7.2. Does anyone know what the problem is? I can't find the message on the net anywhere except in the 'C' source on postgres's developer channel or some japanese site (sigh). strSQL ="SELECT stock," + " p.description," + " p.stockgroup," + " o.ordercurrent," + " o.type," + " s.quantity," + " a.ordercurrent, " + " a.type " + " FROM " + " (SELECT stock,ordercurrent,type,line " + " FROM orderlines o " + " WHERE o.theorder = ? " + " AND (o.TYPE='P' OR o.TYPE='T') ) AS o" + " FULL OUTER JOIN " + " (SELECT DISTINCT ON (stock) stock,quantity " + " FROM standingorders s " + " WHERE s.account = ? " + " AND s.dayno = ? " + " AND s.delivery = ? "+ " AND commencedate <= ? " + " ORDER BY stock, commencedate DESC) AS s " + " USING (stock) " + " FULL OUTER JOIN " + " (SELECT stock,ordercurrent,type " + " FROM orderlines ol " + " WHERE ol.theorder = ? " + " AND ol.TYPE<>'P' " + " AND ol.TYPE<>'T' ) AS a " + " USING (stock) " + " INNER JOIN stockitems p ON (p.id=stock) " + " WHERE p.status='N' " + " ORDER BY "; Any help greatly appreciated. Regards, Kevin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Unique Constraint Based on Date Range
Andrew Milne wrote: ... create table rates ( effective_date AS timestamp, expiry_date AS timestamp, cost AS numeric (12,2), access_time AS integer (in minutes) ); So for a given cost, there may not be a record where the effective date of one record overlaps the expiry date of another (and vice versa). Example record set (effective date, expiry date, cost, access_time): 2003-01-01 | 2003-01-15 | 5.00 | 60 2003-01-15 | infinity | 5.00 | 120 2003-01-01 | infinity | 1.00 | 10 An attempt to insert another 5.00 rate effective now would fail, because a 5.00 rate exists that doesn't expire (i.e. the expiry date would have to be updated to the effective date of the new record minus 1 second). I can enforce this from the front end, but a db constraint would be great. I don't know that a CHECK constraint would allow you to do this. But, you could create a function to perform the check, and fire a trigger on INSERT or UPDATE to execute the function. For example, something like this might do the trick. CREATE FUNCTION "check_record" () RETURNS TRIGGER AS ' DECLARE result RECORD; BEGIN SELECT INTO result * FROM table_rates WHERE effective_date >= NEW.effective_date AND expiry_date <= NEW.expiry_date AND cost = NEW.cost; IF FOUND THEN RAISE EXCEPTION ''record overlaps with existing record''; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER "tg_check_record" BEFORE INSERT OR UPDATE ON table_rates FOR EACH ROW EXECUTE PROCEDURE "check_record" (); Kevin ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Reg: Firing Trigger when a particular column value get changed
Thilak babu wrote: I have a scnerio as to fire a trigger when i update a particular column in a table. Please do help me out in getting thro this. The trigger function can use logic to exclude cases where a particular column does not change. For example: CREATE FUNCTION "column_update" () RETURNS TRIGGER AS ' BEGIN IF ( NEW.column <> OLD.column ) THEN do-your-stuff-here; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER "tg_column_update" BEFORE UPDATE ON "table_name" FOR EACH ROW EXECUTE PROCEDURE "column_update" (); The trigger fires on every update, but the procedure doesn't do anything unless the particular column changes. I don't think a trigger can be defined to fire on anything more granular than a table operation. Kevin ---(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
Re: [SQL] Trigger and function not on speaking terms
You need to define fn_foo w/o params per Doc Section 19.9.
Your intent as expressed in the trigger def (args) can then be fulfilled
through special top level vars.
On Mon, 2004-08-02 at 16:20, Jeff Boes wrote:
> Hmm, this is puzzling me:
>
> create or replace function fn_foo(text) returns trigger as '
> begin
># Do some stuff with $1
> end;
> ' language 'plpgsql';
>
> CREATE FUNCTION
>
> create table bar (aaa text);
>
> CREATE TABLE
>
> create trigger trg_bar
> after insert or update on bar
> execute procedure fn_foo('string');
>
> ERROR: function fn_foo() does not exist
>
> It would seem my trigger definition is trying to find fn_foo(), when I
> mean for it to call fn_foo(TEXT).
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] find the "missing" rows
I would like to find the "missing" rows between two sets without using a
subselect (or views).
This query finds the rows that are in t1 but not in t2. (see the script
below for table definitions.)
QUERY 1:
select * from t1 left join t2 on t1.i = t2.i where t2.i is null
The above query is across two tables. I'm having trouble when all the
data is in the same table. Here is my attempt but it does not work. I've
thought about doing it with views but I really would like to try without
views or a subselect...
QUERY 2:
Select a.i, b.i
from t as a
left join t as b on a.i = b.i
where a.n = 'a' and b.n = 'b' and b.i is null
Is there some clever trick get a query working in similar fashion to QUERY
1 but when all the data is in the same table (as in table "t")?
Definitions for Query 1
create table t1 (i int);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
insert into t1 values(4);
insert into t1 values(5);
create table t2 (i int);
insert into t2 values(1);
insert into t2 values(2);
insert into t2 values(3);
insert into t2 values(5);
Definitions for Query 2
create table t (n varchar(10), i int);
insert into t values('a',1);
insert into t values('a',2);
insert into t values('a',3);
insert into t values('a',4);
insert into t values('a',5);
insert into t values('b',1);
insert into t values('b',2);
insert into t values('b',3);
insert into t values('b',5);
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] filtering
Hi, I have a 14 million row table with one index on two fields one is a varchar the other is a date. The combination of the two makes the row unique. Data - name date... other fields a 1/1/01 a 1/2/01 a 1/3/01 b 1/1/01 b 1/2/01 d 1/1/01 d 1/2/01 I have a table with just the names. each name occurs once. UName - name a b c d I've tried a number of queries to find which name is in UName but not in Data. However, they are all taking too long (more than 30 minutes - but the hard drive is a slow 4200rpm IDE). What is the quickest query to get the result that I want? Also, should I put another index on the Data table for "name" only? Thanks ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] assign the row count of a query to a variable
Hi, I'm trying to assign the row count of a query to a variable in a function but I'm not having any luck. Could someone tell me the syntax? I've been looking in the docs and googling for a long time but just can't find the answer. I've tried: CREATE OR REPLACE FUNCTION ret1() RETURNS int4 AS ' BEGIN declare var int4; begin --select var count(*) from T; --select var (count(*)) from T; --select var = count(*) from T; var = select count(*) from T; return var; END; END; ' LANGUAGE 'plpgsql'; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Moving from Transact SQL to PL/pgSQL
Hello: I am starting a project using Postgres. The requirements are very similar to work I have done in the past using M$-SQL. Therefore, there are many Transact SQL stored procedures I need to port over to PL/pgSQL. Where would I find documentation on PL/pgSQL, with examples? How close to Oracle PL-SQL is Postgres? Would a Oracle PL-SQL book cover the basics? Remember be kind to the newbee. Kevin Duffy
Re: [SQL] Query from shell
On Thursday 06 April 2006 15:37, Owen Jacobson wrote: > Judith wrote: > >Hi every body, somebody can show me hot to execute a > > query from a shell > > echo QUERY HERE | psql databasename > > Or, if you want to run several queries, run psql and run your queries > there. > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster I think you can also run: psql -f filename.sql database_name ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SQL help (Informix outer to EnterpriseDB outer)
On Wednesday 12 April 2006 12:49, [EMAIL PROTECTED] wrote: > Hi all, > I have been working on converting our Informix DB to PostgreSQL. There are > some differences with SQL syntax. > > I have done many outer conversion so far, but all has either one outer or > simple > one. But this one I do not know how to do it. I have searched but could not > find > similar to what I need. > > This is the one works on InformixDB (OUTER inv_contracts ) connects to > three different tables (1. inv_contracts.inv_id = invention.id AND > 2. inv_contracts.con_id = con.id AND > 3. inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id > = 2)) > > -informix outer --- > select count(user.id) > FROM user, ascpDef AS stateDef, address, invention, user as con > , OUTER inv_contracts > WHERE > address.type = 'User' AND > address_id = 1 AND > user.id = address.type_id AND > state_id = stateDef.id AND > invention.user_id = user.id AND > invention.inv_number = '1994376-A' AND > inv_contracts.inv_id = invention.id AND > inv_contracts.con_id = con.id AND > inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = > 2); -- > > If there were only one table connection (say 1. inv_contracts.inv_id = > invention.id AND ) > I would have done it as > (,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = > invention.id) > > but I can do same or similar conversion for 2. and 3. > > I have attempted to do as below but not giving correct count. > > > postgres-- > select count(dbuser.id) > FROM dbuser, ascpDef AS stateDef, address > --, invention > --, dbuser as con > --, OUTER inv_contracts > --1 > --,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = > invention.id > --,dbuser as con LEFT OUTER JOIN inv_contracts ON inv_contracts.con_id = > con.id --,contractDef LEFT OUTER JOIN inv_contracts ON > inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = > 2) > --1 gives ERROR: table name "inv_contracts" specified more than once > -- > --2 > --,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = > invention.id > --,dbuser as con LEFT OUTER JOIN inv_contracts as inv2 ON inv2.con_id = > con.id --,contractDef LEFT OUTER JOIN inv_contracts as inv3 ON > inv3.contract_id IN (select id FROM contractDef WHERE phase_id = 2) > --2 takes very long (cancelled/stoped by me) (informix returns 306229 > within 10sec) > --Cancel request sent > --ERROR: canceling statement due to user request > -- > --3 > --,inv_contracts LEFT OUTER JOIN invention ON inv_contracts.inv_id = > invention.id > --,inv_contracts as inv2 LEFT OUTER JOIN dbuser as con ON inv2.con_id = > con.id --,inv_contracts as inv3 LEFT OUTER JOIN contractDef ON > inv3.contract_id IN (select id FROM contractDef WHERE phase_id = 2) > --3 takes very long (cancelled/stoped by me) (informix returns 306229 > within 10sec) > --Cancel request sent > --ERROR: canceling statement due to user request > -- > --4 > ,inv_contracts LEFT OUTER JOIN invention ON inv_contracts.inv_id = > invention.id LEFT OUTER JOIN dbuser as con ON inv_contracts.con_id = con.id > LEFT OUTER JOIN contractDef ON inv_contracts.contract_id IN (select id FROM > contractDef WHERE phase_id = 2) > --4 returns 1 but (informix returns 306229 within 10sec) > WHERE > address.type = 'User' AND > address_id = 1 AND > dbuser.id = address.type_id AND > state_id = stateDef.id AND > invention.user_id = dbuser.id AND > invention.inv_number = '1994376-A'; > --- > > Thanks for help. > > - > This mail sent through IMP: www.resolution.com > > ---(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 I could probably work this out for you but I have no time today. However, as a 'plan b' maybe try this... 1- create a temp table based on all tables & conditions in the query except for the outer table (i.e. user, ascpDef, address, invention, and user) 2- do an outer join on the above temp table and the inv_contracts table (inv_contracts as the outer) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] MS-SQL<->Postgres sync
Looking to keep 2 databases in sync, at least semi-realtime if possible, although running a batch update every x mins wouldn't be out of the question. One db is postgres and the other is ms-sql. It's to keep inventory in sync from 2 seperate locations, one being a brick and mortar store and the other an ecommerce system for a web site. Any and all help is appreciated since I can't find much of anything on syncing these 2 database systems! Kevin BednarSystems SupportStockwell Design Group http://www.stockwelldesigngroup.com [EMAIL PROTECTED]
Re: [SQL] MS-SQL<->Postgres sync
Yeah, I was kind of thinking that myself. I do have control over both DB's, and postgres does have an ODBC connector available as well. Perhaps using a trigger to watch a temp table on each site and having it replicate to the opposite side and then remove the record from the temp table would work. I'll have to look into it. Thanks! Kevin -Original Message-From: "Aaron Bono" <[EMAIL PROTECTED]>To: "Kevin Bednar" <[EMAIL PROTECTED]>, [email protected]: Mon, 10 Jul 2006 12:15:34 -0500Subject: Re: [SQL] MS-SQL<->Postgres syncPlease reply to all when replying on the list... 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. -Aaron
Re: [SQL] MS-SQL<->Postgres sync
Took a quick glance but were running windows on both sides and it didnt look like that would work. Didnt look real hard though. Kevin -Original Message-From: Scott Marlowe <[EMAIL PROTECTED]>To: "Forums @ Existanze" <[EMAIL PROTECTED]>Cc: [email protected]: Mon, 10 Jul 2006 13:31:35 -0500Subject: Re: [SQL] MS-SQL<->Postgres sync Look at slony.On Mon, 2006-07-10 at 11:06, Forums @ Existanze wrote: > > We are looking for the exact thing but with two PostgreSQL databases> > > __> From: [EMAIL PROTECTED]> [mailto:[EMAIL PROTECTED] On Behalf Of Kevin> Bednar> Sent: 10 July 2006 18:28> To: [email protected]> Subject: [SQL] MS-SQL<->Postgres sync> > > Looking to keep 2 databases in sync, at least semi-realtime if > possible, although running a batch update every x mins> wouldn't be out of the question. One db is postgres and the> other is ms-sql. It's to keep inventory in sync from 2> seperate locations, one being a brick and mortar store and the> other an ecommerce system for a web site. Any and all help is> appreciated since I can't find much of anything on syncing> these 2 database systems!> > Kevin Bednar> Systems Support> Stockwell Design Group> http://www.stockwelldesigngroup.com> [EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[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 ^ I'm still pretty new to postgres, but based on the documentation I'm not picking up what I'm doing wrong. Help much appreciated. Thanks, Kevin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Error when trying to use a FOR loop
Many thanks to Stephan, Richard and George. When I was reading the documentation about FOR loops, I didn't realize that I was in the plpgsql section! CREATE OR REPLACE FUNCTION rgio() RETURNS integer as $$ BEGIN DECLARE lv RECORD; FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP SELECT ldev FROM ldevrg WHERE ldevrg='$lv'; END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; So I've made progress, but I'm running into the same thing. psql:rgio.sql:16: ERROR: syntax error at or near "FOR" at character 86 psql:rgio.sql:16: LINE 6: FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP psql:rgio.sql:16: ^ If I comment out the DECLARE statement above, I get the following: psql:rgio.sql:16: ERROR: loop variable of loop over rows must be record or row variable at or near "LOOP" at character 129 psql:rgio.sql:16: LINE 6: FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP psql:rgio.sql:16: ^ Ideas? ____ From: [EMAIL PROTECTED] on behalf of Kevin Nikiforuk Sent: Thu 7/20/2006 7:46 AM To: [email protected] Subject: [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 ^ I'm still pretty new to postgres, but based on the documentation I'm not picking up what I'm doing wrong. Help much appreciated. Thanks, Kevin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] CREATE TABLE AS inside of a function
So now that I've got my loops working, on to my next newbie question. I've created my function and in it, I want to loop through the results of a select and for each value of my loop counter, I want to create a new table, but I can't figure out how to use a variable in the name of the new table, see below. CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$ DECLARE lv RECORD; BEGIN FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP CREATE TABLE rgio_$lv AS SELECT ldev FROM ldevrg WHERE rg='$lv'; END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; Thanks, Kevin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] CREATE TABLE AS inside of a function
So, I've changed my code as Erik suggested: CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$ DECLARE lv RECORD; BEGIN FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP EXECUTE 'CREATE TABLE rgio_' || $lv || ' AS SELECT ldev FROM ldevrg WHERE rg=' || quote_literal($lv) || ';' END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; And I get: psql:rgio.sql:32: ERROR: syntax error at or near "$" at character 33 QUERY: SELECT 'CREATE TABLE rgio_' || $ $1 || ' AS SELECT ldev FROM ldevrg WHERE rg=' || quote_literal($ $2 ) || ';' END LOOP CONTEXT: SQL statement in PL/PgSQL function "rgio" near line 23 psql:rgio.sql:32: LINE 1: SELECT 'CREATE TABLE rgio_' || $ $1 || ' AS psql:rgio.sql:32: ^ From: [EMAIL PROTECTED] on behalf of Erik Jones Sent: Fri 7/21/2006 3:04 PM To: Rodrigo De Leon Cc: [email protected] Subject: Re: [SQL] CREATE TABLE AS inside of a function Rodrigo De Leon wrote: > On 7/21/06, Kevin Nikiforuk <[EMAIL PROTECTED]> wrote: >> So now that I've got my loops working, on to my next newbie >> question. I've created my function and in it, I want to loop through >> the results of a select and for each value of my loop counter, I want >> to create a new table, but I can't figure out how to use a variable >> in the name of the new table, see below. >> >> CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$ >> DECLARE >> lv RECORD; >> >> BEGIN >> FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP >> CREATE TABLE rgio_$lv AS >> SELECT ldev >> FROM ldevrg >> WHERE rg='$lv'; >> END LOOP; >> RETURN 1; >> END; >> $$ LANGUAGE plpgsql; >> >> Thanks, >> Kevin > > See: > http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > Also, I really recommend enough that you read chapters 32. Extending SQL, 33. Triggers, and 36. Pl/pgSQL in their entirety. In fact, to keep up with the linking to them for you: http://www.postgresql.org/docs/8.1/interactive/extend.html http://www.postgresql.org/docs/8.1/interactive/triggers.html http://www.postgresql.org/docs/8.1/interactive/plpgsql.html And, so that I don't feel like I'm becoming one of those rtfm jerks I always complain about: what you need to do is place your CREATE TABLE statement in an EXECUTE directive like so (inside the the FOR body): EXECUTE 'CREATE TABLE rgio_' || $lv || ' AS SELECT ldev FROM ldevrg WHERE rg=\'' || $lv || '\';' -- this line could have also been: WHERE rg=' || quote_literal($lv) || ';' EXECUTE takes a query in a string to execute and you need to use string concatenation to build the string if you're using variables from the function in the query. Pl/pgSQL doesn't have any variable substitution inside of strings (like in double quoted string in PHP) which is why you need to use the concatenation bit. -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(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 2: Don't 'kill -9' the postmaster
[SQL] Trapping statement timeout
Hello All: I certain that for you guys that this will be trivial. What I need to do is to trap, go into an exception block, when a statement times out. Let me explain. I have a processing that creates summary records based upon records in another table. Think in terms of summary sales records for sales persons at multiple stores in multiple districts. This process can run for a while and moves lots of data around. I foresee the situation where multiple users might kick off a run for the same data at the same time. So I would like to implement a locking mechanism where by if a district is being run, another user can not kick it off. Please consider the following: create or replace FUNCTION fof_run( fundkey_in integer date_in date) returns void as $body$ DECLARE rowcnt integer; BEGIN BEGIN TRANSACTION; SET LOCAL STATEMENT_TIMEOUT = 5000 select count(*)into rowcnt from fofrun where fi_parentkey = fundkey_in and date_ = date_in; if rowcnt = 0 then insert into fofrum (fi_parentkey , date_, start_) values( fund_key_in, date_in, now ); end if; select * from fofrun where fi_parentkey = fundkey_in and date_ = date_in FOR UPDATE; SET LOCAL STATEMENT_TIMEOUT = 0 COMMIT; EXCEPTION WHEN END; The table FOFRUN will be the keymaster. So for a fund and a date if a records exists, lock it for update. But only wait five seconds for the lock. If you get the lock, all is good. If not I need to return a nice error message Question: When the SELECT FOR UPDATE fails/timeout what Error does it toss? I looked in the PostgreSQL Error Codes and did not see one that matched. When I am in the EXCEPTION block can I execute a normal SELECT against FOFRUN to determine when the prior lock was obtained, based upon the value in start_ Thank you for your attention to this matter. Kevin Duffy
Re: [SQL] [PERFORM] SQL Query Performance - what gives?
Karl Denninger wrote: > Let's take the following EXPLAIN results: We could tell a lot more from EXPLAIN ANALYZE results. The table definitions (with index information) would help, too. -Kevin -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [PERFORM] SQL Query Performance - what gives?
Karl Denninger wrote:
>-> Index Scan using forum_name on forum
> (cost=0.00..250.63 rows=1 width=271) (actual time=0.013..0.408
> rows=63 loops=1)
> Filter: (((contrib IS NULL) OR (contrib = '
> '::text) OR (contrib ~~ '%b%'::text)) AND ((permission & 127) =
> permission))
The biggest issue, as far as I can see, is that it thinks that the
selection criteria on forum will limit to one row, while it really
matches 63 rows.
You might be able to coerce it into a faster plan with something like
this (untested):
select *
from (select * from post
where invisible <> 1
and to_tsvector('english', message)
@@ to_tsquery('violence')
) p,
forum
where forum.name = p.forum
and (permission & '127') = permission
and (contrib is null or contrib = ' ' or contrib like '%b%')
order by modified desc
limit 100
;
-Kevin
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [BUGS] Issue while using PostgreSql 8.4.
Atul Kumar wrote: > My issue is, first time I am creating the table and inserting some > rows of data. After doing some logic going to delete that table . > I am observing application is getting hang while executing > statement.execute(). It's taking a long time to run which statement, a DELETE of all rows in a table? This isn't sounding much like a PostgreSQL bug at this point, but with so little information, it is hard to tell for sure. Please review this page: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems As a "shot in the dark" -- if the statement which is taking a long time is the DELETE, and you want to delete all rows in the table, try TRUNCATE TABLE. -Kevin -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Order of evaluation in triggers for checks on inherited table partitions
I am trying to create a trigger on updates to a table that is
partitioned. The child tables are partitioned by month and include
checks on a timestamp field. I want the trigger on the updates to
call a function that replaces the update entirely. In order to do
this my trigger deletes the record from the parent table (which
deletes it from the appropriate child table) and then inserts into the
appropriate child table and returns NULL (thus skipping the actual
update). However when I try to update an existing record with a
timestamp that would place it in a child table different from the
child table it is in I get an error due to the check on the child
table it is currently in. My best guess as to what is happening is
that the trigger is evaluating the check before it evaluates the
trigger function and thus cannot tell that the update to the original
table should never take place. I have included an example below. The
error that results is "new row for relation "t_foo_2011_6" violates
check constraint "t_foo_2011_6_f_timestamp_check""
My questions:
Is the order of evaluation for the trigger causing this error?
If not what is?
Is there another way to update a record in a child table that would
move it to another child table before the update and skip the
evaluation of the check constraints on the current table?
Example code follows:
CREATE SCHEMA some_schema;
CREATE SCHEMA some_schema_children;
--master table
CREATE TABLE some_schema.t_foo (
f_id_foo serial,
f_timestamp timestamp,
f_text varchar(30)
);
CREATE OR REPLACE FUNCTION some_schema.foo_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
v_tablename varchar(13);
v_month integer;
v_year integer;
BEGIN
v_month:=(SELECT date_part from date_part('month', NEW.f_timestamp));
v_year:=(SELECT date_part from date_part('year', NEW.f_timestamp));
v_tablename:='t_foo_'||v_year||'_'||v_month;
IF ((SELECT COUNT(*) FROM pg_tables WHERE tablename = v_tablename AND
schemaname='some_schema_children')=0)
THEN
IF (v_month=12)
THEN
EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year+1||'-01-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';
ELSE
EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year||'-'||v_month+1||'-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';
END IF;
END IF;
EXECUTE 'INSERT INTO some_schema_children.'||v_tablename||' VALUES
('||NEW.f_id_foo||','''||NEW.f_timestamp||''','''||NEW.f_text||''')';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER some_schema_insert_foo_trigger
BEFORE INSERT ON some_schema.t_foo
FOR EACH ROW EXECUTE PROCEDURE some_schema.foo_insert_trigger();
CREATE OR REPLACE FUNCTION some_schema.foo_update_trigger()
RETURNS TRIGGER AS $$
DECLARE
v_tablename varchar(13);
v_month integer;
v_year integer;
BEGIN
v_month:=(SELECT date_part from date_part('month', NEW.f_timestamp));
v_year:=(SELECT date_part from date_part('year', NEW.f_timestamp));
v_tablename:='t_foo_'||v_year||'_'||v_month;
IF ((SELECT COUNT(*) FROM pg_tables WHERE tablename = v_tablename AND
schemaname='some_schema_children')=0)
THEN
IF (v_month=12)
THEN
EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year+1||'-01-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';
ELSE
EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year||'-'||v_month+1||'-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';
END IF;
END IF;
EXECUTE 'DELETE FROM some_schema.t_foo WHERE f_id_foo='||NEW.f_id_foo;
EXECUTE 'INSERT INTO some_schema_children.'||v_tablename||' VALUES
('||NEW.f_id_foo||','''||NEW.f_timestamp||''','''||NEW.f_text||''')';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER some_schema_update_foo_trigger
BEFORE UPDATE ON some_schema.t_foo
FOR EACH ROW EXECUTE PROCEDURE some_schema.foo_update_trigger();
INSERT INTO some
Re: [SQL] Re: Order of evaluation in triggers for checks on inherited table partitions
Can procedural languages be used in rules? I didn't see any examples in the documentation that suggested something like this could be done using rules. --Kevin Crain On Mon, May 30, 2011 at 2:21 AM, Jasen Betts wrote: > On 2011-05-27, Kevin Crain wrote: >> I am trying to create a trigger on updates to a table that is >> partitioned. The child tables are partitioned by month and include >> checks on a timestamp field. > >> However when I try to update an existing record with a >> timestamp that would place it in a child table different from the >> child table it is in I get an error due to the check on the child >> table it is currently in. My best guess as to what is happening is >> that the trigger is evaluating the check before it evaluates the >> trigger function and thus cannot tell that the update to the original >> table should never take place. I have included an example below. The >> error that results is "new row for relation "t_foo_2011_6" violates >> check constraint "t_foo_2011_6_f_timestamp_check"" > > the problem is the check is running before the trigger. > perhaps you can use a rule instead of a trigger? > > -- > ⚂⚃ 100% natural > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Re: Order of evaluation in triggers for checks on inherited table partitions
Okay, I figured out what is going on. Even though I was running the update on the master table the trigger was not being applied because it was actually being ran using the child table where the record to be updated resided. So the trigger function was being skipped and it was running as an ordinary update, hence the error. In order to get this to work I had to add a trigger for each child table as well to call my update function trigger. --Kevin Crain On Tue, May 31, 2011 at 6:40 AM, Kevin Crain wrote: > Can procedural languages be used in rules? I didn't see any examples > in the documentation that suggested something like this could be done > using rules. > > --Kevin Crain > > On Mon, May 30, 2011 at 2:21 AM, Jasen Betts wrote: >> On 2011-05-27, Kevin Crain wrote: >>> I am trying to create a trigger on updates to a table that is >>> partitioned. The child tables are partitioned by month and include >>> checks on a timestamp field. >> >>> However when I try to update an existing record with a >>> timestamp that would place it in a child table different from the >>> child table it is in I get an error due to the check on the child >>> table it is currently in. My best guess as to what is happening is >>> that the trigger is evaluating the check before it evaluates the >>> trigger function and thus cannot tell that the update to the original >>> table should never take place. I have included an example below. The >>> error that results is "new row for relation "t_foo_2011_6" violates >>> check constraint "t_foo_2011_6_f_timestamp_check"" >> >> the problem is the check is running before the trigger. >> perhaps you can use a rule instead of a trigger? >> >> -- >> ⚂⚃ 100% natural >> >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting records X minutes apart
Will you be using a full timestamp with that or are you only concerned about hours and minutes? If you want a full timestamp do you care about the seconds? For example, do you want to be able to do this for '2011-06-01 23:59:04' and '2011-06-02 00:04:04'? On Fri, Jun 3, 2011 at 12:52 PM, wrote: > I have a table that, at a minimum, has ID and timestamp columns. Records > are inserted into with random IDs and timestamps. Duplicate IDs are allowed. > > I want to select records grouped by ID, ordered by timestamp that are X > minutes > apart. In this case X is 5. > > Note, the intervals are not X minute wall clock intervals, they are X minute > intervals from the last accepted record, per-id. > > For instance here is some sample input data: > > ID TS (HH:MM) > --- > 0 20:00 > 1 20:03 > 1 20:04 > 0 20:05 > 1 20:05 > 0 20:08 > 1 20:09 > 0 20:10 > > I'd want the select to return: > > ID TS (HH:MM) > --- > 0 20:00 > 0 20:05 > 0 20:10 > 1 20:03 > 1 20:09 > > > Does my question make sense? > > Thanks in advance, > Wayne > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting records X minutes apart
It looks like maybe he is trying to fetch records that either have no previous entries or have another record with a timestamp 5 minutes before them at the time they are inserted... On Sat, Jun 4, 2011 at 4:45 AM, Jasen Betts wrote: > On 2011-06-03, [email protected] wrote: >> >> ID TS (HH:MM) >> --- >> 0 20:00 >> 0 20:05 >> 0 20:10 >> 1 20:03 >> 1 20:09 >> >> >> Does my question make sense? > > no, why is (1,20:04) excluded, but (0,20:05) included? > both records are 5 minutes from the newest. > > -- > ⚂⚃ 100% natural > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting records X minutes apart
Why is (0,20:10) listed in your expected results when there is a (0,20:08)? On Fri, Jun 3, 2011 at 12:52 PM, wrote: > I have a table that, at a minimum, has ID and timestamp columns. Records > are inserted into with random IDs and timestamps. Duplicate IDs are allowed. > > I want to select records grouped by ID, ordered by timestamp that are X > minutes > apart. In this case X is 5. > > Note, the intervals are not X minute wall clock intervals, they are X minute > intervals from the last accepted record, per-id. > > For instance here is some sample input data: > > ID TS (HH:MM) > --- > 0 20:00 > 1 20:03 > 1 20:04 > 0 20:05 > 1 20:05 > 0 20:08 > 1 20:09 > 0 20:10 > > I'd want the select to return: > > ID TS (HH:MM) > --- > 0 20:00 > 0 20:05 > 0 20:10 > 1 20:03 > 1 20:09 > > > Does my question make sense? > > Thanks in advance, > Wayne > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting records X minutes apart
My approach would be to add a column for LAST_TS and place a trigger on insert that populates this new column. Then you have something you can put in your WHERE clause to test on. On Fri, Jun 3, 2011 at 12:52 PM, wrote: > I have a table that, at a minimum, has ID and timestamp columns. Records > are inserted into with random IDs and timestamps. Duplicate IDs are allowed. > > I want to select records grouped by ID, ordered by timestamp that are X > minutes > apart. In this case X is 5. > > Note, the intervals are not X minute wall clock intervals, they are X minute > intervals from the last accepted record, per-id. > > For instance here is some sample input data: > > ID TS (HH:MM) > --- > 0 20:00 > 1 20:03 > 1 20:04 > 0 20:05 > 1 20:05 > 0 20:08 > 1 20:09 > 0 20:10 > > I'd want the select to return: > > ID TS (HH:MM) > --- > 0 20:00 > 0 20:05 > 0 20:10 > 1 20:03 > 1 20:09 > > > Does my question make sense? > > Thanks in advance, > Wayne > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need help with some aggregation magic
Try this:
select user_id, project_id, date_trunc, sum(sum) FROM (select user_id,
project_id, date_trunc('day', ts), SUM(duration) FROM (select user_id,
project_id, a.ts, ((SELECT MIN(b.ts) FROM log b WHERE b.ts>a.ts AND
(date_trunc('day',a.ts)=date_trunc('day',b.ts)))-a.ts) AS duration
from log a order by user_id, project_id, ts) AS foo group by user_id,
project_id, ts) AS day_set group by user_id, project_id, date_trunc
order by user_id, project_id, date_trunc;
-Kevin Crain
On Thu, Jun 9, 2011 at 6:43 AM, Andreas wrote:
> hi,
> I have a log-table that stores events of users and projects like this
> ( user_id integer, project_id integer, ts timestamp, event_type integer )
>
> I need an aggregated list of worktime per user, per project, per day.
>
> The users can switch projects during the day so I can't work this out with
> min(ts) and max(ts).
>
> Is there a clever way to get this with SQL ?
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] interesting sequence
You don't need a loop there. Assuming your order id field is of type
varchar you can just build the first part of your string and then do a
count to get the last part using a LIKE comparison:
select count(id_order) + 1 from sometable WHERE id_order LIKE 'O-20110704 -%';
If you do this inside a function it will be like running it in a
transaction so you shouldn't have to worry about it being a multi-user
system.
On Tue, Jul 5, 2011 at 12:38 PM, Samuel Gendler
wrote:
>
>
> On Tue, Jul 5, 2011 at 6:52 AM, John Fabiani wrote:
>>
>> Hi,
>>
>> I have a special need to create a sequence like function.
>>
>> "O-20110704 -2" which is
>> "O" for order (there are other types)
>> "20110704" is for July 4, 2011
>> '2' the second order of the day for July 4, 2011
>>
>> I of course can get the type and date. What I don't know is how to get is
>> the
>> last number. It would seem to be that I would need a loop to determine if
>> the
>> next number existed.
>>
>> LOOP
>> --Check to see if the string exist in a table
>> -- count = count +1
>> -- until I don't find the string
>> END LOOP;
>>
>> but then I thought I could do something like
>>
>> for $1 in (select string from sometable)
>> LOOP
>> count = count + 1
>>
>> or something like this
>>
>> for i in 1..999 LOOP
>> -- check for the existence of the string in a table using 'i'
>> -- there will never be 999 orders in one day.
>> END LOOP
>>
>>
>> So here is the question what would be the best way for a multi-user
>> system?
>> If someone has a better thought - it would be helpful.
>>
>> BTW I did NOT design the number - in fact it seems silly to me.
>
> I'd probably do the following. Create a table to hold the current date as a
> string appropriate for use in ids. I'd also create a sequence for each of
> the id types. I'd set up a cron job (or equivalent) to run at midnight which
> updates the date and resets all of the sequences to 1 within a transaction.
> You can probably do all of it in a single query.
> Then I'd do inserts which generate the id by concatenating the type initial
> with the date and a sequence, probably in an insert trigger on the table if
> you are ok with server generated ids. Otherwise, you could do insert with a
> subquery which generates the id:
> insert into order (id, x, y) values ((select 'O-' || d.datestring || '-' ||
> nextval('order_id_sequence') from date_table d), 'x_value', 'y_value');
> If you are using hibernate or some other ORM, you can surely use an insert
> trigger to generate the id and tell the ORM to use a server generated id.
> sequence documentation is here:
> http://www.postgresql.org/docs/8.1/static/functions-sequence.html
>
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] interesting sequence
My previous reply was intended for John.
On Tue, Jul 5, 2011 at 1:11 PM, Kevin Crain wrote:
> You don't need a loop there. Assuming your order id field is of type
> varchar you can just build the first part of your string and then do a
> count to get the last part using a LIKE comparison:
>
> select count(id_order) + 1 from sometable WHERE id_order LIKE 'O-20110704
> -%';
>
> If you do this inside a function it will be like running it in a
> transaction so you shouldn't have to worry about it being a multi-user
> system.
>
>
>
> On Tue, Jul 5, 2011 at 12:38 PM, Samuel Gendler
> wrote:
>>
>>
>> On Tue, Jul 5, 2011 at 6:52 AM, John Fabiani wrote:
>>>
>>> Hi,
>>>
>>> I have a special need to create a sequence like function.
>>>
>>> "O-20110704 -2" which is
>>> "O" for order (there are other types)
>>> "20110704" is for July 4, 2011
>>> '2' the second order of the day for July 4, 2011
>>>
>>> I of course can get the type and date. What I don't know is how to get is
>>> the
>>> last number. It would seem to be that I would need a loop to determine if
>>> the
>>> next number existed.
>>>
>>> LOOP
>>> --Check to see if the string exist in a table
>>> -- count = count +1
>>> -- until I don't find the string
>>> END LOOP;
>>>
>>> but then I thought I could do something like
>>>
>>> for $1 in (select string from sometable)
>>> LOOP
>>> count = count + 1
>>>
>>> or something like this
>>>
>>> for i in 1..999 LOOP
>>> -- check for the existence of the string in a table using 'i'
>>> -- there will never be 999 orders in one day.
>>> END LOOP
>>>
>>>
>>> So here is the question what would be the best way for a multi-user
>>> system?
>>> If someone has a better thought - it would be helpful.
>>>
>>> BTW I did NOT design the number - in fact it seems silly to me.
>>
>> I'd probably do the following. Create a table to hold the current date as a
>> string appropriate for use in ids. I'd also create a sequence for each of
>> the id types. I'd set up a cron job (or equivalent) to run at midnight which
>> updates the date and resets all of the sequences to 1 within a transaction.
>> You can probably do all of it in a single query.
>> Then I'd do inserts which generate the id by concatenating the type initial
>> with the date and a sequence, probably in an insert trigger on the table if
>> you are ok with server generated ids. Otherwise, you could do insert with a
>> subquery which generates the id:
>> insert into order (id, x, y) values ((select 'O-' || d.datestring || '-' ||
>> nextval('order_id_sequence') from date_table d), 'x_value', 'y_value');
>> If you are using hibernate or some other ORM, you can surely use an insert
>> trigger to generate the id and tell the ORM to use a server generated id.
>> sequence documentation is here:
>> http://www.postgresql.org/docs/8.1/static/functions-sequence.html
>>
>>
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] interesting sequence
That's why you need to do this inside a function. Basically just make an insert function for the table and have it calculate the count and do the insert in one transaction. On Tue, Jul 5, 2011 at 5:41 PM, Samuel Gendler wrote: > > > On Tue, Jul 5, 2011 at 2:42 PM, John Fabiani wrote: >> >> On Tuesday, July 05, 2011 01:11:11 pm Kevin Crain wrote: >> > You don't need a loop there. Assuming your order id field is of type >> > varchar you can just build the first part of your string and then do a >> > count to get the last part using a LIKE comparison: >> > >> > select count(id_order) + 1 from sometable WHERE id_order LIKE >> > 'O-20110704 >> > -%'; >> > >> > If you do this inside a function it will be like running it in a >> > transaction so you shouldn't have to worry about it being a multi-user >> > system. >> > >> > >> > >> >> I like this - looks better than what I'm currently doing. Thanks >> Johnf >> > > It is simpler, but it will result in id collision if two inserts runs at the > same time, particularly if the count query takes a while to run, so be > prepared to handle that. Make sure you have an index which can satisfy that > count query quickly. If you are not using the C locale for your database, > that means you must create an index on that column that uses > text_pattern_ops or varchar_pattern_ops (depending on if it is text or > varchar column) so that postgresql can use the index for that comparison, > otherwise LIKE clauses will force a sequential scan of the whole table every > time. C locale does byte by byte text comparison, so the special index > isn't required. > http://www.postgresql.org/docs/9.0/interactive/indexes-opclass.html > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] interesting sequence
IF this field is unique you shouldn't get duplicates from a function; the transaction will either succeed or fail; the beauty of a function is that you can return an error message. I personally prefer to handle errors at the application level, but if you have admins running ad-hoc queries on the database level doing inserts then you definitely do need to handle that properly. Triggers are handy in that regard. You can do loops to check for collisions...I haven't done that so don't know the best way to code that though. On Wed, Jul 6, 2011 at 5:28 AM, Jasen Betts wrote: > On 2011-07-06, Kevin Crain wrote: >> That's why you need to do this inside a function. Basically just make >> an insert function for the table and have it calculate the count and >> do the insert in one transaction. > > you will still get duplicates, so include code in the function to > retry if there is an error. > > -- > ⚂⚃ 100% natural > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Max column number.
This is an unfortunate situation, you shouldn't be required to do this, the people generating your requirements need to be more informed. I would make damn sure you notify the stakeholders in this project that the data model is screwed and needs a redesign. I agree that you should split this table and do a join if you have no option of redesigning this. > > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte wrote: >> Yes, sure. I mean, I can't change the whole process which creates columns >> dynamically. >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen >> wrote: >>> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >>> > Unfortunately It's an inherited data model and I can't make any change >>> > for >>> > now... >>> >>> but by adding columns you *are* making changes to it... >>> >>> Reinoud >>> -- >>> __ >>> "Nothing is as subjective as reality" >>> Reinoud van Leeuwen [email protected] >>> http://reinoud.van.leeuwen.net kvk 27320762 >>> __ >>> >>> -- >>> Sent via pgsql-sql mailing list ([email protected]) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] combining strings to make a query
You can do full-text search in postgres now using ts_vectors. I'd recommend going that route. Doing like comparisons is not a good idea if you don't know the first part of the string you are searching forIt appears to be much faster from my experience to search for ab% than it is to search for %ab%. On Tue, Jul 12, 2011 at 7:51 PM, Wes James wrote: > I'm using Erlang and postgresql to build a web interface. When I > create the query string I get something like: > > select * from table where field::text ilike '%%' > > But when I do that (if someone types in '\' for part of the text > search), I get a pg log entry to use E'\\' > > How would I use E'' with ilike '%%'. ilike E'%\\%' doesn't work. > > Thanks, > > -wes > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Max column number.
I still can't imagine why you'd ever need this...could you explain what this does? I'm just curious now On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain wrote: > This is an unfortunate situation, you shouldn't be required to do > this, the people generating your requirements need to be more > informed. I would make damn sure you notify the stakeholders in this > project that the data model is screwed and needs a redesign. I agree > that you should split this table and do a join if you have no option > of redesigning this. > > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte wrote: >> Yes, sure. I mean, I can't change the whole process which creates columns >> dynamically. >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen >> wrote: >>> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >>> > Unfortunately It's an inherited data model and I can't make any change >>> > for >>> > now... >>> >>> but by adding columns you *are* making changes to it... >>> >>> Reinoud >>> -- >>> __ >>> "Nothing is as subjective as reality" >>> Reinoud van Leeuwen [email protected] >>> http://reinoud.van.leeuwen.net kvk 27320762 >>> __ >>> >>> -- >>> Sent via pgsql-sql mailing list ([email protected]) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Max column number.
How are you determining the data types for these columns? On Wed, Jul 13, 2011 at 8:45 AM, Miguel Angel Conte wrote: > Hi, > Thanks for your interest. This app load scv files which change every day > (sometimes the columns too). The sizes of these files are in avg 15MB. So, > We load something like 100MB each day. We tried to find a better solution > but we couldn't, becouse one of the our requirement is not to use a lot of > space. Also, the app is used to consult these information, and for our > particular type of select's queries, we get the best performance if the > information is all into a same row. > > On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain wrote: >> >> I still can't imagine why you'd ever need this...could you explain >> what this does? I'm just curious now >> >> On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain >> wrote: >> > This is an unfortunate situation, you shouldn't be required to do >> > this, the people generating your requirements need to be more >> > informed. I would make damn sure you notify the stakeholders in this >> > project that the data model is screwed and needs a redesign. I agree >> > that you should split this table and do a join if you have no option >> > of redesigning this. >> > >> > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte >> > wrote: >> >> Yes, sure. I mean, I can't change the whole process which creates >> >> columns >> >> dynamically. >> >> >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen >> >> wrote: >> >>> >> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >> >>> > Unfortunately It's an inherited data model and I can't make any >> >>> > change >> >>> > for >> >>> > now... >> >>> >> >>> but by adding columns you *are* making changes to it... >> >>> >> >>> Reinoud >> >>> -- >> >>> __ >> >>> "Nothing is as subjective as reality" >> >>> Reinoud van Leeuwen [email protected] >> >>> http://reinoud.van.leeuwen.net kvk 27320762 >> >>> __ >> >>> >> >>> -- >> >>> Sent via pgsql-sql mailing list ([email protected]) >> >>> To make changes to your subscription: >> >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> >> >> > > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Max column number.
Is there any room for improvement in the data types? On Wed, Jul 13, 2011 at 11:03 AM, Miguel Angel Conte wrote: > I have the metadata in the same csv. > > On Wed, Jul 13, 2011 at 3:00 PM, Kevin Crain wrote: >> >> How are you determining the data types for these columns? >> >> On Wed, Jul 13, 2011 at 8:45 AM, Miguel Angel Conte >> wrote: >> > Hi, >> > Thanks for your interest. This app load scv files which change every day >> > (sometimes the columns too). The sizes of these files are in avg 15MB. >> > So, >> > We load something like 100MB each day. We tried to find a better >> > solution >> > but we couldn't, becouse one of the our requirement is not to use a lot >> > of >> > space. Also, the app is used to consult these information, and for our >> > particular type of select's queries, we get the best performance if the >> > information is all into a same row. >> > >> > On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain >> > wrote: >> >> >> >> I still can't imagine why you'd ever need this...could you explain >> >> what this does? I'm just curious now >> >> >> >> On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain >> >> wrote: >> >> > This is an unfortunate situation, you shouldn't be required to do >> >> > this, the people generating your requirements need to be more >> >> > informed. I would make damn sure you notify the stakeholders in this >> >> > project that the data model is screwed and needs a redesign. I agree >> >> > that you should split this table and do a join if you have no option >> >> > of redesigning this. >> >> > >> >> > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte >> >> > >> >> > wrote: >> >> >> Yes, sure. I mean, I can't change the whole process which creates >> >> >> columns >> >> >> dynamically. >> >> >> >> >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen >> >> >> wrote: >> >> >>> >> >> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >> >> >>> > Unfortunately It's an inherited data model and I can't make any >> >> >>> > change >> >> >>> > for >> >> >>> > now... >> >> >>> >> >> >>> but by adding columns you *are* making changes to it... >> >> >>> >> >> >>> Reinoud >> >> >>> -- >> >> >>> __ >> >> >>> "Nothing is as subjective as reality" >> >> >>> Reinoud van Leeuwen [email protected] >> >> >>> http://reinoud.van.leeuwen.net kvk 27320762 >> >> >>> __ >> >> >>> >> >> >>> -- >> >> >>> Sent via pgsql-sql mailing list ([email protected]) >> >> >>> To make changes to your subscription: >> >> >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> >> >> >> >> >> > >> > >> > > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Max column number.
Have you tried changing the block size? http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F On Wed, Jul 13, 2011 at 11:03 AM, Miguel Angel Conte wrote: > I have the metadata in the same csv. > > On Wed, Jul 13, 2011 at 3:00 PM, Kevin Crain wrote: >> >> How are you determining the data types for these columns? >> >> On Wed, Jul 13, 2011 at 8:45 AM, Miguel Angel Conte >> wrote: >> > Hi, >> > Thanks for your interest. This app load scv files which change every day >> > (sometimes the columns too). The sizes of these files are in avg 15MB. >> > So, >> > We load something like 100MB each day. We tried to find a better >> > solution >> > but we couldn't, becouse one of the our requirement is not to use a lot >> > of >> > space. Also, the app is used to consult these information, and for our >> > particular type of select's queries, we get the best performance if the >> > information is all into a same row. >> > >> > On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain >> > wrote: >> >> >> >> I still can't imagine why you'd ever need this...could you explain >> >> what this does? I'm just curious now >> >> >> >> On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain >> >> wrote: >> >> > This is an unfortunate situation, you shouldn't be required to do >> >> > this, the people generating your requirements need to be more >> >> > informed. I would make damn sure you notify the stakeholders in this >> >> > project that the data model is screwed and needs a redesign. I agree >> >> > that you should split this table and do a join if you have no option >> >> > of redesigning this. >> >> > >> >> > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte >> >> > >> >> > wrote: >> >> >> Yes, sure. I mean, I can't change the whole process which creates >> >> >> columns >> >> >> dynamically. >> >> >> >> >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen >> >> >> wrote: >> >> >>> >> >> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >> >> >>> > Unfortunately It's an inherited data model and I can't make any >> >> >>> > change >> >> >>> > for >> >> >>> > now... >> >> >>> >> >> >>> but by adding columns you *are* making changes to it... >> >> >>> >> >> >>> Reinoud >> >> >>> -- >> >> >>> __ >> >> >>> "Nothing is as subjective as reality" >> >> >>> Reinoud van Leeuwen [email protected] >> >> >>> http://reinoud.van.leeuwen.net kvk 27320762 >> >> >>> __ >> >> >>> >> >> >>> -- >> >> >>> Sent via pgsql-sql mailing list ([email protected]) >> >> >>> To make changes to your subscription: >> >> >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> >> >> >> >> >> > >> > >> > > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compile postgres with visual studio 2010
The documentation only mentions Visual Studio 2005 and Visual Studio 2008, but I see no reason why it shouldn't work. Check out the requirements listed in the documentation: http://www.postgresql.org/docs/9.0/interactive/install-windows-full.html On Wed, Jul 20, 2011 at 3:55 AM, Sofer, Yuval wrote: > Hi > > I would like to build Postgres from source with the visual studio 2010 > compiler > > Is it supported? Is there any document which describes the process of the > implementation? > > Thanks, > > > Yuval Sofer > BMC Software > CTM&D Business Unit > DBA Team > 972-52-4286-282 > [email protected] > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [ADMIN] function based index problem
Viktor Bojovi* wrote: > i don't know why it doesn't use index scan. Because it thinks your query will return 81226 rows. We need more information to make many suggestions beyond "make sure the table has been analyzed". http://wiki.postgresql.org/wiki/SlowQueryQuestions Also, it's not considered good form to post an issue to more than one PostgreSQL list. This is probably most appropriate on the pgsql-performance list, so if you want to follow up with more detail to get more detailed suggestions, it would be best to abandon this thread and start a new one on that list. -Kevin -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: [BUGS] pg_dump: aborting because of server version mismatch
Mitesh Shah wrote: > *pg_dump: server version: 9.1.2; pg_dump version: 9.0.5* > *pg_dump: aborting because of server version mismatch* This is not a bug. Use a version of pg_dump which is at least as new as the server. The older version of pg_dump is unlikely to be able to recognize everything in the newer server, -Kevin -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Selecting rows with "static" ordering
At 8:01p -0400 on 26 Apr 2007, Steve Midgley wrote: From that application, I want to retrieve all those rows, and I want them in the order they are currently stored in that variable. So take for example this foreign application variable: ids = "3,2,5,1,4" The application then executes this sql: select * from table where id in (3,2,5,1,4) Is there a "neat trick" that anyone knows for pulling this off in a single query? Basically right now I'm issuing 5 queries to the backend to ensure ordering but this horribly inefficient. Why not do this particular ordering on the application side? With this example SQL, and assuming you've PRIMARY KEYed( id ), you're guaranteed to get a unique id for each tuple. Presumably, you've got the order you want stored within your application, so just pull it out of a hash in the order you need it. Or is it terribly more complicated than this? Kevin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] SQL question: Highest column value of unique column pairs
Hi, I have the following table which holds the result of 1 on 1 matches: FName1, LName1, Score1, FName2, LName2, Score2, Date John, Doe,85 Bill, Gates, 20 Jan 1. John, Archer, 90 John, Doe,120 Jan 5 Bob,Barker, 70 Calvin, Klien 8 Jan 8 John, Doe,60 Bill, Gates, 25 Jan 3. So columns 1 and 2 hold the first person. Column 3 holds his score. Columns 4 and 5 hold the second person. Column 6 holds his score. I want to return the most recent score for each person (be they an opponent or myself). And the resultant table shouldn't care if they are person 1 or 2. So the end result would be FName, LName, Score, Date John,Doe, 120Jan 5. John,Archer 90 Jan 5. Bob, Barker 70 Jan 8 Bill,Gates 25 Jan 3 Calvin Klien 8 Jan 8 Thanks for any help! ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] SQL question: Highest column value of unique column pairs
Thanks! How would I find the highest score in the union of the two tables? I tried this but it can't find unionTable: SELECT * FROM (select fnam1 as fname,lname1 as lname, score1 as score from myscorestable union select fnam2 as fname,lname2 as lname, score2 as score from myscorestable) as unionTable WHERE unionTable.score= (SELECT max(unionTable.score) FROM unionTable); Shane Ambler wrote: Kevin Jenkins wrote: Hi, I have the following table which holds the result of 1 on 1 matches: FName1, LName1, Score1, FName2, LName2, Score2, Date John, Doe,85 Bill, Gates, 20 Jan 1. John, Archer, 90 John, Doe,120 Jan 5 Bob,Barker, 70 Calvin, Klien 8 Jan 8 John, Doe,60 Bill, Gates, 25 Jan 3. So columns 1 and 2 hold the first person. Column 3 holds his score. Columns 4 and 5 hold the second person. Column 6 holds his score. I want to return the most recent score for each person (be they an opponent or myself). And the resultant table shouldn't care if they are person 1 or 2. So the end result would be FName, LName, Score, Date John,Doe, 120Jan 5. John,Archer 90 Jan 5. Bob, Barker 70 Jan 8 Bill,Gates 25 Jan 3 Calvin Klien 8 Jan 8 Thanks for any help! First I would say you should have one person in a row and have another table to join them like you want. Try (untested just guessing) - select fnam1 as fname,lname1 as lname, score1 as score from myscorestable union select fnam2 as fname,lname2 as lname, score2 as score from myscorestable order by 3 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] How to test/read a stored procedure that returns a boolean?
I wrote this function but I'm not sure how to test it in PG Admin III
Query.
CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
CREATE TABLE handles (
handleID_pk serial PRIMARY KEY UNIQUE NOT NULL,
userID_fk integer UNIQUE NOT NULL,
handle text UNIQUE NOT NULL);
CREATE TABLE disallowedHandles (
handle text UNIQUE NOT NULL);
create or replace function IsUsedHandle(h text) returns boolean as $$
declare
begin
select COUNT(*) as num_matches from handles where handles.handle = h;
return num_matches > 0;
end;
$$ LANGUAGE plpgsql;
INSERT INTO handles (handle, userid_fk) VALUES ('blah', 0);
select * from IsUsedHandle('k');
Instead of true or false, it says
ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "isusedhandle" line 3 at SQL statement
If I wanted to call this query and get the boolean result in C++,
using PQgetvalue, how would I get this?
Thanks!
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] How to test/read a stored procedure that returns a boolean?
Thanks Tom!
Also, how do I check if a language is already created so I don't load
it twice?
"ERROR: language "plpgsql" already exists
SQL state: 42710"
Here is the code fixed.
/*
CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
CREATE TABLE handles (
handleID_pk serial PRIMARY KEY UNIQUE NOT NULL,
userID_fk integer UNIQUE NOT NULL,
handle text UNIQUE NOT NULL);
CREATE TABLE disallowedHandles (
handle text UNIQUE NOT NULL);
*/
create or replace function IsUsedHandle(h text) returns boolean as $$
declare
num_matches integer;
begin
num_matches := COUNT(*) from handles where handles.handle = h;
return num_matches > 0;
end;
$$ LANGUAGE plpgsql;
-- INSERT INTO handles (handle, userid_fk) VALUES ('blah', 0);
select * from IsUsedHandle('blah');
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] variables with SELECT statement
Hello All: I have a simple issue. Within my table there is a field DESCRIPTION that I would like to parse and split out into other fields. Within DESCRIPTION there are spaces that separate the data items. String_to_array(description, ' ') does the job very well. I need something like this to work. select string_to_array(description, ' ') as a_desc, a_desc[0] as name , a_desc[1] as type, a_desc[2] as size, from prodlist where type = 'B' Need to parse the DESCRIPTION and then reference the pieces. Your kind assistance is requested. Thanks Kevin Duffy WR Capital Management 40 Signal Rd Stamford, CT 203-504-6221
Re: [SQL] variables with SELECT statement
OK that is a syntax I have never seen. But correct we are getting close. Noticed that string_to_array does not handle double spaces very well. If there are double space between the tokens, there is "" (empty string) in the array returned. Not exactly what I expected. KD -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Frank Bax Sent: Friday, September 05, 2008 4:07 PM Cc: [email protected] Subject: Re: [SQL] variables with SELECT statement Kevin Duffy wrote: > Within my table there is a field DESCRIPTION that I would like to parse > and split out into other fields. > > Within DESCRIPTION there are spaces that separate the data items. > String_to_array(description, ' ') does the job very well. > > I need something like this to work. > > select string_to_array(description, ' ') as a_desc, > a_desc[0] as name , a_desc[1] as type, a_desc[2] as > size, from prodlist where type = 'B' You almost had it ... select a_desc, a_desc[1] as name, a_desc[2] as type, a_desc[3] as size from (select string_to_array(description, ' ') as a_desc from prodlist) as foo where a_desc[2] = 'B' -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
Just testing the regexp_string_to_array
This SQL
select description, regexp_string_to_array(description::text , E'\\s+' )
as optdesc, securitytype
from xx where type = 'B' order by 1
produced this error:
ERROR: function regexp_string_to_array(text, text) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may
need to add explicit type casts.
Character: 21
Don't see the difference between the above and the example in the doc's.
kd
-Original Message-
From: Osvaldo Kussama [mailto:[EMAIL PROTECTED]
Sent: Friday, September 05, 2008 4:47 PM
To: Kevin Duffy
Subject: Re: [SQL] variables with SELECT statement
2008/9/5, Kevin Duffy <[EMAIL PROTECTED]>:
> OK that is a syntax I have never seen. But correct we are getting
> close.
>
> Noticed that string_to_array does not handle double spaces very well.
> If there are double space between the tokens, there is "" (empty
string)
>
> in the array returned. Not exactly what I expected.
>
Try regexp_split_to_array().
http://www.postgresql.org/docs/current/interactive/functions-matching.ht
ml#FUNCTIONS-POSIX-REGEXP
SELECT regexp_split_to_array('the quick brownfox jumped over
the lazy dog', E'\\s+');
regexp_split_to_array
{the,quick,brown,fox,jumped,over,the,lazy,dog}
Osvaldo
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
No looks like I have 8.2 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Frank Bax Sent: Friday, September 05, 2008 5:13 PM Cc: [email protected] Subject: Re: [SQL] variables with SELECT statement Kevin Duffy wrote: > Just testing the regexp_string_to_array > > This SQL > select description, regexp_string_to_array(description::text , E'\\s+' ) > as optdesc, securitytype > from xx where type = 'B' order by 1 > > produced this error: > > ERROR: function regexp_string_to_array(text, text) does not exist > SQL state: 42883 > Hint: No function matches the given name and argument types. You may > need to add explicit type casts. > Character: 21 Are you running 8.3? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
Thanks Mr. Lane for catching that.
If I run
SELECT regexp_split_to_array('the quick brown fox jumped over the lazy
dog', \\s+');
Straight out of the documentation I get
ERROR: function regexp_split_to_array("unknown", "unknown") does not
exist
Let me guess I have to upgrade.
kd
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: Friday, September 05, 2008 5:27 PM
To: Frank Bax
Cc: [email protected]
Subject: Re: [SQL] variables with SELECT statement
Frank Bax <[EMAIL PROTECTED]> writes:
> Kevin Duffy wrote:
>> ERROR: function regexp_string_to_array(text, text) does not exist
> Are you running 8.3?
Also, it's regexp_split_to_array ...
regards, tom lane
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
When was 8.3 released? But for today I could do string_to_array(regexp_replace(description, E'\\s+', ' '), ' ') as desc and get what I need to survive. Many thanks for all the replys. Would not have made progress on this by myself. kd -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2008 5:35 PM To: Kevin Duffy Cc: [email protected]; Frank Bax Subject: Re: [SQL] variables with SELECT statement On Fri, Sep 5, 2008 at 3:28 PM, Kevin Duffy <[EMAIL PROTECTED]> wrote: > No looks like I have 8.2 I can attest that all of 8.3's performance improvements as well all of the really useful new functions like the one mentioned here make it well worth the effort to upgrade. I haven't been as excited about a pgsql version since vacuum (regular) was invented. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] simple SQL query
Hello: I have a couple of queries that are giving me headaches. They are actually very simple, but I do not understand why I am not getting the expected results. Maybe I need new glasses. Please be kind. The table definitions are below. The table TMP_INDEX_MEMBER contains 21057 rows. These rows contain 3167 distinct ISINs. ISIN is a type of unique security identifier. This query select * from security where securitytypekey NOT IN ( 5,27) and ISIN IN (select ISIN from tmp_index_member ) returns 3069 rows. This tells me that there are 3069 ISINs in the SECURITY table. ISINs that I already know about. update tmp_index_member set securitykey = security.securitykey from security where securitytypekey NOT IN (5,27) and tmp_index_member.ISIN = security.ISIN results in Query returned successfully: 20545 rows affected, 2169 ms execution time. There are now 512 row in TMP_INDEX_MEMBER that have not been updated. OK now the confusion begins. I would expect the following query to return 512 rows. It returns zero. select * from tmp_index_member tim where tim.ISIN NOT IN (select distinct sec.ISIN from security sec where securitytypekey NOT IN ( 5,27) ) I want to add to SECURITY the securities that are new to me. To do this I need the above query to work. Question: does a UNIQUE constraint create an index? Maybe your fresh eyes will see something obvious. Many thanks to taking a look at this issue. KD SECURITY - contains the list my in-house security list CREATE TABLE "security" ( securitykey serial NOT NULL, securitytypekey integer, securitydesc character varying(125), bbcode character(25), ric character(15), sedol character(15), cusip character(12), isin character(15), securityissuecurriso character varying(3), underlyingcusip character varying(15), ticker character(30), underlyingisin character varying(15), expirationdate date, strikeprice numeric(19,6), put_call character(1), multiplier integer, createdate timestamp without time zone DEFAULT now(), ccy1isocode character(3), ccy2isocode character(3), contractdate date, fwdrate numeric(15,8), contract character(25), contractsize integer, unitprice numeric(10,6), underlyingticker character(20), underlyingbloomberg character(20), couponrate numeric(15,8), maturitydate date, exchangekey integer, CONSTRAINT pk_security PRIMARY KEY (securitykey), CONSTRAINT fk_security_securitytype FOREIGN KEY (securitytypekey) REFERENCES securitytype (securitytypekey) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT unq_security_cusip UNIQUE (cusip, securitytypekey, securityissuecurriso), CONSTRAINT unq_security_isin UNIQUE (isin, securitytypekey, securityissuecurriso) ) WITHOUT OIDS; ALTER TABLE "security" OWNER TO postgres; -- Index: security_bbcode -- DROP INDEX security_bbcode; CREATE INDEX security_bbcode ON "security" USING btree (bbcode, securitytypekey); -- Index: "security_sectype_isoCurr" -- DROP INDEX "security_sectype_isoCurr"; CREATE INDEX "security_sectype_isoCurr" ON "security" USING btree (securitytypekey, securityissuecurriso); TMP_INDEX_MEMBER - contains the members of indexes such as S&P 500 and Russell 1000 CREATE TABLE tmp_index_member ( tmp_index_member_key serial NOT NULL, index_key integer, taskrunkey integer, isin character(15), cusip character(12), sedol character(12), bbcode character(15), curr character(5), bbtype character(20), secweight numeric(19,6), securitykey integer, gics_sector integer, gics_sector_name character(75), gics_industry_group integer, gics_industry_group_name character(75), gics_industry integer, gics_industry_name character(75), bbdesc character(50), CONSTRAINT pk_tmp_index_member PRIMARY KEY (tmp_index_member_key), CONSTRAINT fk_tmpindexmember_index_ FOREIGN KEY (index_key) REFERENCES index_ (index_key) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_tmpindexmember_taskrun FOREIGN KEY (taskrunkey) REFERENCES taskrun (taskrunkey) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITHOUT OIDS; ALTER TABLE tmp_index_member OWNER TO postgres; Kevin Duffy WR Capital Management
Re: [SQL] simple SQL query
Gentlemen: Thanks so much for your assistance. This returns 512 rows. select * from tmp_index_member tim where tim.ISIN NOT IN (select ISIN from security sec where ISIN is NOT NULL and securitytypekey IS NOT NULL and securitytypekey NOT IN ( 5,27) ) Can someone explain why the NULL ISINs in Security is causing so much grief? I do not get it. KD -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andreas Joseph Krogh Sent: Wednesday, October 29, 2008 3:58 PM To: [email protected] Subject: Re: [SQL] simple SQL query On Wednesday 29 October 2008 18:39:42 Kevin Duffy wrote: > Hello: > > > > I have a couple of queries that are giving me headaches. > > They are actually very simple, but I do not understand why > > I am not getting the expected results. Maybe I need new glasses. > > Please be kind. > > > > The table definitions are below. > > > > The table TMP_INDEX_MEMBER contains 21057 rows. > > These rows contain 3167 distinct ISINs. ISIN is a type of unique > security identifier. > > > > This query > > select * from security > >where securitytypekey NOT IN ( 5,27) and ISIN IN > > (select ISIN from tmp_index_member ) > > returns 3069 rows. This tells me that there are 3069 ISINs > > in the SECURITY table. ISINs that I already know about. > > > > > > update tmp_index_member set securitykey = security.securitykey > >from security > >where securitytypekey NOT IN (5,27) and tmp_index_member.ISIN = > security.ISIN > > results in Query returned successfully: 20545 rows affected, 2169 ms > execution time. > > > > There are now 512 row in TMP_INDEX_MEMBER that have not been updated. > > OK now the confusion begins. > > > > I would expect the following query to return 512 rows. It returns zero. > > select * from tmp_index_member tim > > where tim.ISIN NOT IN > > (select distinct sec.ISIN from security sec where securitytypekey NOT > IN ( 5,27) ) > > > > > > I want to add to SECURITY the securities that are new to me. To do this > I need the above > > query to work. I bet you have NULLs in some of the rows so your "NOT IN" doesn't work. I suggest you rewrite to something like: ... WHERE (securitytypekey IS NOT NULL OR securitytypekey NOT IN (5,27))... > Question: does a UNIQUE constraint create an index? Yes. > Maybe your fresh eyes will see something obvious. > > Many thanks to taking a look at this issue. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / CEO +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] inserts within function, within insert
Hello All: I am hoping to get some guidance from the local gurus, before I go off and write this code. Consider this scenario: I receive a file containing inventory info, make it simple, clothes items. Within the file there is info for different locations, so an inventory item can occur more than once. I.e. a certain jacket could be in the inventory at more than one location. It is also possible that an inventory item in the file could be a new item, an item that is not in my item table. When I parse and import the table there will be enough info provided, so that I can create the new inventory items. I would like to write a function that based upon the info provided for an inventory item will either return the item's key or if the item is new, create the item and then return the item's key. Say I am doing a simple insert to inventory like the following: INSERT INTO INVENTORY ( itemkey, locationkey, qty) SELECT getitemkey(itemtype, style, color, size), lockey, qty from IMPORT_INV Question: New records added to the ITEM table within getitemkey(), will they be available to inserts that follow? I.e. if record 2 in IMPORT_INV is for the same clothing item as record 27, and this a new inventory item, but for different locations. Will this new inventory item get added twice to my ITEM table. Many thanks for considering this issue. Kevin Duffy
[SQL] function - string ends with
Hello All: Is anyone aware of a function in PgSQL that will check if a string ends with a specific string? I.e. rposition(substring in string ) returns int Starts searching right to left within string Thanks for your attention to this matter. Kevin Duffy
Re: [SQL] function - string ends with
Take a look at LIKE or ILIKE kd From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kevin Duffy Sent: Friday, November 21, 2008 11:31 AM To: [email protected] Subject: [SQL] function - string ends with Hello All: Is anyone aware of a function in PgSQL that will check if a string ends with a specific string? I.e. rposition(substring in string ) returns int Starts searching right to left within string Thanks for your attention to this matter. Kevin Duffy
[SQL] simple text parsing function
Hello All:
I am requesting your kind assistance with a simple text parsing
function.
The source code for the function is below. What is interesting is that
the
path through the code that actually does something works. The simple
path through the code fails.
For example
select getrfs_bbcode('CHF/USD 12/17/2008 CURNCY ', null, null);
returns "CHFUSD CURNCY " this is correct.
select getrfs_bbcode('BPZ8 CURNCY ', NULL, NULL);
returns nothing. I need to receive 'BPZ8 CURNCY ' in this
case.
What am I missing?
Many thanks for your assistance.
KD
CREATE OR REPLACE FUNCTION getrfs_bbcode( bbcode_in bpchar,
sectype_bbsuffix_in bpchar, sectypekey_in integer )
RETURNS character AS
$BODY$
DECLARE
rtn_bbcode char(25);
tmp_bbcode char(25);
abbcode char(10)[3];
BEGIN
tmp_bbcode := upper( rtrim(bbcode_in ) );
--
if tmp_bbcode like '%CURNCY' then
if strpos( tmp_bbcode, '/' ) > 0 then
-- Is there / in the BB Code
abbcode := string_to_array(tmp_bbcode, ' ');
rtn_bbcode := replace(abbcode[1], '/', '') ||' '|| abbcode[3];
else
rtn_bbcode := tmp_bbcode; -- simple pass though case does
not work?!?!
end if;
end if;
return rtn_bbcode ;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Kevin Duffy
[SQL] where-used function
Hello All: Hope everyone is enjoying a peaceful holiday season. I am using this quite time between the holidays to get my dev and production environment back into sync. It would be very helpful, if I had a where-used function. This function, given an object name, would determine if the object exists in the database ( i.e. is it a table or a view) and then, most important, what other objects are dependant on the object. A database I worked with years ago, distributed by a marketing company in the NorthWest USA, had such a function. As I remember that function was buggy. Does anybody know if a where-used function exists for the wonderful database known as Postgres? Many Thanks Kevin Duffy
[SQL] simple SQL question
Hello All: I would like your input on how I should approach a problem. Say I have a table of companies and one attribute is the market capitalization of these companies. I have another table (definition below) and it contains capitalization levels. For example Micro Cap, Mid Cap, and Large Cap.However, the table CAPITALIZATIONLEVEL, only contains the upper cutoff of the levels. The question is: What is the most efficient way to assign/join the capitalization levels to the companies? I could create a function that given a market cap in millions would return the matching cap level, by using a cursor to step through CAPITALIZATIONLEVEL from lowest to highest. This function would be declared STABLE. -or maybe- a function that RETURNS SETOF and the rows in the set returned would contain both the lower and upper limits of the cap level. The lower limit would be calc'ed by using a cursor to step through CAPITALIZATIONLEVEL from lowest to highest. This function would be declared STABLE. Which method would execute more efficiently? Thanks for considering my issue. Kevin Duffy CREATE TABLE capitalizationlevel ( capitallevelkey serial NOT NULL, caplevelname character(10) NOT NULL, caplevelmillions integer NOT NULL, <-- this is the upper limit CONSTRAINT pk_capitalizationlevel PRIMARY KEY (capitallevelkey) ) WITH (OIDS=FALSE); ALTER TABLE capitalizationlevel OWNER TO postgres;
Re: [SQL] simple SQL question
Mr. McFadyen: Thanks for your quick reply. The coloumns in the SECURITY Table of interest would be: securitykey serial NOT NULL, securitytypekey integer, securitydesc character varying(125), marketcap_usd numeric(19,6) where marketcap_usd would be used to join into capitalizationlevel. I was hoping to create something, how shall we say, tidy". So I could write something like Select SECURITY.* , getcaplevelkey( marketcap_usd ) From security Or Select security.*, caplevelkey, caplevelname From SECURITY, getcaplevel() as gcl Where marketcap_usd between ( gcl.caplow and gcl.caphigh ) I would expect that in the first option the function would be called for every security and would have to loop through a cursor for each call. In the second option where the function is returning a SETOF, how many times would it be called? If I put it in parentheses, how many times would it be called? Any comments from the PostgresSQL gurus out there? Happy Friday. KD From: Dan McFadyen [mailto:[email protected]] Sent: Friday, March 20, 2009 3:51 PM To: Kevin Duffy Subject: RE: [SQL] simple SQL question Hi again, You got me curious so I went into my SQL browser and got cracking: SELECT companyName, capName FROM ( SELECT companyName, MIN(capitalizationlevel.caplevelmillions) FROM companies, capitalizationlevel WHERE companymillions < capitalizationlevel.caplevelmillions GROUP BY companyName ) as something JOIN capitalizationlevel ON something.min= capitalizationlevel.caplevelname I don't know what your company table looks like, so that's the closest I can get. Enjoy From: [email protected] [mailto:[email protected]] On Behalf Of Kevin Duffy Sent: March 20, 2009 3:06 PM To: [email protected] Subject: [SQL] simple SQL question Hello All: I would like your input on how I should approach a problem. Say I have a table of companies and one attribute is the market capitalization of these companies. I have another table (definition below) and it contains capitalization levels. For example Micro Cap, Mid Cap, and Large Cap.However, the table CAPITALIZATIONLEVEL, only contains the upper cutoff of the levels. The question is: What is the most efficient way to assign/join the capitalization levels to the companies? I could create a function that given a market cap in millions would return the matching cap level, by using a cursor to step through CAPITALIZATIONLEVEL from lowest to highest. This function would be declared STABLE. -or maybe- a function that RETURNS SETOF and the rows in the set returned would contain both the lower and upper limits of the cap level. The lower limit would be calc'ed by using a cursor to step through CAPITALIZATIONLEVEL from lowest to highest. This function would be declared STABLE. Which method would execute more efficiently? Thanks for considering my issue. Kevin Duffy CREATE TABLE capitalizationlevel ( capitallevelkey serial NOT NULL, caplevelname character(10) NOT NULL, caplevelmillions integer NOT NULL, <-- this is the upper limit CONSTRAINT pk_capitalizationlevel PRIMARY KEY (capitallevelkey) ) WITH (OIDS=FALSE); ALTER TABLE capitalizationlevel OWNER TO postgres; The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. Please see our legal details at http://www.cryptocard.com <http://www.cryptocard.com> CRYPTOCard Inc. is registered in the province of Ontario, Canada with Business number 80531 6478. CRYPTOCard Europe is limited liability company registered in England and Wales (with registered number 05728808 and VAT number 869 3979 41); its registered office is Eden Park, Ham Green, Bristol, BS20 0EB
[SQL] Re: SQL problem
Here's one approach:
create view vw_maxrain as select max(rain) as rain, date_part('year',day) as
year from meteo group by year;
select day, meteo.rain from meteo, vw_maxrain where
meteo.rain=vw_maxrain.rain;
"Salvador Mainé" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> Hello:
>
> I have a table with pluviometrical data
>
> meteo (rain float, day date)
>
> I want to select the the day of maximum value for each year.It should be
> something like :
>
>
> select max(rain),day from meteo group by date_part('year', day);
>
>
> but it obiously doesn't work.
> I thought of doing it with aggregates, but it's far too complicated. Is
> there an 'easy' way to do this?
>
>
>
>
> --
> Salva
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
