[SQL] Re: [HACKERS] Facing authentication error on postgres 9.2 -> dblink functions

2013-02-06 Thread Andrew Dunstan
s time. So do NOT follow up this email. This question belongs on pgsql-general. If you have further questions pleease ask there. The short answer is that you need to provide the user name in your connect string. cheers andrew -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org

Re: [SQL] PostGres Tables in ArcSDE and ArcCatalog.

2010-08-31 Thread Michael Andrew Babb
fine. However, PostGRES can interact with tables with mixed case letters just fine. Thanks, Mike -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Monday, August 30, 2010 11:40 PM To: Michael Andrew Babb Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] PostGres

[SQL] PostGres Tables in ArcSDE and ArcCatalog.

2010-08-30 Thread Michael Andrew Babb
Hi All, I am using PostGRES 8.3 in conjunction with ESRI's ArcSDE. ArcSDE allows the storage of spatial data inside of an PostGRES database. One of the programs bundled with ESRI's GIS suite is ArcCatalog. ArcCatalog is a spatial data manager. It is possible to browse and analyze the contents o

[SQL] enforcing constraints across multiple tables

2010-06-25 Thread Andrew Geery
cated nature of the solution make the design poor? (C) Should I not worry about this constraint at the DB level and just enforce it at the application level? Below are the tables, functions and triggers I was using. Thanks! Andrew

[SQL] Foreign key - Indexing & Deadlocking.

2009-12-29 Thread Andrew Hall
the safe side. Thanks, Andrew. _ Use Hotmail to send and receive mail from your different email accounts http://clk.atdmt.com/UKM/go/186394592/direct/01/

[SQL] PostgreSQL Security/Roles/Grants

2009-11-01 Thread Andrew Hall
signed to a user (or more accurately a 'login role') in postgreSQL? Many thanks, Andrew. _ New Windows 7: Find the right PC for you. Learn more. http://www.microsoft.com/uk/windows/buy/

Re: [SQL] Table Valued Parameters

2009-10-24 Thread Andrew Hall
e to express my thanks to you for taking the time to suggest an approach. Cheers, Andrew. > Date: Fri, 23 Oct 2009 20:32:37 +0200 > Subject: Re: FW: [SQL] Table Valued Parameters > From: br...@zwartberg.com > To: andre...@hotmail.com > CC: pgsql-sql@postgresql.org > > 2

FW: [SQL] Table Valued Parameters

2009-10-23 Thread Andrew Hall
question more clear. Thanks, Andrew. > Date: Fri, 23 Oct 2009 20:10:48 +0200 > Subject: Re: [SQL] Table Valued Parameters > From: pavel.steh...@gmail.com > To: andre...@hotmail.com > CC: pgsql-sql@postgresql.org > > Hello > > 2009/10/23 Andrew Hall : > > Hi, > &

[SQL] Table Valued Parameters

2009-10-23 Thread Andrew Hall
ould help me to arrive at an optimal solution. Cheers, Andrew. _ Download Messenger onto your mobile for free http://clk.atdmt.com/UKM/go/174426567/direct/01/

[SQL] Dynamic Query

