[SQL] Re: Casting

Yes. Casts are done like this: SomeFiled::int4 or SomeField::text Double colons is the key '::' --rob - Original Message - From: "Thomas SMETS" <[EMAIL PROTECTED]> To: "psql novice" <[EMAIL PROTECTED]>; "psql sql" <[EMAIL PROTE

[SQL] plpgsql grief

could do some kind of looping through the OLD and NEW dataset-array things, comparing them against each other, sorta like this : for ($i = 0 ; $i < count($NEW) ; $i++) { /* since $NEW and $OLD are essentially the same we can do this */ if ($OLD[$i] != $NEW[$i]) record the change bla bla bla } I'm really hoping I can, as at this rate I've spent the better part of three days trying to figure the simple things above out and the only thing i'm about to reach is breaking point... Sorry for the sarcasm, I'm about to pop. Rob

[SQL] Re: plpgsql grief

everal months of news group postings there has been the recognision of the need for examples for us newies, and some mention of people compiling various docs for just such a purpose - anyone get anywhere with any of these, as they were several months ago. I'm certainly gaining some real gotcha type info on all of this :) Now I've had it. Burned out. So off to the pub and henceforth become as drunk as a skunk ! Regards Rob

Re: [SQL] Rank

Martin Knipper wrote: Am 04.05.2004 16:11 schrieb george young: On Sun, 2 May 2004 02:22:37 +0800 "Muhyiddin A.M Hayat" <[EMAIL PROTECTED]> threw this fish to the penguins: I Have below table id | site_name | point +---+--- 1 | Site A|40 2 | Site B|90 3 | Site D

Re: [SQL] Equivalant of SQL Server's Nchar and NVARCHAR

ii_to_utf_8); select * from nvctest; text functions including encoding conversions are here: http://www.postgresql.org/docs/current/static/functions-string.html and http://www.postgresql.org/docs/current/static/functions-string.html#CONVERSION-NAMES Good Luck, Rob

[SQL] Sql Query help: Remove Sub Selects

Hi Gang, I'm trying to optimize a query: This query below returns something like this: event_id | eu_tid | event_name | event_when | day | mon | start | end|event_users | contact_phone| contact_pager | num_opps --++-

[SQL] Storing HTML in table

Hi: I need to store markup in a database to control how the browser renders the page information. I would like complete control over this information -- so if sometime in the future it's decided to totally redesign the layout. Also, at some point a tool will be created so novice computer users ca

[SQL] Help with displaying data types.

= pg_class.oid and atttypid != 26 and atttypid != 27 and atttypid != 28 and atttypid != 29; But what do I need to add to return the datatype of each attribute. Any suggestions please? Regards, Rob. _ Get Your Private, Free E-mail from

[SQL] Access Permissions/Security

? Many thanks, Rob. _ Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com

[GENERAL] Re: [SQL] Permissons on database

Are you looking for "grant all to public" or "grant select to public"? - Original Message - From: "Roland Roberts" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, March 07, 2001 3:40 PM Subject: Re: [SQL] Permissons on database > >

Re: [SQL] How does postgres handle non literal string values

+ "', '" + password + "')"; %> However, you would probably be better off using a PreparedStatement object rather than a Statement for various reasons, but especially to avoid trying to get the single- and double-quotes right in the above statement. HTH,

[SQL] searching cidr/inet arrays

i'm having trouble figuring out how to search in inet arraysits been a long time since i used postgres array support, so i may just be bone-headed.. how can i determine if a given ip address is contained in the subnet declaration inside an array? {134.53.25.0/24,134.53.0.0/16} {13

[SQL] 'select where' using multiple columns.

up into forename and surname because I won't also know. Is there a way to do something like a 'select * where forename_1,forename_2,surname like '%String%' ?? Thanks for any help. Apologies if its a FAQ. Rob ---(end of broadcast)---

Re: [SQL] 'select where' using multiple columns.

Ian Johannesen wrote: Hi. Rob Kirkbride wrote: I've got a persons name which is being stored say in 3 columns :- Title, Forename_1, Forename_2, Surname. I want to allow a search say for 'John Smith'. Problem is I can't just break it up into forename and surname becau

Re: [SQL] Type inheritance

On Sat, Jun 6, 2009 at 8:30 AM, Richard Broersma wrote: > On Sat, Jun 6, 2009 at 12:10 AM, Gianvito Pio > wrote: > > > That value doesn't have to be fixed, but I want to define it in a > > way that it changes its structure when the sensor type changes. For > > example, for Temperature sensor I w

