Re: [GENERAL] Searching for Duplicates and Hosed the System

Bill Thoen <[EMAIL PROTECTED]> writes: > I ran VACUUM ANALYZE just before I launched this and there were no other > postgress jobs running. I'm the only user as well. I also ran EXPLAIN > prior to the run and got this: > Nested Loop (cost=11.71..28800.34 rows=7219 width=584) >-> Seq Scan o

Re: [GENERAL] Automated testing of functions

2007/8/18, Brian Donovan <[EMAIL PROTECTED]>: > I'm using postgresql in a Ruby on Rails app and I've moved some of the > application logic from Ruby (ActiveRecord callbacks) to postgresql > (triggers + functions) for performance reasons. The problem is that > now those parts of the app remain untes

Re: [GENERAL] PGError: input out of range

dustov <[EMAIL PROTECTED]> writes: > PGError: ERROR: input is out of range I see no such error string in the current sources ... what Postgres version are you using? If you set "\set VERBOSITY verbose" in psql before trying the query, you should get some extra info about where the message is com

Re: [GENERAL] Join query help

On Aug 20, 2007, at 20:33 , novice wrote: Many many thanks for all the advice =) Glad to help. Good luck! Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.p

Re: [SQL] [GENERAL] Join query help

On Aug 20, 2007, at 20:27 , Michael Glaesemann wrote: Note: record_id is in integer, yet you're quoting the value ('1'). This causes the server to cast the text value to an integer. Here it's not going to cause much of a problem, just a couple CPU cycles. In table definitions (and possibly

Re: [GENERAL] Join query help

Many many thanks for all the advice =) On 21/08/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: > > On Aug 20, 2007, at 19:52 , novice wrote: > > > Try 2: Here are my DDLs & DMLs > > Thanks for the data. It looks like the data you provided in the first > set was a little different, and the quer

Re: [GENERAL] Join query help

