[GENERAL] variadic function, query "in", help with syntax/function

Briefly, what would it take to make the following work? create function getbatch (variadic ids int8[]) returns setof foobar as $$ begin return query select * from foobar where id in (ids); end; $$ language plpgsql; -- Scott Ribe scott_r...@elevated-dev.com http

Re: [GENERAL] variadic function, query "in", help with syntax/function

On Oct 21, 2012, at 11:01 AM, Pavel Stehule wrote: > Hello > > 2012/10/21 Scott Ribe : >> Briefly, what would it take to make the following work? >> >> create function getbatch (variadic ids int8[]) returns setof foobar as $$ >> begin >>retur

[GENERAL] progress of long running operation

so explain on the select statement shows a bit better than 50% reduction in predicted work for that part. And I will go ahead and drop all indexes on the target table.) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-g

Re: [GENERAL] progress of long running operation

t's exactly the kind of thing I was hoping for. I'm actually inserting into an empty table, so "dead" tuples would be dead accurate in my case ;-) Or I could suck it up and do them in batches instead of one giant pass... -- Scott Ribe scott_r...@elevated-dev.com

[GENERAL] bug with indexing of infinite timestamps?

essage::text) <> ''::text) "page_log_pager_num_check" CHECK (btrim(pager_num::text) <> ''::text) Foreign-key constraints: "page_log_request__id_fkey" FOREIGN KEY (request__id) REFERENCES page_requests(id) "page_log_user__id_

Re: [GENERAL] Storing many big files in database- should I do it?

On Apr 29, 2010, at 10:45 AM, Justin Graf wrote: > Many people encode the binary data in Base64 and store as text data > type?? Then never have to deal with escaping bytea data type. Which i > have found can be a pain Damn. Wish I'd thought of that ;-) -- Scott Ribe scott

Re: [GENERAL] PostgreSQL vs. Microsoft SQL server

ections scales extremely well with multiple processors. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL vs. Microsoft SQL server

nge. Well, OK, I'm sure I read it in the release notes at the time, but it made no important different to the app I was working on at the time. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-gen

Re: [GENERAL] simple functions, huge overhead, no cache

, 2010, at 1:07 AM, Craig Ringer wrote: > For such simple expressions, you should use 'SQL' functions. These can > often be inlined to allow the query planner to avoid call overheads > entirely, and are WAY cheaper even if they can't be inlined. They're > less flexib

Re: [GENERAL] Question about SCO openserver and postgres...

On Jul 23, 2010, at 7:42 AM, Edmundo Robles L. wrote: > By the way i send a mail to SCO but until now they don't answer to me. Not surprising, since they've been in bankruptcy for a while now (a couple of years?) and have almost no employees left. -- Scott Ribe scott_r...@ele

Re: [GENERAL] Question about SCO openserver and postgres...

rious PostgreSQL work was funded by profits I made shorting their stock in > 2003. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] MySQL versus Postgres

It's not a requirement, just a reasonable default. On Aug 7, 2010, at 11:09 AM, Martin Gainty wrote: > 3)eliminate the requirement to create a postgres user to execute the server > binaries..I guess i never understood that requirement -- Scott Ribe scott_r...@elevated-d

Re: [GENERAL] Feature Request: bzip2 support in pg_dump, pg_restore

pg_dumpall | bzip2 > mydump.txt.bz2 bunzip2 -kc mydump.txt.bz2 | bin/psql template1 -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: h

Re: [GENERAL] When is newly inserted data visible to another connection?

ding from that. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] "private" installation of postgres

socket directory somewhere within my own directory Or is there a doc on this somewhere? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] "private" installation of postgres

n't be the > first time. Well, unless I hear definitively, I'll consider that something I need to test. Of course there's also an option to specify the default when building the source, I expect *that* should work, and as far as I'm concerned there would be no pro

Re: [GENERAL] "private" installation of postgres