2009-10-19 Thread Andrew Hall
tance? The postgresql documentation seems to suggest that I can use the RETURN QUERY EXECUTE feature, or simply build my query with a string and execute it (I don't see how the latter can protect me from SQL Injection though???) Any help would be appreciated! Thanks, Andrew

Re: [SQL] surrogate vs natural primary keys

2008-09-16 Thread Andrew Sullivan
for which the identifier is assigned. ISO, for instance, is willing to re-use country codes (even though the specification never suggested they were). So if you expect to use the ISO 2-letter codes over time, you may get a nasty surprise. (For an example, in 2003 "CS" became historically am

Re: [SQL] How long - Vacumm full - 10 million to 90,000

2008-05-29 Thread Andrew Sullivan
lease? A really really long time. You might actually be better off to dump the table and restore it from that. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscript

Re: [SQL] Get the last inserted id

2008-04-11 Thread Andrew Sullivan
On Fri, Apr 11, 2008 at 07:23:04PM +0200, Nacef LABIDI wrote: > Hi all, > > I was wondering if postgres can return the last ID inserted for a table > which the primary key is autoincremented. I need to retrieve this ID in my > code to continue processing on that inserted row. Use select currval()

Re: [SQL] duplicate key violates unique constraint

2008-02-26 Thread Andrew Sullivan
On Tue, Feb 26, 2008 at 05:05:01PM +0100, Shavonne Marietta Wijesinghe wrote: > Thank you. I tried as you said. But i get a ERROR: syntax error at or near > "INSERT" at character 9 > I don't see anything wrong with the code.. Well, except that there's no "EXCEPTION" statement in SQL? I think

Re: [SQL] Cheers for DISTINCT ON

2008-01-07 Thread Andrew Sullivan
On Mon, Jan 07, 2008 at 12:51:19PM -0500, Chris Browne wrote: > I have heard that Gabriel has, at different conferences at different > times, taken and argued opposite positions on this; he has both argued > "Worse is Better" and that "Worse isn't Better." Yes. That history is actually outlined b

Re: [SQL] Cheers for DISTINCT ON

2008-01-07 Thread Andrew Sullivan
On Mon, Jan 07, 2008 at 05:02:27PM +0100, Peter Kovacs wrote: > I just wanted to give my cheers for DISTINCT ON. It is a great > feature, I've just found a really good use for it. I am just wondering > why it didn't make it into the standards. Likely because neither Oracle Corp nor IBM nor (at the

Re: [SQL] case sensitivity for tables, columns, and constraint names

2007-12-11 Thread Andrew Sullivan
On Tue, Dec 11, 2007 at 11:05:25AM -0600, Ertel, Steve wrote: > is wrapped in quotes. Is there a setting to allow upper case and mixed > case names for database tables, fields, etc, without having to wrap each > in quotes? No, sorry. The always-one-case rule for unquoted identifiers is ANSI con

Re: [SQL] Is there anything special about pg_dump's compression?

2007-11-15 Thread Andrew Sullivan
t's zlib, if I recall correctly. So probably not. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscrib

Re: [SQL] Backup Database

2007-10-31 Thread Andrew Sullivan
t; insert) on the backup 7.4 database? You can do this with Slony, assuming nothing else needs to be written (on the replicated tables) into the 7.4 system. A -- Andrew Sullivan | [EMAIL PROTECTED] Never get involved in litigation. Your hair will fall out, your bones will turn to sand. And it wi

Re: [SQL] request for help with COPY syntax

2007-10-23 Thread Andrew Sullivan
on the way into the import step. A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 9: In versions

Re: [SQL] what's wrong with my date comparison?

2007-10-16 Thread Andrew Sullivan
datecreated > '2007-10-02'; Note the quotes. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---

Re: [SQL] Extracting hostname from URI column

2007-09-17 Thread Andrew Sullivan
sibility from the DNS labels, and it's sometimes terrifically important not to make that mistake. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Database normalization

2007-08-28 Thread Andrew Sullivan
field that does not get used very much, you have to pay the I/O for it every time you look at that row, even if it's not used. Also, it sounds like it might not be used by every row? In that case, normalization calls for it to be pushed out too. A -- Andrew Sullivan | [EMAIL PROTECTED] Howeve

Re: [SQL] Database normalization

2007-08-28 Thread Andrew Sullivan
ecome > very large. Should I take this in to > consideration when deciding whether to split the tables? In terms of > searching speed that is. I'd put it in its own table, probably, unless you're going to use it frequently. A -- Andrew Sullivan | [EMAIL PROTECTED] Everyt

Re: [SQL] Database normalization

2007-08-28 Thread Andrew Sullivan
re you add a CHECK constraint where !(col1 IS NULL and col2 IS NULL). A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin -

Re: [SQL] Sequences problem

2007-08-17 Thread Andrew Sullivan
because the message is in spanish) You _must_ call nextval() before a currval(). This is documented behaviour. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--

Re: [SQL] Comparing two slices within one table efficiently

2007-08-13 Thread Andrew Kroeger
y selective - once it finds the correct run_id in the index, there will still be a lot of index entries to scan to match on domain_id and mta_id. Re-ordering the way I propose should narrow down the number of index entries quite quickly, as it will first be narrowed on domain_id, then mta_id, a

Re: [SQL] Database synchronization

2007-07-31 Thread Andrew Sullivan
ndant objects > - FKeys, views, functions etc). It doesn't allow this, but you could cause it to happen anyway on the replica using a trigger. You have to use STORE TRIGGER to make this work. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern

