[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])
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] 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] 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
[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
Re: [SQL] Is there any way to stop triggers from cycling?
trying to do this exlusively in triggers is a forray into folly. take advantage of "instead of" or "do also" rules to create a compound statement before your triggers do their work. (in terms of maintenance and sanity, it's best if a trigger touches only its own record.) as a handsweep example: create view tree_v as select * from tree; grant select, insert, update on tree_v to public; create or replace rule 'tree_update' as on update to tree_v do instead( -- update tree set seq = seq+1 where old.pnt=new.pnt and old.seqhttp://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] Question re: relational technique
--- Robert Paulsen <[EMAIL PROTECTED]> wrote: > One problem with the above is that the list of attributes is fixed. I > am looking for a way to assign new, previously undefined, attributes to > items without changing the table structure. Is it ever appropriate to do > the following? > ... There are two ways for extending tables, one static and one dynamic. Your scheme is dynamic. You will have problems with typing and performance. What you really want is to be able to list more attributes, similar to attributes attached to a tag in html. If you want a base table that has, under different specified conditions, extra attributes, there are better techniques. IMHO the best solution is, for each condition, create a table containing the primary table's id plus the extra attributes; then join that to the base table; then write a view to cover it all; then write rules for dml. Sounds difficult but a cake walk once you've done it a few times. __ 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] to_char with time
this does not work: select to_char(current_time,'HH24:MI')what am I missing? is it possible to format a time column in a select?thanks,stumped, aka, chester __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[SQL] sessions and prepared statements
in PHP for example, where there are multiple sessions and which you get is random:how do you know if the session you're in has prepared a particular statement?and/or how do you get a list of prepared statements?last, is there any after login trigger that one could use to prepare statements the session would need? or is this a dumb idea?thankschester __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[SQL] how to tell if column set on update
within a trigger need to know if the UPDATE statement set a column. the column might be set to the old value or a different value. (want to make sure the app is sending all necessary values) thanks -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] unused columns in copy
is there a way for COPY FROM to ignore unused columns in CSV? in other words, if table t1 has columns c1, c2, and if csv has columns c1, c3, c2, could I do something like COPY t1( c1, null, c2 ) FROM 'file.csv' -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to tell if column set on update
> Le 20/07/09 15:19, chester c young a écrit : > > within a trigger need to know if the UPDATE statement > set a column. the column might be set to the old value > or a different value. > > > > (want to make sure the app is sending all necessary > values) > > > > thanks > > > > If the column to test is known -- e.g. column MyCol --, > NEW.MyCol and > OLD.MyCol -- respectively value of MyCol after UPDATE and > value of MyCol > before UPDATE -- can be compared. for example, create table t1( c1 int, c2 int ); insert into t1 values( 1, 2 ); 1) update t1 set c1=4 where c1=1; 2) update t1 set c1=4, c2=2 where c1=1; each update results in the same row, but in the second update c2 was actually set. a trigger on the update - how do we know if c2 has been actually set or not? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] best performance for simple dml
what is the best performance / best practices for frequently-used simple dml, for example, an insert 1. fast-interface 2. prepared statement calling "insert ..." with binary parameters 3. prepared statement calling "myfunc(..." with binary parameters; myfunc takes its arguments and performs an insert using them
Re: [SQL] best performance for simple dml
two questions: I thought copy was for multiple rows - is its setup cost effective for one row? copy would also only be good for insert or select, not update - is this right? --- On Mon, 6/27/11, Pavel Stehule wrote: From: Pavel Stehule Subject: Re: [SQL] best performance for simple dml To: "chester c young" Cc: [email protected] Date: Monday, June 27, 2011, 12:35 AM Hello try it and you will see. Depends on network speed, hw speed. But the most fast is using a COPY API http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html Regards Pavel Stehule 2011/6/27 chester c young what is the best performance / best practices for frequently-used simple dml, for example, an insert 1. fast-interface 2. prepared statement calling "insert ..." with binary parameters 3. prepared statement calling "myfunc(..." with binary parameters; myfunc takes its arguments and performs an insert using them
Re: [SQL] best performance for simple dml
very nice pointers. thank you very much! --- On Mon, 6/27/11, Pavel Stehule wrote: From: Pavel Stehule Subject: Re: [SQL] best performance for simple dml To: "chester c young" Cc: [email protected] Date: Monday, June 27, 2011, 1:05 AM 2011/6/27 chester c young > > two questions: > I thought copy was for multiple rows - is its setup cost effective for one > row? I expect it will be faster for one row too - it is not sql statement if you want to understand to performance issues you have to understand to a) network communication costs b) SQL parsing and SQL planning costs c) commits costs d) other costs - triggers, referential integrity costs > > copy would also only be good for insert or select, not update - is this right? sure, If you need to call a lot of simple dml statement in cycle, then a) try tu move it to stored function b) if you can't to move it, then ensure, so statements will be executed under outer transaction slow code for(i = 0; i < 1000; i++) exec("insert into foo values($1), itoa(i)); 10x faster code exec('begin'); for(i = 0; i < 1000; i++) exec("insert into foo values($1), itoa(i)); exec('commit'); Regards Pavel Stehule > > --- On Mon, 6/27/11, Pavel Stehule wrote: > > From: Pavel Stehule > Subject: Re: [SQL] best performance for simple dml > To: "chester c young" > Cc: [email protected] > Date: Monday, June 27, 2011, 12:35 AM > > Hello > > try it and you will see. Depends on network speed, hw speed. But the most > fast is using a COPY API > > http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html > > Regards > > Pavel Stehule > > > 2011/6/27 chester c young > > what is the best performance / best practices for frequently-used simple dml, > for example, an insert > 1. fast-interface > 2. prepared statement calling "insert ..." with binary parameters > 3. prepared statement calling "myfunc(..." with binary parameters; myfunc > takes its arguments and performs an insert using them >
Re: [SQL] best performance for simple dml
forgive me for brain storming a little re copy: if there are a limited number of tables you're inserting, would there be anything wrong with the app opening a copy connection? ie, a connection initiates the copy and then stays open like a pipe for any inserts coming through it. visually it's a very cool paradigm, but is it actually a good idea? --- On Mon, 6/27/11, Pavel Stehule wrote: From: Pavel Stehule Subject: Re: [SQL] best performance for simple dml To: "chester c young" Cc: [email protected] Date: Monday, June 27, 2011, 1:05 AM 2011/6/27 chester c young > > two questions: > I thought copy was for multiple rows - is its setup cost effective for one > row? I expect it will be faster for one row too - it is not sql statement if you want to understand to performance issues you have to understand to a) network communication costs b) SQL parsing and SQL planning costs c) commits costs d) other costs - triggers, referential integrity costs > > copy would also only be good for insert or select, not update - is this right? sure, If you need to call a lot of simple dml statement in cycle, then a) try tu move it to stored function b) if you can't to move it, then ensure, so statements will be executed under outer transaction slow code for(i = 0; i < 1000; i++) exec("insert into foo values($1), itoa(i)); 10x faster code exec('begin'); for(i = 0; i < 1000; i++) exec("insert into foo values($1), itoa(i)); exec('commit'); Regards Pavel Stehule > > --- On Mon, 6/27/11, Pavel Stehule wrote: > > From: Pavel Stehule > Subject: Re: [SQL] best performance for simple dml > To: "chester c young" > Cc: [email protected] > Date: Monday, June 27, 2011, 12:35 AM > > Hello > > try it and you will see. Depends on network speed, hw speed. But the most > fast is using a COPY API > > http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html > > Regards > > Pavel Stehule > > > 2011/6/27 chester c young > > what is the best performance / best practices for frequently-used simple dml, > for example, an insert > 1. fast-interface > 2. prepared statement calling "insert ..." with binary parameters > 3. prepared statement calling "myfunc(..." with binary parameters; myfunc > takes its arguments and performs an insert using them > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] partitions versus databases
have an db with about 15 tables that will handle many companies. no data overlap between companies. is it more efficient run-time to use one database and index each row by company id, and one database and partition each table by company id, or to create a database for each company? it is a web-based app using persistent connections. no copying. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] pg_dump not correctly saving schema with partitioned tables?
have database with many partitions. each partition table has its own primary
key sequence.
Column || Modifiers
---++--
uno_id|| not null default nextval('cmp0004.cmt_uno_id_seq'::regclass)
when dumped and then resorted, the pk sequence is changed to an inherited- from
table:
Column|| Modifiers
--++--
uno_id || not null default nextval('uno_uno_id_seq'::regclass)
(another error is that the pk sequence does not spec the schema!)
is there any way I can reliably backup and restore the db?
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Fw: Re: [SQL] pg_dump not correctly saving schema with partitioned tables?
> From: chester c young > Subject: Re: [SQL] pg_dump not correctly saving schema with partitioned > tables? > To: "Tom Lane" > Date: Tuesday, January 31, 2012, 5:40 PM > --- On Tue, 1/31/12, Tom Lane > wrote: > > > From: Tom Lane > > Subject: Re: [SQL] pg_dump not correctly saving schema > with partitioned tables? > > To: "chester c young" > > Cc: [email protected] > > Date: Tuesday, January 31, 2012, 2:04 PM > > chester c young > > writes: > > > have database with many partitions. each > > partition table has its own primary key sequence. > > > Column || > > > > Modifiers > > > > > > > > > > ---++-- > > > uno_id || not null default > > nextval('cmp0004.cmt_uno_id_seq'::regclass) > > > > > when dumped and then resorted, the pk sequence is > > changed to an inherited- from table: > > > > > > > Column || > > > > Modifiers > > > > > > > > --++-- > > > uno_id || not null > > default nextval('uno_uno_id_seq'::regclass) > > > > > (another error is that the pk sequence does not > spec > > the schema!) > > > > I see no reason to think there is a bug here; it's > more > > likely that you > > do not understand the display behavior of regclass > > constants. They only > > print a schema name if your current search_path is such > that > > the > > relation wouldn't be found by writing just the > unqualified > > name. > > So the most likely explanation for the discrepancy > above is > > that you > > executed the two \d commands under different > search_path > > settings. > > > > It's possible that you have actually found a pg_dump > bug, > > but if so > > you'll need to submit a complete test-case exhibiting > the > > bug. > > > here is a test case: > > > create database test2; > \c test2; > > create schema s1; > create schema s2; > > create table s1.t1( > c1 bigserial primary > key, > c2 text ); > > create table s2.t1( > c1 bigserial primary > key > ) inherits( s1.t1 ); > > test2=# \d s2.t2 > > Table "s2.t2" > Column | Type | > > Modifiers > > ++ > c1 | bigint | not null default > nextval('s2.t2_c1_seq'::regclass) > c2 | text | > > -- pg_dump test2 > test2.dmp > > create database test3 > \c test3 > \i test2.dmp > > test3=# \d s2.t1 > > Table "s2.t1" > Column | Type | > > Modifiers > > ++ > c1 | bigint | not null default > nextval('s1.t1_c1_seq'::regclass) > c2 | text | > > ## in the original db table s2.t1 pk is using s2.t1_c1_seq > ## in the dumped db table s2.t1 pk is using s1.t1_c1_seq > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] possible bug in psql
> > do not know if right mailing list
> >
> > in psql in Ubuntu, when beginning with a smaller
> terminal, eg, 80
> > col wide, then moving to a larger terminal, eg, 132 col
> wide, the
> > readline(?) editor in psql still treats like 80 cols,
> making it
> > impossible to edit longer text.
>
> As far as I know (as this is the behavior in debian stable)
> the ubuntu people use libgettext and not libreadline.
>
> You can force it to use libreadline by using
> LD_PRELOAD. I reccomend
> that you do I did this by editing /usr/bin/psql which is a
> perl script
>
> In any case linux doesn't propogate SIG_WINCH to the other
> processes
> that are using the pty so even if you are using readline you
> have to
> force this by resizing the window again after exiting the
> pager, and
> then press ctrl-l to clear the screen.
seems to me that Ubuntu is using readline (but I might be wrong)
from /usr/bin/psql:
# libreadline is a lot better than libedit, so prefer that
if ($cmdname eq 'psql') {
my @readlines;
push @readlines, sort();
if (@readlines) {
$ENV{'LD_PRELOAD'} = ($ENV{'LD_PRELOAD'} or '') . ':' . $readlines[-1];
}
}
also, LD_PRELOAD is not referenced anywhere in /etc/postgrest/...
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] possible bug in psql
do not know if right mailing list in psql in Ubuntu, when beginning with a smaller terminal, eg, 80 col wide, then moving to a larger terminal, eg, 132 col wide, the readline(?) editor in psql still treats like 80 cols, making it impossible to edit longer text. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] regexp_replace usage
column name in table bue has miscapitalized Mc names, eg, 'John Mcneil' instead of 'John McNeil'.(this should be easy but) how do you construct the update query?also, regexp_string( 'Mcneil', 'Mc(.*)', initcap('\\1') ) => 'neil' _not_ Neil' - is this correct?
All-new Yahoo! Mail - Fire up a more powerful email and get things done faster.
[SQL] tree-structured query
in a simple tree structured tabletable t( id primary key, pnt_id references t( id ), name);does anyone know an easy howbeit sneaky way of determining ancestory and decendency without recursive functions,select name from t where exists thanks for insight Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small Business.
Re: [SQL] regexp_replace usage
Thanks !Michael Fuhr <[EMAIL PROTECTED]> wrote:Offhand I can't think of a way to do what you want with regexp_replace()but you could use PL/Perl. Something like this should work:CREATE FUNCTION mcfix(text) RETURNS text AS $$ $_[0] =~ s/\bMc([a-z])/Mc\u$1/g; return $_[0];$$ LANGUAGE plperl IMMUTABLE STRICT;SELECT mcfix('John Mcneil');mcfix- John McNeil(1 row)
How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
[SQL] timestamps over the web - suggestions
My server is based MST, but web clients from Maine to Hawaii, and they wish to see timestamps based in their own locale.Can anyone tell me how they're handling this? (sorry - can't get rid of my clients) Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail.
Re: [SQL] delete on cascade
--- Luca Ferrari <[EMAIL PROTECTED]> wrote: > Hi all, > I guess this is an already asked question, but I didn't found an > answer, so > apologize me. Imagine I've got two tables: > skill(id,description) // primary key => id > family(id,description)// primary key => id > and I want to associate skills to families: > ass_sf(id_skill,id_family)// appropriate foreign keys > > Tables are already created and the database is running. Now I'd like > to > implement a delete cascade, thus when I delete a skill also its > association > with the family must be deleted. I gues I should have declared the > skill > table as follows: > CREATE TABLE skill > ( > id varchar(20) on delete cascade, > description varchar(50), > primary key(id) > ); > > right? The problem is: how can I alter the table to add the column > constraint > now, without redeclaring the table? > Thanks very much for helping me. > Luca drop the constraint and re-declare it. (you can see the constraint name by \dt skill from psql.) __ 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] Add calculated fields from one table to other table
roopa perumalraja <[EMAIL PROTECTED]> wrote: Hi I have two tables. Tick table has fields like ticker, time, price & volume and Timeseries table has fields like ticker, time, avg_price, avg_volume. The time field in Timeseries table is different from time in tick table, its the timeseries for every minute. Now I want to calculate the average price & volume from tick table for each ticker and for every minute and add those fields to timeseries table. Can anyone please help me out with the sql query. Note: The ticker in the tick table also has duplicate values, so i am not able to create relation between two tables. Thanks in advance Roopa You can't get there from here. It is like saying a hitter has a .250 average and in this game he was 2 for 4, what's his average? Timeseries must also have quantity and price, just as you would need to know number of bats and hits for the baseball player. Want to be your own boss? Learn how on Yahoo! Small Business.
Re: [SQL] or function
--- "A. R. Van Hook" <[EMAIL PROTECTED]> wrote: > I have been trying to do an 'or' function such that if a field value > is > zero then use 1 as a multiplier: > "select sum((1 | i.count) * s.cost) as COST ... try "select sum( (case when i.count=0 then 1 else i.count end) * s.cost ) as COST ... __ 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
[SQL] quoted variables in pgsql
cannot figure this out # \set var 'value' # select * from some_table where some_col = :var; ERROR: column value does not exist cannot get those quotes around the value. tried: # \set var ''value'' # \set var value in each case: # \echo :var value thanks __ 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] Query to return schema/table/columname/columntype
> I am trying to modify the dabo (a python wxpython > ide for database forms creation) code to allow the > selection of tables in any schema. I need a query > that will return records with schema, table, > columname and columne type. create view pg_cols as select s.nspname as schema_nm, r.relname as table_nm, a.attnum as column_sq, a.attname as column_nm, t.typname as datatype, a.atttypmod as datalen frompg_attribute a joinpg_type t on( t.oid = a.atttypid ) joinpg_class r on( r.oid = a.attrelid ) joinpg_namespace s on( s.oid = r.relnamespace ) where a.attnum > 0 and r.relkind = 'r' and s.nspname = :schemaq; Finding fabulous fares is fun. Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains. http://farechase.yahoo.com/promo-generic-14795097 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] simple web search
> I'm considering implementing a search box on my review web site > http://lesculturelles.net and am looking for a simple way to match > entered words against several columns on related tables: > show.show_name, story.title, person.firtname, person.lastname, etc. one solution would be a view: create view search_v as select 'show'::name as tab_nm, show_id as tab_pk, 'Show Name' as description, show_name as search from show union select 'story'::name, story_id, 'Story Title', title from story union ... your query would be select * from search_v where '$string' ilike search this would return a list the user could use to drill down further. many ways to skin this cat. It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] simple web search
> > create view search_v as select > > 'show'::name as tab_nm, > > show_id as tab_pk, > > 'Show Name' as description, > > show_name as search > > from show > > union select > > 'story'::name, > > story_id, > > 'Story Title', > > title > > from story > > union ... > > > What is that ::name cast for? it's not needed here - sorry. name is the data type pg uses for table names &tc. it's frequently a good idea to cast to name when when messing around in the data dictionary. TV dinner still cooling? Check out "Tonight's Picks" on Yahoo! TV. http://tv.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] inheritance
> --- Greg Toombs <[EMAIL PROTECTED]> wrote: > > > I'm trying to figure out how to nicely implement a C++ class-likesystem > > with PostgreSQL. Consider the following: > > Tables Fruit, Apple, Orange you can do this traditionally or through pg inheritance, although I do not think inheritance is well supported before 8.2. from years of experience the easiest approach and aesthetically the least satisfying approach is to put everything into the fruit table create table fruit( fruit_id integer primary key, fruit_tp varchar(12), ... ); with this approach you simply deal with whatever column's your interested in - with apples the orange specific columns are dead wood - they don't get in the way and take up no storage - you just need to learn to ignore them, maybe using views to help. you can have a fruit table plus apple and orange tables. create table fruit( fruit_id integer primary key, ) create table apple( apple_id integer primary key, fruit_id integer not null references fruit, ) you then need to build views to join fruit with apple and oranges, because some of the apple attributes are in the fruit table. lastly you need to handle dml. for example, when you insert an apple you need to insert into the fruit and the apple table. this can be done either through your application bracketing your dml with a begin and commit, or can be done through rules (much, much cooler) (the doc on rules will hold your hand through this). Finding fabulous fares is fun. Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains. http://farechase.yahoo.com/promo-generic-14795097 ---(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] inheritance
--- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > > > > Maybe then you'll add a table basket that has a foreign key to the > fruit > > table... ;-) > > From the inheritance link: > ... > A serious limitation of the inheritance feature is that ... it's my understanding that inheritance has become much stronger in 8.2, although it still only inherits parts of the table. when primary keys and triggers are inherited then I'll be in heaven - until then rules rule. Looking for earth-friendly autos? Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center. http://autos.yahoo.com/green_center/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] how to use a date range in a join
trying to do something like select d.day, c.name from [dates between day1 and day2] d left join c.some_table; but cannot figure out what to put into the brackets. Finding fabulous fares is fun. Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains. http://farechase.yahoo.com/promo-generic-14795097 ---(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] better approach: case or join
from id extracting name from several tables. which is generally the better approach? select case when tab_tla='usr' then (select name from users where ... ) case when tab_tla='con' then (select title from contents where ...) endas name; as versus select name fromusers where tab_tla='usr' and ... join select title fromcontents where tab_tla='con' and ... I assume the former since the optimizer does not examine whether the entire where clause might be false before executing the query. thanks, chester Don't pick lemons. See all the new 2007 cars at Yahoo! Autos. http://autos.yahoo.com/new_cars.html ---(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] Foreign Unique Constraint
> > create table table1 ( > id SERIAL PRIMARY KEY > extension UNIQUE, > > ) > > create table table2 ( > id SERIAL PRIMARY KEY > extension UNIQUE, > > ) > > Basically table 1 and table 2 both have the concept of an extension > that must be unique but the rest of the info in the tables are > different. I need to ensure that if i add an entry to table 1 with > extension 1000 that it will fail if there is already an entry in > table2 with the same extension. use a pre-insert triggers - one for each table. include something like if exists( select 1 from table2 where extension=new.extension ) then raise exception ... end if; Expecting? Get great news right away with email Auto-Check. Try the Yahoo! Mail Beta. http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Large journal as psql table. Good idea? Triggering.
> But I'm thinking that maybe it's a job for a database table. Each > new > row would be written with a status (10="new"). And that the modem > process would poll for new rows. Problem is there will be lots of > rows, > but only a trivial few will be "new". The huge index file and the > polling seem like a drag on the database, unless there is a way to > optimize. create index on ( ) where status = 'new'; this index will be very fast. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Selecting rows with "static" ordering
> them in the order they are currently stored in that variable. So take > > for example this foreign application variable: > >ids = "3,2,5,1,4" kludgy, but: 1. store your ids in a pg array 2. select from the array 3. on order by, write a function that takes the row.id and array as parameters, returning the id offset into the array __ 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] Count rows by day interval
> ...
instead of date_trunc('day',sent_messages.date)
why don't you have a function that takes four three arguments:
- beginning date of query
- interval, ie, reminder_services.activity_days_min
- timestamp, ie, sent_messages.date
have it return the minimum date for that interval
as I think I understand your problem, that should work
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] ignoring primary key violations in COPY command
> In my opinion your best bet in terms of getting around the primary > key > violation is to create a temporary table ... good idea! from my experience it's almost always best to pull raw info into a buffer table before introducing it into the real world. Bored stiff? Loosen up... Download and play hundreds of games for free on Yahoo! Games. http://games.yahoo.com/games/front ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] off topic
on lwn I read that pg is having problems releasing because of a want of reviewers. although my C is far too rusty I'd like to help out, perhaps with doc or testing. can someone direct me to the appropriate site? Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 ---(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] schema propagation
Hi has anyone done any work on comparing schemas? I'm trying to automatically propagate changes in a master schema to child schemas. All schema changes will originate at master. Currently child schemas are in the same database, but in the future might be in different databases (clusters?). thanks! Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] search path within trigger
within a table pl/pgsql trigger, what's the easiest way to see if the schema for the triggered table is the same as search_path? Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list&sid=396545469 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] trigger firing order
tables A and B: a post row trigger on A cause updates on B which has its own post row trigger. does the post trigger on A wait until post trigger on B is executed? - this seems intuitive to me. does the post trigger on B wait until the trigger on A has completed? or is post trigger A launched as its own process (in which case who completes first is indeterminate). or am I missing this entirely? pre-row thanks for your insight! Shape Yahoo! in your own image. Join our Network Research Panel today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] trigger firing order
> > does the post trigger on A wait until post trigger on B is > executed? -> > this seems intuitive to me. > > How can it wait until the trigger on B is executed if the trigger on > B doesn't > actually get triggered until someone updates B and it's the trigger > on A > doing the update? trigger A executes until it updates table B, at which point it does it pause execution until the post update trigger on table B completes, and then trigger A resumes at the next statement. Pinpoint customers who are looking for what you sell. http://searchmarketing.yahoo.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Iterate and write a previous row to a temp table?
--- Bob Singleton <[EMAIL PROTECTED]> wrote: > Revisiting a Time In Status query I received help on - I'm trying to > narrow down a subset of data I return for analysis. > > Given a statusLog as entityId, statusId, timestamp that might look > something like > > entityId | statusId | timestamp > > 001 | HLD | 2007-06-14 11:07:35.93 > 001 | RDY | 2007-06-15 11:07:35.93 > 001 | USE | 2007-06-16 11:07:35.93 > 001 | RDY | 2007-06-17 11:07:35.93 > 001 | MNT | 2007-06-18 11:07:35.93 > > I need to pull for a given span of time - say 2007-06-16 00:00:00.01 > (let me call it startTime) to 2007-06-17 23:59:59.99 (call it > endTime) > in such a way that rows with a timestamp between startTime and > endTime > AND the latest record prior to or equal to startTime are returned. In > > the above simplified example, only the second and third rows would be > > returned. > > A colleague suggested a temp table, but I'm unsure how to iterate > until > I pass the startTime and then write the _previous_ and all subsequent > > rows to a temp table, stopping when I pass the endTime parameter. > > Any hints? > > Thanks! > Bob Singleton > couldn't you use the simple query: select * from sometable where timestamp between (select max(timestamp) from sometable where timestamp <= minTime) and maxTime Finding fabulous fares is fun. Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains. http://farechase.yahoo.com/promo-generic-14795097 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Converting from MS Access field aliases
>SELECT field1 / 2 AS foo, > field2 * 2 AS bar, > foo + bar AS total >WHERE foo < 12; > > The first two fields are fine, it's the third that's a problem. The > database reports > >ERROR: column "foo" does not exist > First, I think it would be great if this worked - like the alias to an update table added in 8.2 - saves a lot of typing and makes queries much more readable. Second, only way I see is to set this on top of a view generating your foo and bar aliases, and go from there. Compared to rewriting the expression each time, it has the advantages of better syntax in the end and might yield better performance as well. Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&cs=bz ---(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] data dependent sequences?
> > CREATE TABLE items ( > id INT, > typ INT... > PRIMAY KEY (seq,typ)); > >id typ > +- > 1 'a' > 2 'a' > 3 'a' > 1 'b' > 4 'a' > 2 'b' > you will need to use pre insert trigger since you cannot use column references in default expression. you could use this same trigger to either: - create sequences as needed and apply the right one - with locking, lookup for last id of typ - with locking, keep another table of typ and nextval Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC ---(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] Assistance with a trigger
--- Paul Lambert <[EMAIL PROTECTED]> wrote: > I have some triggers in an MS SQL server database which I need to > copy > across to a PG database. I've not yet done triggers in PG so I was > hoping to get a little bit of a pointer on the first one as a place > to > start and work my way through from there. http://www.postgresql.org/docs/8.2/interactive/plpgsql.html I found the doc to be quite good. I'd start there, and then, if you have problems, email again. Need a vacation? Get great deals to amazing places on Yahoo! Travel. http://travel.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] problem join
having problem joining these correctly: schedule - cal_id references calendar not null - usr_id references users not null = unique( calZ_id, usr_id ) - result_no not null activity - cal_id references calendar not null - usr_id references users not null = unique( cal_id, usr_id ) - from_ts timestamp not null trying to join - cal_id - usr_id - schedule.result_no (might be null) - activity.from_ts (might be null) where schedule.usr_id = activity.usr_id would result in one row thanks for insight! Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&cs=bz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Authentification failed
> I'm trying in SUSE to connect to a postgres db and this is the > error: > > Ident Authentification failed for user <> > others will guide better, but for now, in pg_hba.conf # "local" is for Unix domain socket connections only local all all ident sameuser also, as postgres user, createuser this will let you into pg as yourself Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list&sid=396545433 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Comparing two slices within one table efficiently
> I have a table with the following simplified form: > > create table t ( > run_id integer, > domain_id integer, > mta_id integer, > attribute1 integer, > attribute2 integer, > unique(run_id, domain_id, mta_id) > ); > > The table has about 1 million rows with run_id=1, another 1 million > rows with run_id=2, and so on. > > I need to efficiently query the differences between "runs" - i.e. For > each (domain_id, mta_id) tuple in run 1, is there a coresponding > tuple in run 2 where either attribute1 or attribute2 have changed? > > The only way I have been able to think of doing this so far is an > o(n^2) search, which even with indexes takes a long time. e.g. > > select * from t t1 where exists (select 1 from t t2 where > t2.mta_id=t1.mta_id and t2.domain_id=t1.domain_id and (t2.attribute1 > != t1.attribute1 or t2.attribute2 != t1.attribute2) > > This query takes millenia... > first, add a change flag change_tf that is set through a trigger whether this record different from record in the previous run. second, create an index on domain and mta where change_tf, so you're only indexing changed records. this would allow you to find your changes very efficiently at the relatively small cost of adding one lookup and one extra index per insert. Pinpoint customers who are looking for what you sell. http://searchmarketing.yahoo.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Execute SQL statements with 'context'/predefined variables
> I was wondering if it is possible to set the 'context' for running an
> sql command
Oracle has a Context('varname') that returns the value of varname for
the session. to best of my knowledge pg has nothing like this.
> I guess bottom line, is it possible to execute a bunch of SQL
> statements with some predefined variables?
you could use rules to inject you constants as long as you were dealing
with known tables/views; you'd have to rewrite the rules each time the
constants changed; the rules are not session oriented - each sessions
would use the same rule.
(if you have not used pg rules imho you are in for a very pleasant
surprise.)
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search
http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz
---(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] Execute SQL statements with 'context'/predefined variables
... > When you say use rules to inject constants, how would I go about > doing this? Could you maybe give a brief example? create view tab1_dml as select * from tab1; -- note: -- CONSTANT1 = 8 -- CONSTANT2 = 15 create or replace rule tab1_insert as on insert to tab1_dml do instead( insert into tab1( c1, c2 ) values( new.c1 * 8, 15 ); ); Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Extracting hostname from URI column
> I'm trying to use substr() and position() functions to extract the > full host name (and later a domain) from a column that holds URLs. substring( href from '.*://\([^/]*)' ); Pinpoint customers who are looking for what you sell. http://searchmarketing.yahoo.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Extracting hostname from URI column
> >> I'm trying to use substr() and position() functions to extract the > >> full host name (and later a domain) from a column that holds URLs. > > > > substring( href from '.*://\([^/]*)' ); > > typo: no backslash in front of left paren substring( href from '.*://([^/]*)' ) match up thru // within parens, match anything except / return match within parens Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online. http://smallbusiness.yahoo.com/webhosting ---(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] Extracting hostname from URI column
> And what I'd like is something that would give me the counts for the > number of occurrences of each unique hostname. Something much like > `uniq -c'. Can anyone tell me how that's done or where I should look > for info? (I'm not sure what to look for, that's the problem). > select substring( ... ), count(1) from your_table group by 1; Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list&sid=396545469 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] problems with copy
I'm getting lots of delimited files from Excel and MySQL users that, mid-file, begin truncating lines if ending in null values. for example: 1781: "one","two","three",, 1782: "one","two","three",, 1783: "one","two","three",, (delimited files from Open Office are well behaved) is there any way to handle this apparently standard aberration in PG? Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] backup database tablespace with rsync?
postgres A, db 'test', tablespace /pg/test1 postgres B, db 'test', tablespace /pg/test2 tablespace /pg/test1 only has A db 'test' tablespace /pg/test2 only has B db 'test' if - A and B shut down - /pg/test1 copied to /pg/test2 - A and B restarted would B db 'test' be running the data that was in A db 'test'? __ 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] general question on optimizer
I have found that in many complex queries left join is exponentially faster than a (not) exists clause. I don't understand why, generally speaking, this is so frequently so effective. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] update on join ?
> I tried > > UPDATE things JOIN inventory ON things.thing_id = inventory.thing_fk > SET number = 0 > WHERE color = 'red' > use the cool "from" clause in the update update things t set number = 0 from inventory i where t.thing_id = i.thing_fk and i.color = 'red'; Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Advice for generalizing trigger functions
--- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > I've created quite a few functions that log modifications to various > history tables. (the history table has the same name as the base > table but is prefixed by the 'History.' schema.) The only difference > between functions I can find is the table name. the problem is that prepared code is referenced by oid, not name. so any structural references need to by dynamic. what I do for change log is to have one change log table with table_id and column_id attributes that refer by to my internal meta_table and meta_column tables. this always works and is in the end, I have found, a bit more flexible, allowing you to search for changed columns, for example. but I still generate the change triggers. in this case from in my meta_table and meta_column tables I note which table/columns I want changes tracked. those can be changed at any time, but the change log triggers need to be recompiled. Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Proposed archival read only trigger on rows - prevent history modification
> I'm considering building a protective mechanism, and am seeking > feedback > on the idea. The approach would be to add a new column named "ro" to > each table at invoice level and below. Then have a trigger on > 'ro'==true deny the write, and probably raise a huge stink. As > invoice > are mailed each month, all the supporting data would be set to "ro" > true. instead of triggers I use update-able views and permissions. 1. all dml goes through the view 2. use rules on the view to do dml to the table 3. in rules prevent updating all/any columns when whatever 4. grant dml to view to your pgconnect user 5. revoke dml from table to your pgconnect user imho another instance where rules rule. for example, you can easily fit logging into the same view. Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] cursors and sessions
is there any way to share a cursor between sessions? I have a costly query whose records need to be visited by in order by n number of concurrent sessions, and am unable to find the best way of doing this. I'd almost like to write a daemon that hands out the next record, but that's a royal pain to set up and maintain. Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create on insert a unique random number
> When inserting a record is there a way to have postgres create a > random number for a field such that it is unique? you could use oid Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Date and filling issues
> > A sample of the current results data would be like > datesales > 2008-03-07 100.00 > 2007-03-10 150.00 > 2007-03-18 50.00 > > and what I'm trying to do is fill in the missing dates with sales > values of 0. what I do is have a table called days that is the left side of a left join. I'm not proud of this, but it works well. Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] returning on inserts
insert into t1( c1 ) select c1 from t2 returning t1.c1, t2.c2; ERROR: missing FROM-clause entry for table "t2" is there any way to make this work? Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] apparent RI bug
it appears I have a broken RI in my db. call_individual.clh_id references call_household.clh_id \d call_individual ... Foreign-key constraints: "call_individual_clh_id_fkey" FOREIGN KEY (clh_id) REFERENCES call_household(clh_id) ON DELETE CASCADE however: development=# select clh_id from call_individual cli where not exists( select 1 from call_household clh where clh.clh_id=cli.clh_id ); clh_id 14691 should not matter, but call_individual has a pre-delete trigger that simply raises an exception to prevent deletions: raise exception 'calls may not be deleted'; You rock. That's why Blockbuster's offering you one month of Blockbuster Total Access, No Cost. http://tc.deals.yahoo.com/tc/blockbuster/text5.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] apparent RI bug
Stephan Szabo <[EMAIL PROTECTED]> wrote: > On Wed, 2 Apr 2008, chester c young wrote: > > > it appears I have a broken RI in my db. > Yeah, that looks pretty broken. Can you reproduce this from a clean > start repeatedly or is this a one off? Do you ever turn off triggers, > perhaps by modifying the pg_class row's reltriggers (I'd guess the > answer is no, but it'd be good to make sure)? only one error. unable to duplicate so far. this is a development db - triggers are frequently dropped and created, but I don't think ever concurrently with db activity. You rock. That's why Blockbuster's offering you one month of Blockbuster Total Access, No Cost. http://tc.deals.yahoo.com/tc/blockbuster/text5.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] apparent RI bug
--- Stephan Szabo <[EMAIL PROTECTED]> wrote: > Is it possible you ever had a before delete trigger that just did a > return > NULL rather than raising an exception? IIRC, explicitly telling the > system to ignore the delete will work on the referential actions. yes, it is possible, for example, a function without a body or without a "return old". are you saying this would override the RI constraint? if so, is this by design? You rock. That's why Blockbuster's offering you one month of Blockbuster Total Access, No Cost. http://tc.deals.yahoo.com/tc/blockbuster/text5.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] apparent RI bug
--- Stephan Szabo <[EMAIL PROTECTED]> wrote: > > is it is possible, for example, a function without a body or > without a "return old". > > > > are you saying this would override the RI constraint? > > If it returned something that would have prevented the delete without > an error, yes. this is very good news that there is a reason why the RI did not work, which is to say, RI not working randomly is very frightening > > if so, is this by design? > > It's basically an ongoing question (without concensus AFAIK) about > whether > a rule or trigger should be allowed to stop the referential action > and > what should happen if it does. in my opinion the most important thing is that it's documented. btw, cheers! you're my hero of the week!! You rock. That's why Blockbuster's offering you one month of Blockbuster Total Access, No Cost. http://tc.deals.yahoo.com/tc/blockbuster/text5.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] trim(both) problem?
--- Emi Lu <[EMAIL PROTECTED]> wrote: > Isn't this a bug about trim both. > > select trim(both '' from 'ROI Engineering Inc.'); > btrim > - > OI Engineering Inc. > (1 row) > > > "R" is missing? How? you misread - '' argument is a list of characters, _not_ a string. change 'ROI' to 'XOI' and you'll see. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] psql: no schema info
have several schemae, each with identical tables. in create scripts have been taking great care to fully qualify, eg, col1 references schema1.tab1( col1 ) however, just got burnt big time on sequences! need to qualify them as well, eg col1 integer default nextval( 'schema1.seq1' ) \dt is no help at all since it does not specify schema so it's impossible to know if all is aligned correctly. is there a way to tell psql to fully qualify relations in the listing? when everything was in public this wasn't a problem, but with elaborate use of schemae it's pretty scary (for me). Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] psql: no schema info
> > however, just got burnt big time on sequences! need to qualify > them as > > well, eg > > col1 integer default nextval( 'schema1.seq1' ) > > Move to something newer than 8.0.x, and this is automatic (because > nextval's argument is actually a regclass constant). > > regards, tom lane using 8.2 and 8.3 here's (psychological) problem as I see it: # set search_path=old_schema; # # create sequence new_schema.seq1; # # create table new_schema.table1( #col1 integer default nextval( 'seq1' ) # ); using old_schema.seq1, not new_schema.seq1 and imho to make matters more difficult to troubleshoot: # \dt table1 -> does not show which schema for seq1 Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] psql: no schema info
> > # \dt table1 -> does not show which schema info. was wrong on this - \dt shows schema for relations _not_ in the search path. my new good practice is to keep search_path = PUBLIC so all schema info is displayed always all the time invariably. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] columns for count histograms of values
--- Alexy Khrabrov <[EMAIL PROTECTED]> wrote: > Greetings -- I have a table of the kind > > Ratings: > id integer > rating smallint > > -- where value can take any value in the range 1 to 5. Now I want to > > have a statistical table Stats of the form > > id integer > min smallint > max smallint > avg real > r1 integer > r2 integer > r3 integer > r4 integer > r5 integer > > -- how can I create it in one pass over Ratings? select id, min(rating), max(rating), avg(rating), sum( case rating = 1 then 1 else 0 end ), ... Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] numbering rows on import from file
--- Alexy Khrabrov <[EMAIL PROTECTED]> wrote:
> Now I want to number the rows, adding an id column
> as an autoincrement from a sequence. How should I do the import now
> for the sequence to work -- should I add the id column last, so it
> will not be filled by copy and presumably autoincrement?
use a sequence
restart sequence to 1 before copy
have column id default to nextval('seq')
when doing copy don't have a column matching your id (duh)
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Difference in columns
--- Mag Gam <[EMAIL PROTECTED]> wrote: > Hi All, > > I have a view that generates output similar to this. > > select * from foo.view; > >ts | size > ---+- > 2002-03-16| 11 > 2002-03-17| 16 > 2002-03-18| 18 > 2002-03-19| 12 > > I am trying to find the difference between the size column. So the > desired > output would be > >ts | size| Diff > ---+-+-- > 2002-03-16| 11 | 0 > 2002-03-17| 15 | 4 > 2002-03-18| 18 | 3 > 2002-03-19| 12 | -6 > > > I need the first column to be 0, since it will be 11-11. The second > colum is > 15-11. The third column is 18-15. The fourth column is 12-18. > > Any thoughts about this? > select cur.ts, cur.size, cur.size - coalesce( (select size from view next where next.ts = cur.ts - '1 day'::interval), cur.size ) as diff from view cur; alternately: select cur.ts, cur.size, case when cur.ts = '2002-03-16' then 0 else cur.size - coalesce( (select size from view next where next.ts = cur.ts - '1 day'::interval), cur.size ) end as diff from view; Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL question....
> create table access (name text, address ip) > > I want to construct a SELECT statement which will return ONLY tuples > containing IP and name pairs IF there is an IP that has two or more > NAMEs associated with it. > > many ways: select a1.* from access a1 where exists( select 1 from access a2 where a2.name=a2.name and a1.ip!=a2.ip ); select a1.* fromaccess a1 joinaccess a2 using( name ) where a1.ip != a2.ip; -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PERSISTANT PREPARE (another point of view)
1. like the idea because right now I'm having to track which prepared statement (sets) are in which session. using xcache to track this, but little bit dangerous. could query the database first but the whole idea is to take a load off the db. 2. perhaps "global" could mean simply that the definition is global - if called for session and not exist in session, then session prepares it first from the global def. there would need to be a back reference in case the global def was changed or dropped. 3. don't understand your statement about how prepared statements can be used as subqueries - you mean something like select .. from tab1 t1 where t1.col1 in( exec prep1(..) ) or exactly what? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
