[SQL] Create Assertion -- Question from a newbie
Sorry if my question is a little off topic. I am reading my new "SQL for Smarties" book side by side with the PostgreSQL 8.1 manual. I noticed that this particular feature is not included in PostgreSQL. Some of the achieve threads mostly discuss that this feature is currently not supported. My understanding is that Assertions place constraints upon data spanning multiple related tables. Is the Assertion feature slated to be added in the future? (Perhaps rolled up in a more generalized "TO-DO" item?) Would this feature add functionality that can not be achieved by other means? (i.e. alternative schema definitions or triggers?) Or does it merely provide a redundant means to constrain data, and thereby not warrant addition into the features of PostgreSQL? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Create Assertion -- Question from a newbie
On Sun, Aug 27, 2006 at 00:19:59 -0700, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > Sorry if my question is a little off topic. > > I am reading my new "SQL for Smarties" book side by side with the PostgreSQL > 8.1 manual. I > noticed that this particular feature is not included in PostgreSQL. Some of > the achieve threads > mostly discuss that this feature is currently not supported. My understanding > is that Assertions > place constraints upon data spanning multiple related tables. > > Is the Assertion feature slated to be added in the future? (Perhaps rolled > up in a more > generalized "TO-DO" item?) > > Would this feature add functionality that can not be achieved by other means? > (i.e. alternative > schema definitions or triggers?) Or does it merely provide a redundant means > to constrain data, > and thereby not warrant addition into the features of PostgreSQL? You can accomplish what assertions do using triggers. I think the issue is generating triggers for general assertions that don't totally suck performancewise. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Create Assertion -- Question from a newbie
> You can accomplish what assertions do using triggers. > I think the issue is generating triggers for general assertions that don't > totally suck performancewise. Ah, I see. So the points is that checking the integrity between two complete data sets can become a preformace killer. Thanks for the feed back. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Performance Problem with sub-select using array
On 8/24/06, Travis Whitton <[EMAIL PROTECTED]> wrote: Hello all, I'm running the following query on about 6,000 records worth of data, and it takes about 8 seconds to complete. Can anyone provide any suggestions to improve performance? I have an index on two columns in the transacts table (program_id, customer_id). If I specify a number for customer.id in the sub-select, query time is reduced to about 2 seconds, which still seems like a million years for only 6,000 records, but I'm guessing that the sub-select can't resolve the id since it's done before the outer query, so it scans the entre recordset for every row? Transacts is a many to many table for customers and programs. I know this query doesn't even reference any columns from programs; however, I dynamically insert where clauses to constrain the result set. SELECT distinct customers.id, first_name, last_name, address1, contact_city, contact_state, primary_phone, email, array(select programs.program_name from transacts, programs where customer_id = customers.id and programs.id = transacts.program_id and submit_status = 'success') AS partners from customers, transacts, programs where transacts.customer_id = customers.id and transacts.program_id = programs.id My guess is that your problem is that you may be getting 6000 rows, but the array(select ) is having to run once for each of record returned (so it is running 6000 times). Try an explain analyze: http://www.postgresql.org/docs/7.4/interactive/sql-explain.html - that will reveal more of where the performance problem is. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] Importing data from csv
On 8/24/06, Sumeet <[EMAIL PROTECTED]> wrote: Hi Folks,sorry if this is a duplicate post, i've been tryin to find a solution of importing data into postgres from a csv file. The problem is, I have a database which consists of columns which contain newline characters (mac and unix). now when i export these files to a csv format, there are some line breaks (mixed unix and mac) in the data which breaks the copy procedure. Sounds like you are checking the file in as binary when it should be checked in as ASCII. You may want to consider changing that in your repository so this does not continue to be an issue. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