Re: [SQL] Connection Limit

2007-07-30 Thread Andrew Sullivan
> differ? I _believe_ [local] means UNIX domain socket. As to your other question, either ps or pg_stat_activity is your friend. A -- Andrew Sullivan | [EMAIL PROTECTED] The very definition of "news" is "something that hardly ever happens." --Bruce Schneier

Re: [SQL] Database Synchronization

2007-07-23 Thread Andrew Sullivan
You _might_ be able to get this to work by installing whatever extra bits the YAST installation tool offers (probably something with -dev- or -src- in it). A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there

Re: [SQL] data dependent sequences?

2007-07-17 Thread Andrew Sullivan
hat the requirement _really_ is, and then you won't have to implement what sounds like a bad idea. A -- Andrew Sullivan | [EMAIL PROTECTED] However important originality may be in some fields, restraint and adherence to procedure emerge as the more significant virtues in a great many oth

Re: [SQL] data dependent sequences?

2007-07-17 Thread Andrew Sullivan
nce or whatever for your ORDER BY clause, and not store data that you actually don't care about. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 9:

Re: [SQL] data dependent sequences?

2007-07-17 Thread Andrew Sullivan
for each typ, then? If they're just there to preserve order, one sequence will work just fine. Otherwise, I think you have a normalisation problem. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for rai

Re: [SQL] using EXECUTE on Selects

2007-07-11 Thread Andrew Sullivan
Is there a way to select data using EXECUTE? You missed the opening sentence of the previous paragraph: The INTO clause specifies where the results of a SELECT command should be assigned. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away fr

Re: [SQL] select from table and add rows.

2007-07-06 Thread Andrew Sullivan
ther'::text as c UNION SELECT a::text, b::text, c::text FROM sometable WHERE [criteria] The casts might not be needed, of course. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)

Re: [SQL] select from table and add rows.

2007-07-06 Thread Andrew Sullivan
perly, because I think you want ot set up a PHP array with values from your table, without using PHP. Which would of course make no sense. Do you mean, how do you populate an array data type with data from individual database columns? A -- Andrew Sullivan | [EMAIL PROTECTED] However important

Re: [SQL] record fields as columns in reports

2007-06-28 Thread Andrew Sullivan
2 0 0 I think this should be possible with the "crosstab" functionality delivered in Joe Conway's tablefunc package, in contrib/. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them

Re: [SQL] NO DATA FOUND Exception

2007-06-25 Thread Andrew Sullivan
On Mon, Jun 25, 2007 at 04:20:37PM -0300, Fernando Hevia wrote: > when <> then return variable; > when <> then <> ; > when <> then <> ; Check out the FOUND variable in the documentation for the first two, and the "trapping errors" section

Re: [SQL] joining a table whose name is stored in the primary record

2007-06-19 Thread Andrew Sullivan
On Tue, Jun 19, 2007 at 02:02:46PM -0400, John Gunther wrote: > Well, Andrew, you're certainly right but I made an exception because of > the data needs. Ah, well, in that case, you'll need something other than SQL for sure. A function as suggested is probably your friend

Re: [SQL] joining a table whose name is stored in the primary record

2007-06-17 Thread Andrew Sullivan
very normal-form thing to do, because the data has been broken into pieces dependent on the data itself, rather than the kind of data it is. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook de

