[GENERAL] Is this a bug or am I doing something wrong?

Hi, I am trying to create a foreign key, followed by creating anindex on the foreign key, using PgAdmin III The command that are generated : ALTER TABLE prototype.orderlines ADD CONSTRAINT fk_orderlines_orders FOREIGN KEY (orderobjectid) REFERENCES prototype.orders (objectid) ON UPDATE RE

Re: [GENERAL] Support for Limit in Update, Insert...

Well, I do have a use case for it. Context: We have data coming in from web requests, which must be fast, so we just insert them in temporary tables without any verification. Then they are periodically processed by a background task, but even that one will process just a chunk at a time to avoid

[GENERAL] Problem using NULLIF in a CASE expression

Hello, I have a problem using NULLIF inside a CASE expression. I explain the goal of my query : I have a table where there is two fields : one varchar field and one INT8 field. When the varchar field is empty I have to display the INT8 field. If the varchar field is not empty I have to display

Re: [GENERAL] Problem using NULLIF in a CASE expression

You're misusing the case construct, and try to compare a text result (from the 'nullif') with a boolean result (from the 'not null'). You probably should use the other form of case: SELECT CASE WHEN NULLIF(btrim(' A string', ' '), ') IS NULL THEN NULL ELSE 6 END AS type_id; I guess you were looki

Re: [GENERAL] Problem using NULLIF in a CASE expression

Try this: SELECT CASE WHEN btrim(' A string', ' ') = '' OR IS NULL THEN NULL ELSE 6 END AS type_id; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Bruno BAGUETTE Sent: Friday, September 09, 2005 4:45 AM To: pgsql-general@

Re: [GENERAL] Is this a bug or am I doing something wrong?