On Sep 7, 2010, at 6:28 PM, Greg Sabino Mullane wrote: > Yep, that should do it. I do this all the time for testing > various programs. Set the socket with pg_ctl -o "-k newsocketdir" Thanks :-) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303

Re: [GENERAL] "private" installation of postgres

e default port number (I forgot to state that in my email), but also moving the socket dir just to be really sure. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make c

Re: [GENERAL] "private" installation of postgres

to Tom also used somehow in shared memory segments. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] where does postgres keep the query result until it is returned?

niques you use, and to a very great extent your specific app and exactly what it does. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

[GENERAL] What was new in 8.4 & 8.3?

being a good time to really update my dbs... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What was new in 8.4 & 8.3?

Actually, I found the feature matrix on the wiki, which serves my purpose pretty well--reminder of major new features that I might not have adopted... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list

Re: [GENERAL] Trade Study on Oracle vs. PostgreSQL

On Sep 24, 2010, at 12:04 PM, Lincoln Yeoh wrote: > No bullshit *THAT*, for me, was one of the most attractive aspects of the community. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-gene

Re: [GENERAL] PostgreSQL 9 Mac OS X one-click install - PL/perl broken

as 64-bit, and will be if there's a 64-bit executable, regardless of which mode the kernel is booted into. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] help modeling a schedule system

or logging/debugging/info. You could have a trigger that updates that "next time to fire" column each time the "last time fired" column is updated. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general ma

Re: [GENERAL] creating "a perfect sequence" column

he document it was originally assigned to, and present instead of the document a message "document deleted", possibly with date & time & user. Whether you actually delete the document or not, is a detail dependent on the use case. -- Scott Ribe [EMAIL PROTECTED] http://www.kille

Re: [GENERAL] Confronting the maximum column limitation

> The > real problem lies with the columns (biological samples) in that it is > rarely the case that we'll have multiple matrices with overlap in columns Should each configuration have its own table, while inheriting from a common base table? -- Scott Ribe [EMAIL P

Re: [GENERAL] Fastest way to restore a database

s? Or is it always faster (or at least as fast)? -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

ng someone else's undocumented code is easier than writing it from scratch is probably not going to be able to learn the code via debugging tools. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) T

Re: [GENERAL] OR or IN ?

with some development data, I inadvertently confirmed that it works and performance is not too bad with >34,000 items in an IN clause ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql

Re: [GENERAL] max time in a table query takes ages

choosing a good plan.) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

#x27;t help with latency. Which is why I went with 3 tiers, so that all communication with Postgres occurs on the server, and all communication between server & client is binary, compressed, and a single request/response per user request regardless of how many tables the data is pulled from. -

Re: [GENERAL] postgresql and Mac OS X

ngside each other. The question then is why the OP doesn't also have make in /usr/bin, or why his path is configured so that it finds /Developer/usr/bin first--*that* is what is non-standard. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-

Re: [GENERAL] Database access over the Internet...

eing a whiny rude jackass. This message sent to the list at your request ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/ma

Re: [GENERAL] Database access over the Internet...

translation and she really meant something closer to "harassment". But even so, her position is outstandingly absurd, to accuse people of harassing her for attempting to answer her question. Perhaps someone should speak to her about the advantages of MySQL? -- Scott Ribe [EMAIL PROTECTED] h

Re: [GENERAL] Database access over the Internet...

orks for the members of this list, so it's really your choice.) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] RAM is good!

I know that my database is not that big (4-5GB), but I am shocked at how well it's performing during a RAID rebuild. For light use, the difference is hardly noticeable. Nothing like avoiding hitting the disk ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567

[GENERAL] ERROR: compressed data is corrupt

I'm seeing that now when trying to run an update. Is there anything I should investigate? This is a test database, and I can just wipe it and restore from dump. BTW, this is not related to my earlier message about rebuilding a RAID ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbyte

[GENERAL] ERROR: compressed data is corrupt, ignore earlier message