Re: [SQL] current_date / datetime stuff

2007-06-04 Thread Andrew Sullivan
est possible Monday. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Versionning (was: Whole-row comparison)

2007-06-04 Thread Andrew Sullivan
x27;s a fundamental reason, no. But why couldn't you change your query to issue the SRF directly, with the parameter: SELECT * FROM some_srf(param1, param2)? A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying

Re: [SQL] Versionning (was: Whole-row comparison)

2007-06-01 Thread Andrew Sullivan
On Fri, Jun 01, 2007 at 09:07:10PM +0300, [EMAIL PROTECTED] wrote: > > Hi Andrew, > > I must admit I don't really understand what you say. I don't know what > SRF stand for, and what you say about generic case is not clear to me, > sorry. Sorry, it stands fo

Re: [SQL] Versionning (was: Whole-row comparison)

2007-06-01 Thread Andrew Sullivan
uld > work but would definitely be ugly. Can someone think of a better way to > do that ? I sort of don't see how that hack would be any different from a SRF. You'd lose the planner benefits anyway, I think, because you'd have to plan for the generic case where the data co

Re: [SQL] slow query execution

2007-05-30 Thread Andrew Sullivan
er that SQL returns in is not determined until the data has come back. Are you doing this all in one serialisable transaction, though? If not, what guarantee will you have that new data won't mess up your row numbering from query to query? A -- Andrew Sullivan | [EMAIL PROTECTED] The plur

Re: [SQL] slow query execution

2007-05-30 Thread Andrew Sullivan
o do the DROP, you just ROLLBACK. I think there's some nifty way to get generate_series to do this too, but I don't know it offhand (generating row numbers sounds to me like a bad idea, so I don't do it). A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] slow query execution

2007-05-30 Thread Andrew Sullivan
n the table? I'm not surprised it takes a long time -- you have to join the whole table to itself and then do a lot of counting. Are you just trying to get the "row number" for your query answer? You can do this with a temporary sequence, among other approaches, more cheaply

Re: [SQL] aggregate query

2007-05-29 Thread Andrew Kroeger
1,3,4,5,6,7,8,9; If you add more bays for tracking, you can simply add additional joins against new_bay_use to mimic what you would have had in your original table structure. Hope this helps. Andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Serial number of a record

2007-05-15 Thread Andrew Sullivan
? > if yes then how? > plz. help me. This depends. Does the record have a serialno field? If not, you can do it with a temporary sequence. But you should be aware that this "serial number" as you call it changes from result to result in that case. A -- Andrew Sullivan | [EMAIL PR

Re: [SQL] pg_dump?

2007-05-15 Thread Andrew Sullivan
spyware > protection. > http://new.toolbar.yahoo.com/toolbar/features/norton/index.php > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers

Re: [SQL] Sequence vs. Index Scan

2007-05-07 Thread Andrew Sullivan
creasing the stats sample, and see if that helps. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)-

Re: [SQL] Sequence vs. Index Scan

2007-05-05 Thread Andrew Sullivan
ecause of a failed foreign key? If so, you can end up with dead tuples. I'd look at the output of VACUUM VERBOSE to make sure you don't have a lot of dead tuples. That said, I wonder if fiddling with the statistics on your tables might help. A -- Andrew Sullivan | [EMAIL PROTECTE

Re: [SQL] Sequence vs. Index Scan

2007-05-05 Thread Andrew Sullivan
annot figure out why the optimizer is > looking at these differently. In fact, the table the branch_id comes from > has the exact same indices and foreign keys on both schemas. Different data? Different statistics? Different numbers of (possibly dead) rows? A -- Andrew Sullivan

Re: Fwd: Re[2]: [SQL] We all are looped on Internet: request + transport = invariant

2007-04-26 Thread Andrew Sullivan
orks, but is instead some sort of veneer over the face of it? In that case, why not just write some user-space application that takes this (IMO useless) TML and translates it into proper SQL? You don't need to make any changes to Postgres at all, it seems. A -- Andrew Sullivan | [EMAIL PROTE

Re: [SQL] We all are looped on Internet: request + transport = invariant

2007-04-20 Thread Andrew Sullivan
or Perl. That doesn't mean they can get away without learning a bit about XML, Perl, and Pg.pm. Anyway, I've said enough on this topic. When you have the start of a user library that implements your proposal, perhaps you can post it to -hackers for the response you'll get there. A

Re: [SQL] Doing a conditional insert/update

2007-04-19 Thread Andrew Sullivan
.2 release. But I have doubts that your program design is right if this is the approach you're trying to take (it's not impossible that it's the right way, just that this is often a workaround for a dodgy data model in the first place). What is the conflict you're trying to avoi

Re: [SQL] We all are looped on Internet: request + transport = invariant

2007-04-19 Thread Andrew Sullivan
eSQL back end), then write that. Don't bollocks up the back end and change the syntax of SQL to achieve what is, IMNVHO, a really bad idea. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of cod

Re: [SQL] Floating point type to store numbers

2007-04-17 Thread Andrew Sullivan
On Tue, Apr 17, 2007 at 04:33:33PM -0400, Radhika Sambamurti wrote: > Andrew, > This has been quite helpful. My main concern is CPU cost. Thanks for the > input. You're welcome. Are you sure your main concern should be CPU cost? It's true that numeric is more costly that floa

Re: [SQL] Floating point type to store numbers

2007-04-17 Thread Andrew Sullivan
d lose precision). > The question is: how accurate is floating point numbers in Postgres. We As accurate as they are in the underlying C implementation, which is to say "not accurate enough for financial data". A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are

