Re: [SQL] returning the number of rows output by a copy command from a function

On 01/16/2013 09:30 AM, James Sharrett wrote: I have a function that generates a table of records and then a SQL statement that does a COPY into a text file. I want to return the number of records output into the text file from my function. The number of rows in the table is not necessarily the

Re: [SQL] Why doesn't this work

On 01/15/2013 04:50 AM, Barbara Woolums wrote: I am running a query like so SELECT id FROM image WHERE image='demo-harvey wallbanger.jpg' It returns nothing My table looks like this "demo-820.jpg";1 "demo-lemon-mousse-1.jpg";2 "demo-pumpkinchaibars.jpg";3 "demo-Lolly-Shop.jpg";4 "demo-scan000

Re: [SQL] Uniform UPDATE queries

On 04/19/2012 04:55 AM, Dennis wrote: Hello Tom, The example you have given is EXACTLY why something like CURRENT is needed to limit the number of unique queries or prepared statements. (or to do a selection of all values before an update meaning two executed queries.) regards,. Dennis On 04/

Re: [SQL] Uniform UPDATE queries

On 04/18/2012 04:11 AM, Dennis wrote: When a query is written to update a table, the usual process is to list all the columns that need updating. This could imply the creation of many possible queries for many columns. In an effort to keep the UPDATE queries more uniform, less number of unique qu

Re: [SQL] syntax of joins

On 04/06/2012 01:46 PM, Pavel Stehule wrote: 2012/4/6 Rob Sargent: On 04/06/2012 01:23 PM, Pavel Stehule wrote: Hello 2012/4/6 Andreas: hi, is there a disadvantage to write a join as select * froma, b where a.id = b.a_id; over select * froma join b on a.id = b.a_id

Re: [SQL] syntax of joins

On 04/06/2012 01:23 PM, Pavel Stehule wrote: Hello 2012/4/6 Andreas: hi, is there a disadvantage to write a join as select * froma, b where a.id = b.a_id; over select * froma join b on a.id = b.a_id; yes - newer notation has some advantages * clean specification join p

Re: [SQL] Can I read the data without commit

le? Can I read the data I just inserted without a commit. Johnf On Friday, March 23, 2012 03:46:10 PM Rob Sargent wrote: If possible have the review done before starting the transaction. No sense in holding on to that stuff too long. Potential concurrency issues etc. On 03/23/2012 03:40 PM, Jonath

Re: [SQL] Can I read the data without commit

If possible have the review done before starting the transaction. No sense in holding on to that stuff too long. Potential concurrency issues etc. On 03/23/2012 03:40 PM, Jonathan S. Katz wrote: On Mar 23, 2012, at 5:33 PM, John Fabiani wrote: I start a transaction. Begin Then I insert a l

Re: [SQL] Change in 9.1?

On 11/22/2011 12:39 AM, Jasmin Dizdarevic wrote: > Hi, > > we have a reporting tool, that sometimes uses this kind of condition. > ...WHERE a.field = a.field > > To explain this: a.field can be filtered by the user. the user can > choose some values. if he does, this condition will be build: >

Re: [SQL] select xpath ...

Having a name space in the doc requires it's usage in the query. On 09/17/2011 11:48 AM, boris wrote: > hi all, > I've inserted xml file : > > > xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";> > > zz > .. > > > to a table: > > CREATE TABLE "temp".tempxml

Re: [SQL] Append n Rows into a Single Row

Look at the array aggregation functions here Tripura wrote: Hi, I am totally new to PostgreSQL and this is my first script. Can anyone please help me with my following requirement I have script that returns 1 column and 40 row

Re: [SQL] Function to total reset a schema

I would hope you have readily at hand the ddl for the schema in question. Then it's simply a matter of drop schema cascade and re-run you ddl scripts. Surfing wrote: Hi all, I need to write a function that totally empty a schema. So I have written a TRUNCATE statement for each table and set

Re: [SQL] extracting location info from string