Oops. I switched over to a test machine (laptop) that hadn't been used in a while. Didn't think to check pg version before loading up test db. I'm running 8.3.1. S... Never mind. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -- Sent vi

[GENERAL] pgbouncer connections used

miss something easy in the docs? -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.45.1 on mac - Plist problems

g to the plist); would probably not have helped you in this case. -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpr

Re: [GENERAL] Cheapest way to poll for notifications?

PQnotifies( mDbConn ); if( notify ) { free( notify ); needsnotify = true; } else break; } } if( needsnotify ) // go off & query the db & handle updates here } Of course you could also use select to wait on the socket.

Re: [GENERAL] Justifying a PG over MySQL approach to a project

nk ;-) Seriously, founder & current owner engaged in political intrigue over licensing? Try running that by a risk-averse manager! -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.or

Re: [GENERAL] how do I disable automatic start on mac os x?

> Ah, there it is... but now what do I do with it to disable the auto-start? sudo launchctl unload -w blahblahblah.plist The -w option causes it to not only unload the item, but also write a key into it which will stop it from loading at launch. -- Scott Ribe scott_r...@killerbytes.com h

[GENERAL] SQL question re aggregates & joins

ot; from "PtStaffAccess" t1, "Person" t2 where t1."Pt_Id" = t2.id group by t2.id; Come on, I'm grouping on the primary key and it thinks that there might be multiple values for the other columns? -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SQL question re aggregates & joins

irth" from "PtStaffAccess" t1, "Person" t2 where t1."Pt_Id" = t2.id group by t2.id, t2."DateOfBirth"; Given that t2.id is the primary key, grouping by any other column of t2 is really redundant. I know *what* SQL won't allow me to do, I'm interest

Re: [GENERAL] error migrating database from 8.4 to 8.3

> Export/import is the only option. Or upgrade PostgreSQL to 8.4 on the downgraded server. -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscript

Re: [GENERAL] What's the best way to deal with the pk_seq sequence value after a restore (bulk loading)?

> OK, what probably happened was that the data was loaded okay and then > you got these errors from the commands that attempted to create unique > indexes. Isn't it also possible that she was not restoring into an empty database? -- Scott Ribe scott_r...@killer

[GENERAL] sql query bug???

x query involving 3-way union of 5-way joins--intended for end-user querying. Of note, this query works (and performance is good enough as well): select "ICD9", count(*) from (select distinct "Person_Id", "ICD9" from "PatientDiagnoses") as t0 group by "I

Re: [GENERAL] sql query bug???

. But it took someone pointing it out to me to get me to notice that irregularity. Fatigue... One more day of super-crunch and then I get to take a break... Thanks. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailin

[GENERAL] Rails & pg setup question

; best practice, which is, ahem, different than with MySQL.) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] The future of Solaris?

> Could use a new sure thing now that there's > no more money for me to make shorting SCO stock. That really was easy money, wasn't it? ;-) -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing lis

Re: [GENERAL] [PGSQL 8.3.5] Use of a partial indexes

er comparisons is to reboot between trials in order to compare queries with cold caches, or use the latter of multiple runs in order to compare queries with hot caches. -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing lis

Re: [GENERAL] [PGSQL 8.3.5] Use of a partial indexes

> voila! cache dumped. What about read caches in the disk devices themselves? -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: h

Re: [GENERAL] Pet Peeves?

n named foobar? Perhaps "drop function foobar(*)" could drop all functions named foobar? -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] clearing the buffer cache

he? That will also have a huge effect on performance, and so you'll need to clear it as well. -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Is there a meaningful benchmark?

go to the table pages to check which rows are actually visible in the current transaction.) See? Overall, PG performs very well and is certainly comparable to the "big boys" on the same hardware. But the strengths & weaknesses can be *very* specific to particular qu

Re: [GENERAL] What are the benefits of using a clustered index?

