Re: [SQL] reading WAL files in python
On Mon, 2008-01-07 at 10:19 -0300, Gerardo Herzig wrote: > Hi all. Im having some fun trying to write my own replication system > using python. I will use the postgres own WAL archiving to write the > files, then my app will read them and do some stuff. As im not a C > programmer, im stuck in the mission of reading the binary files. > > I guess im needing to know the internals of how wals archives are, how > big the buffer has to be, in order to have a complete sql command. > > Can someone point some advice? > I was trying to understad src/backend/access/transam/xlog.c, but seems > too difficult to me :( > imho you can save yourself a lot of time by taking a look at skytools[1] as it may be more along the lines of what you're trying to accomplish anyway. Cheers, ./C [1] https://developer.skype.com/SkypeGarage/DbProjects/SkyTools ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] benchmarks
hi all, lately at work there has been a debate over mysql versus postgres im just looking for independent benchmarks i personally love postgres at work they like mysql currently we are investigating other possible db solutions and they are looking at oracle, i think we could save a lot of dollarsz if we decided to go to postgres i was wondering if anyone can share links to any current independent benchmarks as i would like some real data on these or at the very least give me a how to so i can do my own testing! thanks!
[SQL] Returning records from a function
I've spent the last few hours trying to come up with a function that at the most basic returns the result of a SELECT * FROM . This is easy to do in something like MSSQL or Oracle. For example in MSSQL i can do something like: CREATE PROCEDURE proc_name AS BEGIN SELECT * FROM sometable END How do I do something like that with a Postgresql function/stored procedure? All I really want to know is how to by using a function how can I return the results of a SELECT * query!? The end result of the query I need is more complicated, but I can't even get something that easy to work. Are functions unable to return result sets?? All of the examples i've seen always return a single value, like an int. That doesn't do me any good. I want to use a few stored procedures to create some faster processing for some queries that involve 3-4 tables plus require some input parameters, and return a result set. Any help would be appriciated. I've just recently started using PostgreSQL and I'm very impressed! I'm sure I'm jsut overlooking something easy, but for the life of me i just cant figure it out. Thanks, John ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Returning Multiple Values from CASE statement?
Hello, This is probably an odd request, but I have my reasons :) Basically, what I need to do is return *multiple* values each for a number of criteria. Imagine being able to return multiple values for each CASE statement... that's what I'm trying to do. I can solve this with subqueries, but as you'll see it's *really* ugly, and I'm sure there's a better way (performance is key). The other way obviously is to repeat the CASE statement for each value I want to return but that seems inefficient also. So I was wondering if someone could take a look at my query and slap me up side the head and set me straight? Here's the kind of query that would *like* to be able to do. select (case when column1 = column2 then column5,column6) as alias5,alias6, (case when column3 = column4 then column7,column8) as alias7,alias8from mytable; and here's the only ugly way I know how to do it.. select a.*, (case a.alias2 = true then b.column5) as alias5, (case a.alias2 = true then b.column6) as alias6, (case a.alias4 = true then b.column7) as alias7, (case a.alias4 = true then b.column8) as alias8from (select (case when column1 = column2 then true else false end) as alias2, (case when column3 = column4 then true else false end) as alias4 from mytable) a, mytable bwhere a.id = b.id; Thanks!! Do you Yahoo!? Free online calendar with sync to Outlook(TM).
[SQL] SQL Help
Hello, I already tried this same basic question with no response maybe I was too wordy. So here it is simplified what's the best way to write this query? I'm open to using stored procedures, but even then I don't know how I would conditionally populate a resultset (refcursor). Notice that in the first three cases, the _expression_ is the exact same, only the return value is different. This seems inefficient select (case when column1 = column2 then column3 end) as alias1, (case when column1 = column2 then column4 end) as alias2, (case when column1 = column2 then column5 end) as alias3, (case when column6 = column7 then column8 end) as alias4from mytable; Any ideas? Thanks! Do you Yahoo!? Free online calendar with sync to Outlook(TM).
Re: [SQL] CASE returning multiple values (was SQL Help)
I was afraid someone was going to ask that :) Okay, I'll do my best at explaining where I'm coming from I'm working on a mapping application it is user-configurable. What this means (as it pertains to this disucssion) is that the through a configuration file, the user is able to define the rules that the application will use to determine which geometries *and* attributes to pull from PG at various scales. These 'rules' that the user defines also contain other specifics such as how to symbolize the geometry, how to label the geometry etc. All of these parameters can either be hard coded by the user into the configuration file, or they can define an _expression_ that will be used to dynamically pull it from the database. On top of all of this, we have two more levels of queries. So... at the very top level, the user can define an _expression_ that will determine that everything queried from this table will match these criteria... this is my WHERE clause. Then below this level, various rules can be defined... each rule can have another definition that evaluates into a SQL _expression_. Now, I could make each rule an entirely separate query, but for one, they all share the exact same top level WHERE clause, and two, there could potentially be many many rules which I would think would cause severe performance issues. Let me give you an example... Let's say we're mapping cities of the United States based on population... In other words, I want to symbolize the cities on the map based on population (larger symbol for larger populations, smaller symbol for smaller populations, etc). I also want to show the city names of the larger cities *only*. So, what the client application (client to PostgreSQL) needs is; the city location, which rules evaluate to true, and the city names of those larger cities (defined by a rule). We have a table of cities of the world. So the top level filter (that all rules will share) is, "COUNTRY = 'USA'". Rule 1 says that cities with a population over 1,000,000 will have a large symbol and be labeled with the city name. So the sql could look like this... select longitude, latitude, city_name from city where country = 'USA' and population > 100; ... seems easy enough, but remember we can have an infinite number of rules (not really inifinite, but you get the point). So Rule2 says that cities with a population under 1,000,000 will have a small symbol (note, we do not care about the city name here). So, by itself, the SQL could look like this... select longitude, latitude from city where country = 'USA' and population < 100; Okay, for this simple example, I would have no problem doing two different queries (this example is extremely simplified compared to what is possible/likely). But what if the user wanted to give a different symbol for every population in 100,000 increments? If our range of populations was 100,000 to 5,000,000 that would be 50 queries! Not only would it be 50 queries, but it would be 50 queries using a nearly identical WHERE clause. So I thought it would be more efficient to combine the queries into something like the following... select longitude, latitutde, (case when population > 100 then true else false end) as rule1, (case when population > 100 then city_name end) as label1, (case when population < 100 then true else false end) as rule2 where country = 'USA' ; I could just only concern the SQL with the boolean values for the rules, and return all city names, and let the application simply discard them, but that seems like not a good thing to do for very large resultsets (and again, this is overly simplified, we could have many such columns full of uncessary data being returned). And by the way, that query cannot be written as something like... (case when population > 100 then 'rule1' when population < 100 then 'rule2' end) as rules ... because the rules are NOT mutually exclusive, there can many positives. Anyway, hopefully I didn't leave anything important out. It sounds like there's no obvious solution to avoiding multiple evaluations of the test expressions. The rules are relatively static once the config files are read in, so I could conceivably create stored procedures with a bunch of IF statements at that time. However, I'm not sure if in PG there is a way to dynamically populate the resulting recordset on the fly. I can think of 10 different ways accomplish what I'm trying to do, but hopefully someone has some ideas on what would be the best performing. Sorry if it's information overload, but you tried to answer my questions, so I thought I should at least try to answer yours :) Any thoughts much appreciated. You could write a set returning function, but you'd just end up doing the same thing. Can you explain what it is you're trying to acheive - real fields/schemas etc?-- Richard Huxton Do you Yahoo!? Free online calendar with sync to Outlook(TM).
[SQL] Inserting into table only if the row does not already exist.
Hey folks, I am inserting data into a table with a three-column primary key (the table is only three columns). The rows I'm trying to insert may very well be duplicates of ones already in the table, so I would like to have PostgreSQL handle the insert and possible error resulting from dup data. I can certainly do a SELECT first and then INSERT if it's not a duplicate, ala: * Do a SELECT against the three columns * If there are no rows returned, then do the INSERT But I thought this would be possible with the following: INSERT INTO table ( column1, column2, column3 ) SELECT column1, column2, column3 WHERE NOT EXISTS ( SELECT column1, column2, column3 FROM table WHERE column1 = $column1 AND column2 = $column2 AND column3 = $column3 ) .. which gave me 'ERROR: column1 does not exist'. Nuts. Is this possible to do in one statement? I checked with a DBA friend (he's an Oracle guy), Google, and the list archives, and either didn't find anything helpful or simply didn't try the correct search terms. Or should I be doing this sort of thing in two separate queries? Thanks for all the help you folks have given me, Benny -- "Even if a man chops off your hand with a sword, you still have two nice, sharp bones to stick in his eyes." -- .sig on Slashdot ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Inserting into table only if the row does not already
> You just have to put it in the select list as a constant. If you're > feeling > generous to the next programmer to read it you could put "AS column1" > after > each one, but the column name doesn't actually have to match the column > you're > inserting into. Sweet GOD, I hope no one ever has to read the stuff I'm working on! This is just a personal curiousity project, not anything for work or anything released publicly. I'd be far too embarrassed to ever release this, as I'm just fumbling along, learning. :) > Note that this is going to have some concurrency issues. I think it will > be > possible for a second query to execute before the first commits. In that > case > it won't see the record the first query inserted and try to insert again. > You'll just get a primary key violation though which I guess you can just > ignore. Concurrency shouldn't be an issue - this is a perl script running from cron. The only concurrency that will ever happen is if I'm a bonehead and I run the script manually right as cron kicks off another copy. > Which raises a question. Why not forgoe this complicated SQL and try to do > the > insert. If you get a primary key violation, well there's your answer... If > you > don't care about the failure just ignore it and move on. I would suggest > checking specifically for a primary key violation and still stopping > execution > on unexpected errors though. Well, I was hoping to minimize the amount of perl needed to get this one task done, but I think that's going to be the best way to do it. > Incidentally, if you're putting your parameters directly into your queries > using $column1 then you've got a potential security problem. Unless you're > quoting every variable everywhere religiously using postgres's quoting > functions an attacker can sneak extra SQL into your queries. Potentially > including whole new statements such as "DELETE FROM table"... Yes indeed, good catch. I'll clean that up immediately. Thanks, Greg! Benny -- "Even if a man chops off your hand with a sword, you still have two nice, sharp bones to stick in his eyes." -- .sig on Slashdot ---(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] Inserting into table only if the row does not already
> Even cron'd scripts can run amok. Lock files are great as a CYA > measure even if you're relatively certain there will be no opportunity > for one copy to step on the next. Yes, you are absolutely correct, of course. :) I should be more specific - I'm working on a personal project, and I'm not going to worry about that right now. The risk of a cron gone wild is acceptable to me at this moment. Right now my priority is getting data flowing into the database instead of piling up on the host like it is. :) Thanks much! Benny -- "Even if a man chops off your hand with a sword, you still have two nice, sharp bones to stick in his eyes." -- .sig on Slashdot ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Inserting into table only if the row does not already
> Gee, now I have images of late-night advertisements for bofh-porn video > tapes > of Cron Jobs Gone Wild(tm) dancing through my head... thanks. Wow. THERE'S something I didn't need before hitting the sack. ;) Benny -- "Even if a man chops off your hand with a sword, you still have two nice, sharp bones to stick in his eyes." -- .sig on Slashdot ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Stuffing six separate columns into a single array?
Hey folks,
I'm in the middle of a database design update, mostly to undo all of
the stupid things I did in version 0.01. :) God knows I made enough
of them...
I have a table with the following columns:
dns1_ptr | inet | default '0.0.0.0'::inet
dns2_ptr | inet | default '0.0.0.0'::inet
dns3_ptr | inet | default '0.0.0.0'::inet
dns4_ptr | inet | default '0.0.0.0'::inet
dns5_ptr | inet | default '0.0.0.0'::inet
dns6_ptr | inet | default '0.0.0.0'::inet
(yes, I know, I didn't know any better)
It is being replaced by:
dns_ptr| inet[] | default ...etc
(hopefully this is more intelligent)
Now, as I migrate the data from the old table to the new, is there
any way to just do the typical 'INSERT INTO blah SELECT a,b,c FROM blah2'
type of thing? ie,
INSERT INTO new_table ( dns_ptr ) SELECT dns1_ptr, dns2_ptr .. FROM
old_table;
I can't seem to get the CASTing right on the SELECT statement, but
perhaps it's not even possible. It would be nice to be able to do
this so I don't have to write yet another perl script. I thought a
'CAST( '{dns1_ptr, dns2_ptr}' AS inet[] )' would work, but it complained
about the input syntax on dns1_ptr. I'm learning as I go, so maybe
that's not even close to being valid.
Any help/advice would be greatly appreciated,
Benny
--
"Even if a man chops off your hand with a sword, you still have two nice,
sharp bones to stick in his eyes."
-- .sig on Slashdot
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Stuffing six separate columns into a single array?
> How does dns_ptr relate to other data? Depending on what you're > doing, other ways of organizing your tables might also make sense. These are actually DNS servers authoritive for a domain that is stored in a VARCHAR() in the same table. After sleeping on it, I think using an array is indeed not the right choice. I think splitting the nameservers off into their own table is probably smarter for what I want to do. Thanks, Michael, for the mental boot to the head. :) Benny -- "Even if a man chops off your hand with a sword, you still have two nice, sharp bones to stick in his eyes." -- .sig on Slashdot ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Impact of foreign keys on a simple count(*) ?
Hey folks (long email, my apologies),
I am wrapping up my schema upgrade, and I just noticed a real
show-stopper for me... Here is the "before" table structure:
email_id | integer | not null
joejob| boolean | default false
bayes_poison | boolean | default false
perm_error| boolean | default false
bait_id | integer | default 0
num_recip | integer | default 1
product | integer | default 1043
date_received | timestamp with time zone|
only_date | date|
only_time | time with time zone |
maildir_file | character varying(64) |
errors_to | character varying(512) |
reply_to | character varying(512) |
spammer | character varying(512) |
return_path | character varying(512) |
received_from | character varying(512) |
message_id| character varying(512) |
target_domain | character varying(512) |
mail_date | character varying(512) |
x_priority| character varying(512) |
x_msmail_priority | character varying(512) |
x_mimeole | character varying(512) |
mime_version | character varying(512) |
subject | character varying(1024) |
mail_to | character varying(2048) |
x_mailer | character varying(2048) |
content_type | character varying(2048) |
user_agent| character varying(2048) |
cc| character varying(2048) |
comments | character varying(8192) |
last_mod | timestamp without time zone | default
('now'::text)::timest
amp(6) with time zone
Indexes:
"emails_pkey" primary key, btree (email_id)
"emails_idx_bait_id" btree (bait_id)
"emails_idx_mail_to" btree (mail_to)
"emails_idx_only_date" btree (only_date)
"emails_idx_only_time" btree (only_time)
"emails_idx_product" btree (product)
"emails_idx_received_from" btree (received_from)
"emails_idx_subject" btree (subject)
"emails_idx_target_domain" btree (target_domain)
And here is the "after" structure:
email_id | integer | not null
joejob| boolean | default false
bayes_poison | boolean | default false
perm_error| boolean | default false
num_recip | integer | default 1
mydom_id | integer | default 0
spamv_id | integer | default 1053
spammer_id| integer | default 1003
last_mod | timestamp with time zone | default
('now'::text)::timestamp
with time zone
bait_id | integer |
product_id| integer |
date_received | timestamp with time zone |
only_date | date |
only_time | time with time zone |
maildir_file | character varying(128) |
x_priority| character varying(128) |
x_msmail_priority | character varying(128) |
x_mimeole | character varying(128) |
mime_version | character varying(512) |
received_from | character varying(512) |
content_type | character varying(512) |
errors_to | character varying(512) |
user_agent| character varying(512) |
mail_date | character varying(512) |
x_mailer | character varying(512) |
return_path | character varying(512) |
message_id| character varying(512) |
reply_to | character varying(512) |
subject | character varying(1024) |
mail_to | character varying(1024) |
cc| character varying(2048) |
Indexes:
"emails_pkey" primary key, btree (email_id)
"emails_maildir_file_key" unique, btree (maildir_file)
Foreign-key constraints:
"$1" FOREIGN KEY (bait_id) REFERENCES bait(bait_id) ON UPDATE CASCADE ON
DELETE SET NULL
"$2" FOREIGN KEY (product_id) REFERENCES products(product_id) ON UPDATE
CASCADE ON DELETE SET NULL
"$3" FOREIGN KEY (mydom_id) REFERENCES my_domains(mydom_id) ON UPDATE
CASCADE ON DELETE SET NULL
"$4" FOREIGN KEY (spamv_id) REFERENCES spamvertisers(spamv_id) ON UPDATE
CASCADE ON DELETE SET NULL
"$5" FOREIGN KEY (spammer_id) REFERENCES spammers(spammer_id) ON UPDATE
CASCADE ON DELETE SET NULL
I am absolutely aware of the fact that the new schema can still use
some work. :) But, it's a step in the right direction as I muddle my
way through this. Not to mention, I really did need those foreign keys.
The problem is this:
Original schema:
prod01=> EXPLAIN ANALYZE SELECT count(*) FROM emails;
QUERY PLAN
-
Re: [SQL] Impact of foreign keys on a simple count(*) ?
> Foreign keys have zero, nada, zilch to do with the performance of > count(*). OK, I just wanted to make sure. > The only plausible theory I can think of for the performance > difference is that in your "new" database the table has been through > several mass updates, leading to a whole lot of dead rows and a much > larger physical table size to scan through. I'd suggest a VACUUM > VERBOSE on both old and new copies of the table to get an idea of the > relative physical sizes. You might need a VACUUM FULL to get the new > table back down to a reasonable size... Oh good lord. *waves the Idiot Flag as he slinks back under his rock* Thank you, Tom, for your patience. That was it. I'm a potatohead, but I'm learning. :) Benny, swearing he DID do a vacuum, but obviously not... -- "Even if a man chops off your hand with a sword, you still have two nice, sharp bones to stick in his eyes." -- .sig on Slashdot ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] append fields for *where...*
I have the following tables: TABLE A month | year | item | num 1 2005 myitem 003 TABLE B num| date | descr 003 02-01-2005 blahblah 003 01-01-2005 toratora I am trying to come up with something like: select date,item,descr from TABLEA a LEFT OUTER JOIN TABLEB b ON b.num=a.num WHERE date=month-01-year; How can you make month (append) - 01 - (append) year? and pass that as a condition to the field date? Is this possible? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] "Installing" dbLink
Hi When I execute the query: SELECT *FROM dblink('dbname=Bas','SELECT id FROM person') AS t(a int)WHERE t.a > 9000 AND T.a < 9050; I receive the error message ERROR: function dblink("unknown", "unknown") does not exist How do I install or setup the function dbLink so that I can use this feature. Regards Richard
Win a BlackBerry device from O2 with Yahoo!. Enter now.
Re: [SQL] "Installing" dbLink
Hi. Thanks for the assistance. I have another question. Is it possible to install this function on Window XP?"A. Kretschmer" <[EMAIL PROTECTED]> wrote: am 24.02.2006, um 11:48:44 + mailte Richard C folgendes:> Hi> > When I execute the query: > > SELECT *> FROM dblink('dbname=Bas','SELECT id FROM person') AS t(a int)> WHERE t.a > 9000 AND T.a < 9050;> > I receive the error message> > ERROR: function dblink("unknown", "unknown") does not exist> > How do I install or setup the function dbLink so that I can use this feature.You should read the docu.Which distribution do you have? You should install the contrib-packageand then you should run the dblink.sql.psql your_database < dblink.sqlAfter this, y
ou can
use dblink.HTH, Andreas-- Andreas Kretschmer (Kontakt: siehe Header)Heynitz: 035242/47215, D1: 0160/7141639GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe === ---(end of broadcast)---TIP 4: Have you searched our list archives?http://archives.postgresql.org
Yahoo! Photos NEW, now offering a quality print service from just 8p a photo.
[SQL] dbLink Query
Hi When I execute the query: SELECT *FROM dblink('dbname=Bas','SELECT id FROM person') AS t(a int)WHERE t.id > 9000 AND T.ID < 9050; I receive the error message ERROR: function dblink("unknown", "unknown") does not exist How do I install or setup the function dbLink so that I can use this feature. Regards Richard
To help you stay safe and secure online, we've developed the all new Yahoo! Security Centre.
[SQL] Date ranges + DOW select question
Hi, I have a table which contains starttime, endtime and DOW; i.e. a weekly list of times for when a process must be started and ended. TABLE: cronTimes FIELDS: starttime, endtime, dayOfWeek I have another table which contains date ranges. TABLE: dateRanges FIELDS: dateStart, dateEnd I need to get a list of cronTimes records for a record in dateRanges and push these to a temporary table. i.e. tell me which dates are affected by cronTimes. I have got a little way on this but need some assistance. SELECT * FROM cronTimes WHERE starttime >= '00:00:00' AND endtime <= '23:59:59' AND dayOfWeek >= (EXTRACT(DOW FROM TIMESTAMP '2006-06-26')) AND dayOfWeek <= (EXTRACT(DOW FROM TIMESTAMP '2006-07-04')); The problem with the above is that by casting the dateStart and dateEnd they become numbers between 0 and 6 which inturn invalidates the < & > as they are nolonger working on dates, nor a sequence as numbers can be repeated. Do I need to generate a sequence of dates somehow so that each date in the range can be compared to the cronTimes table (so I can use the 'IN' condition)? Am I in the realms of plpgsql? Any advice on the above welcome. Joseppic. Send instant messages to your online friends http://uk.messenger.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Differences between bit string constant sintax
Dear All,
I have a table which has a field that is of type bit varying.
When I do a direct INSERT with , say, X'1F', everything
works fine.
But in this table I have to insert several tens of thousands
of records each time, so I decided to use
COPY command.
And if I build a file like this (columns separated by tab char)
primary_key1X'1F'
primary_key2X'1FF'
...
The system will complain on the first line,
saying that ' is not an hexadecimal digit.
I tried to remove the single quotes and it worked fine...
With values like this : X1F, X1FF, etc
Is the system supposed to behave like this, or have
I gone sideways somewhere on this?
My copy command is just this :
COPY t_unique_browsers
("IDUniqueBrowsers","browsersSet")
FROM $file$C:\temp\pg\totalAccount.sql$file$;
Also,
SELECT version() says :
"PostgreSQL 8.3.7, compiled by Visual C++ build 1400"
Thanx in advance for your help
Best,
Oliveiros
Re: [SQL] simple (?) join
You mean to list the complete orders table and for each of its records, the corresponding record on the orders_log with the latest ol_timestamp? SELECT * FROM orders_log main JOIN ( SELECT orders.*, MAX(orders_log.ol_timestamp) as latest FROM orders NATURAL JOIN orders_log GROUP BY orders.* ) subquery ON main.ol_timestamp = subquery.latest AND main.o_id = subquery.o_id This query is untested, but could you give it a try? Then tell me the results. NB - I am not sure if it is legal to use * on a GROUP BY clause, but if it isnt please kindly substitute by orders.o_id, orders.next_field, etc... Best, Oliveiros - Original Message - From: "Gary Stainburn" To: Sent: Thursday, September 24, 2009 4:16 PM Subject: [SQL] simple (?) join Hi folks. I have two tables create table orders ( o_id serial primary key ... ); create table orders_log ( ol_id serial primary key, o_id int4 not null references orders(o_id), ol_timestamp timestamp, ol_user, ); How can I select all from orders and the last (latest) entry from the orders_log? Cheers -- Gary Stainburn Gary's Haircut 700 Please visit http://www.justgiving.com/Gary-Stainburn/ to help me raise money for Cancer Research - in return I'll have my head shaved -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple (?) join
Hmm...no, it seems, it is not allowable to use orders.* on a GROUP BY clause. Unless you've defined for the table something called an ordering operator. If you didn't, you'll have to include all the fields from the orders table in the GROUP BY clause HTH Best, Oliveiros - Original Message - From: "Oliveiros C," To: "Gary Stainburn" ; Sent: Thursday, September 24, 2009 6:17 PM Subject: Re: [SQL] simple (?) join You mean to list the complete orders table and for each of its records, the corresponding record on the orders_log with the latest ol_timestamp? SELECT * FROM orders_log main JOIN ( SELECT orders.*, MAX(orders_log.ol_timestamp) as latest FROM orders NATURAL JOIN orders_log GROUP BY orders.* ) subquery ON main.ol_timestamp = subquery.latest AND main.o_id = subquery.o_id This query is untested, but could you give it a try? Then tell me the results. NB - I am not sure if it is legal to use * on a GROUP BY clause, but if it isnt please kindly substitute by orders.o_id, orders.next_field, etc... Best, Oliveiros - Original Message - From: "Gary Stainburn" To: Sent: Thursday, September 24, 2009 4:16 PM Subject: [SQL] simple (?) join Hi folks. I have two tables create table orders ( o_id serial primary key ... ); create table orders_log ( ol_id serial primary key, o_id int4 not null references orders(o_id), ol_timestamp timestamp, ol_user, ); How can I select all from orders and the last (latest) entry from the orders_log? Cheers -- Gary Stainburn Gary's Haircut 700 Please visit http://www.justgiving.com/Gary-Stainburn/ to help me raise money for Cancer Research - in return I'll have my head shaved -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple (?) join
Hello, Justin, Gary. Justin, your (the second one) query is not much different from mine. You previewed the possibility of having orders without any matching entry on orders_log with your left join, something that I haven't. Gary, will you have records on your orders table that don't reference any record on your orders_log table? If so, Justin's query is the right one you should use. You return the full record from orders and an additional column from orders_log, the ol_timestamp column. I understood that Gary wanted the full record from orders_log, not just the timestamp column. That part is done by my subquery . I think Gary could clarify what he wants exactly. Gary? :) Also, Justin, your query design seems right to me, but maybe you should add this (the part in comment) to your subquery SELECT MAX(ol_timestamp) /* as ol_timestamp */ , o_id FROM orders_log group by o_id because the MAX(ol_timestamp) will receive the name max, not ol_timestamp, and probably the parser will complain that column ol_timestamp does not exist. Ain't I right? Best, Oliveiros - Original Message - From: justin To: David W Noon Cc: pgsql-sql@postgresql.org ; gary.stainb...@ringways.co.uk Sent: Thursday, September 24, 2009 9:15 PM Subject: Re: [SQL] simple (?) join David W Noon wrote: On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn wrote about [SQL] simple (?) join: create table orders ( o_id serial primary key ... ); create table orders_log ( ol_id serial primary key, o_id int4 not null references orders(o_id), ol_timestamp timestamp, ol_user, ); How can I select all from orders and the last (latest) entry from the orders_log? SELECT * FROM orders WHERE o_id IN (SELECT o_id FROM orders_log WHERE ol_timestamp = (SELECT MAX(ol_timestamp) FROM orders_log)); No joins required. I don't think that is what he is requesting. I read it he also wants the timestamp included in the result set A nested query Select orders.*, (SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id = orders.oid) From orders Still another option is using a join Select orders.*, ol_timestamp From orders left join (SELECT MAX(ol_timestamp), o_id FROM orders_log group by o_id) as JoinQuery on JoinQuery.o_id = orders.o_id The second one should be faster
Re: [SQL] simple (?) join
Hello, Gary. thank you for your e-mail This is a slightly modified version of the query I sent you on first place (have you tried it out?). It will return (I hope :) the full orders record plus the maximum ol_timestamp and respective o_user. HTH Best, Oliveiros SELECT subquery.*, orders_log.ol_user FROM orders_log main JOIN ( SELECT orders.o_id, /* The remaining fields of orders go here */, MAX(orders_log.ol_timestamp) as latest FROM orders NATURAL JOIN orders_log GROUP BY orders.o_id, /* the remaining fields of orders go here */ ) subquery ON main.ol_timestamp = subquery.latest AND main.o_id = subquery.o_id - Original Message - From: "Gary Stainburn" To: Sent: Monday, September 28, 2009 12:02 PM Subject: Re: [SQL] simple (?) join On Saturday 26 September 2009 21:15:37 justin wrote: David W Noon wrote: On Sat, 26 Sep 2009 14:54:24 -0400, justin wrote about Re: [SQL] simple (?) join: [snip] Quoting Gary "How can I select all from orders and the last (latest) entry from the orders_log?" In that case, a simple Cartesian product will do: SELECT o.*, maxi.ts FROM orders AS o, (SELECT MAX(ol_timestamp) AS ts FROM orders_log) AS maxi; Since the cardinality of the subquery "maxi" is 1, it will give a result set with cardinality of the complete orders table. I don't understand why anybody would want to do that. [De gustibus ... ] Guessing here Answer to return the last time someone either viewed or edited the order. This is a very common audit requirement to track who what, when and why something happened. For some reason the reply I sent on Friday didn't get through. What I need is all of the order record and all of the latest log entry returning as a join. Specifically I want for each order the most recent log entry timestamp and it's associated user - i.e. who made the the last log entry and when. I suppose I'm asking how I would do the sub-query to pull the most recent log entry per order. -- Gary Stainburn Gary's Haircut 700 Please visit http://www.justgiving.com/Gary-Stainburn/ to help me raise money for Cancer Research - in return I'll have my head shaved -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to order varchar data by word
Hello, list. I have a table with a varchar field that I would like to order by word, not by ordinal, which seems to be the default on postgres. Does anyone have a clue on how this can be done? Many thanx in advance, Best, Oliveiros
Re: [SQL] How to order varchar data by word
Hello, Adrian. Thank you for your email. I am not using any SQL at this time, but I 'd like to understand what is exactly the criterion that the ORDER BY uses to order text. Giving you some background : I need to make an efficient join between data generated by my program and data output by postgres and of course, it is mandatory that both lists of records be ordered using the same criterion. I need to figure out what is exactly the criterion used by postgres so I can mimic it on my program. I thought it was ordinal, but it seems it isnt, as the @ symbol comes before the 0 (zero). According to ASCII numeric codes, it shouldn't If any one can explain me exactly how the order by clause works on varchars, I 'd really appreciate it. I've already examined documentation carefully, but couldn't find it. Maybe I looked on the wrong place... Best, Oliveiros - Original Message - From: "Adrian Klaver" To: Cc: "Oliveiros C," Sent: Wednesday, October 14, 2009 9:54 PM Subject: Re: [SQL] How to order varchar data by word On Wednesday 14 October 2009 7:13:22 am Oliveiros C, wrote: Hello, list. I have a table with a varchar field that I would like to order by word, not by ordinal, which seems to be the default on postgres. Does anyone have a clue on how this can be done? Many thanx in advance, Best, Oliveiros Can you show the SQL you are using? -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to order varchar data by word
Hello, Tom. Thank you for your e-mail Even though I am not familiar with the exact procedure to switch to other locales, I'm gonna research this more deeply. Anyway, I would appreciate if you could spare me some time and give me a couple of pointers to this subject. Again, thank you Best, Oliveiros - Original Message - From: "Tom Lane" To: "Oliveiros C," Cc: "Adrian Klaver" ; Sent: Monday, October 19, 2009 6:32 PM Subject: Re: [SQL] How to order varchar data by word "Oliveiros C," writes: If any one can explain me exactly how the order by clause works on varchars, I 'd really appreciate it. It sorts according to the sort order imposed by your locale setting (see LC_COLLATE in particular). A lot of locales have weird and wonderful rules that embody somebody's notion of "dictionary order". If you don't like it, try switching to C locale. Or you could learn enough about locale definitions to create your own. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to order varchar data by word
Hello, Adrian, thanks for the links. I enjoyed the technique on the end of the second page (on the version 8.3 with comments), the shadow column trick. Given that LC_COLLATE is immutable, unless a new database is created, I guess I'll give the shadow column a try. Best, Oliveiros - Original Message - From: "Adrian Klaver" To: "Oliveiros C," Cc: "Tom Lane" ; Sent: Tuesday, October 20, 2009 2:46 PM Subject: Re: [SQL] How to order varchar data by word On Tuesday 20 October 2009 6:39:23 am Oliveiros C, wrote: Hello, Tom. Thank you for your e-mail Even though I am not familiar with the exact procedure to switch to other locales, I'm gonna research this more deeply. Anyway, I would appreciate if you could spare me some time and give me a couple of pointers to this subject. Again, thank you Best, Oliveiros A good place to start is: http://www.postgresql.org/docs/8.4/interactive/locale.html Check out the Further Reading section at: http://www.postgresql.org/docs/8.4/interactive/multibyte.html#AEN30078 -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need nelp with aggregate functions
Try substituting the SELECT count(c) as qtd_client,count(cm) as qtd_computers by SELECT count( DISTINCT c.cliente_id) as qtd_client,count(/* put here the primary key of the computer table */ ) as qtd_computers Then tell me if it output what you want Best, Oliveiros - Original Message - From: Another Trad To: pgsql-sql@postgresql.org Sent: Wednesday, November 18, 2009 4:55 PM Subject: [SQL] need nelp with aggregate functions The DB structure is in attachment. I with the number of clients and the number of computers that have processors with "manufacturer" = "INTEL" and "speed" = "2GB" I am trying: select count(c) as qtd_client, count(cm) as qtd_computers from cliente c inner JOIN computer cm on (c.cliente_id = cm.cliente_id) inner join processor p on (cm.processor_id = p.processor_id)inner join speed s on (s.speed_id = p.speed_id)INNER JOIN manufacturer m on (p.manufacturer_id=m.manufacturer_id) where m.manufacturer = 'INTEL'and s.speed = '2GB'but is not working anyone can help me? -- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need nelp with aggregate functions
Howdy, It is quite simple, the joins you used would output a long list of pairs (client,computer), and, as many people has more than one computer, many clients will appear repeated, but the COUNT keyword will count them more than once though. The DISTINCT keyword prevents one client from appearing more than once... It's basically that... Hope this helped Best, Oliveiros - Original Message - From: Another Trad To: Oliveiros C, Cc: pgsql-sql@postgresql.org Sent: Wednesday, November 18, 2009 5:37 PM Subject: Re: [SQL] need nelp with aggregate functions ok, I did: SELECT count(DISTINCT c.cliente_id) as qtd_client,count(cm.cm_id) as qtd_computers GREAT. It works. Please, explain me why and how it works, I wanna learn and do by myself next time :) 2009/11/18 Oliveiros C, Try substituting the SELECT count(c) as qtd_client,count(cm) as qtd_computers by SELECT count( DISTINCT c.cliente_id) as qtd_client,count(/* put here the primary key of the computer table */ ) as qtd_computers Then tell me if it output what you want Best, Oliveiros - Original Message - From: Another Trad To: pgsql-sql@postgresql.org Sent: Wednesday, November 18, 2009 4:55 PM Subject: [SQL] need nelp with aggregate functions The DB structure is in attachment. I with the number of clients and the number of computers that have processors with "manufacturer" = "INTEL" and "speed" = "2GB" I am trying: select count(c) as qtd_client, count(cm) as qtd_computers from cliente c inner JOIN computer cm on (c.cliente_id = cm.cliente_id) inner join processor p on (cm.processor_id = p.processor_id)inner join speed s on (s.speed_id = p.speed_id)INNER JOIN manufacturer m on (p.manufacturer_id=m.manufacturer_id) where m.manufacturer = 'INTEL'and s.speed = '2GB'but is not working anyone can help me? -- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Is there any function to test for numeric ips?
Dear All, I have a table with host names and some happen to be numeric IPs. I would like to be able to filter out the later. Is there any function pre-defined in the system that can test a particular text type value to see if it is a numeric ip? Something that returns true if applied to '192.168.1.1' but false if applied to 'videos.sapo.pt' ? I considered NOT LIKE '%.%.%.%' but I'm affraid it will filter out host names like 'www.google.com.br' I've realized that, for ex, inet 'x.x.x.x' will fail if the input is not a numeric IP, is there any simple and direct way to somewhat trap that error and convert it to a false value that can be used in a WHERE clause? This is not to be included in a program, I'm just interested in a query that I can do directly to the back end on a client program like pgAdmin. I know very little ( nothing, actually...) about function creation in pgplsql, so, before investing in learning, I would like to know if there is any built in function that does the trick, so I can skip the (potential) learning curve. So far, I've googled up and down but found none Thanx in advance for your kind and valuable help Best, Oliveiros
Re: [SQL] Is there any function to test for numeric ips?
Howdy, Alvaro, Fernando and Harald. Thanks a lot for your prompt reply. As I'm in a big hurry with this I used the regular expressions thing, it worked fine. But your solution looks good as well, Alvaro, the problem is my poor knowledge on pgplsql Definitely, I have to study in more depth subjects like pgplsql not to mention regular expressions Thanx a lot for your fast help, Best, Oliveiros - Original Message - From: "Alvaro Herrera" To: "Oliveiros C," Cc: Sent: Friday, December 11, 2009 4:28 PM Subject: Re: [SQL] Is there any function to test for numeric ips? Oliveiros C, wrote: I've realized that, for ex, inet 'x.x.x.x' will fail if the input is not a numeric IP, is there any simple and direct way to somewhat trap that error and convert it to a false value that can be used in a WHERE clause? Yes, you can create a plpgsql function with an exception block; return false inside the exception, true otherwise. It looks something like begin perform $1::inet; return true; exception when invalid_something then return false end; I don't remember the exact syntax and the exception name but that should get you started. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting rows tagged with "a" but not "b"
Darrell, Can you provide a little more information and background on your problem. please? What values can the "tag" column assume? Just "a" and "b" ? Both? Please give examples of table contents and desired output, your mail doesn't contain enough info to give you more advises Thank you Best, Oliveiros - Original Message - From: <8q5tmky...@sneakemail.com> To: Sent: Monday, February 01, 2010 1:31 PM Subject: [SQL] selecting rows tagged with "a" but not "b" Hi, I have a two tables: article articleID, name, content tags articleID, tag I want to find all articles that are tagged with "a" but not "b" how do I do this? what I'd like to do is: select articleID from tags where tag="a" SUBTRACT select articleID from tags where tab="b" how do I do this in real SQL? thanks Darrell -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "left join" not working?
My first guess is that NULL fails the condition on your WHERE clause, p.id_line = 1 So your WHERE clause introduces an additional level of filtering that filters out the NULLs coming from the LEFT JOIN... Didn't do any tests, it's just a guess... Best, Oliveiros - Original Message - From: "Louis-David Mitterrand" To: Sent: Friday, February 12, 2010 11:14 AM Subject: [SQL] "left join" not working? Hi, This query: select c.id_currency,max(p.modified_on) from currency c left join price_line p using (id_currency) where p.id_line=1 group by c.id_currency; doesn't list all c.id_currency's, only those with a price_line. However this one does: select c.id_currency,max(p.modified_on) from currency c left join price_line p on (p.id_currency = c.id_currency and p.id_line=1) group by c.id_currency; How come? Thanks, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "left join" not working?
Yes, because your WHERE is something that comes after the operation of the LEFT JOIN, in practice, defeating the purpose you intented. On your second query the p.id_line = 1 doesn't do that because it is part of the LEFT JOIN itself.. HTH Best, Oliveiros d'Azevedo Cristina - Original Message - From: "Louis-David Mitterrand" To: Sent: Friday, February 12, 2010 11:57 AM Subject: Re: [SQL] "left join" not working? On Fri, Feb 12, 2010 at 11:35:02AM -, Oliveiros C, wrote: My first guess is that NULL fails the condition on your WHERE clause, p.id_line = 1 So your WHERE clause introduces an additional level of filtering that filters out the NULLs coming from the LEFT JOIN... So, if I understand correctly, a WHERE filters all results regardless of join conditions and can turn an OUTER JOIN into an INNER JOIN. Thanks for pointing that out! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Returning a set of dates
Hey folks, I am still slogging away on my pet project, and I'm giving up - I need help from the experts to try to get this function working like I want it to. I have a very basic function, thanks to you guys a few months ago: CREATE OR REPLACE FUNCTION public.next_bill_date(d date, period interval, i interval) RETURNS date LANGUAGE plpgsql AS $function$ DECLARE ret DATE; BEGIN -- We must use "CURRENT_DATE + period" in SELECT below - if we just -- use "d + i", we will only get the next billing date after the -- bill's *epoch*. Since we're passing an epoch, we need to -- make sure we're getting the real next billing date SELECT INTO ret generate_series( d, CURRENT_DATE + period, i ) AS next_bill_date ORDER BY next_bill_date DESC; RETURN ret; END; $function$ If I call the function like so: SELECT next_bill_date( '2011-06-10', '1 month', '1 year' ); next_bill_date 2011-06-10 .. it gives me the *next date* a bill will be due. Yay. However, several of my bills are recurring in a time period shorter than the interval I will pass the function. Ie, I have several savings "bills" that occur twice a month, aligned with my paycheck. If I call the function: SELECT next_bill_date( '2011-06-01', '2 weeks', '1 month' ) .. I need it to return the two dates during the interval (1 month) that this "bill" will be due. I am brain-weary looking at this, so in case my explanation is not clear, here's what I'm shooting for: 1) The function must accept an epoch date, when the bill "starts". Ie, my mortgage's epoch is on 2011-01-01, as it's due on the 1st of the month. The month and year aren't as critical, they just need to represent a "starting date" in the past. This epoch could just as well be '2011-06-01'. I manually enter the epochs, so I can make the assumption that it will always be in the past. 2) The function must accept a period, or how often the bill recurs. So, most bills will have a period of '1 month'. Some might be '2 weeks'. Some, like insurance, might be '6 months' or even '1 year'. 3) The function must accept an interval, describing how long of a time period we want to look at. Ie, "I want to look at all bills over the next six months." The interval would be '6 months'. Or the upcoming bills over '6 weeks'. You get the idea. So, for example, if I call the function to determine my mortgage's due dates over the next four months: SELECT next_bill_date( '2011-01-01', '1 month', '4 months' ); .. I expect the following result set: next_bill_date 2011-07-01 2011-08-01 2011-09-01 2011-10-01 I know I must use SETOF to return the set. But I just can't seem to get the damned syntax correct, and I'm more than a little lost trying to get this function put together. Can someone please help me out? Thanks much! Benny -- "You were doing well until everyone died." -- "God", Futurama -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Returning a set of dates
> http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
>
>CREATE OR REPLACE FUNCTION public.next_bill_date(d date, period
>interval,
> i interval)
> RETURNS SETOF date
> AS $function$
> DECLARE
> max_date date;
> due_date date;
> BEGIN
> max_date := CURRENT_DATE + i;
> due_date := d;
> WHILE due_date + period <= max_date LOOP
> RETURN NEXT due_date; -- add d to the result set
> due_date := due_date + period;
> END LOOP;
> RETURN; -- exit function
> END;
> $function$ language plpgsql;
> testdb=# select next_bill_date('2011-06-11', '2 week', '3 month');
next_bill_date
>
> 2011-06-11
> 2011-06-25
> 2011-07-09
> 2011-07-23
> 2011-08-06
> 2011-08-20
Almost, but not quite - the d parameter is a bill's "start date",
and the function shouldn't show dates in the past. So, when the
above function is called with say '2011-06-01' as the beginning
date, the function will happily return '2011-06-01' in the result
set, even though it's in the past.
I've modified it a bit. I renamed the function arguments to be a
bit more descriptive, did a little more math, and added an IF
statement to not return any dates in the past:
CREATE OR REPLACE FUNCTION public.next_bill_date(d date, frequency
interval, daterange interval)
RETURNS SETOF date
AS $function$
DECLARE
max_date date;
due_date date;
BEGIN
-- We need to add the epoch date and daterange together, to
-- get the "max_date" value. However, this would cause us
-- to lose the last due date in the result set. Add one more
-- frequency to it so we don't lose that.
max_date := CURRENT_DATE + frequency + daterange;
due_date := d;
WHILE due_date + frequency <= max_date LOOP
-- Don't include dates in the past - we only want future
-- due dates for bills.
IF due_date >= CURRENT_DATE
THEN
RETURN NEXT due_date;
END IF;
due_date := due_date + frequency;
END LOOP;
RETURN; -- exit function
This appears to work properly:
SELECT next_bill_date( '2011-06-01', '2 weeks', '3 months' );
next_bill_date
2011-06-15
2011-06-29
2011-07-13
2011-07-27
2011-08-10
2011-08-24
2011-09-07
(7 rows)
Thanks for all your help! I'm not at all experienced with plpgsql,
so this was very much appreciated. :)
Benny
--
"You were doing well until everyone died."
-- "God", Futurama
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] underscore pattern in a query doens't work
Thanks Tom, for your suggestions. We started the cluster up with this command: ./initdb -D /usr/local/postgre/data -E UTF8 -U sir The rest of the variables related to encoding (locale) are: lc_collate=C lc_ctype=C lc_messages=C lc_monetary=C lc_numeric=C lc_time=C Could you tell me which more info could I give you? De: Tom Lane Para: Sergio Calero. CC: "pgsql-sql@postgresql.org" Enviado: Jueves 13 de septiembre de 2012 16:19 Asunto: Re: [SQL] underscore pattern in a query doens't work "Sergio Calero." writes: > I'd like to execute a query using the underscore as a pattern. > select id,etiqueta from limites_municipales where etiqueta like 'Garaf_a'; > [ but this fails to match 'Garafía' ] I suspect what you have here is an encoding problem. That is, probably the "í" is represented as a multi-byte character (most likely UTF8) but the server thinks it's working with a single-byte encoding so that any one character should be only one byte. You didn't say what your encoding setup is, so it's hard to do more than speculate. > PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 > 20060404 (Red Hat 3.4.6-10), 64-bit You do realize this is about 3 years out of date? The 8.4 series is up to release 8.4.13, and a lot of those updates contained fixes for serious bugs. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Time Help
Hello all, I have a query result of @ 2 hours 10 mins 6 secs and I would like to change that to 02:10:06. Currently the field is listed as "timespan" This allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 min 6 sec which are all the formats that I will be entering the time formats! How do I convert it into a the format of 02:10:06 Thanks to you all
Re: [SQL] Time Help
Mark, I tried that and had to change it to: SELECT '0:00:00'::timespan + '02:10:06'::timespan; To get any response. the response i got was @ 2 hours 10 mins 6 secs Still in the wrong format If is use : SELECT '0:00:00'::time + '02:10:06'::timespan; It get No such function 'time_timespan' with the specified attributes So i guess what I want to do is convert a timespan into time How would I do that? At 09:55 AM 8/22/00 -0400, Mark Volpe wrote: >I'm not sure at all what you are asking, but I'm thinking you're trying to >convert a "timespan" to a "time". Try adding it to a time like this: >SELECT '0:00:00'::time + '02:10:06'::timespan; > >Mark > >"Brian C. Doyle" wrote: > > > > Hello all, > > > > I have a query result of @ 2 hours 10 mins 6 secs and I would like to > > change that to 02:10:06. Currently the field is listed as "timespan" This > > allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 min 6 sec > > which are all the formats that I will be entering the time formats! How do > > I convert it into a the format of 02:10:06 > > > > Thanks to you all
Re: [SQL] Time Help
In Conclusion: 7.0.2 by default outputs the time span as 00:00:00 no matter what format you put in. There is no need to convert it! Thanks to Mark for all your help!! At 11:03 AM 8/22/00 -0400, Mark Volpe wrote: >SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs'::timespan; > > ?column? >-- > 02:10:06 > >Mark > > >"Brian C. Doyle" wrote: > > > > Mark, > > > > On your 7.0 box would you do: > > > > SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs':timespan; > > > > For me and see if it will convert it! Need to decide if the upgrade will be > > with it and if it does this then it is > > > > Thanks for your help Mark > > > > At 10:36 AM 8/22/00 -0400, you wrote: > > >I tried it on a box with postgres 6.5.3 and I got the result you did. On > > >postgres 7.0 I get 02:10:06 for both results. You should upgrade to > 7.0 - it > > >is _much_ better. > > > > > >Mark > > > > > >"Brian C. Doyle" wrote: > > > > > > > > Mark, > > > > > > > > I tried that and had to change it to: > > > > > > > > SELECT '0:00:00'::timespan + '02:10:06'::timespan; > > > > > > > > To get any response. the response i got was > > > > > > > > @ 2 hours 10 mins 6 secs > > > > > > > > Still in the wrong format > > > > If is use : > > > > > > > > SELECT '0:00:00'::time + '02:10:06'::timespan; > > > > > > > > It get > > > > > > > > No such function 'time_timespan' with the specified attributes > > > > > > > > So i guess what I want to do is convert a timespan into time > > > > How would I do that? > > > > > > > > At 09:55 AM 8/22/00 -0400, Mark Volpe wrote: > > > > >I'm not sure at all what you are asking, but I'm thinking you're > trying to > > > > >convert a "timespan" to a "time". Try adding it to a time like this: > > > > >SELECT '0:00:00'::time + '02:10:06'::timespan; > > > > > > > > > >Mark > > > > > > > > > >"Brian C. Doyle" wrote: > > > > > > > > > > > > Hello all, > > > > > > > > > > > > I have a query result of @ 2 hours 10 mins 6 secs and I would > like to > > > > > > change that to 02:10:06. Currently the field is listed as > > > "timespan" This > > > > > > allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 > > > min 6 sec > > > > > > which are all the formats that I will be entering the time formats! > > > How do > > > > > > I convert it into a the format of 02:10:06 > > > > > > > > > > > > Thanks to you all
[SQL] Time Results Conversion
Hello all, I have now upgraded to 7.0.2 and am very pleased with it. I do have a question about a result that I am getting. When I sum an interval field will get "1 01:01:01" representing "25 hours 1 minute 1 second" The result that I need is just the sum of the hours minutes and seconds and No days ie roll the day back into the hour field. Is it possible for me to do this and if so How?
Re: [SQL] Week of the Year?
John,
Would you have any clue how to figure out the first saturday of any month -
6 days and the last saturday of that month?
I know that this seems odd but i have to run reports for "Non Standard
Months" and well I am clueless.
At 09:55 PM 8/11/00 -0500, you wrote:
>Try using the function date_part such as:
>
>select date_part('week',now());
>
>"and the days that are in that week" I guess want to answer a question
>such as:
>Given a date, what is first date in that same week, and what is the last
>date in that week. There are a couple of approaches to this. My first was:
>
>select
>to_date(date_part('year',now()),'')+(7*date_part('week',now()));
>
>and the above +6 to the the last day of the week. Another approach for
>this same question is much simplier (if the question is indeed what you
>are asking)
>
>select now()-date_part('dow',now());
>
>This last select gives the Sunday for the current week. To get the
>Saturday, simply:
>
>select now()-date_part('dow',now())+6;
>
>Of course, replace the now() with whatever contains the date or timestamp.
>
>John McKown
>
> > I'm probably staring right at it. (One of the difficulties with RTFMing, is
> > having too many docs!)
> >
> > Is there anything in the API that produces the week of the year, from 1 to
> > 52 or 53 depending on the week of the year, and the days that are in that
> > week?
> >
> > Many thanks.
> >
> > -dlj.
> >
> >
> >
[SQL] Multiple Index's
Hello all, How would I prevent a user from submitting information to a table once they have already done so for that day. I would need them to be able information on future dates as well as have information in the table from past dates from that user. I am looking for something like insert user_id, date, info where user_id and date are not the same... does that make sense? Brian C. Doyle
Re: [SQL] Multiple Index's
See if this help the table has userid | date | helped_customers An employ will enter in their userid, the date and how many customer they helped that day. What I want to do is prevent the employees from enter the data more than once a day At 10:28 AM 9/21/00 -0700, Mitch Vincent wrote: > > Hello all, > > > > How would I prevent a user from submitting information to a table once >they > > have already done so for that day. > >The best you could probably do is to go back and delete undesired recoords >at the end of the day because if it is as you said, they've already put the >information into the database. > > > I would need them to be able > > information on future dates as well as have information in the table from > > past dates from that user. > >Not positive what you mean here but just use a date (or timestamp) column in >the table to indicate when the record was added. > > > I am looking for something like insert user_id, date, info where user_id > > and date are not the same... does that make sense? > >Nope, it doesn't -- at least to me :-) > >How about some table structures and some more information, I'm just not >exactly sure what you'd like to do.. > >-Mitch
Re: [SQL] Convert from Seconds-Since-Epoch to Timestamp
how would you do that with in a query? ie select date , interval(reltime 'table.secs'); when the value in table.secs = 54321 and "secs" is not a part of it? At 11:35 AM 9/21/00 -0700, Jie Liang wrote: >Hi, Webb, > >I am not quit sure what you really want to do, however, I assume that >following maybe >what you mean: > > >db=# select interval(reltime '12313221 secs'); > interval > > 4 mons 22 12:20:21 >(1 row) > > >Webb Sprague wrote: > > > Hi all, > > > > How do I convert from seconds (an integer) to > > timestamp? > > > > I am sure it is easy, but I can't find it in the docs, > > so far. > > > > Thanks > > Webb > > > > __ > > Do You Yahoo!? > > Send instant messages & get email alerts with Yahoo! Messenger. > > http://im.yahoo.com/ > >-- >Jie LIANG > >Internet Products Inc. > >10350 Science Center Drive >Suite 100, San Diego, CA 92121 >Office:(858)320-4873 > >[EMAIL PROTECTED] >www.ipinc.com
[SQL] Table Attribute Help
Hello all, I am trying to find a query to retrive the attributes of a table as in \d tablename but as a select command. Is this possible?
Re: AW: [SQL] Table Attribute Help
That is great thank you. How would I grab the attribute type for an attribute with it so that the out put would look like attname atttype -- -- userid varchar(30) I know that is not correct but is it possible to get that out put At 05:27 PM 10/9/00 +0200, you wrote: >yes it's possible, > >SELECT pg_attribute.attname >FROM pg_class, pg_attribute >WHERE >pg_class.relname = 'xxx' and pg_attribute.attrelid = pg_class.oid > >and pg_attribute.attnum>=1 order by pg_attribute.attnum; > >xxx is your tablename! > > >-Ursprüngliche Nachricht- >Von: Brian C. Doyle [mailto:[EMAIL PROTECTED]] >Gesendet: Montag, 9. Oktober 2000 17:21 >An: [EMAIL PROTECTED] >Betreff: [SQL] Table Attribute Help > > >Hello all, > >I am trying to find a query to retrive the attributes of a table as in \d >tablename but as a select command. Is this possible?
[SQL] if else query help
Hello all, I need to write a query that will pull information from table2 if information in table1 is older then xdate. My laymen example: SELECT table2.date, count(table2.name) as count WHERE table1.startdate > 2 weeks AND table2.submitdate > 2 weeks ; So i Guess my real questions is how do I determine the age of an entry to another table?
Re: [SQL] COUNT
Hello, You will need to do "SELECT count(attribute) FROM table;" or SELECT count(table.attribute);" At 04:58 AM 10/20/00 +, Craig May wrote: >Hi, > >How do I get a row count, like "Select [COUNT] from Table" ?? > >Regards, >Craig May > >Enth Dimension >http://www.enthdimension.com.au
Re: [SQL] Alternate Database Locations
Never mind... I got it working At 02:40 PM 10/24/00 -0400, Brian C. Doyle wrote: >Hello, > >I am working on seting up alternate database locations for my users. >I have done > >% initlocation /home/userid >% createdb -D /home/userid userid > >and i always get > >ERROR: The database path '/home/httpd/jbbent' is invalid. This may be due >to a character that is not allowed or because the chosen path isn't >permitted for databases > >I have done both of these as the user and as postgres I have had the dir >chmod to 777 and still nothing. what am I doing wrong? >
[SQL] Alternate Database Locations
Hello, I am working on seting up alternate database locations for my users. I have done % initlocation /home/userid % createdb -D /home/userid userid and i always get ERROR: The database path '/home/httpd/jbbent' is invalid. This may be due to a character that is not allowed or because the chosen path isn't permitted for databases I have done both of these as the user and as postgres I have had the dir chmod to 777 and still nothing. what am I doing wrong?
Re: [SQL] Alternate Database Locations
Well I got it working kinda. You have set up an environment for the new directory and export it under the PGSQL super user account PGDATA2=/home/userid export PGDATA2 Then run initlocation PGDATA2 After that you have to restart your postmaster with the data dir as PGDATA2 postmaster -d PGDATA2 Now you can greate a new db in a new location createdb mydb -D PGDATA2 that is it and you have a new location for ALL your database if you use it ... If you do not add -D PGDATA2 for future db it writes to the default location but you may have problems access it. I am still trying to find out how to get multiple postmasters running on different ports at the same time. Does anyone have any clue how to do that? At 12:04 PM 10/25/00 -0700, Roderick A. Anderson wrote: >On Tue, 24 Oct 2000, Brian C. Doyle wrote: > > > Never mind... I got it working > >OK, don't play your cards so close to the vest. What was the problem (and >solution)? > > >TIA, >Rod >-- >Roderick A. Anderson >[EMAIL PROTECTED] Altoplanos Information Systems, Inc. >Voice: 208.765.6149212 S. 11th Street, Suite 5 >FAX: 208.664.5299 Coeur d'Alene, ID 83814
Re: [SQL] Alternate Database Locations
Okay I am still doing something wrong here I set PGDATA2=/home/user1/database export PGDATA2 then I start postmaster postmaster -d PGDATA2 -i -p 5431 & and i get Can't create pid file: /usr/local/pgsql/data/postmaster.pid Is another postmaster (pid: 10686) running? What am I missing... I know it must be simple!!! Thanks for all of your help! At 10:43 PM 10/25/00 -0400, Tom Lane wrote: >"Brian C. Doyle" <[EMAIL PROTECTED]> writes: > > I am still trying to find out how to get multiple postmasters running on > > different ports at the same time. Does anyone have any clue how to do > that? > >Uh, you just do it. Start each postmaster in a different data directory >and with a different port number (-D and -P switches) and you're set. > >Unless you run out of shared memory or some such, in which case some >tweaking of kernel parameters is called for... > > regards, tom lane
Re: [SQL] Alternate Database Locations
Okay... I can now run as many postmasters as I could imagine... KICK BUTT I am still running into a problem with the "alternate database locations" and that is I seem to be able to only set up 1 other alternate location. After getting the first alt location setup Iset PGDATA2= /home/user1/database export it initdb -D /home/user1/database started postmaster -D /home/user1/database initlocation 'PGDATA2' createdb user1 -D 'PGDATA2' it creates but it is putting it in /home/user/database the first alt location i set up!!! I know I am being a pain in the ass but I truly do appreciate all the help At 10:02 AM 10/26/00 -0400, Tom Lane wrote: >"Brian C. Doyle" <[EMAIL PROTECTED]> writes: > > Okay I am still doing something wrong here > > I set PGDATA2=/home/user1/database > > export PGDATA2 > > > then I start postmaster > > postmaster -d PGDATA2 -i -p 5431 & > >I think you are confusing the "alternate database directory" stuff with >having a separate installation (primary directory). To set up a second >postmaster, you first have to initdb its installation directory: > > initdb -D /home/user1/database > >then start the postmaster like so: > > postmaster -D /home/user1/database -p whatever ... > >Instead of -D (note upper case), you can alternatively set env variable >PGDATA for these two programs. > >The stuff in the manual about alternate database directories is to allow >a *single* postmaster to manage databases located somewhere other than >underneath its installation directory. It's got nothing at all to do >with starting additional postmasters. > > regards, tom lane
[SQL] Query Help
What do I have to do a query where information in table1 is not in table2 I am looking for something like Select table1.firstname where table1.firstname is not in table2.firstname and table2.date='yesterday' I tried Select table1.firstname where table1.firstname != table2.firstname and table2.date='yesterday' and that did not work just listed everyone
Re: [SQL] Query Help
Thank you to everyone with their suggestions. Where on the PostgreSQL site would I have found more info on the NOT EXISTS At 11:20 AM 12/27/00 -0500, you wrote: >What do I have to do a query where information in table1 is not in table2 > >I am looking for something like > >Select table1.firstname where table1.firstname is not in table2.firstname >and table2.date='yesterday' > >I tried >Select table1.firstname where table1.firstname != table2.firstname and >table2.date='yesterday' > >and that did not work just listed everyone >
Re: [SQL] Problem with Day of Week
Keith,
Try:
select to_char('now'::timestamp,'Dy');
to_char
-
Mon
(1 row)
--
DAY = full upper case day name (9 chars)
Day = full mixed case day name (9 chars)
day = full lower case day name (9 chars)
DY = abbreviated upper case day name (3 chars)
Dy = abbreviated mixed case day name (3 chars)
dy = abbreviated lower case day name (3 chars)
DDD = day of year (001-366)
DD = day of month (01-31)
D = day of week (1-7; SUN=1)
-
Taken from
http://www.postgresql.org/users-lounge/docs/7.0/user/functions2972.htm
At 12:55 PM 1/29/01 -0500, Keith Perry wrote:
>Greetings,
>
>I notice some talk about date problems and interestingly enough planning
>out an application in which I will need to be able to manipulate dates.
>I notice however that there seems to be a discrepancy with the day or
>week in 7.0.3
>
>---
>
>pmhcc=# select date_part('dow','now'::timestamp);
> date_part
>---
> 1
>(1 row)
>
>pmhcc=# select to_char('now'::timestamp,'D');
> to_char
>-
> 2
>(1 row)
>
>pmhcc=# select version();
>version
>-
> PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
>(1 row)
>
>pmhcc=# select now();
> now
>
> 2001-01-29 12:57:46-05
>(1 row)
>
>---
>
>Now as far as I know, Sunday is supposed to 1, so I would think that
>date_part is doing something funky unless I am mis-understanding
>something. I was hoping that someone might be able to shead some light
>on this. Thanks.
>
>Keith C. Perry
>VCSN, Inc.
>http://vcsn.com
[SQL] Complex Query Help
Hello all, I have a table of work shifts. It has Table "teams" Attribute | Type | Modifier +---+-- team_id| varchar(50) | team_name | varchar(100) | location | varchar(100) | department | varchar(100) | shift | varchar(100) | start_time | numeric(30,6) | end_time | numeric(30,6) | mon| boolean | tue| boolean | wen| boolean | thu| boolean | fri| boolean | sat| boolean | sun| boolean | start_day | char(3) | end_day| char(3) | What I need to be able to do is select team_name where team works between a time span ie teams that work between 730 and 1600 how would I do this What type of field should i sue for start_time and end_time? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: enumerating rows
Try this
CREATE SEQUENCE just_a_seq;
Select nextval('just_a_seq') as row_no, * from pg_tables ;
drop SEQUENCE just_a_seq;
>
> row_no | column1 | column2 | ...
> ---+-+-+ ...
> 1 | datum11 | datum12 | ...
> 2 | datum21 | datum22 | ...
>... | ... | ... | ...
>
> I didn't find anything in the docs.
>
> TIA, Zoltan
>
---(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] [HACKERS] why is postgres estimating so badly?
The first thing to point out is that the estimated cost is measured in terms of page reads while the actual time is measured in milliseconds. So even if the cost estimate is accurate it is unlikely that those numbers will be the same. -N -- Nathan C. Burnett Research Assistant, Wisconsin Network Disks Department of Computer Sciences University of Wisconsin - Madison [EMAIL PROTECTED] On Wed, 17 Jul 2002, Luis Alberto Amigo Navarro wrote: > I have a query and estimations and results don´t look similar, here is explain >analyze: > > NOTICE: QUERY PLAN: > > Sort (cost=12443.90..12443.90 rows=1 width=93) (actual time=505331.94..505332.67 >rows=175 loops=1) > -> Aggregate (cost=12443.88..12443.89 rows=1 width=93) (actual >time=472520.29..505326.48 rows=175 loops=1) > -> Group (cost=12443.88..12443.89 rows=1 width=93) (actual >time=472307.31..485173.92 rows=325302 loops=1) > -> Sort (cost=12443.88..12443.88 rows=1 width=93) (actual >time=472307.24..473769.79 rows=325302 loops=1) > -> Nested Loop (cost=12439.25..12443.87 rows=1 width=93) >(actual time=103787.68..441614.43 rows=325302 loops=1) > -> Hash Join (cost=12439.25..12440.64 rows=1 width=85) >(actual time=103733.76..120916.86 rows=325302 loops=1) > -> Seq Scan on nation (cost=0.00..1.25 rows=25 >width=15) (actual time=7.81..8.72 rows=25 loops=1) > -> Hash (cost=12439.25..12439.25 rows=1 width=70) >(actual time=103722.25..103722.25 rows=0 loops=1) > -> Nested Loop (cost=0.00..12439.25 rows=1 >width=70) (actual time=95.43..100162.91 rows=325302 loops=1) > -> Nested Loop (cost=0.00..12436.23 >rows=1 width=62) (actual time=84.91..47502.93 rows=325302 loops=1) > -> Nested Loop >(cost=0.00..12412.93 rows=4 width=24) (actual time=66.86..8806.01 rows=43424 loops=1) > -> Seq Scan on part >(cost=0.00..12399.00 rows=1 width=4) (actual time=24.88..4076.81 rows=10856 loops=1) > -> Index Scan using >partsupp_pkey on partsupp (cost=0.00..13.89 rows=4 width=20) (actual time=0.20..0.34 >rows=4 loops=10856) > -> Index Scan using >l_partsupp_index on lineitem (cost=0.00..6.02 rows=1 width=38) (actual >time=0.20..0.61 rows=7 loops=43424) > -> Index Scan using supplier_pkey on >supplier (cost=0.00..3.01 rows=1 width=8) (actual time=0.08..0.10 rows=1 >loops=325302) > -> Index Scan using orders_pkey on orders >(cost=0.00..3.22 rows=1 width=8) (actual time=0.85..0.87 rows=1 loops=325302) > Total runtime: 505563.85 msec > > estimated 12000msec > > here is the query: > SELECT > nation, > o_year, > CAST((sum(amount))AS NUMERIC(10,2))AS sum_profit > FROM( > SELECT > nation.name AS nation, > EXTRACT(year FROM orders.orderdate) AS o_year, > lineitem.extendedprice*(1-lineitem.discount)-partsupp.supplycost*lineitem.quantity >AS amount > FROM > part, > supplier, > lineitem, > partsupp, > orders, > nation > WHERE > supplier.suppkey=lineitem.suppkey > AND partsupp.suppkey=lineitem.suppkey > AND partsupp.partkey=lineitem.partkey > AND part.partkey=lineitem.partkey > AND orders.orderkey=lineitem.orderkey > AND supplier.nationkey=nation.nationkey > AND part.name LIKE '%green%' > ) AS profit > GROUP BY > nation, > o_year > ORDER BY > nation, > o_year DESC; > > lineitem is about 6M rows > partsupp 800K rows > part 200K rows > > any advice? > Thanks and regards > > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] unnecessary updates
When doing database work over the web, especially when many records are on one page, *many* updates get posted to pg that do not change the record. Eg, the page may contain 50 records, the user changes 1, and submits. I assume that a no-change update takes the same resources as a "real" update, ie, a new block is allocated to write the record, the record written, indicies are rerouted to the new block, and the old block needs to be vacuumed later. Is this true? In SQL, the only way I know to prevent this thrashing is to write the update with an elaborate where clause, eg, "update ... where pk=1 and (c1!='v1' or c2!='v2' or ... )". This adds cost both to the app server and to pg - is the cost justified? Finally, is there anyway to flag pg to ignore no-change updates? This seems to me to me the most efficient way of handling the needless work. thanks chester __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] What benefits can I expect from schemas ?
> > I could not find much documentation about SQL 92 schemas that > > 7.3 now supports. I understood it was a structure to group various > > objects, which allows faster privilege management, namespaces > > definition. > > Besides that, I don't see any advantages. I'd be glad if someone > > could point them out for me, or give doc links. > > That's pretty much it. You can give each user their own namespace so > they can all create tables with the same name, or put applications in > their own namespace so they don't conflict with other applications. > You can also control object creation easier by restricting privs on > the > public namespace. Can you copy by schema? __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] cannot create function that uses variable table name
--- Matthew Nuzum <[EMAIL PROTECTED]> wrote: > I thought I would be crafty and devise a function that would always > return the highest numbered item in the table. But it doesnt work. > It always gives me a parse error at $1. Heres the function: build the query as a string and execute it. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Inheritence and Integrity
> inheriting pk and triggers pg inheritance is quite limited. what i (and i'm sure many others) have done is: 1. create master sequence 2. create base table 3. create base trigger procedures 4. create derived tables, using "inherit" 5. write procedure p( table_name ) that a) sets pk of table_name using master sequence b) attaches base trigger procedures onto table_name 6. run procedure p() against each derived table another way to skin this cat is to use "objects" in the database: -- base table table common( int id primary key ..., ref_tab name, -- name of secondary table using common ...-- common columns and constraints ) without oids; -- secondary table table secondary1( int id1 not null references common(id), int id2 primary key, -- (can use id1 as pk!) ... -- secondary columns and constraints ) without oids; -- views for secondary table - generate! create secondary1_v1 as select c.*, s.* from secondary1 s join common c on( s.id1 = c.id ); -- (if you want) dml for view to make life easier - generate! ... if you are maintaining the common info, or if you want a many to one secondary to master, this approach is easier. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] rownum
sorry about this - braindead and cannot find in doc. what's pg's rownum pseudo-column or function name that returns the record number of a set? __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] PL/Pgsql trigger function problem.
This is suppose to pull all the columns of the table that initiated the trigger func from the sys catalogs, loop through them and put everything that has changed between OLD and NEW into a comma delimited string for input into a log like table for future analysis via middleware (php,perl..,etc). Here is the problem, OLD.A results in 'old does not have field A', which is true. I cant get the OLD and NEW record objects to realize that I want OLD. for the column name instead of an explicit A as the column name. The only way I can find to make this work is by using TCL for the procedural language because of the way it casts the OLD and NEW into an associative array instead of a RECORD object, but by using TCL I will lose functionallity in the "complete" version of the following function which has been stripped to show my specific problem so using TCL is currently not in my list of options. Any insight will be greatly appreciated. create or replace function hmm() returns TRIGGER as 'DECLAREtable_cols RECORD;attribs VARCHAR;A VARCHAR;BEGINIF TG_OP = ''UPDATE'' THEN FOR table_cols IN select attname from pg_attribute where attrelid = TG_RELID and attnum > -1 LOOP A := table_cols.attname; IF OLD.A != NEW.A THEN --Begin problem IF attribs != THEN attribs := attribs || '','' || table_cols.attname || ''='' || OLD.A || ''->'' || NEW.A; ELSE attribs := table_cols.attname || ''='' || OLD.A || ''->'' || NEW.A; END IF; END IF; END LOOP;END IF;RAISE EXCEPTION ''%'', attribs;RETURN NULL;END;' Language 'plpgsql';
Re: [SQL] Rows as Columns
This is obtuse, but it would work: Have a function that would dynamically build a view that has each of your type names as a column name. It could be triggered by any changes to the types table. The data table would be a left joined onto the customer table for each column. Then select from the view as you would any other view. If you'd like some suggestions on the procedure, let me know. --- James Taylor <[EMAIL PROTECTED]> wrote: > Hi everyone, I just joined this list a few days ago... > > Now, I have a question, and I'm not even sure if this is possible > without having to make numerous statements. > > Say I have two tables with the following columns and this data: > > types: > id | name > - > 1| first_name > 2| last_name > 3| phone_number > > data: > id | data | t_key | cust_id > - > 1| Sam | 1 | 1 > 2| Smith| 2 | 1 > 3| 555-1212 | 3 | 1 > 4| John | 1 | 2 > > > Types basically describes what the column type is. t_key references > the > type, cust_id is the user id. > > i'd want to do something like "select t.name, d.data from types t, > data > d where d.cust_id = 1" > > This would return something like: > > name | data > > first_name | Sam > last_name| Smith > phone_number | 555-1212 > > > Well, I need it to somehow return that data in this format: > > first_name | last_name | phone_number > > Sam | Smith | 555-1212 > > The information in Types is not static, so I can't declare the col > names based on what you see here. > > Any suggestions on this one? __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Domains and Joins
-- standard setup: create table t1( c1 int primary key, data text ); create domain dom_c1 int references t1 on delete cascade; create table t2( c2 int primary key, c1 dom_c1, moredata text ); -- will not work with "using" create view v1 as select t1.*, t2.moredata from t1 join t2 using( c1 ); -- will work with "on" create view v1 as select t1.*, t2.moredata from t1 join t2 on t1.c1 = t2.c1; is this right? __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] create tables within functions
--- Demidem Mohamed Amine <[EMAIL PROTECTED]> wrote: > hello, > > Can anyone help me create a function that creates a table: create function create_table( text ) returns integer as ' declare p_tab alias for $1; v_exec text; begin v_exec := ''create table '' || p_tab || ''( id integer )''; execute v_exec; end; ' language 'plpgsql'; __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com ---(end of broadcast)--- TIP 9: most folks find a random_page_cost between 1 or 2 is ideal
[SQL] production parameters
What is the best source doc for postgresql setup in a production environment? I have read - giving it a big chuck of shmem - os not marking data files as accessed or modified but cannot find the doc. thanks, Chester __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] sorting
how do you set sorting for indicies and order by? is it set once for the database, or can it be set per index? __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] off subject - pg web hosting
can anybody recomend web hosting that provides postgresql? I have found a couple, but their pricing is several times the going rate using mySql. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Index not recognized
Hello. Why is it that my index for text[] data type is not recognized by the Planner? I did these steps: 1. create function textarr(text[]) returns text language sql as 'select $1[1]' strict immutable 2. create index org_idx on EmpData (textarr(org)); 3. vacuum full 4. explain analyze select name from EmpData where org *= 'math'; Result: = Seq Scan on EmpData (cost=0.00..3193.20 rows=102 width=488) (actual time=3.71.35..371.35 rows=0 loops=1) Filter: (org[0]='math'::text) Total runtime: 371.47 msec (3 rows) == I have 20,496 records. My postgres version is 7.3. Any help is greatly appreciated. Thank you.. Ace ---(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] Rule won't let me NOTIFY, no matter how hard I try
El Mar 13 Ene 2004 18:07, Jeff Boes escribió:
>JB: Here's the setup: I wanted to write a rule that would fire on an update
>JB: to one table, and do an update to another table, followed by a notify.
>JB: My first attempt wasn't acceptable to PG (7.3.4):
>JB:
>JB: create rule "my_rule" as
>JB: on update to table_A
>JB: where new.col_A != old.col_A
>JB: do
>JB:(update table_B ...;
>JB: notify "my_signal";
>JB:);
>JB:
>JB: ... because you can't have a "notify" statement in a rule that fires on
>JB: update (only select, update, and delete, I guess).
>JB:
hi,
you have to write to rules
first one (update):
create rule "my_rule" as
on update to table_A
where new.col_A != old.col_A
do
update table_B ...;
second one (notify):
create rule "my_rule2" as
on update to table_A
notify my_rule;
>JB: Second attempt was to "hide" the notify in a function:
>JB:
>JB: create function fn_notify(TEXT) returns VOID as '
>JB: execute ''notify " || $1 || "'';
>JB: ' language 'plpgsql';
The correct syntax...
create function fn_notify ( text ) RETURNS void as '
declare
v_signal alias for $1;
begin
execute '' notify "'' || v_signal || ''"'';
return;
end;
' language 'plpgsql';
...and re-write the first rule
create rule "my_rule" as
on update to table_A
where new.col_A != old.col_A
do
( update table_B ...;
SELECT fn_notify('my_signal'); );
CAVEAT: This rule always returns a tuple:
fn_notify
---
(1 row)
--
---
Luis Carlos Ferreira [EMAIL PROTECTED]
Centro de CómputosJunin 2957 - Santa Fe - Argentina
Sindicato de Luz y Fuerza Tel.: (54)(342) 4520-075
---
Estas loco? come vaca!!
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] [PHP] [ADMIN] Data insert
On Sun, Aug 21, 2005 at 06:35:22AM +0100, Aldor wrote: > if you want to insert biiig data volumes try either using COPY instead > of INSERT - it will run much much faster And if for some reason you have to stick with inserts, group them into transactions; it will perform much better than individual transactions. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] insert only if conditions are met?
SELECT sum(hours) FROM table WHERE emp_name = 'JSMITH' AND work_date = '8-15-2005'::date will give you the hours. So... INSERT INTO table SELECT blah WHERE (SELECT sum(hours) FROM table WHERE emp_name = 'JSMITH' AND work_date = '8-15-2005'::date) != 8 Should do what you want. On Wed, Aug 31, 2005 at 12:49:14PM -0400, Henry Ortega wrote: > Ok. Here's TABLE A > > emp date hours type > JSMITH 08-15-2005 5 WORK > JSMITH 08-15-2005 3 WORK > JSMITH 08-25-2005 6 WORK > > I want to insert the ff: > 1.) JSMITH 08-15-2005 8 VAC > 2.) DOE 08-16-2005 8 VAC > > #1 should fail because there is already 8 hours entered as being > Worked on 08-15-2005 (same date). > > Any suggestions? > > > > On 8/31/05, Ragnar Hafsta? <[EMAIL PROTECTED]> wrote: > > > > On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote: > > > What I am trying to do is > > > * Insert a record for EMPLOYEE A to TABLE A > > > IF > > > the sum of the hours worked by EMPLOYEE A on TABLE A > > > is not equal to N > > > > > > Is this possible? > > > > Sure, given a suitable schema > > > > It is not clear to me, if the hours worked are > > to be found in the same table you want to insert > > into, or not. > > > > gnari > > > > > > > > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(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] Help with multistage query
On Wed, Sep 07, 2005 at 05:37:47PM -0400, Matt Emmerton wrote:
>
> - Original Message -
> From: Russell Simpkins
> To: pgsql-sql@postgresql.org
> Sent: Wednesday, September 07, 2005 4:05 PM
> Subject: Re: [SQL] Help with multistage query
>
> I have a perl script that issues a series of SQL statements to perform
> some queries. The script works, but I believe there must be a more elegant
> way to do this.
>
>
>
> The simplified queries look like this:
>
>
>
> SELECT id FROM t1 WHERE condition1; ;returns about 2k records which are
> stored in @idarray
>
>
>
> foreach $id (@idarray) {
>
>SELECT x FROM t2 WHERE id=$id; ; each select returns about 100
> records which are saved in a perl variable
>
> }
>
> how about
> select t1.id from t1, t2 where t1.id = t2.id and t2.id = x
>
> or more correctly, based on the OP's example:
>
> select t2.x from t1, t2 where t1.id = t2.id and t1.id =
Actually, I think you want AND t2.x , not t1.id.
BTW, I recommend not using id as a bareword field name. Very easy to get
confused when you start joining a bunch of stuff together.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Why doesn't the SERIAL data type automatically have a
On Tue, Sep 27, 2005 at 10:33:14AM -0500, Scott Marlowe wrote: > On Mon, 2005-09-26 at 20:03, Tom Lane wrote: > > Ferindo Middleton Jr <[EMAIL PROTECTED]> writes: > > > Is there some reason why the SERIAL data type doesn't automatically have > > > a UNIQUE CONSTRAINT. > > > > It used to, and then we decoupled it. I don't think "I have no use for > > one without the other" translates to an argument that no one has a use > > for it ... > > I have to admit, right after the change was made, I was of the opinion > that no one would ever need that. Then, a few months later, it was > exactly what I needed for some project... :) Arguably it would have been better to make the default case add either UNIQUE or PRIMARY KEY with a way to over-ride. If newbies are getting burned maybe it would be useful to toss a NOTICE or maybe even WARNING when a serial is created without a unique constraint of some kind? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Why doesn't the SERIAL data type automatically have a
On Tue, Oct 04, 2005 at 07:50:28PM -0400, Ferindo Middleton Jr wrote: > Based on the feedback I received after I made that original post, it > seemed most people don't use SERIAL with a unique constraint or primary > key and I was blasted for making such a suggestion. I'm sorry... It I don't think either assertion is true. I'd bet most of the developers actually do normally use an index on a serial, since it's normally used as a PK. And while people can be a bit terse with their replies, I wouldn't say you were blasted. :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [DOCS] [SQL] Update timestamp on update
On Wed, Oct 12, 2005 at 10:52:04PM -0400, Tom Lane wrote: > Jeff Williams <[EMAIL PROTECTED]> writes: > > Thanks. Triggers was my first thought, but chapter 35 on Triggers didn't > > really indicate a way I could do this easily and scared me with a lot of > > c code. > > Yeah. This is a documentation issue that's bothered me for awhile. > The problem is that we treat the PL languages as add-ons and therefore > the documentation of the "core" system shouldn't rely on them ... but > that leaves us presenting C-code triggers as the only examples in > chapter 35. There is a paragraph in there suggesting you go look at > the PL languages first, but obviously it's not getting the job done. Chapter 35 is plpgsql.. do you mean chapter 32.4? > Anybody have a better idea? What about a See Also section ala man pages that links to trigger info for other languages? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] broken join optimization? (8.0)
in php (for example) it's frequently nice to get the structure of a table without any data, ie, pull a single row with each attribute's value is null. I use the query (dual is a table of one row ala Oracle): select m.* from dual left join mytable m on( false ); this works every time, but if mytable is big, then takes a long time. needed to rewrite the query to: select m.* from dual left join (select * from mytable limit 1) m on( false ); this works as it should - pulls empty row but fast. it seems to me that a full table scan should not be necessary if the join condition is false. __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs ---(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] broken join optimization? (8.0)
> Tom Lane <[EMAIL PROTECTED]> wrote: >> Michael Fuhr <[EMAIL PROTECTED]> writes: > > On Tue, Oct 25, 2005 at 04:56:11PM -0700, chester c young wrote: > >> in php (for example) it's frequently nice to get the structure of > >> table without any data, > > > Have you considered "SELECT * FROM mytable LIMIT 0"? > > Indeed. i think i misled: the goal is to retrieve _one_ row where the value of each attribute is null. this can be done laborously using meta data, but is done quite niftily using a left join against one row. > > I see the same behavior in the latest 8.1beta code. Maybe one of > > the developers will comment on whether optimizing that is a simple > > change, a difficult change, not worth changing because few people > > find a use for it, or a behavior that can't be changed because of > > something we're not considering. > > Not worth changing --- why should we expend cycles (even if it only > takes a few, which isn't clear to me offhand) on every join query, to > detect what's simply a brain-dead way of finding out table structure? again, the goal is a quick way to retrieve one row from a table where each attribute value is null, NOT to get the table structure. > I can't think of any realistic scenarios for a constant-false join > clause. i would like a better idea on how to retrieve one row from a table where the value of each attribute is null - i felt this a perfectly good use of sql. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.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
Re: [SQL] broken join optimization? (8.0)
> Tom Lane <[EMAIL PROTECTED]> wrote: > chester c young <[EMAIL PROTECTED]> writes: > > i think i misled: the goal is to retrieve _one_ row where the value > of each attribute is null. > > Er, what for? There's no data content in that, by definition. Why > not retrieve zero rows and look at the metadata anyway? > with a form that is used for CRUD, values are filled in from a record (either an object or array). when creating, you want an empty record so that form.item values are set to null. makes for much easier programming and ensures all variables are defined. retrieving the metadata and then creating the record seems like a lot of work when the whole thing can be done with one select (which would needed in any case to get the metadata). __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to speed up the database query?
Have you taken a look at http://www.postgresql.org/docs/8.0/interactive/performance-tips.html ? On Thu, Oct 27, 2005 at 03:03:36PM +0800, Abdul Wahab Dahalan wrote: > Hi everyone! > > I'm looking for solution to speed up the database query, means that to get > resultset as quicker as we can. > > For example if I've 700 records in the table it will take longer time > compared if I've only 20 records. How do we speed up the query?. Any query > technique that can be applied?. > > Thus wild card query like : select * from tableA will cause query time > increased compare to say select a,b from tableA. > > any help, prettymuch appreciated. > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] why vacuum
On Thu, Oct 27, 2005 at 02:21:15PM +0100, Richard Huxton wrote: > So - if your statement contains something non-deterministic that isn't > catered for in Mysql's code then it will break. > > At it's simplest - if I write a function my_random() and then do: > UPDATE foo SET a=1 WHERE b < my_random(); > IF my_random() returns different results on different machines, then the > replication will be broken. See the manual entry below: > http://dev.mysql.com/doc/refman/5.0/en/replication-features.html > > That's not to say the system is worthless - it works fine for many > people. But it does have limitations. And you can easily have multi-master syncronous replication in PostgreSQL using the same idea; just see pgCluster. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] A Not Join
On Tue, Nov 01, 2005 at 04:27:01PM +0200, L van der Walt wrote: > I have three table: > Users - Contains username, ID etc... > Permissions - A permission name and ID > Link up table - The user.id and permission.id > > If a user.id and a permission.id row exists in the linkuptable the user > have that permission granted. > > With the statement below I can see the permissions a user have. > > SELECT users.username, permissions.name > FROM users INNER JOIN linkuptable > ON (users.id = linkuptable.userid) > INNER JOIN permissions > ON (permissions.id = linkuptable.permissionid) > WHERE users.username = 'DummyUser' > > How do I see the permissions that user DON'T have with a fast SQL statement. > > Thus, a NOT the statement for the above SQL statement LEFT JOIN permissions ON (...) WHERE permissions.id IS NULL You might have to do the NULL check in a HAVING clause instead... try it. BTW, this is probably better asked on pgsql-sql. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Nested Table in PostgreSQL or some alternative Variants
What do you mean by 'nested table'? Maybe arrays will do what you want? Typically (and this applies to other databases as well), this is done using two tables and refferential integrity. IE: CREATE TABLE purchase_order( po_id serial CONSTRAINT purchase_order__po_id PRIMARY KEY , customer_id int CONSTRAINT purchase_order__customer_RI REFERENCES customer(id) , more fields... ) CREATE TABLE po_lines ( po_id int CONSTRAINT po_lines__po_id_RI REFERENCES purchase_order(po_id) , line_number smallintNOT NULL , ... , CONSTRAINT po_lines__po_id_line_number PRIMARY KEY( po_id, line_number) ) On Mon, Oct 31, 2005 at 02:22:05PM +0100, Thomas Zuberbuehler wrote: > Hello there > > I've a problem. I can't find some information about nested tables in > PostgreSQL. Is this Features possible in pgsql or not? > > * When yes, how i can use and create nested tables with pgsql? > * When no, which alternative are there (for same problem definition)? > > Thank you for help. > Greetings from Zurich, Switzerland. > Thomas Zuberbuehler > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Problem with "NOT IN (subquery) - use NOT EXISTS
--- Steve SAUTETNER <[EMAIL PROTECTED]> wrote: > SELECT * FROM famille WHERE famille_code NOT IN > (SELECT DISTINCT famille_mere_famille_code FROM famille);" try select * from famille f1 where not exists (select 1 from famille f2 where f1.famille_code = f2.famille_mere_famille_code); __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] deferrable on unique
table t1: id integer primary key, seq integer not null unique the seq is for ordering the rows as the user likes. however, if the rows are moved around, eg begin update t1 set seq=4 where id=5 update t1 set seq=5 where id=4 end will bomb because the first update has two rows of seq=4 (although correct after the transaction). I thought "deferrable initally deferred" would fix this, but the phrase is not allowed on unique. is this correct? any ideas? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Does VACUUM reorder tables on clustered indices
On Sun, Dec 18, 2005 at 07:01:39PM -0300, Alvaro Herrera wrote: > Martin Marques escribi?: > > On Sun, 18 Dec 2005, frank church wrote: > > > > > > > >Does VACUUMing reorder tables on clustered indices or is it only the > > >CLUSTER > > >command that can do that? > > > > Cluster does that. Vacuum only cleans dead tuples from the tables. > > Note that while reordering, CLUSTER also gets rid of dead tuples, so if > you cluster you don't need to vacuum. It also does a REINDEX... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Does VACUUM reorder tables on clustered indices
On Wed, Dec 21, 2005 at 12:34:12AM +0100, [EMAIL PROTECTED] wrote: > Hi, > > Utilize CLUSTER; (after vacuum) to reorder the data. Why would you vacuum when cluster is just going to wipe out the dead tuples anyway? > >>Note that while reordering, CLUSTER also gets rid of dead tuples, so if > >>you cluster you don't need to vacuum. > > > >It also does a REINDEX... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] plpgsql triggers in rules
is is possible for to have a "do instead" trigger on a view that is a plpgsql function? __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] session variables in 8.0
understand that in 8.0 pg has session variables. cannot find in doc how to use them. __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] exceptions in rules
is there any way within a rule to raise an exception? __ Yahoo! for Good - Make a difference this year. http://brand.yahoo.com/cybergivingweek2005/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] best way for constants in the database
anybody have a good way to impliment constants in the database? using an immutable pgpgsql function, constant_name() - works, but not necessarily very efficient. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] group by complications
--- Mark Fenbers <[EMAIL PROTECTED]> wrote: > select l.lid,l.fs,max(h.obstime) from location as l > inner join height as h on h.lid = l.lid > where l.fs > 0.0 > group by l.lid,l.fs; > > The above query works as expected in that is fetches the lid, fs and > time of the latest observation in the height table (for the > corresponding lid), but I also want to fetch (i.e., add to the select > list) the corresponding reading (h.obsvalue) which occurs at > max(h.obstime). I'm having trouble formulating the correct SQL > syntax > to pull out the l.lid, l.fs, and the most recent h.obvalue (with or > without the time that it occurred). > > Logistically, I want to do something like this: > > select l.lid,l.fs,most_recent(h.obsvalue) from location as l > inner join height as h on h.lid = l.lid > where l.fs > 0.0 > group by l.lid,l.fs; > use your original query as part of the from clause, then add columns to it through a subquery or a join. try something like this: select q1.*, (select obsvalue from height where lid=q1.lid and obstime=q1.obstime) as obsvalue from (select l.lid,l.fs,max(h.obstime) as obstime1 from location as l inner join height as h on h.lid = l.lid where l.fs > 0.0 group by l.lid,l.fs ) q1; __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Non Matching Records in Two Tables
> Here is my query SQL: > > SELECT key100 FROM ncccr10 > WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9); > > It is is running after 30 minutes. Here is the query plan: > I would try an outer join: select a.key100 from ncccr10 a left join ncccr9 b on( key100 ) where b.key100 is null; also (hate to be obvious) have you analyzed lately? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.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
[SQL] grant select,... over schema
is there any way to grant over all applicable objects in a schema, > grant select on schema pop to public; <-- wrong without specifically granting the select on each table? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] grant select,... over schema
do not want to grant ALL PRIVILEGES to user, only SELECT - do not want PUBLIC to have any dml priveleges on this schema --- Michael James <[EMAIL PROTECTED]> wrote: > GRANT ALL PRIVILEGES ON databaseName To username; > > > is there any way to grant over all applicable objects in a schema, > > > grant select on schema pop to public; <-- wrong > > without specifically granting the select on each table? > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