[SQL] 'cost' and 'rows' for volitile function

pg-admin is showing 'COST 100' and 'ROWS 1000' for my explicitly VOLATILE functions. Is one hundred the new ninety-nine? and therefore these values are ignored by the planner? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postg

[SQL] Taking the cache out of the equation?

Caching helps a *lot* and I'm thankful for that but I would like to take it out of the picture as I massage my queries for better performance. Naturally the first invocation of the query cannot take advantage of the cache and these queries would normally only be called once for the same target

Re: [SQL] setting the where clause

Tom Lane wrote: johnf writes: I'm am programming in python using the Dabo modules. www.dabodev.com if your interested. Dabo is a framework that provides an easy way to build desktop app's. To clear a data entry form. I have been setting the where clause to "where 1=0". This of course

Re: [SQL] Avoiding "will create implicit index" NOTICE

A. Kretschmer wrote: In response to Bryce Nesbitt : Hmm, no. I still get the NOTICE. How can I create the primary key without triggering a NOTICE? Sure, set client_min_messages='...' test=*# create table bla(id int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implici

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

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] 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] 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

[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] 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

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] 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

[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

[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

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

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] 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] 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] 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

Did you look at the query plans for the various record counts? That might show which index is missing or misinformed :). I wonder if clustering the status table on objectid would help? This does then require maintenance so you might only load it at 75%. wkipj...@gmail.com wrote: Hi Rob

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] Create table command fails with permission denied

Moderately curious. I wonder if 'cms' is on a different tablespace? Are there tables in that db? Btw, I don't have 7.4 so I'm only guessing based on 8.3 rjs Venkateswara Rao Bondada wrote: Hi Rob, I'm using postgres account (which is a superuser in the database

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] 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] 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: 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: 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: [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: [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: 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

[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: [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

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] 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] how to: refer to select list calculations other places in the calculations.

I might do the substitution, analyse the query and hope to break it up. Can it possibly be optimal? Little, Douglas wrote: Hi Rob, Thanks for the response. Repeating the base calculation will work but the calculations are stunningly complex and long. Repeating them will undoubtly increase

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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] '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] 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] 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] 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

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] 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... > > >

[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] 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

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] 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] Emacs sql-postgres (please, sorry for question not about PostgreSQL).

I recommend switching to aqua data studio I can query mysql, postgres, db2, oracle with the same tool From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Dmitriy Igrishin Sent: Thursday, March 18, 2010 4:44 PM To: postgres list Subject: [SQL] Emac

Re: [SQL] Week to date function

U only 52 calendar weeks in a year... I'm almost sure that is the norm -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Ireneusz Pluta Sent: Saturday, March 27, 2010 3:22 PM To: Jorge Godoy Cc: Sergey Konoplev; pgsql-sql@pos

Re: [SQL] SQL Developer accessing PostgreSQL

I recommend Aqua Data Studio Just drop in the jdbc jar From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Snyder, James Sent: Monday, March 29, 2010 11:34 AM To: pgsql-sql@postgresql.org Subject: [SQL] SQL Developer accessing PostgreSQL Hello

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] 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] [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] 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] 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

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] how to construct sql

db2 has a group by rollup function.. does this exist in postgres? -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Oliveiros Sent: Wednesday, June 02, 2010 11:55 AM To: Wes James; pgsql-sql@postgresql.org Subject: Re: [SQL] how to

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

[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] 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

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] 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] Duplicates Processing

but slightly more involve maintenance issue of course. On 10/08/2010 02:42 PM, Gary Chambers wrote: > Rob, > >> Perhaps a trade off between nullable fields and redundant types. If >> your original table simply had a nullable column called >> isReplacementFor, into which you pl

Re: [SQL] Duplicates Processing

8/2010 04:12 PM, Gary Chambers wrote: > Rob, > >> 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. > > I understand -- thanks. I have rec

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! > >> An

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] 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

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

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

[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] 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

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] 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] DELETE FROM takes forever

don't. DB2 doesn't (( at least not without more effort than reasonably necessary )) so I write my delete queries to use chunks at a time. The most I would recommend is 100,000 records deleted at once. Play with timing and see what works for you Hope this helps -Rob -Original Mess

  1   2   >