> Just think one index tuple points to more than one heap row. We would > still need to check visibility on the rows returned to ensure MVCC. So you wind up with the heap rows stored in their own tree-like structure outside the index? OK. -- Scott Ribe scott_r...@killerbytes.co

Re: [GENERAL] What are the benefits of using a clustered index?

> Hopefully we regard it as a missing feature rather than as a separate > definition. We could cluster the index, we just don't, yet. Wouldn't this require keeping around multiple versions of index pages for MVCC? Which would create performance degradations elsewhere? -- Sc

Re: [GENERAL] Best practices: Handling Daylight-saving time

efault behavior--I don't know whether there are settings to change it. All I know is that I regularly work with a database located in a different time zone, and displayed times are adjusted to my local time. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.co

Re: [GENERAL] Problems building postgresql 8.0.1 on OS X 10.3.8

> Hm, it works for me and for other people on OS X. Look into the > config.log file for more details. Yeah, I built it on 3 different machines running 10.3.8 just yesterday. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(

[GENERAL] Can't pg_dumpall, claims database exists twice

ster running, and it seems OK from client apps, both my own app and psql.) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan i

Re: [GENERAL] Can't pg_dumpall, claims database exists twice

ght be a likely candidate. I only have 8.0.1 on this machine. I removed the prior version before installing. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [GENERAL] Can't pg_dumpall, claims database exists twice

res} (4 rows) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Can't pg_dumpall, claims database exists twice

1 | 482 | 482 1262 |538 |0 | 1 | template1 | 1 | 482 | 482 (4 rows) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 6: Have you sea

Re: [GENERAL] Can't pg_dumpall, claims database exists twice

t cannot possibly be used anywhere since it lacks associated data files? Could you give me an outline of how to clean this up? Should I be looking at other catalog info to see if there are components of the db that are also duplicated? Or should I just delete the bogus pg_database entry, then dump and

Re: [GENERAL] Can't pg_dumpall, claims database exists twice

rectories > under $PGDATA/base for both of those OIDs? So I guess I should also ask if there's anything I could do to give you more information about how this might have happened? Would it be helpful to brute-force rename the bogus entry and submit a dump of the schema? -- Scott Ri

Re: [GENERAL] Postgresql 8.0.2 and Tiger?

to get it working ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [GENERAL] artificial keys or not?

icial keys are evil for some inscrutable reason, but hard experience has taught me to never, ever, under any circumstance, make a primary key out of data that comes from humans, and to be very suspicious of using data that will be visible to humans. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbyt

Re: [GENERAL] Limits of SQL

lationships. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [GENERAL] Asychronous database replication

und the idea, the approach I think I'd look into: - clients don't access the database directly - there's a middleware layer and clients make higher-level RPC-type calls whose semantics more closely match the client functionality - then those calls can be logged and repl

Re: [GENERAL] Replication

> How about a third choice: you can also use a proven, reliable and tested > replication solution that is included in the core system because the > core system basiclly provides it anyway. Sure, but that one is spelled "Sybase", not "MySQL" ;-) -- Scot

Re: [GENERAL] Asychronous database replication

ome-grown (predates Slony) replication system to maintain an offsite near-real-time backup server. The fact that most of my data is insert only greatly simplifies things. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of

Re: [GENERAL] Replication

lear whether you're saying "MySQL claims... and it would be good for Postgres to have its own solution..." or "MySQL has an actual working full-blown good solution for... that Postgres would do well to emulate". -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/

Re: [GENERAL] Sun supporting PostgreSQL