Re: [SQL] A long-running transaction

2007-04-13 Thread Andrew Sullivan
n-place storage management that EnterpriseDB contributed was both somewhat controversial and somewhat limited in its application. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well.

Re: [SQL] A long-running transaction

2007-04-13 Thread Andrew Sullivan
On Fri, Apr 13, 2007 at 07:49:19AM -0400, Andrew Sullivan wrote: > On Fri, Apr 13, 2007 at 09:02:58AM +0800, John Summerfield wrote: > > 1. For the first day or so, my observation was that the disk was not > > particularly busy. > > That's completely consistent with

Re: [SQL] We all are looped on Internet: request + transport = invariant

2007-04-13 Thread Andrew Sullivan
do those things? SQL is not so fantastically hard that you can't learn it. Indeed, I'm pretty sure that if someone as foolish as I can learn it, anyone can. A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym

Re: [SQL] A long-running transaction

2007-04-13 Thread Andrew Sullivan
's current capabilities, > but by itself it doesn't make what I'm trying to do unreasonable. No, it's not unreasonable, but it happens to be a pessimal case under Postgres. A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a g

Re: [SQL] ERROR: column "crc" does not exist

2007-04-12 Thread Andrew Sullivan
ll the column name correctly. Or don't quote the identifier when you create the table. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton

Re: [SQL] A long-running transaction

2007-04-11 Thread Andrew Sullivan
//www.powerpostgresql.com/Downloads/annotated_conf_80.html Also http://varlena.com/varlena/GeneralBits/Tidbits/perf.html And the -performance list will probably help you more than this one. Hope this helps. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this softw

Re: [SQL] A long-running transaction

2007-04-11 Thread Andrew Sullivan
You can probably bump a little in this case. Your other config seems ok to me. But I don't think this is your problem -- the update pattern is. A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on

Re: [SQL] A long-running transaction

2007-04-10 Thread Andrew Sullivan
the fact it uses so little RAM suggests it doesn't do > that at all). What do you have configured as your shared buffers? If you haven't given very much, there won't be much in the way of buffers used, of course. Note that there's a much earlier diminishing return on the size of sh

Re: [SQL] new idea