Joost Kraaijeveld wrote: Hi, I am trying to create a foreign key, followed by creating anindex on the foreign key, using PgAdmin III The command that are generated : ALTER TABLE prototype.orderlines ADD CONSTRAINT fk_orderlines_orders FOREIGN KEY (orderobjectid) REFERENCES prototype.orders (ob

[GENERAL] constraints on composite types

This fails on 8.0.3 (syntax error at or near "." at character): CREATE TYPE ct AS ( foo INTEGER, bar INTEGER ); CREATE TABLE t1 ( attr ct, CONSTRAINT uq UNIQUE (attr.foo) ); Should it be possible? From reading http://www.postgresql.org/docs/current/static/rowtypes.html it looks like almo

Re: [GENERAL] Problem using NULLIF in a CASE expression

Bruno BAGUETTE wrote: I wrote another (and quite shorter!) SQL query to resume the problem : SELECT CASE NULLIF(btrim(' A string', ' '), '') WHEN NOT NULL THEN NULL ELSE 6 END AS type_id; ERROR: operator does not exist: text = boolean Why this query does not a

Re: [GENERAL] Is this a bug or am I doing something wrong?

On Fri, Sep 09, 2005 at 09:09:11AM +0200, Joost Kraaijeveld wrote: > ALTER TABLE prototype.orderlines ADD CONSTRAINT fk_orderlines_orders > FOREIGN KEY (orderobjectid) REFERENCES prototype.orders (objectid) >ON UPDATE RESTRICT ON DELETE RESTRICT; > > CREATE INDEX fki_orderlines_orders ON >

Re: [GENERAL] constraints on composite types

Roman Neuhauser wrote: This fails on 8.0.3 (syntax error at or near "." at character): CREATE TYPE ct AS ( foo INTEGER, bar INTEGER ); CREATE TABLE t1 ( attr ct, CONSTRAINT uq UNIQUE (attr.foo) ); Should it be possible? From reading http://www.postgresql.org/docs/current/static/rowtype

Re: [GENERAL] SQL - planet redundant data

To store the detailed records the SQL novice would construct one table pr. file and exclude any constant columns since these are redundant (given in summary table). These detailed tables would then have different column schemas - (mn,tmp,wind) for ~58%, (hr,mn,tmp,wind) for ~40%, (d_o_y,hr,mn,t

[GENERAL] FW: Configuring Postgres to use unix sockets

Title: FW: Configuring Postgres to use unix sockets Hi Everyone I am not too sure how to do this but I'll ask I want two postgres servers to communicate over a serial port. I want to avoid using the TCP/IP network protocol for added security. I heard this can be accomplished through unix

[GENERAL] Route Miles

Does anyone have a source (free if possible) of data to get "Route" miles between 2 US zipcodes. I am currently using the "earthdistance" contrib code but it does a straight line which doesn't work well if you are trying to figure out the cost of driving from 03101 to 11530 Thanks Jim ---

Re: [GENERAL] Is this a bug or am I doing something wrong?

Hi Richard (s), The problem was reproducable untill I restarted my whole machine. Now it works. Still have no clue what this was about, but thanks to you, now I know where to start looking next time. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 /

Re: [GENERAL] FW: Configuring Postgres to use unix sockets

"John Zubac" <[EMAIL PROTECTED]> writes: > Hi Everyone > > I am not too sure how to do this but I'll ask > > I want two postgres servers to communicate over a serial port. I want to > avoid using the TCP/IP network protocol for added security. I heard this > can be accomplished through unix socket

Re: [GENERAL] FW: Configuring Postgres to use unix sockets

Hi John, Unix sockets are a presentation layer interface to TCP/IP. You're not avoiding it by using them. Rick "John Zubac" <[EMAIL PROTECTED]

Re: [GENERAL] Route Miles

We use PCMiler and Rand McNally products, but they certainly aren't free. Both programs have good API's and run on Linux, which is of interest to us. We have the mileage lookup functionality integerated in to trigger code with C functions that do the work. I don't know of any free products. For

Re: [GENERAL] FW: Configuring Postgres to use unix sockets

[EMAIL PROTECTED] writes: > Hi John, > > Unix sockets are a presentation layer interface to TCP/IP. You're not > avoiding it by using them. That's not true. Unix sockets are fundamentally different and do not use TCP/IP at all. They have their own namespace (in the Unix filesystem) and go thro

Re: [GENERAL] Partial commit within the trasaction

On Thu, Sep 08, 2005 at 05:35:40PM +0200, Bohdan Linda wrote: > On Thu, Sep 08, 2005 at 04:35:51PM +0200, Michael Fuhr wrote: > > On Thu, Sep 08, 2005 at 03:39:50PM +0200, Bohdan Linda wrote: > > commit it now." You have to do some extra bookkeeping and you can't > > commit several prepared transa

Re: [GENERAL] Route Miles

On Fri, 2005-09-09 at 10:13 -0400, Terry Lee Tucker wrote: > we would have to use the [non-free] products above because > the customers demand calculations based on a commercial product. Are you saying that customers demand that you use a non-free product, or are you saying that customers demand a

Re: [GENERAL] constraints on composite types

Richard Huxton writes: > You might get somewhere with: > CREATE OR REPLACE FUNCTION testfunc(ct) RETURNS int AS > 'SELECT $1.foo;' > LANGUAGE SQL IMMUTABLE; > CREATE UNIQUE INDEX t1_b_uniq ON t1 (testfunc(b)); The point is that "attr.foo" is an expression, not a column name, and the SQL spec al

Re: [GENERAL] RAID0 and pg_xlog

On Wed, 2005-09-07 at 16:15, Jim C. Nasby wrote: > On Wed, Sep 07, 2005 at 01:02:18PM -0500, Scott Marlowe wrote: > > On Wed, 2005-09-07 at 12:40, Jim C. Nasby wrote: > > > On Wed, Sep 07, 2005 at 12:47:43PM -0700, Qingqing Zhuo wrote: > > > > Xlog will be the only believable data if your system cr

Re: [GENERAL] Route Miles

Matt, This is a logistics package and we move freight for customers for a fee. Quite often that fee is based on the number of miles traveled. The vast majority of our customers demand that we calculate their rate using either PCMiler or Rand McNally. A handful use their own mileage calcuations

Re: [GENERAL] FW: Configuring Postgres to use unix sockets

Douglas McNaught <[EMAIL PROTECTED]> writes: > You could also enforce use of the > private network (in both cases) by use of firewall rules. I think the real point here is that any additional security you gain comes from having the physically separate wire. It's foolish to suppose that using a no

Re: [GENERAL] constraints on composite types

Tom Lane wrote: Richard Huxton writes: You might get somewhere with: CREATE OR REPLACE FUNCTION testfunc(ct) RETURNS int AS 'SELECT $1.foo;' LANGUAGE SQL IMMUTABLE; CREATE UNIQUE INDEX t1_b_uniq ON t1 (testfunc(b)); The point is that "attr.foo" is an expression, not a column name, a

Re: [GENERAL] constraints on composite types

On Fri, Sep 09, 2005 at 10:39:58AM -0400, Tom Lane wrote: > I don't believe you need the function -- this should be enough: > > CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo)); I was expecting that to work too, but it doesn't: ERROR: relation "attr" does not exist -- Michael Fuhr --

Re: [GENERAL] constraints on composite types

# [EMAIL PROTECTED] / 2005-09-09 09:10:30 -0600: > On Fri, Sep 09, 2005 at 10:39:58AM -0400, Tom Lane wrote: > > I don't believe you need the function -- this should be enough: > > > > CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo)); > > I was expecting that to work too, but it doesn't: > > ERR

Re: [GENERAL] constraints on composite types

Richard Huxton writes: > Tom Lane wrote: >> I don't believe you need the function -- this should be enough: >> >> CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo)); > I got: Relation "attr" does not exist (on 8.1 beta) Sorry, make that CREATE UNIQUE INDEX t1_b_uniq ON t1 (((attr).foo));

Re: [GENERAL] constraints on composite types

On Fri, Sep 09, 2005 at 05:20:58PM +0200, Roman Neuhauser wrote: > # [EMAIL PROTECTED] / 2005-09-09 09:10:30 -0600: > > On Fri, Sep 09, 2005 at 10:39:58AM -0400, Tom Lane wrote: > > > I don't believe you need the function -- this should be enough: > > > > > > CREATE UNIQUE INDEX t1_b_uniq ON t1 ((

Re: [GENERAL] per user logging

I believe it is possible for a superuser to do something like"ALTER USER victim SET log_min_messages = whatever", so that the log verbosity is different for different users.regards, tom lane I'll try that and see how it works.

Re: [GENERAL] data Transfer rate priority on Postgres ?

[EMAIL PROTECTED] wrote: Does Postgres have any kind of configuration that determines the speed of data transfer to the clients? No. Is there any kind of connection priority? No, although it is possible to crudely set priorities via OS-level tools like nice(1). -Neil ---

Re: [GENERAL] Route Miles

Terry, How easy is it to get the route miles from one zip to another using with PCMiler or Rand McNally. Is it just a query on some route table ie: select miles from route where zip1 = '1' and zip2 = '2'. Also do either of these cover Canada? Thanks Jim -- Original Message

[GENERAL] to_ascii, or some other form of magic transliteration

I'm working on a problem that I imagine others have had, which basically boils down to having nice unicode display text that users are going to want to search against without typing it correctly e.g. let a search for "sma" match "små". It seems like the best way to do this is to find a magi

Re: [GENERAL] Route Miles

Jim, It is fairly easy to do with both products. The PCMiler API is better, but doesn't support socket connections to the server. It basically works like this: 1. Open a connection to the server. 2. Validate the city name/zip code. 3. Look up miles. 4. Close the connection.

[GENERAL] Problem creating a database in a script; '"template1" is being accessed'

Hi all, I've got a script that creates a database for my program in it's installer script. The problem I am having is that when the installer calls: /usr/bin/createdb tle-bu -U tle-bu I often get the error: createdb: database creation failed: ERROR: source database "template1" is being

[GENERAL] query profiling

I'm aware of log_statement_stats, but as far as profiling goes, it's a bit crude. Is there an easier way to determine, for example, all the queries that take longer than 0.5 seconds? I'd like to do this on a production system, so I'd like to avoid anything that would crush performance or cause any

Re: [GENERAL] Problem creating a database in a script; '"template1" is being accessed'

--- Madison Kelly <[EMAIL PROTECTED]> wrote: > Hi all, > > createdb: database creation failed: ERROR: source > database "template1" > is being accessed by other users I get that error every time that anyone is connected to the database using pgAdmin. We have to get everyone to disconnect b

Re: [GENERAL] Problem creating a database in a script; '"template1"

Dennis Jenkins wrote: --- Madison Kelly <[EMAIL PROTECTED]> wrote: Hi all, createdb: database creation failed: ERROR: source database "template1" is being accessed by other users I get that error every time that anyone is connected to the database using pgAdmin. We have to get everyon

[GENERAL] Can't connect to instance with pgaccess

After several years away from Postgres, I'm back to using it for a small project. I installed 7.4.8 on a FreeBSD 5.4-STABLE machine. Then I init'd the db and added myself as a user, then created a ned db for the prohect. I can connect fine with psql. Then I built pgaccess, and p5-DBD-Pg-13 from t

Re: [GENERAL] Can't connect to instance with pgaccess

stan <[EMAIL PROTECTED]> writes: > After several years away from Postgres, I'm back to using it for a small > project. > > I installed 7.4.8 on a FreeBSD 5.4-STABLE machine. Then I init'd the db and > added myself as a user, then created a ned db for the prohect. I can > connect fine with psql. >

[GENERAL] ecpg -C INFORMIX

Title: ecpg -C INFORMIX I'm in the process of porting INFORMIX ESQL/C code to Postgres.  I've had great success until I've come upon Informix's datetime.h functions. The code compiles, but when it comes time to link, I get the following error: gcc -L/usr/local/postgresql-8.0.3/lib -o tem

[GENERAL] ERROR: bogus varno

8.1beta1, linux If I issue a query from a view with a WHERE condition, w/ EXPLAIN ANALYZE, I am receiving an error: dev#EXPLAIN ANALYZE select sum(bytes) from user_bw where id=33897; ERROR: bogus varno: 205 (user_bw is a VIEW) If I omit the WHERE condition, or the EXPLAIN ANALYZE, or query dir

Re: [GENERAL] ecpg -C INFORMIX

On Fri, Sep 09, 2005 at 02:18:07PM -0700, Van Tress,Michael J - PGPW wrote: > I'm in the process of porting INFORMIX ESQL/C code to Postgres. > I've had great success until I've come upon Informix's datetime.h functions. > The code compiles, but when it comes time to link, I get the following err

Re: [GENERAL] ERROR: bogus varno

On Fri, Sep 09, 2005 at 02:35:07PM -0700, Bricklen Anderson wrote: > 8.1beta1, linux > > If I issue a query from a view with a WHERE condition, w/ EXPLAIN ANALYZE, I > am > receiving an error: > > dev#EXPLAIN ANALYZE select sum(bytes) from user_bw where id=33897; > ERROR: bogus varno: 205 Sear

Re: [GENERAL] ERROR: bogus varno

Bricklen Anderson <[EMAIL PROTECTED]> writes: > 8.1beta1, linux > dev#EXPLAIN ANALYZE select sum(bytes) from user_bw where id=33897; > ERROR: bogus varno: 205 Known bug, fixed a week or two back. regards, tom lane ---(end of broadcast)---

Re: [GENERAL] query profiling

On Fri, Sep 09, 2005 at 12:39:14PM -0700, Jeff Davis wrote: > I'm aware of log_statement_stats, but as far as profiling goes, it's a > bit crude. > > Is there an easier way to determine, for example, all the queries that > take longer than 0.5 seconds? I'd like to do this on a production > system

Re: [GENERAL] ERROR: bogus varno

Tom Lane wrote: > Bricklen Anderson <[EMAIL PROTECTED]> writes: > >>dev#EXPLAIN ANALYZE select sum(bytes) from user_bw where id=33897; >>ERROR: bogus varno: 205 > > > Known bug, fixed a week or two back. > > regards, tom lane > Thanks, I'll have to grab the more recent v

Re: [GENERAL] ERROR: bogus varno

Tom Lane wrote: > Bricklen Anderson <[EMAIL PROTECTED]> writes: > >>8.1beta1, linux > > >>dev#EXPLAIN ANALYZE select sum(bytes) from user_bw where id=33897; >>ERROR: bogus varno: 205 > > > Known bug, fixed a week or two back. > > regards, tom lane > Yep, that fixed it

Re: [GENERAL] ERROR: bogus varno

On Fri, Sep 09, 2005 at 03:50:28PM -0600, Michael Fuhr wrote: > Grab the latest code from CVS or wait until 8.1beta2 comes out. It looks like snapshots are available here: http://www.postgresql.org/ftp/dev/ -- Michael Fuhr ---(end of broadcast)--

Re: [GENERAL] How to write jobs in postgresql

On Thu, Sep 08, 2005 at 10:27:59AM -0700, Chris Travers wrote: > Karsten Hilbert wrote: > > >>3. An integrated way of logging what ran when (rather than either > >>stuffing logging code into each cron job or rummaging thru > >>cron logs) > >> > >> > >Cron can log to syslog. > > > >Kars

Re: [GENERAL] RAID0 and pg_xlog

On Fri, Sep 09, 2005 at 09:43:56AM -0500, Scott Marlowe wrote: > On Wed, 2005-09-07 at 16:15, Jim C. Nasby wrote: > > On Wed, Sep 07, 2005 at 01:02:18PM -0500, Scott Marlowe wrote: > > > On Wed, 2005-09-07 at 12:40, Jim C. Nasby wrote: > > > > On Wed, Sep 07, 2005 at 12:47:43PM -0700, Qingqing Zhuo

Re: [GENERAL] Postgresql Hosting

Various people wrote: ...PostgreSQL...crontab support...pl/pgsql I've found that with if you can get 5 companies/users to share a dedicated server you can be much better off than a hosting plan, and have full control over the services you run. I'm sharing with 5 other small businesses a dedi

Re: [GENERAL] PostgreSQL and XML support

On Tue, 06 Sep 2005 18:48:01 +0400, Andrey Fomichev wrote: > Hello! > > I would like to raise a discussion about XML support in PostgreSQL. I'm a > person who is quite experienced in XML data management and new to > PostgreSQL. So, excuse me if I ask trivial questions about PostgreSQL... > > I t

Re: [GENERAL] RAID0 and pg_xlog

On Fri, 2005-09-09 at 18:16, Jim C. Nasby wrote: > On Fri, Sep 09, 2005 at 09:43:56AM -0500, Scott Marlowe wrote: > > On Wed, 2005-09-07 at 16:15, Jim C. Nasby wrote: > > > On Wed, Sep 07, 2005 at 01:02:18PM -0500, Scott Marlowe wrote: > > > > On Wed, 2005-09-07 at 12:40, Jim C. Nasby wrote: > > >

Re: [GENERAL] Cost based SELECT/UPDATE

On Thu, Sep 08, 2005 at 06:45:33AM +0400, Leonid Safronie wrote: > Hi, ppl > > Is there any way to do SELECTs with different priorities? > > Once a month I need to do some complex reports on table with over 7 > billion rows, which implies several nested SELECTS and grouping (query > runs over 20

Re: [GENERAL] Postgresql Hosting

On Sep 9, 2005, at 7:21 PM, Ron Mayer wrote: I've found that with if you can get 5 companies/users to share a dedicated server you can be much better off than a hosting plan, and have full control over the services you run. I'm sure the numbers work out, but it sounds like being in a Band.

Re: [GENERAL] query profiling

Steve Atkins wrote: > On Fri, Sep 09, 2005 at 12:39:14PM -0700, Jeff Davis wrote: > > >>I'm aware of log_statement_stats, but as far as profiling goes, it's a >>bit crude. >> >>Is there an easier way to determine, for example, all the queries that >>take longer than 0.5 seconds? I'd like to do th

Re: [GENERAL] Support for Limit in Update, Insert...

On Thu, Sep 08, 2005 at 10:49:25PM -0400, Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > This has been discussed before, and rejected. Please see the archives. > > For SELECT, both LIMIT and OFFSET are only well-defined in the presence > of an ORDER BY clause. (One could argue

Re: [GENERAL] SQL - planet redundant data

On Thu, Sep 08, 2005 at 07:58:55PM -0800, Poul Jensen wrote: > This novice must be missing a sneaky way to avoid massive redundancy and > still maintain easy access. I've been suggested to look at inheritance > and foreign keys. Foreign keys I don't see how to use, but I could make What you see

Re: [GENERAL] RAID0 and pg_xlog

On Fri, 2005-09-09 at 18:54, Jim C. Nasby wrote: > On Fri, Sep 09, 2005 at 06:20:21PM -0500, Scott Marlowe wrote: > > > pgpool is a connection pool; it has (almost) nothing to do with > > > replication. It certainly doesn't work to provide any kind of data > > > security on a RAID0 setup. > > > >

Re: [GENERAL] RAID0 and pg_xlog

On Fri, Sep 09, 2005 at 06:20:21PM -0500, Scott Marlowe wrote: > > pgpool is a connection pool; it has (almost) nothing to do with > > replication. It certainly doesn't work to provide any kind of data > > security on a RAID0 setup. > > > > I'm not arguing against anything people have suggested, o

Re: [GENERAL] PostgreSQL and XML support

In general, I have the following questions to PostgreSQL users. - Are there any of you who need to store and query XML data? - Do you already use PostgreSQL to store XML data or you are just thinking about it? - If you store XML data in PostgreSQL, how do you do it? What tool do you use? - Do

Re: [GENERAL] Support for Limit in Update, Insert...

On Fri, Sep 09, 2005 at 06:42:10PM -0500, Jim C. Nasby wrote: > On Thu, Sep 08, 2005 at 10:49:25PM -0400, Tom Lane wrote: > > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > > This has been discussed before, and rejected. Please see the archives. > > > > For SELECT, both LIMIT and OFFSET are only

Re: [GENERAL] Postgresql Hosting

On Sep 9, 2005, at 8:02 PM, Matthew Terenzio wrote: On Sep 9, 2005, at 7:54 PM, Scott Marlowe wrote: On Fri, 2005-09-09 at 18:35, Matthew Terenzio wrote: On Sep 9, 2005, at 7:21 PM, Ron Mayer wrote: I've found that with if you can get 5 companies/users to share a dedicated server you can

Re: [GENERAL] PostgreSQL local replication

> Can anyone point me to a guide that explains how to combine pgpool and > slony1 together, and what to watch out for? > Unfortunately, I haven't seen any complete documents. Look at PgPool's site at: Look in particular at the "master/slave mode". If yo

Re: [GENERAL] RAID0 and pg_xlog

> > On Fri, Sep 09, 2005 at 06:20:21PM -0500, Scott Marlowe wrote: > > > > pgpool is a connection pool; it has (almost) nothing to do with > > > > replication. It certainly doesn't work to provide any kind of data > > > > security on a RAID0 setup. > > > > > > > > I'm not arguing against anything

Re: [GENERAL] back references using regex

One other thing, when I wrote back I actually used 34.31.29.20 (random), not 12.00.00.34 like i showed in the example, which is why i said it didn't work on digits. SELECT substring('34.31.29.20' FROM $$((\w+)\.\2)$$); substring --- (1 row) little did i know writing it with 12.00.