On 05/25/2011 03:13 PM, Tarlika Elisabeth Schmitz wrote: On Wed, 25 May 2011 09:25:48 -0600 Rob Sargent wrote: On 05/24/2011 10:57 AM, Lew wrote: Tarlika Elisabeth Schmitz wrote: CREATE TABLE person ( id integer NOT NULL, "name" character varying(256) NOT NULL, "loca

Re: [SQL] extracting location info from string

On 05/24/2011 10:57 AM, Lew wrote: Tarlika Elisabeth Schmitz wrote: Lew wrote: That isn't a table structure, that's a freeform text structure. You didn't state your question, Tarlika, but your database structure is terrible. For example, "region" and "country" should be different columns.

Re: [SQL] slightly OT - Using psql from Emacs with sql.el

On 05/05/2011 04:55 PM, Seb wrote: On Thu, 05 May 2011 16:47:09 -0600, Rob Sargent wrote: [...] Doesn't appear to. I use sql-mode alot/daily. The multiple prompts never bothers me, though the output not starting at the left kind of does. I've adapted someone's suggesti

Re: [SQL] slightly OT - Using psql from Emacs with sql.el

On 05/05/2011 04:01 PM, Seb wrote: Hi, When working with psql via sql.el, multiple prompts accumulate in a single line when sending multi-line input to the SQLi buffer. For example, sending the following: SELECT a, b, c, FROM some_table; with 'C-c C-r' results in these lines in th

Re: [SQL] pass in array to function for use by where clause? how optimize?

On 04/13/2011 09:09 AM, Tom Lane wrote: Anish Kejariwal writes: (select store_id, avg(sales) sales from store where group_id in(select $1[i] from generate_subscripts($1, 1) g(i)) Seems like a pretty brute-force way to deal with the array. Try where group_id = any($1) Not sure if it'll be

[SQL] xml2 support

I'm confused by the deprecation notice on xml2 seen here . What if anything was removed in the making of 8.4? Was the newer standard API implemented? Do I still need namespaces? I'm running PostgreSQL 9.0.3 built locally with --with-li

Re: [SQL] xml2 support

On 03/23/2011 11:10 AM, Serdar Gül wrote: if you want to export the data in xml type i suggest you to use the EMS SQL Manager for PostgreSQL because you can export the whole table or an executed query in many different file types including xml 2011/3/23 Rob Sargent mailto:robjsarg

[SQL] xml2 support

I'm confused by the deprecation notice on xml2 seen here . What if anything was removed in the making of 8.4? Was the newer standard API implemented? Do I still need namespaces? I'm running PostgreSQL 9.0.3 built locally with --with-li

Re: [SQL] distinguishing identical columns after joins

On 03/01/2011 03:13 PM, S G wrote: > On Tue, Mar 1, 2011 at 2:53 PM, Rob Sargent wrote: >> >> >> On 03/01/2011 12:47 PM, S G wrote: >>> This question is particularly geared towards self-joins, but can apply >>> to any join where the tables involved have

Re: [SQL] distinguishing identical columns after joins

On 03/01/2011 12:47 PM, S G wrote: > This question is particularly geared towards self-joins, but can apply > to any join where the tables involved have any identical column names. > Aside from explicit column references, is there any way to pull all > columns (*) from each table in a join and q

Re: [SQL] what's wrong in this procedure?

On 02/25/2011 10:46 AM, Camaleon wrote: > This error is returned Erro de SQL: > > ERROR: column "Aguardando Pagto" does not exist at character 352 >>> > > > create or replace function get_historico() RETURNS SETOF > twiste.type_cur__historico AS ' > >SELECT o.data_fim, sum(t.num_iten

Re: [SQL] Drawing a blank on some SQL

On 02/11/2011 11:46 AM, Aaron Burnett wrote: > > Hi, > > I'm just drawing a blank entirely today and would appreciate some help on > this. > > The long and short; there are 12 distinct activities that need to be queried > on a weekly basis: > > SELECT count(activity_id), activity_id > FROM fo

Re: [SQL] Using count on a join, group by required?

If you showed your work, you might get decent hints if not solutions. On 12/14/2010 09:23 AM, Rob Sargent wrote: > Shouldn't you be doing your own homework? > > emaratiyya wrote: >> Hi,Please help me solving this problem. I appreciate..Thankyou. >> >> Create th

Re: [SQL] Using count on a join, group by required?

Shouldn't you be doing your own homework? emaratiyya wrote: Hi,Please help me solving this problem. I appreciate..Thankyou. Create the following table and insert few arbitrary records. Product (product_id, product_name, supplier_name, quantity, price_per_unit) You are required to create PL/SQ

Re: [SQL] indexing longish string

for indexing ? > > 2010/11/30 Rob Sargent : >> Were we to create a table which included a text field for a small block >> of xml (100-1000 chars worth), would an index on that field be useful >> against exact match queries? >> >> We're wondering if a criter

[SQL] indexing longish string

Were we to create a table which included a text field for a small block of xml (100-1000 chars worth), would an index on that field be useful against exact match queries? We're wondering if a criterion such as "where 'a string expected to be of size range 100 to 500' = tabelWithStrings.stringSearc

Re: [SQL] large xml database

Skipping much of the included thread, urgently. btw. you have mentioned "This I believe would parse nicely into a tidy but non-trivial schema directly", does it mean that postgre has a support for restoring the database schema from xml files? -- --- Viktor

Re: [SQL] large xml database

Viktor Bojović wrote: On Sun, Oct 31, 2010 at 9:42 PM, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: Viktor Bojovic' wrote: On Sun, Oct 31, 2010 at 2:26 AM, James Cloos mailto:cl...@jhcloos.com> <mailto:cl...@jhcloos.com <m

Re: [SQL] large xml database

Viktor Bojovic' wrote: On Sun, Oct 31, 2010 at 2:26 AM, James Cloos > wrote: > "VB" == Viktor Bojovic' mailto:viktor.bojo...@gmail.com>> writes: VB> i have very big XML documment which is larger than 50GB and want to VB> import it into databse

Re: [SQL] large xml database

Andreas Joseph Krogh wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 10/30/2010 11:49 PM, Viktor Bojović wrote: Hi, i have very big XML documment which is larger than 50GB and want to import it into databse, and transform it to relational schema. When splitting this documment to

Re: [SQL] Duplicates Processing

Gross generalization perhaps, but keep in mind what the over app/system needs of the components. Bounce those off you standard ER modeling instincts and vice versa and you have a chance! On 10/12/2010 08:19 AM, Gary Chambers wrote: > Rob, > > Thanks for your reply! > >> And to your point of sel

Re: [SQL] Duplicates Processing

My understanding was that the values were in fact in the data of the "replacers". If not, you are correct. In this case the replacers are more like alias for the only instance you have. If the replacers are immutable by all means ship them off to some other table (where I suppose the become poi

Re: [SQL] Duplicates Processing

Yes. With this you can find all part numbers/supplies which match your value, wattage criteria in one table. Or exclude any which have a non-null is_replacement_for value. If you need to drop the "replaceable" variant, you choose which of the replacements to promote and update the others to match

Re: [SQL] Duplicates Processing

On 10/08/2010 01:42 PM, Gary Chambers wrote: > Tim, > > Thanks for taking the time to reply! > >> | INSERT INTO substitutes ([...]) >> | SELECT [...] FROM >> | (SELECT *, >> | ROW_NUMBER() OVER (PARTITION BY wattage, tolerance, temperature >> | ORDER BY

Re: [SQL] pg_config -less

And while on the topic of uuid (again), building postgres 9 from source seems to transpose the library name: libossp-uuid v. libuuid-ossp. I had to put in a simlink to get configure to agree I had the library (rev 1.6.2 from ossp.org) On 09/23/2010 08:49 AM, Tom Lane wrote: > Rob Sarg

Re: [SQL] pg_config -less

Absolutely correct. The dev package was later installed so I got my answer (no real uuid support) but I was wondering if it was possible to get that sort of info from psql directly. On 09/23/2010 08:49 AM, Tom Lane wrote: > Rob Sargent writes: >> A local installation of 9.0 does no

[SQL] pg_config -less

A local installation of 9.0 does not seem to include pg_config. (not with pg_dump pg_ctl etc, no man page) This is a Suse box (openSUSE 11.2 (x86_64)). Is it possible to dig around for the info returned from pg_config --configure (especially uuid support)? Thanks. -- Sent via pgsql-sql mailing

Re: [SQL] Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock

You could implement an optimistic lock strategy by placing a 'version' column in the table and increment it on successful 'check-out' and test against the value the user has as he/she tried to act on the record. If some else got there first the second user fails to check-out the queue item. Trigg

Re: [SQL] safely exchanging primary keys?

And relying on keys for a sort order is a very wrong tree :) On 05/24/2010 08:05 AM, Louis-David Mitterrand wrote: > On Mon, May 24, 2010 at 10:51:01AM +0200, Louis-David Mitterrand wrote: >> Hi, >> >> I have this function which swaps primary keys for cabin_types (so that >> id_cabin_type ordering

Re: [SQL] Invalid message format Exception

On 05/13/2010 01:04 AM, Gnanakumar wrote: > Hi Rob, > >> I'm sure most will urge you to move to UTF-8 encoding asap. > Did you mean the database encoding to changed from SQL_ASCII to UTF-8? > Yes. That's pretty much the standard now. I think it's Postgres' default installation now (but don't

Re: [SQL] Invalid message format Exception

I'm sure most will urge you to move to UTF-8 encoding asap. Have you tracked down the "offending" insert statement? Perhaps it's a trigger trying to generate a log message? On 05/12/2010 04:34 AM, Gnanakumar wrote: > Hi, > > Because there was no response for this question already posted in > pg

Re: [SQL] [ADMIN] Getting the initdb parameter values

As I read it, it doesn't matter what the value was originally, it's what's in postgresql.conf _now_ that matters. This is a resource allocation: I suspect there's no limit on how much of your (often precious) memory you wish to set aside for this. On 04/13/2010 07:01 AM, Satish Burnwal (sburnwal)

Re: [SQL] Table Design for Hierarchical Data

Believe me: "ego-ma-pa" will correctly define genealogical relationships (at least among humans). On 04/12/2010 02:14 AM, Achilleas Mantzios wrote: > Στις Thursday 08 April 2010 17:59:01 ο/η Rob Sargent έγραψε: >> The "parent" node in a genealogy is the mother-father

Re: [SQL] Table Design for Hierarchical Data

The "parent" node in a genealogy is the mother-father tuple, so given that as a singularity it still fits a tree. On 04/08/2010 12:56 AM, Achilleas Mantzios wrote: > Στις Wednesday 07 April 2010 23:33:07 ο/η Yeb Havinga έγραψε: >> Achilleas Mantzios wrote: >>> Στις Wednesday 07 April 2010 11:06:44

Re: [SQL] strange issue with UUID data types

On 03/17/2010 10:29 AM, Michael Gould wrote: > I'm running Windows 2008 64 bit server with Postgres 8.4.2 (also have > tried Windows 7 both 32 and 64 bit). The origin database is SQL > Anywhere 10. > > I've got several tables that have a UUID data type with > isscontrib.uuid_generate_v4() as th

Re: [SQL] installing uuid generators

On 03/16/2010 02:26 PM, Tom Lane wrote: > Richard Huxton writes: >> On 16/03/10 18:08, Rob Sargent wrote: >>> I'm still left worried about the correct procedure for getting uuid-oosp >>> installed properly on SUSE 11. Does the server release's contrib >&

Re: [SQL] installing uuid generators

On 03/16/2010 03:20 AM, Richard Huxton wrote: > On 15/03/10 23:58, Rob Sargent wrote: >> Stop me if you've heard this one before :) >> >> Given that pg_config --libdir yields "/usr/lib64" >> to where/what would you expect >> >>"A

[SQL] installing uuid generators

Stop me if you've heard this one before :) Given that pg_config --libdir yields "/usr/lib64" to where/what would you expect "AS '$libdir/uuid-ossp', 'uuid_generate_v5'" to resolve? The loader script, ~/tools/postgresql-8.4.2/contrib/uuid-ossp/uuid-ossp.sql, generates "tools/postgresql-8.4.2/

Re: [SQL] Odd query behavior

Any views involved, or separate users/roles? On 03/12/2010 08:41 AM, Dan McFadyen wrote: > Hello, > > > > I've come across an odd situation. I've had access to a database where a > the following happens: > > > > " SELECT * FROM table WHERE name LIKE 'abc%' "returns 2 rows... > > >

Re: [SQL] an aggregate to return max() - 1 value?

'select max(col) where col < max(col)' should work but you have to do 'where col < (select max(col) ' On 03/04/2010 01:09 PM, Louis-David Mitterrand wrote: Hi, With builtin aggregates is it possible to return the value just before max(col)? Thanks, -- Sent via pgsql-sql mailing list (pgsql

Re: [SQL] Join Advice and Assistance

My mistake. Should answer these things late at night. I think you will find that arrays will be your friend[s] On 02/22/2010 08:51 AM, Gary Chambers wrote: Rob, Thanks for the reply... If you want records for user without email addresses you will need an outer join on user_emailaddrs /* un

Re: [SQL] Join Advice and Assistance

Gary Chambers wrote: All, I've encountered a mental block due primarily to my inexperience with moderately complex joins. Given the following three tables: Table "public.users" Column | Type | Modifiers ---++--

Re: [SQL] Can i force deletion of dependent rows?

then I think OP needs to delete A where "your x"; On 02/13/2010 12:05 AM, Tim Landscheidt wrote: Shruthi A wrote: I have 2 tables (A and B) where the table B has a foreign key reference to table A. Like this: create table A (x int primary key); create table B (y int primary key, z int re

Re: [SQL] 'image' table with relationships to different objects

You can also invert this, making all the image owner share a common base table and then images are dependent on that base base (id, type) where type is an enumeration or some such person (id, name, etc) where id is FK to base id locations (id, address, etc) where id is FK to base.id events(id, dat

Re: [SQL] Sqldf - error message

I think you (or I) might misunderstand TL's comment. The sql is probably being sent by some late-in-the-client-side-game driver with names quoted with double-quote marks, but the database does not have the column names etc in mixed case. Can you connect directly to the postgres server (psql -h d

Re: [SQL] slow count(CASE) query

Grant Masan wrote: > Hi all, > > I have this kind of query that I need to do, yes my query is giving > right answers now but it is long and slow. I am now asking you that if > you have another solution for my query to make that more smarter ! Hope > you can help me with this ! > > > select

Re: [SQL] report generation from table.

A. Kretschmer wrote: > In response to sathiya psql : >> Hi All, >> >> I have been searching for, Preparing report from a huge table. >> >> Queries am executing now are, >> SELECT count(*) from HUGE_TBL where cond1, cond2; >> SELECT count(*) from HUGE_TBL where cond1, cond3; >> --- like this i hav

Re: [SQL] Random Unique Id

randomly (like the example I post). > I used to insert an MD5 field but this time I need "only numbers" Id. > Regards... > > Nahuel Alejandro Ramos. > > > On Tue, Oct 20, 2009 at 4:53 PM, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: > &

Re: [SQL] Random Unique Id

Nahuel Alejandro Ramos wrote: > Hi all, > I was searching for a sequence (for serials) that let me use a random > unique number ID on a Primary Key or a simple index. > I have not found a solution so I have done it by myself. I would like > to share it so here it is: > > --

Re: [SQL] Efficiency in Multiple Queries of Same Table in a PL/PgSQL Function

IF; END; $poly_example$ LANGUAGE plpgsql STRICT IMMUTABLE; I've just put it in your plpgsql body to handle the case where table blah contains no rows. Matthias Rob Sargent schrieb: I don't see anything in the assignment statements (sigma_* :=) which would prevent one from doing all three

Re: [SQL] Efficiency in Multiple Queries of Same Table in a PL/PgSQL Function

I don't see anything in the assignment statements (sigma_* :=) which would prevent one from doing all three of them within a single for loop. In fact, written as is there's some chance the values of the sigma_*s might change between repeated calls to the function since there is no explicit ord

Re: [SQL] reading last inserted record withoud any autoincrement field

Osvaldo Kussama wrote: 2009/10/4 mohammad qoreishy How can get last inserted record in a table without any autoincrement filed? I need to frequently fetch the last inserted record. If I must use the "Cursor" please explain your solution. RETURNING clause? http://www.postgresql.org/

Re: [SQL] Data integration tool in Chinese?

Are you asking that all strings be stored into the other three languages as part of (potentially many-master) replication? hfdabler wrote: Hello to all, Being in a pretty much international company, I have come here to ask a few things about ETL tools and their different languages. We have

Re: [SQL] selecting latest record

Let's say there's an index on the date column: Does the where clause approach necessarily out perform the distinct on version? Hoping the OP has enough data to make analyse useful. A. Kretschmer wrote: In response to Louis-David Mitterrand : Hi, I have a simple table price(id_product, p

Re: [SQL] Working slow

Judith Altamirano wrote: hello every body, I'm having a data base in a point of sale that is getting frozen, I already have run a vacuum -z -d to reindex the data base and nothing happens.. Some suggestions to speed the process, Do you guys think that the data base is nearly to broke? As

Re: [SQL] Hibernate, web application and only one sequence for all primary keys

Above all, do not fret about whether or not it is "cool to lose" some ids. There are plenty of integers; the ids need not be consecutive. I don't think Grails requires a single sequence source and I know hibernate does not. Hibernate will allow one to inject any sequence/id generator you wis

Re: [SQL] CHECK constraint on multiple tables

Mario Splivalo wrote: Tom Lane wrote: Mario Splivalo writes: I have two tables, tableA and tableB: CREATE TABLE tableA (idA integer primary key, email character varying unique); CREATE TABLE tableB (idB integer primary key, email character varying unique); Now, I want to create

Re: [SQL] how to: refer to select list calculations other places in the calculations.

Doug -Original Message- From: Rob Sargent [mailto:robjsarg...@gmail.com] Sent: Wednesday, September 09, 2009 11:59 PM To: Little, Douglas Subject: Re: [SQL] how to: refer to select list calculations other places in the calculations. Would reverting to the base columns work? select a, b, a+b as c,

Re: [SQL] Question

Call nextval first? Too many quotes? aymen marouani wrote: Hi for all, What is the possible sources of the SQLState 55000 "OBJECT NOT IN PREREQUISITE STATE" ? The error 55000 was triggered when I executed the following query : "select currval('"BatchTreatment_batch_treatment_id_seq"');" Tha

Re: [SQL] Odd sort behaviour

one was rather perplexing. I'm sure this a life-time's worth of discussion on the merits of treating "." as nothing when sorting Sorry for the noise. Greg Stark wrote: On Tue, Sep 1, 2009 at 11:31 PM, Rob Sargent wrote: How many ways might one accidentally

Re: [SQL] Odd sort behaviour

How many ways might one accidentally do that I wonder. Scott Marlowe wrote: On Tue, Sep 1, 2009 at 3:01 PM, Rob Sargent wrote: Since when does "." sort as "nothing at all" Since you set your locale equal to something like en_US instead of C -- Sent via pg

[SQL] Odd sort behaviour

Since when does "." sort as "nothing at all" This select select distinct u.user_name from subscriber_user u, subscription s, subscription_template t where u.id = s.subscriber_entity_id and s.template_id = t.id a

Re: re[SQL] cursively isolate pedigrees, based only on person_id, mother_id and father_id

On the assumption that you wish to generate the pedigrees for analysis or charting, why not perform the recursion in those layers (or their supporting software), This does not require a large number of sql calls since using an in-clause each call will gather one generation (ascending or descendi

Re: [SQL] [GENERAL] Data audit trail techniques in postgresql

Alvaro Herrera wrote: Rob Sargent escribió: tablelog doesn't appear any more lively than the OPs audittrail2. Perhaps, but I have heard of people using it successfully recently, whereas Nathaniel reported that audittrail2 seems to have obvious bugs. Fair enough. -- Sen

Re: [SQL] [GENERAL] Data audit trail techniques in postgresql

tablelog doesn't appear any more lively than the OPs audittrail2. Alvaro Herrera wrote: Nathaniel Smith wrote: What do others use to accomplish this? Do most pg users just write triggers by hand? Or is there some nice auditing module that Google just isn't revealing to me? I think ta

Re: re[SQL] solved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable

In so much as id-ma-pa is near and dear to my heart, I would really appreciate and performance metrics you might be able to share. Especially size of person table, typical pedigree size and pedigree retrieval time (tainted by hardware specs of course). Thanks rjs rawi wrote: me again...

Re: Re[SQL] write multiple joins...

Well indexing (or lack thereof) could be the real problem but you could try "chaining" the tables select * from sale s, taxes t, property p, buyer b where s.id = t.id and t.id = p.id and p.id = b.id and see if that (or other combination) changes the query plan appreciably. (I would have to wo

Re: [SQL] mail alert

is not registered yet, is it best to poll on this column to send a warning, or use a trigger?? Thanks!! Jan -Original Message- From: Rob Sargent [mailto:robjsarg...@gmail.com] Sent: Wednesday, August 12, 2009 3:38 AM To: Denis BUCHER Cc: Jan Verheyden; 'pgsql-sql@postgresql.org&

Re: [SQL] mail alert

Denis BUCHER wrote: Hello, Jan Verheyden a écrit : I was looking in what way it’s possible to alert via mail when some conditions are true in a database. a) If the alert is not "very urgent" i.e. you can alter some minutes later I would do it like this : 1. Create a function that ret

Re: [SQL] Determining logically unique entities across many partially complete rows where at least one column matches

Seems to me that if you can safely identify which snippets correspond to a given entity you want a single id for the entity. An entity-snippet relationship seems a must. I would not lean too heavily on a single table solution unless you're considering arrays for openid,email and phone. (And g

Re: [SQL] Create table command fails with permission denied

E TABLE camd=# \c cms postgres You are now connected to database "cms" as user "postgres". cms=# create table test(id character varying(80)); ERROR: could not create relation "test": Permission denied cms=# == Thanks, Venkat --

Re: [SQL] Create table command fails with permission denied

Looks to me as though you are not the owner of the schema nor superuser nor in a role with permission to create tables in said schema. See the DBA if it's not you. If it is sign on as postgres (superuser) and grant yourself some access rights. Venkateswara Rao Bondada wrote: Hi, I’m new t

Re: [SQL] SQL report

planner and there is no maintainance for B-tree indexes once it is created. (Please point me out if I am wrong about this) I will probably try to partition the status table to group more recent status records together to minimize the dataset I am querying. Thx John On Jul 31, 2009 1:16am, Rob

Re: [SQL] Tweak sql result set... ?

I agree. All clients issue the same sql and deal with it as they will. The psql client for example can format the results in various ways (pset variations etc). Your client(s) need(s) to interpret their identical results differently. Doesn't seem to me to be the job of SQL? Jasmin Dizdarev

Re: [SQL] SQL report

I would be curious to know the performance curve for let's say 20K, 40K , 60K, 80K, 100K records. And what sort of indexing you have, whether or not it's clustered, re-built and so on. One could envision partitioning the status table such that recent records were grouped together (on the assu

Re: [SQL] Detect referential integrity structure

Perhaps another option: Alter the references to ON DELETE CASCADE as seen here Akos Gabriel wrote: Hi, I've a big/complex database (Adempiere - www.adempiere.org ) where I'd like to delete some rows from some tables (delete a

Re: [SQL] Order of operations in ascii vs archive format (moderately urgent)

The ascii dump has serveral CREATE FUNCTION gbtreeN_{in,out} but I don't see them in the current (source) database using '\df gbtree*'. Using '\df gbt*' I get 111 functions for which all the names begin 'gbt_'. Have I lost them? The gbtreekeyN types are s

Re: [SQL] Order of operations in ascii vs archive format (moderately urgent)

Wonderful news. I just ran dump and restore against same production server. Constraints and the absence of drop calls appears to have saved my butt. Tom Lane wrote: Rob Sargent writes: Is there a difference in the order of execution between an ascii dump and one using the "c

[SQL] Order of operations in ascii vs archive format (moderately urgent)

Is there a difference in the order of execution between an ascii dump and one using the "custom" format? Or any difference in the general operation? I need to know if I can rely on the ascii version to tell me what the custom format might have done. -- Sent via pgsql-sql mailing list (pgs

[SQL] Moving text columns, when it actually is large

I have to restructure some tables, coalescing common elements from three tables (sub-classes) into a single table (super-class). Each source table has a text field which actually gets stuffed with a largish (1Mb+) blob of xml. Is there any way to simply, um, er, transplant the pointer rather

Re: [SQL] it's not NULL, then what is it?

So they were null, and null turns out to be a seven-character blank string!? Btw, you can change the displayed value of null with \pset null nil and you will seem 4+ million 'nil's in your output Tena Sakai wrote: Hi Osvaldo, > Try: > SELECT count(*) FROM gallo.sds_seq_reg_shw; > SELECT c

Re: [SQL] it's not NULL, then what is it?

Tena Sakai wrote: Hi Everybody, I have a table called gallo.sds_seq_reg_shw, which is like: canon=# \d gallo.sds_seq_reg_shw Table "gallo.sds_seq_reg_shw" Column | Type | Modifiers --+-+--- name | text| response

Re: [SQL] Bucketing Row Data in columns

I would be suspicious of this sort of solution of turning rows into columns by mean of a series of correlated sub-selects. Once the data set gets large and the number of columns goes over 2 or 3 this will in all likelihood not perform well. I had the pleasure of re-writing a "report" which was

[SQL] Client-side compression

Not sure if this belongs here or on the admin or performance list. Apologies if so. (And this may be a second posting as the first was from an un-registered account. Further apologies) My assumption is that any de/compression done by postgres would be server-side. We're considering minim

Re: [SQL] 2 tables or two db?

ivan marchesini wrote: Dear postgres users, Suppose I have two tables of data. suppose the two table are really similar in dimensions suppose they will receive the same number and type of queries. in tems of performance (velocity of answer) it is better to place the two tables in the same d

Re: [SQL] left outer join on more than 2 tables?

Richard Broersma wrote: On Tue, Jun 16, 2009 at 2:40 PM, Rob Sargent wrote: Is there a city without a reference to region? I don't know, but the OP wanted to know complaints by region. I didn't try this, but with regionless cities, you may need a full join if you want

Re: [SQL] left outer join on more than 2 tables?

Richard Broersma wrote: On Tue, Jun 16, 2009 at 1:59 PM, Carol Cheung wrote: I would like to find the counts of complaints by region and I would like all regions to be displayed, regardless of whether or not complaints exist for that region. Is left outer join what I'm looking for?

Re: [SQL] polymorphic function in 7.4 vs. 8.3

Richard Rosenberg wrote: On Thursday 11 June 2009 14:49:46 Tom Lane wrote: Sure you can't move the DB off 7.4? There would be pretty considerable benefits from adopting some recent release instead. regards, tom lane Don't I know it. I am SOL as the machine is

  1   2   >