2007-04-09 Thread Andrew Sullivan
s a good thing. (I'm not actually convinced even of that. The whole point of SQL was to move away from the hierarchical model, and so grafting a lot of hierarchy back onto it suggests to me that the OP has picked the wrong technology for the problem at the outset.) A -- Andrew Sullivan | [

Re: [SQL] new idea

2007-04-09 Thread Andrew Sullivan
? What does this do that inheritance doesn't already do? I don't think I see anything. A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--

Re: [SQL] slow query

2007-04-05 Thread Andrew Sullivan
experience with a table that was updated _very very_ often. The answer turned out to be to update less often. Aggregating queries that could use an index over a large number of "expired" rows worked better than seqscans over large numbers of dead tuples.) A -- Andrew Sullivan | [EMAIL P

Re: [SQL] A long-running transaction

2007-04-03 Thread Andrew Sullivan
> For each record, I update a non-key field in another table; the source > data for that other table is less than a megabyte. this is a real issue. Basically, you're constrained at the rotation speed of your disk, because for each record, you have to first find then update one row

Re: [SQL] Update problem.

2007-04-03 Thread Andrew Sullivan
r use as selection criteria. > And Andrew can explain a bit the setval() The setval() function sets the current value of a sequence. The problem that you have is that there's no way to LOCK a sequence, so you might find that you can't do it effectively. LOCKing the calling table,

Re: [SQL] Update problem.

2007-04-02 Thread Andrew Sullivan
nnections could be using it. I'd lock the table in question while you did all this. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack.

Re: [SQL] Track query status

2007-03-28 Thread Andrew Sullivan
uery is running?? > here is the query i've ran 48 million records is a lot. You oughta see activity with iostat or something. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --at

Re: [SQL] Regular Expressions

2007-03-21 Thread Andrew Sullivan
g for "like 'blahblah%' or " ~ 'blahblah.*'", they're AFAIK about the same. When you have a more complicated RE, though, it might turn out to be a win. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technologica

Re: [SQL] triple self-join crawling

2007-03-19 Thread Andrew Sullivan
; ORDER BY history.stock, history.day DESC > > > How can I speed this up? > > > -- > > > Regards, > > Tarlika Elisabeth Schmitz > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > &

Re: [SQL] import CSV file

2007-03-14 Thread Andrew Sullivan
On Tue, Mar 13, 2007 at 11:52:17PM +, T E Schmitz wrote: > Also, it's nopt happy about the date format : 2007/02/09 You may need to fiddle with your date style. It works for me on 8.1: SELECT '2007/02/09'::date; date 2007-02-09 (1 row) A -- Andrew

Re: [SQL] index not being used. Why?

2007-03-09 Thread Andrew Sullivan
ows. How big a percentage of the table is that? Also, what does EXPLAIN ANALYSE say about this? A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---

Re: Fw: [SQL] CREATE TABLE

2007-03-09 Thread Andrew Sullivan
topic for the manuals for that environment. A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 3

Re: [SQL] CREATE TABLE

2007-03-09 Thread Andrew Sullivan
you have to pick one style, and be absolutely certain to use it consistently. If you mix the styles, you'll get surprises. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell -

Re: [SQL] SHA-1 vs MD5

2007-03-07 Thread Andrew Sullivan
if not, if the team has plan to introduce > it on PostgreSQL. What is the problem you're trying to solve? Md5 is probably good enough for many cases, but for long-term use, you're right that sha-1 is what you need. Actually, you need sha-256, quite frankly. a -- Andrew Sulliva

Re: [SQL] best index for ~ ordering?

2007-03-07 Thread Andrew Sullivan
everything to lower(). A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] best index for ~ ordering?

2007-03-06 Thread Andrew Sullivan
t sort of match are you doing? If you're doing left-anchored searches (LIKE 'blah%') then your standard btree is good. If you're doing unanchored searches (LIKE '%blah%' or similar) you're best doing tsearch. If it's right-anchored, you can do an in

Re: [SQL] [Fwd: View Vs. Table]