firms supporting PG, we have a pat answer. (Assuming of course that Sun sticks with Postgres and their attention doesn't wander somewhere else. That is one problem with big companies--priorities sometimes shift.) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665

Re: [GENERAL] PostgreSQL slammed by PHP creator

w set in quickly ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [GENERAL] postgresql rising

> Berkus doesn't count??! He's got long hair! What more do you want?! banjos playing in background... -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 9: In versions b

[GENERAL] Generating synthetic keys on copy

f individual INSERT statements. I'm just asking to make sure my understanding is correct. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Generating synthetic keys on copy

n't read the docs for COPY in a few years, and didn't realize I could specify a column list. RTFM, duh. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 9: In versions below

Re: [GENERAL] more anti-postgresql FUD

> Used to run a time reporting system on a 3b2 > 400, 4MB Ram, WE32100 10MHz processor, 1.1 MIPS. You had zeroes? We had to use the letter "O"! -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(

Re: [GENERAL] A query planner that learns

estion to answer because there are 0 good answers. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] fetching unique pins in a high-transaction

> That second query may not be the best, as it will probably seqscan and > grab all the pins before only returning the first one ... A partial index where USED_BY is null would eliminate the need for the seqscan on the table... -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com

Re: [GENERAL] Table and Field namestyle best practices?

ever, past present or future. And I don't even think it's much more typing on the whole, because of how many _ characters I dropped. I now just use _ as "namespace separation", nothing else. Although the C++ side of me is tempted to use "::", just because I can ;-)

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

> Bottom line: check digits are in SSNs Uhm, no they're not. And this is of course one of the huge problems with SSNs. (Although not quite as bad as the fact that they're not strictly unique. Yes, really, duplicates have been issued in the past.) -- Scott Ribe [EMAIL PR

Re: [GENERAL] OID Perfomance - Object-Relational databases

billion or > so rows in the table). There's a third way: int8 serial, lower 48 bits for row id, upper 16 bits appended with your own table id. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

cribed, but to base a database design on that assumption is to create a brittle design--as you described. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 9: In versions below 8.0

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

he (formerly) two companies' accounts thus absolutely requiring account number changes. This is exactly the kind of thing I'm talking about, and why I think account # + date/time would be a lousy primary key. It's fine to treat it as a key, but certainly not the primary. -- S

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

> primary key (first_name,last_name,address_id) > > One will enable you to have a distinct Joshua Drake, one in portland > oregon and one in portland maine. What happens when you move? Do we treat you as a different person? Or do we pretend that you've always lived in the same

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

I shipped last week's orders. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

UUIDs everywhere if it weren't such a pain in the neck to type them into queries. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

ta to make > them merge. Also, there are plenty of circumstances besides the sale/merge one I mentioned which would require account # changes but not require changes to the synthetic keys. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

ion of primary key that something that not only can, but will, change for many records should be part of the primary key. "Unique" and "primary" are *not* synonyms. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

ve table >> associated with the user.id. > > But what about historical data that referenced the address? If you move > today, I still want to know where I shipped last week's orders. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice --

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

numbers, credit card used, order history--all of these could help discriminate between users and not one of them has any place in the users' primary key. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)---

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

ppy approximation of a natural one (or even an accurate-looking one handed to me by a clean-shaven guy in an expensive suit), I'll take the synthetic one because my experience has been that over the long term it will cause fewer problems by far. -- Scott Ribe [EMAIL PROTECTED] http://www.kill

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

But firstname/lastname is *NOT* a primary key. Merely calling it one in the db schema does not make it so. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[GENERAL] NULLs ;-)

t, is there any reason not to request a new operator? (Perhaps "a nktbe b"? The C guy in me prefers "a != b" but that would be *FAR* too prone to confusion with <>.) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---

Re: [GENERAL] NULLs ;-)

long time ago and then forget it when I needed it. I didn't ask for that ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] NULLs ;-)

> how about > > SELECT * > FROM > YOURTABLE > where > ( a = b ) IN UNKNOWN; Well, actually, it would be: a = b or (a = b) is unknown But that certainly would more concise. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/

Re: [GENERAL] NULLs ;-)

> But > > regression=# select (null = null) is unknown; > ?column? > -- > t > (1 row) > > which I think is not what you wanted. Right. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -

<    1   2   3   4   >