On Aug 20, 2007, at 19:52 , novice wrote: Try 2: Here are my DDLs & DMLs Thanks for the data. It looks like the data you provided in the first set was a little different, and the queries I supplied in my previous message give you the results you want. CREATE TABLE record ( record_id

Re: [GENERAL] Join query help

Try 2: Here are my DDLs & DMLs -- Start CREATE TABLE record ( record_id integer PRIMARY KEY, record_date timestamp with time zone NOT NULL ); INSERT INTO record(record_id, record_date) VALUES ('1', '2007-07-23 11:30:37'); INSERT INTO record(record_id, record_date) VALUES ('2', '2007-07-27 1

Re: [GENERAL] Using oid as pkey

Rainer Bauer wrote: "D. Dante Lorenso" wrote: Using a brain-dead sample table that looks like this: CREATE table some_table ( col0 SERIAL, col1 VARCHAR, col2 VARCHAR ); I want to do something like this: INSERT INTO some_

Re: [GENERAL] Seeking datacenter PITR backup procedures [RESENDING]

> I'm guessing you're in a hurry or in a pinch that you need to repost > after one day on a weekend. I was waiting to let someone more > knowledgeable answer, but I've had some experience with this, so > I'll answer to the best of my ability. I apologize. I wasn't sure if my first email ended

Re: [GENERAL] Using oid as pkey

"D. Dante Lorenso" wrote: >Using a brain-dead sample table that looks like this: > > CREATE table some_table ( > col0 SERIAL, > col1 VARCHAR, > col2 VARCHAR > ); > >I want to do something like this: > > INSERT INTO some_table (col1, col2)

Re: [GENERAL] Using oid as pkey

D. Dante Lorenso wrote: > I want the value of col0 returned to the application and I don't want to > know the name of the sequence involved in the SERIAL column. I just want > the value inserted into the column by using just it's column name. Use pg_get_serial_sequence(). Don't use lastval()

Re: [GENERAL] Using oid as pkey

On Aug 20, 2007, at 17:51 , D. Dante Lorenso wrote: Michael Glaesemann wrote: On Aug 20, 2007, at 16:58 , Ed L. wrote: You'd have to specify your table WITH OIDS anyway as they're no longer used by default for table rows, so there's really nothing to be gained by using oids. How exactly

Re: [GENERAL] Searching for Duplicates and Hosed the System

Something is really screwy here. I tried what looked like a simpler task than I tried when I started this message thread. The only unusual issue here is that the table compliance_2006 is rather big (over 18 million records). The table ers_regions is pretty small (about 3100 records) and all the

Re: [GENERAL] Using oid as pkey

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 D. Dante Lorenso wrote: > Michael Glaesemann wrote: >> >> On Aug 20, 2007, at 16:58 , Ed L. wrote: >> You'd have to specify your table WITH OIDS anyway as they're no >> longer used by default for table rows, so there's really nothing to be >> gained b

Re: [GENERAL] Using oid as pkey

Michael Glaesemann wrote: On Aug 20, 2007, at 16:58 , Ed L. wrote: You'd have to specify your table WITH OIDS anyway as they're no longer used by default for table rows, so there's really nothing to be gained by using oids. How exactly can you get rid of OIDs when using a language like PHP?

Re: [GENERAL] Using oid as pkey

On 8/20/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: > > On Aug 20, 2007, at 16:58 , Ed L. wrote: > > > What are the concerns with using oid as the column for a primary > > key declaration for use in trigger-based replication? > > Just don't. oids are intended to be used by the database server

Re: [GENERAL] Using oid as pkey

On Aug 20, 2007, at 16:58 , Ed L. wrote: What are the concerns with using oid as the column for a primary key declaration for use in trigger-based replication? Just don't. oids are intended to be used by the database server itself rather than as part of the user-defined data. If you're lo

[GENERAL] Using oid as pkey

What are the concerns with using oid as the column for a primary key declaration for use in trigger-based replication? TIA, Ed ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] PGError: input out of range

On Sat, Aug 18, 2007 at 03:21:02PM -0700, dustov wrote: > > My database just had this new error, and I have no idea why (because I > haven't intentionally made any changes to this table). Does anyone have an > idea which input is out of range-- or what the problem might be? The only thing in yo

[GENERAL] Auto-partitioning?

As I understand partitioning, you can automatic "locate the partition into which a row should be added" by adding rules such as (from the documentation): -- CREATE RULE measurement_insert_y2004m02 AS ON INSERT TO measurement WHERE ( lo

Re: [GENERAL] Enterprise Wide Deployment

Scott Marlowe wrote: > On 8/14/07, john_sm <[EMAIL PROTECTED]> wrote: >> Hey guys, for an enterprise wide deployment, what will you suggest and why >> among - Red Hat Linux, Suse Linux and Ubuntu Linux, also, do you think, we >> can negotiate the support pricing down? > > It's more about your skil

Re: [GENERAL] Stripping apostrophes from data

[Please don't top post as it makes the discussion more difficult to follow.] On Aug 20, 2007, at 13:21 , Andrew Edson wrote: The dollar quoting appears to have fixed it; thank you. I apologize for my folly in sending out the original message. I think this might be giving you a false sense

[GENERAL] Solution to Bus error(coredump) from postgres binary

Hi, I'm in the process of building Postgres 7.4.2 on a HP-UX PA Machine. I'm in a stage where postgres builds properly, but when initdb is run, it is resulting in a bus error/coredump. The core seems to come from postgres binary. I tried to look further into this. The point of failure is the trigg

Re: [GENERAL] Help with this query (some join stuff I think)

? Try some thing like ths: SELECT companies.id, companies.name, companies.nickname, (Select count(*) from videos where companies.id=videos.company_id and videos.status= 'complete') num_videos FROM companies ORDER BY num_videos DESC Hope this help Carlos E. Ortiz ___

[GENERAL] waiting in pg_stats_activity

Hi, little question: when is WAITING in PG_STATS_ACTIVITYset to TRUE ? When this connection is waiting on a lock , or are there any other reasons, waiting on another resource ? TIA ruediger ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postma

Re: [GENERAL] Partitioning

Julio Cesar Sánchez González wrote: Hi guys, It's natural what master table in the partitioning table contain data (http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html) ? or to be empty. Hi, the master table should be empty when doing partitioning. Regards, roppert Thanks

[GENERAL] Automated testing of functions

I'm using postgresql in a Ruby on Rails app and I've moved some of the application logic from Ruby (ActiveRecord callbacks) to postgresql (triggers + functions) for performance reasons. The problem is that now those parts of the app remain untested. Does anyone have a suggestion for doing a

Re: [GENERAL] PostgreSQL clustering (shared disk)

On Fri, Aug 17, 2007 at 09:59:26AM -0400, Tom Lane wrote: > "Mikko Partio" <[EMAIL PROTECTED]> writes: > > This was my original intention. I'm still quite hesitant to trust the > > fencing devices ability to quarantee that only one postmaster at a time is > > running, because of the disastrous poss

[GENERAL] PGError: input out of range

My database just had this new error, and I have no idea why (because I haven't intentionally made any changes to this table). Does anyone have an idea which input is out of range-- or what the problem might be? Thanks, Dustin PGError: ERROR: input is out of range : SELECT DISTINCT locations.*

[GENERAL] SUBSTRING performance for large BYTEA

I'm working on reading large BYTEA fields from PostgreSQL 8.1. (For legacy reasons, it's unattractive to move them to large objects.) I'm using JDBC, and as various people have pointed out , the standard stream-style access method ru

[GENERAL] Join query help

Hi, We have the following three tables. safety=> SELECT record_id, record_date FROM record; record_id | record_date ---+ 1 | 2007-07-23 11:30:37+10 2 | 2007-07-27 11:30:14+10 3 | 2007-07-17 13:15:03+10 (3 rows) safety=> SELECT obser

Re: [GENERAL] Stripping apostrophes from data

The dollar quoting appears to have fixed it; thank you. I apologize for my folly in sending out the original message. Michael Glaesemann <[EMAIL PROTECTED]> wrote: On Aug 20, 2007, at 11:19 , Andrew Edson wrote: > Is there some program or procedure for stripping apostrophes (') > from data

Re: [GENERAL] Installation problems

ter?" > > > > Any Idea is appreciated. > > > > Thanks in advance. > > > > Luca. > > -- > > Email.it, the professional e-mail, gratis per te: http://www.email.it/f > > > > Sponsor: > > Viaggi, voli, soggiorni...cattura

Re: [GENERAL] Stripping apostrophes from data

On 8/20/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > > > Alternately, is there some way of inserting or selecting data from the > db which doesn't require the use of apostrophes for non-numeric fields? > > Uhmm just prepare all your statements and this shouldn't be an issue. > .. which is a

Re: [GENERAL] Stripping apostrophes from data

On Aug 20, 2007, at 11:19 , Andrew Edson wrote: Is there some program or procedure for stripping apostrophes (') from data in the db? Most of our data has been shuffled over to Postgres from an older system, and I'm occasionally running into data entered in the old system that has apostro

Re: [GENERAL] Stripping apostrophes from data

Andrew Edson wrote: Is there some program or procedure for stripping apostrophes (') from data in the db? Most of our data has been shuffled over to Postgres from an older system, and I'm occasionally running into data entered in the old system that has apostrophes in it. (Most recent example

Re: [GENERAL] Table description

Thanks a lot, guys. \d is exactly what I needed! On 8/20/07, Robert Gravsjö <[EMAIL PROTECTED]> wrote: > > Livia Santos wrote: > > Hi. > > > > Is there any command that describe a table, such as desc table_name as > > in Oracle? > > Not sure how desc table_name works in Oracle, but from psql you

Re: [GENERAL] Stripping apostrophes from data

On Mon, Aug 20, 2007 at 09:19:14AM -0700, Andrew Edson wrote: > Is there some program or procedure for stripping apostrophes (') from > data in the db? Most of our data has been shuffled over to Postgres > from an older system, and I'm occasionally running into data entered > in the old system tha

Re: [GENERAL] Stripping apostrophes from data

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Andrew Edson wrote: > Is there some program or procedure for stripping apostrophes (') from data in > the db? Most of our data has been shuffled over to Postgres from an older > system, and I'm occasionally running into data entered in the old syste

[GENERAL] Stripping apostrophes from data

Is there some program or procedure for stripping apostrophes (') from data in the db? Most of our data has been shuffled over to Postgres from an older system, and I'm occasionally running into data entered in the old system that has apostrophes in it. (Most recent example: A name field with t

[GENERAL] tcp_keepalive values not taking

Postgresql 8.2.4 FreeBSD 6.2 According to http://www.postgresql.org/docs/current/static/runtime-config-connection.html the tcp_keepaliave variables take the OS setting if 0, or use whatever value one puts in postgresql.conf. I tried the following values in postgresql.conf: tcp_keepalives_idl

Re: [GENERAL] Table description

Livia Santos wrote: Hi. Is there any command that describe a table, such as desc table_name as in Oracle? Not sure how desc table_name works in Oracle, but from psql you can use: \dt table_name Issue \? in psql for more information. Regards, roppert Thanks in advance. -- Lívia Silva San

Re: [GENERAL] Table description

On 8/20/07, Livia Santos <[EMAIL PROTECTED]> wrote: > Hi. > > Is there any command that describe a table, such as desc table_name as in > Oracle? Yes and no. The psql client has a series of \ commands that can describe pretty much anything in the db in a pretty print nature and a minimum of typin

Re: [GENERAL] Table description

--- Livia Santos <[EMAIL PROTECTED]> wrote: > Is there any command that describe a table, such as desc table_name as in > Oracle? from the psql user interface, you can use the following command: \dt to list all table in the currently set schema \d [table_name] the see the table attributes \d+ [

Re: [GENERAL] ERROR: relation "xxx" already exists but where????

On 8/20/07, Joost Kraaijeveld <[EMAIL PROTECTED]> wrote: > I managed to drop a table without apparently droppig it's primary key > After recreating the table I try to recreate the primary key. > > If I run the following: > > ALTER TABLE case_histories > ADD CONSTRAINT case_histories_pkey PRIMARY

[GENERAL] ERROR: relation "xxx" already exists but where????

I managed to drop a table without apparently droppig it's primary key After recreating the table I try to recreate the primary key. If I run the following: ALTER TABLE case_histories ADD CONSTRAINT case_histories_pkey PRIMARY KEY(case_history_id); Postgresql responds with: NOTICE: ALTER TABL

[GENERAL] Table description

Hi. Is there any command that describe a table, such as desc table_name as in Oracle? Thanks in advance. -- Lívia Silva Santos

Re: [GENERAL] POSTGRE CRASH AND CURRVAL PROBLEM HELP!

On 8/19/07, David Azevedo <[EMAIL PROTECTED]> wrote: > Please, i need help desperately. > > Im running postgresql 8.1.9 on windows 2003 server standard edition service > pack 2. intel pentium 4 3.2 - 1 gb ram > I have 5 databases in this server and they all have action all day ( > inserts, selects

Re: [GENERAL] Help creating a function

am Wed, dem 15.08.2007, um 17:29:17 -0400 mailte Madison Kelly folgendes: > What I would like to do is create a function that would do the same > thing so I could read out the IP addresses as standard dotted-decimal > format. Could anyone help me with this? I am quite the n00b when it > comes

Re: [GENERAL] Installation problems

ownload for Windows XP Mediacenter?" Any Idea is appreciated. Thanks in advance. Luca. -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Viaggi, voli, soggiorni...cattura l'offerta e parti con Mondolastminute Clicca qui: http://adv.emai

[GENERAL] Installation problems

it/f Sponsor: Viaggi, voli, soggiorni...cattura l'offerta e parti con Mondolastminute Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6850&d=20070820 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] entry log

Le dimanche 19 août 2007, Robin Helgelin a écrit : > My question, is this interesting information enough to save on the > table itself? If so, I guess this could easily be solved with a > trigger, however, should one instead create a log table and log > changes, etc? > > Hints and tips are apprecia

Re: [GENERAL] pg_class.relfilenode for large tables

On Mon, Aug 20, 2007 at 08:57:14AM +0200, Luca Ferrari wrote: > Hi all, > when a table becomes large a new file on disk is created. Such file has the > name compound by the pg_class.relfilenode attribute and an incremental index. > However it seems to me this does not appears in the pg_class tabl

[GENERAL] pg_class.relfilenode for large tables

Hi all, when a table becomes large a new file on disk is created. Such file has the name compound by the pg_class.relfilenode attribute and an incremental index. However it seems to me this does not appears in the pg_class table. Is there any place where this extra file appears? As an example:

Re: [GENERAL] Help with this query (some join stuff I think)

Pat Maddox wrote: > I've got a bunch of companies that are associated with several videos. > The videos have different statuses. I want to select all the > companies in the database, and order them by videos that have a > complete status. > > Here's what I have so far > > SELECT > companies.i