2007-03-06 Thread Andrew Sullivan
ussion of rules in the manual (views are basically just an automatic application of certain rules). A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(

Re: [SQL] Case with Char(1)

2007-02-28 Thread Andrew Sullivan
On Wed, Feb 28, 2007 at 10:02:38AM -0300, Ezequias Rodrigues da Rocha wrote: > Hi list, > > it is possible to use case with character (1) ? > > I am having problems to formate the SQL statement. Your example looked like it worked. A -- Andrew Sullivan | [EMAIL PROTECTED] Use

Re: [SQL] pg_dump fails (timestamp out of range)

2007-02-26 Thread Andrew Sullivan
forget, or you'll end up in the same place next time. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP

Re: [SQL] Problems with temp table and PL

2007-02-21 Thread Andrew Sullivan
for this. See the docs. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--

Re: [SQL] Uppercase and Lowercase

2007-02-19 Thread Andrew Sullivan
On Mon, Feb 19, 2007 at 04:22:06PM +0100, Shavonne Marietta Wijesinghe wrote: > Hello > > In my PostgreSQL database i have records inserted in Uppercase. > For example: VIA SENATO > > What i want is to change them to "Via Senato". Have a look at the initcap

Re: [SQL] ordering of selected rows from an ordered subselect

2007-02-17 Thread Andrew Sullivan
unc(name) might do something that alters it, no? All things considered, it's a pretty good _bet_ it will be ordered as you wish, though. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell --

Re: [SQL] Open a Transaction

2007-02-08 Thread Andrew Sullivan
On Thu, Feb 08, 2007 at 03:13:16PM -0200, Ezequias Rodrigues da Rocha wrote: > > Any suggestion instead of change my max_stack_depth ? Well, I suppose you could put the numbers in a temp table an NOT IN on that. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn&

Re: [SQL] Removing duplicate rows

2007-02-06 Thread Andrew Sullivan
definition > of a table. an easy way to do this is "CREATE TABLE name AS SELECT . . . WHERE 1=0". You get a table with no rows. (WHERE FALSE and similar constructs all work equally well.) A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the midd

Re: [SQL] Nocount and scope_identity()

2007-02-02 Thread Andrew Sullivan
the value of the sequence. There's no race condition there unless you are sharing a pooled connection (and if transactions mean anything to you, you'd better not be doing). A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work i

Re: [SQL] Nocount and scope_identity()

2007-02-02 Thread Andrew Sullivan
n't gotten there yet, but I'm also wondering if the > SCOPE_IDENTITY() method is going to work or not. I doubt it. What does it do? A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end

Re: [SQL] Duplicate records

2007-02-02 Thread Andrew Sullivan
> > ps: I just think postresql could make this easyly. Don't you think ? Any > function or anything else. What's hard about the self-join? That's how SQL works. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusi

Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values

2007-02-01 Thread Andrew Sullivan
ot; violates the SQL-92 Specification. I don't want to put words in his mouth, but I think you missed the bit where Peter said "the result is not what some people expect". Hint: 'somevalue = NULL' is not a violation of SQL in that you don't get an ERROR. A -- Andr

Re: [SQL] Insert Data and autonumeric field

2007-02-01 Thread Andrew Sullivan
ot a race condition; see the docs. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 9: In versions below

Re: [SQL] Compilation Error AIX

2007-02-01 Thread Andrew Sullivan
ent or the employee or agent > responsible for delivering it to the intended recipient, you are hereby > notified that any use, dissemination or copying of this email transmission is > strictly prohibited by the sender. If you have received this transmission in > error, please de

Re: [SQL] Index Anding

2007-01-31 Thread Andrew Sullivan
ry limitation on every program at compile time. There are some hints about this in the FAQ_AIX. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of

Re: [SQL] Log, Logs and more Logs

2007-01-30 Thread Andrew Sullivan
7;ll see the COMMIT as the thing that caused the error. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris -

Re: [SQL] Log, Logs and more Logs

2007-01-30 Thread Andrew Sullivan
cs/8.2/static/runtime-config-logging.html A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 7: You can help su

  1   2   3   4   >