[SQL] Sum of datetime différence...
Hi, I would like to know what is the easiest way to add multiple difference of datetime but show the result in hour and minute something like : table foo: start datetime end datetime I would like to do : select sum(end-start) from foo; but it gives me 3 days 12 hours 23 minutes...etc I would like to see 84 hours and 23 minutes... How can I do this? PS: Is there a way to know which week of the year a current day is in? Frédéric Boucher [EMAIL PROTECTED]
[SQL] \copy...
Hi, I have a file that look like this : firstname|lastname|[EMAIL PROTECTED] firstname2|lastname2|[EMAIL PROTECTED] and a table foo like : firstname varchar(30), lastname varchar(30), emailvarchar(50) and I would like to do a : copy tablename from '/home/ed/import.dat'; But then, everything goes in the first field... what does i'm doing wrong?!?! Frédéric Boucher [EMAIL PROTECTED] Programmation, Support technique Jetumele Communications inc.
[SQL] Simply append...
Hi, I would like to append 2 simple query's result but they are ordered completly different like : select * from foo order by col1 asc union select * from foo2 order by col2 desc how could I do this? When I use union I can't use order by clause. Is there a way to tell "simply append bot results in given order?" thanks Frédéric Boucher [EMAIL PROTECTED]
[SQL] Corruption... please help
Hi... I am unable to select every row from a table. Every time, the backend disconnect. When I do a : The_DB=> vacuum; I get : ERROR: Invalid XID in t_cmin (2) What does it meens and how can I recover from it? Frédéric Boucher [EMAIL PROTECTED]
[SQL] Error : Unknown address family (0)
Each time I try to do an pg_dump or pg_dumpall I receive this message : Error : Unknown address family (0) What can I do to resolve this? Or at least what does it means? Thanks a lot! Frédéric Boucher [EMAIL PROTECTED]
Re: [SQL] Scheduling Events?
On Thursday January 23 2003 5:16, David Durst wrote: > Is there anyway to schedule DB Events based on time? If you're using one of the unices (linux, etc.), how about... crontab + perl + DBI + DBD::Pg? or crontab + bash/sh + psql + pl/pgsql? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] integrity of column used to order rows
On Wednesday March 19 2003 9:18, [EMAIL PROTECTED] wrote: > > What I want to be able to do is make sure that at all times the child > records linked to a parent record have values for the "rank" field that > are consecutive starting at 1, ie (1,2,4,5,6) not (1,2,4,5,7). > > Can someone offer the best suggestion to do this? This is certainly possible via a triggered PL/pgSQL function. Of course, whether or not it is feasible w/r/t performance or exactly how you choose to set the ranks depends on your context. If you have appropriate indices on the foreign key to the parent table, the overall size of the table is probably irrelevant; use of the index would allow you to avoid traversal of the entire table. You also have the option of writing the function in C to get better performance. Ed ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PG/DBI: 'NOTICE: UserAbortTransactionBlock and not in in-progress state'
Markus Wagner wrote: > > Hi, > > I am using PG 6.5.1 and DBI. My Perl programs always print to stderr: > > "NOTICE: UserAbortTransactionBlock and not in in-progress state" or > "NOTICE: EndTransactionBlock and not inprogress/abort state" > > I tried to do -> finish and -> commit after each > query, but the messages still appear. > > Any hints? I think that happens if the backend aborted (elogged) with an ERROR message and you did not start a new transaction. ERROR kills the current transaction altogether in 6.5.*, IIRC. Regards, Ed Loehr
Re: [SQL] POSTGRESQL and PERL?
Peter Landis wrote:
>
> but what if you have a variable set like:
> $sort_selection = "name";
>
> How do you sort by the variable?
>
> For instance you cannot do...
> $sqh = $dbh->prepare(q{select name from company order
> by $sort_selection;});
Why not? The query string is created before prepare is called...
Regards,
Ed Loehr
Re: [SQL] psql problem
What is the definition of the table 'some_table'?? Regards, Ed Loehr Rick Parker wrote: > > Does anyone know why when I am in a particular DB as user postgres and use > the following statement, why I get this error?" > > This is the statement; > SELECT * FROM some_file where ID = 1; > > > Error: ERROR: attribute 'id' not found > Execution time 0.02 sec. > > But if I use the following statement, everything is returned fine. > > SELECT * FROM servlet_file; > > Thanks ahead of time, > > Rick Parker
Re: [SQL] how to know when a table is altered
Vincenzo Passoli wrote: > > i'm developing a framework (mod_perl+apache) that reads the db-schema and > explode html forms. > > now i read the schema and cache it into perl-hashes to speedup things. > > my problem is to recognise when a table is altered so that the framework can > update the related forms connected to the db tables. > i don't want to read the schema every time. > > How can i implement this ? My sub-optimal approach was to cache all of the generally static tables (requiring a restart to reload them if they changed), and query the rest. You can avoid a lot of joins by querying the db for the foreign keys to static tables and then looking them up only in the app cache. But caching query results and invalidating them when the underlying tables changed would greatly simplify my app and speed things up, so I'd love to hear if others have a better/faster solution here. Regards, Ed Loehr
Re: [SQL] oracle rownum equivalent?
mikeo wrote: > > is there an equivalent to rownum in oracle? > > also, where can one find reference to "hidden columns" > such as OID or, as in oracle, rownum? oid is the equivalent. not sure documentation exists for these... Regards, Ed Loehr
Re: [SQL] how to know when a table is altered
Ed Loehr wrote: > > Vincenzo Passoli wrote: > > > > i'm developing a framework (mod_perl+apache) that reads the db-schema and > > explode html forms. > > > > now i read the schema and cache it into perl-hashes to speedup things. > > > > my problem is to recognise when a table is altered so that the framework can > > update the related forms connected to the db tables. > > i don't want to read the schema every time. > > > > How can i implement this ? > > My sub-optimal approach was to cache all of the generally static tables > (requiring a restart to reload them if they changed), and query the > rest. You can avoid a lot of joins by querying the db for the foreign > keys to static tables and then looking them up only in the app cache. > But caching query results and invalidating them when the underlying > tables changed would greatly simplify my app and speed things up, so I'd > love to hear if others have a better/faster solution here. I was thinking about another possible approach (and definitely half-baked). I'd call it "table-based caching". Suppose you created a table specifically for tracking how recently a table had been updated, e.g., create table table_status ( tablename varchar not null unique, last_change timestamp not null ); Then create triggers for every table that updated table_status.last_change = now() on every UPDATE/DELETE/INSERT. Then, to determine when you need to invalidate the application cache, you'd load this table at the beginning of the request and invalidate cache entries involving tables with table_status.last_change more recent than when the query results were cached. If, like most DBs yours is mostly reads, you'd suffer one pretty light DB query in order to validate your cache on each request. Then, each That would be a significant hit on big changes involving many records. But where that's unusual, it might be a big win. There are a lot of gotchas with this approach (figuring out the query-table dependencies, etc.), but it seems possible. BTW, I thinking server-side caching is the optimal solution here. I previously lobbied -hackers for implementing a server-side result-set cache in which entire query result sets could be cached (up to a configurable limit) and returned immediately when none of the underlying tables had changed (http://www.deja.com/viewthread.xp?AN=602084610&search=thread). I still think that would be a huge performance win in the vast majority of systems (including mine), but it is not supposedly not trivial. The idea won absolutely no fans among the developers/hackers. There was some talk about caching the query plans, but I think that ultimately got dismissed as well. I wish I had time to work on this one. Regards, Ed Loehr
Re: [SQL] oracle rownum equivalent?
mikeo wrote: > > thanks for the response. oid is equivalent to oracle rowid. > rownum can be used similar to the limit option of select in postgres > but in oracle it can also be used in a where clause, or as an assigment > in an update statement for instance. > > eg: update ctmaster set bsc_id = mod(rownum,3) +1; > > this gives me a way to assign streams to rows in a load balanced manner > on the fly, for example. i use it in other more involved ways than this > also. i cannot do this with limit. i could do this with sequence with > a max value but i'd have to define a sequence each time i wanted to do > something "on the fly" or for what ifs. > > what i'm also interested in is how to find reference to these type of > pseudo-columns, even just the names of them, if they're listed somewhere. I think this might be the list, but you might query pgsql-hackers for more info. There was a recent thread involving this.. ctid oid xmin (minimum transaction number) xmax cmin (minimum command number) cmax ctid may be what you're looking for, but I don't understand very well how these are used. Maybe someone else can say or you can experiment... Regards, Ed Loehr (PS: Posting to only one of -general or -sql will almost always be sufficient.)
Re: [SQL] how to know when a table is altered
Vincenzo Passoli wrote:
>
> 3.SQL does't have a TRIGGER on this 'event' (CREATE TRIGGER mytrig ON
> mytable FOR ALTER AS ...). Can be Added ?
I don't know. Maybe someone else does (though I think pgsql-sql is very
low volume...pgsql-general would get a lot more readers).
> 4.May be beautiful if the db tells to the app when a trigger is fired, so
> the app can update thing without go crazy with asking that to the db every
> time. Is there a solution?
Maybe. Check out NOTIFY (and LISTEN) at
http://www.postgresql.org/docs/postgres/sql-listen.htm
http://www.postgresql.org/docs/postgres/sql-notify.htm
I haven't tried it, not sure it fits into DBI's API or model. I'd like
to hear if you use it with success (or anyone else who is already using
it successfully within modperl/DBI).
If it turns out that DBI can't handle LISTEN/NOTIFY, it might be possible
to do it through a 3rd app that somehow listens and signals the modperl
servers (yuck).
> 5.For the query table dependencies (a proposal, i've not used this
> solution!):
>
> $sql= "select a.f1,a.f2,b.f3,c.f4 from t1 as a, t2 as b, t3 as c where
> "
> we can extract the tables used in a query
>
> instead of writing $sql=as before, write a thing similar to (supposing
> DBI+perl+mod_perl)
>
> my @array;
> my $ptr_array= \@array;
>
> $sql = "select a.f1,a.f2,b.f3,c.f4 from
> ".&add_check_table('t1',$ptr_array)." as a,".
>&add_check_table('t2',$ptr_array) . " as b,
>
> ---> &add_check_table=sub to push table to check in the array @array, return
> the name of the table, i.e. t1, t2 ...
>
> then
>
> call &do_check ($ptr_array)
> using table_status, the sub do_check return 1 if min(last_changes for every
> table in @array) is older that the caching of this query results, we must
> have the query result somewhere (on ( properly locked) files?) and the last
> time we perfomed the query.
>
> then
>
> if (&do_check($ptr_array)){
>fetch rows
>store in cache
> }
> -->use the cache
I haven't seen that syntax before with your use of "as", but I get your
gist. Sounds reasonable, though it looks like a major pain, stealing
most of the pleasure and convenience of SQL. I'd almost be tempted to
build a regex'er to pick out the table names from each query in a layer
between DBI and the app until the regex performance became an issue.
Regards,
Ed Loehr
Re: [SQL] Newbie question on how to check triggers created
Chunky wrote: > > Could someone please enlightenment me what command in psql i should use > to show the various triggers and rules that i have created? These might work... select tgname from pg_trigger select rulename from pg_rewrite Regards, Ed Loehr
Re: [SQL] Outer join in postgresql
Patrick Kay wrote: > > I am looking for a way run an outer join in psql. Can anyone help? > > Informix has an "OUTER" keyword. I don't see anything like this in the docs > for psql. There are many examples on how to do this in the archives or via deja.com's power search. Regards, Ed Loehr
Re: [SQL] Type conversion
Tom Lane wrote: > > Ice Planet <[EMAIL PROTECTED]> writes: > > B: insert into t2 values (select int2(int4(b)) from t1 where a = 1) > > Works for me when spelled correctly: I think you can also leave out the 'values' for a sub-select insert, though I haven't checked to see if it matters... Regards, Ed Loehr
Re: [SQL] Wildcard in date field???
Web Manager wrote:
>
> I need to make a query that will select items that have a date matching
> the current month for example.
>
> For June this means that any day between 2000-06-01 and 2000-06-30 are
> ok. To do that I need a wildcard like "%" to replace the actual day in
> the date field.
>
> Ex.: select * from item where date = '2000-06-%%';
Multiple ways to do it, but here's one:
select *
from item
where date_part('month',mydate) = 6
and date_part('year',mydate) = 2000;
Regards,
Ed Loehr
Re: [SQL] timespan casting
Jeff MacDonald wrote: > bignose=> select start,stop, stop-start as start_stop from foo; > start | stop | start_stop > ++ > 2000-06-22 20:37:12-03 | 2000-06-22 20:37:12-03 | 00:000 > > now my question.. first of all is the first start_stop result a little > off ? (talking about the 3 0's..) What's off about it?? Your start and stop appear identical. > second is there a way i can do a select so it says something more human > usable ie : 100 days 22 hours 32 minutes.. Try to_char(). http://www.postgresql.org/docs/postgres/x2976.htm Regards, Ed Loehr
Re: [SQL] A subselect in an aggregate
Bryan White wrote:
>
> This statement works:
> select date, (select sum(qty * price) from orderdetail d where d.orderid =
> orders.orderid) from orders
>
> But when I try to do something like this:
>
> select date, sum(select sum(qty * price) from orderdetail d where d.orderid
> = orders.orderid) from orders group by date
>
> I get ERROR: parser: parse error at or near "select"
>
> Is there a way to apply an agregate function to a subselect like this?
Avoiding the question, I'm wondering if this simpler form wouldn't be
what you're after?
select o.date, sum(d.qty * d.price)
from orderdetail d, orders o
where d.orderid = o.orderid
group by o.date
Regards,
Ed Loehr
Re: [SQL] pg_dump problem
Graham Vickrage wrote: > > I am trying to backup a production database running on v6.5 and restore it > on our test machine running v6.5. > > The largest table has about 750,000 rows, the other 5 tables are very small > approx 100 rows. > > When I try to restore the database using "psql -e database < db.out" I get > the error message > "query buffer max length of 16384 exceeded" after each row. > > Would somebody please tell me how to increse this buffer (assuming this is > whats required to solve the problem) as I have looked though the > documentation and I am still struggling :-( You probably don't need to increase the buffer. More likely, there is an unterminated string constant somewhere in db.out. Yes, that would probably be a bug in pg_dump, but I do vaguely recall problems in 6.5* with pg_dump. You might search deja for your error message. Regards, Ed Loehr
Re: [SQL] SERIAL type does not generate new ID ?
Jean-Marc Libs wrote:
>
> I have this table:
>
> CREATE TABLE film (
>film_id serial,
> ...
>
> SELECT setval ('film_film_id_seq', 6);
>
> I have this query in PHP:
> insert into film
>
>(film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
> values ('FR','','','','2000','f','f','f','f','f')
>
> And it gives the following error:
> ERROR: Cannot insert a duplicate key into a unique index
You should not have to do anything special with the serial or the
sequence (including setting it to 6). Possible sources for for your
error: 1) you are resetting the sequence value to 6 when you already
have a row with that value for film_id in the table, or 2) could be the
message is coming from a triggered insert "downstream" from your initial
insert (see your server log). BTW, 7.0+ tells you *which* index caused
the problem.
Regards,
Ed Loher
Re: [SQL] Timestamp problem
Bernie Huang wrote: > > table > - > ... > borrow timestamp > return timestamp > > Now, I thought timestamp is in sec eg; 35434434, but in PostgreSQL, it > shows up as datetime datatype eg; 2000-06-07 17:00:05-07. > > I was wondering is this format a correct one for timestamp, or is it a > bug? Abd if I want to show the time, each retrieval I have to chop of > the '-07' timezone at the end. Is there a way to avoid this being show > up in Postgres? You might want to check out the date/time and formatting functions at http://www.postgresql.org/docs/postgres/index.html Regards, Ed Loehr
Re: [SQL] Re: Matching and Scoring with multiple fields
Tim Johnson wrote: > > I have a table like this: > > a,b,c,d,e,f,g,h > --- > 2,5,3,4,4,5,2,2 > 1,1,1,1,1,1,1,1 > > a to h are of type int. > > I want to take input values which relate to this table say: > how do you feel about a: > how do you feel about b: > how do you feel about c: > ... > > and the answers will be 1 to 5. > > Now I want to take those answers for my incoming a to h and scan down the > table pulling out the closest matches from best to worst. I wonder if you don't really just want to find the vector(s) closest in N-space to the input vector. You might dig up an old 3-variable calculus book, find the formula, and write a PL/pgSQL function to compute the distance between two N-dimensional vectors... Regards, Ed Loehr
[SQL] selects on differing subsets of a query
First post, be gentle as I have terminology problems and so the subject might be wrongly worded. Say I have a table with fields ... gender diet_pref ... What I am trying to construct is a *single* query showing the total number of males in the table and also the total number of male vegetarians in the table, i.e. the 2nd value is computed on a subset of the records needed for the first value. As 2 queries this would be: select count(*) from mytab where gender='m' select count(*) from mytab where gender='m' and diet_pref='veg' The table is big and I'd like to do the select where gender='m' only once. (In the actual situation the select is on a date range) If there is a TFM, please point me at it with an indication of exactly what it is I am trying to achieve. If I'm trying to do something stupid, gentle advice would be appreciated. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] selects on differing subsets of a query
Hello Markus, It's actually a temporary mailbox just in case the list attracts spam :-) Thank you for your help, I will study it when I get development time on the database. On 03/05/06, Markus Schaber <[EMAIL PROTECTED]> wrote: Hi, Ed Temp, [EMAIL PROTECTED] wrote: > First post, be gentle as I have terminology problems and so the > subject might be wrongly worded. Hmm, the biggest mistake seems that "et temp 01" is a very unlikely real name, so you should reconfigure your mail client :-) > What I am trying to construct is a *single* query showing the total > number of males in the table > and also the total number of male vegetarians in the table, i.e. the > 2nd value is computed on a subset of the records needed for the first > value. > > As 2 queries this would be: > select count(*) from mytab where gender='m' > select count(*) from mytab where gender='m' and diet_pref='veg' Have you tried to UNION ALL the two queries? > The table is big and I'd like to do the select where gender='m' only > once. (In the actual situation the select is on a date range) SELECT count(*),diet_pref='veg' FROM table WHERE gender='m' GROUP BY diet_pref='veg' Is not exactly what you want, as your application still has to add two numbers to get the total result, but avoids the duplicated table scan. SELECT count(*),count(nullif(diet_pref='veg', f)) FROM table WHERE gender='m' Should also give you both counts, this time in different columns, also avoiding the duplicated table scan. It relies on the fact that count(something) is only called if something is not null, whereas count(*) is called for every row (as a special case). HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
