Re: [SQL] FW: view derived from view doesn't use indexes

@postgresql.org Subject: Re: [SQL] FW: view derived from view doesn't use indexes Russell Keane writes: > Using PG 9.0 and given the following definitions: > CREATE OR REPLACE FUNCTION status_to_flag(status character) RETURNS > integer AS $BODY$ ... > $BODY$ > LANGUAGE plpgsql >

Re: [SQL] FW: view derived from view doesn't use indexes

Russell Keane writes: > Using PG 9.0 and given the following definitions: > CREATE OR REPLACE FUNCTION status_to_flag(status character) > RETURNS integer AS > $BODY$ > ... > $BODY$ > LANGUAGE plpgsql > CREATE OR REPLACE VIEW test_view1 AS > SELECT status_to_flag(test_table.status) AS flag, > tes

[SQL] FW: view derived from view doesn't use indexes

Using PG 9.0 and given the following definitions: CREATE SEQUENCE test_code_id; CREATE TABLE test_table ( status character(1) NOT NULL DEFAULT 'C'::bpchar, code_id integer NOT NULL DEFAULT nextval('test_code_id') ); CREATE INDEX test1 ON test_table USING btree (status, code_id); CREATE OR REPLA

Re: [SQL] FW: Hi

On 11/09/2011 5:22 AM, Mandana Mokhtary wrote: *From:* Mandana Mokhtary *Sent:* 10 September 2011 23:21 *To:* pgsql-sql-ow...@postgresql.org *Subject:* Hi Hi All I tried to import shapfile into postgres using this comand:

Re: [SQL] FW: Hi

On Saturday, September 10, 2011 2:22:32 pm Mandana Mokhtary wrote: > > From: Mandana Mokhtary > Sent: 10 September 2011 23:21 > To: pgsql-sql-ow...@postgresql.org > Subject: Hi > > Hi All > I tried to import shapfile into postgres using this comand: > > pgsql -c -

[SQL] FW: Hi

From: Mandana Mokhtary Sent: 10 September 2011 22:31 To: pgsql-sql-ow...@postgresql.org Subject: Hi Hi I am running Get Capabilities application under Apache Tomcat which some how connected to postgre in order to retreie data. I am facing with this error LOG: u

[SQL] FW: Hi

From: Mandana Mokhtary Sent: 10 September 2011 23:21 To: pgsql-sql-ow...@postgresql.org Subject: Hi Hi All I tried to import shapfile into postgres using this comand: pgsql -c -s 3006 -W LATIN1 c:/.. |psql _U postgres (database name) I could import some but

Re: [SQL] FW: simple? query

Jan Verheyden wrote: > Thanks for the suggestion, the only problem is, if primary key is used then > each row should be unique what is not true; since I have a column > 'registered' what only can be 1 or 0... > [...] I have no idea what you are trying to say. Tim -- Sent via pgsql-sql mail

[SQL] FW: trigger problem

Hi, I keep looking for myself and tried as well the following code: 'perform dblink_connect('myconnect','dbname=postgres password=uzleuven'); create view remote as select * from dblink('myconnect','sel

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

writes: > the data is insert once, read many so we should be fine on that side. It's not only the insert side where you pay for so many partial indexes. On every query of the table, the planner is going to examine every one of those indexes and determine whether the index is potentially usable. W

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

09, 2009 11:29 AM To: Jacob Bresciani Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] FW: Query length limitation in postgres server > 8.2.9 writes: > leaf_category_1 Ah. So you are wishing it would use this index: "search_site1_2009_03_13_leaf_category_1" btree (

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

writes: > leaf_category_1 Ah. So you are wishing it would use this index: "search_site1_2009_03_13_leaf_category_1" btree (leaf_category_1, site_id) WHERE leaf_category_1 IS NOT NULL If I were you I'd drop the WHERE clause, which is eliminating no index entries whatsoever (since the c

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

Being just the server admin I'll forward this thought on to the dev's for consideration. -Original Message- From: Hartman, Matthew [mailto:matthew.hart...@krcc.on.ca] Sent: Thursday, July 09, 2009 10:58 AM To: Jacob Bresciani; pgsql-sql@postgresql.org Subject: RE: [SQL] FW: Qu

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

Good think I obfuscated it in one place eh :) leaf_category_1 -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, July 09, 2009 11:14 AM To: Jacob Bresciani Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] FW: Query length limitation in postgres server

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

writes: > \d search_site1_2009_03_13 And "MyColumn1" is really which column? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

tree (leaf_category_1, site_id) WHERE leaf_category_1 IS NOT NULL "search_site1_2009_03_13_seller_user_id" btree (seller_user_id, site_id) WHERE seller_user_id IS NOT NULL "search_site1_2009_03_13_upc_code" btree (upc_code, site_id) WHERE upc_code IS NOT NULL -Origi

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

writes: > I've simplified the query to make it easier to look at. We need to see the table/index declarations. The query by itself is just about useless. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscriptio

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of ja...@aers.ca > Sent: Thursday, July 09, 2009 1:53 PM > > I've simplified the query to make it easier to look at. > > This one doesn't use the index's and therefore takes about 11713ms to > return. Hav

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

ersion has 101 items. -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of ja...@aers.ca Sent: Tuesday, July 07, 2009 3:34 PM To: pgsql-sql@postgresql.org Subject: [SQL] FW: Query length limitation in postgres server > 8.2.9 One of our pro

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

Greg Stark writes: > On Tue, Jul 7, 2009 at 11:33 PM, wrote: >> After some investigation it seems that the new server is refusing to use the >> index's but if I >> limit the number of arguments in the latter part of the statement to 100 >> then it works as >> expected in the expected amount of

Re: [SQL] FW: Query length limitation in postgres server > 8.2.9

On Tue, Jul 7, 2009 at 11:33 PM, wrote: > After some investigation it seems that the new server is refusing to use the > index's but if I > limit the number of arguments in the latter part of the statement to 100 then > it works as > expected in the expected amount of time using the indexs. Ugh

[SQL] FW: Query length limitation in postgres server > 8.2.9

One of our programmers has come to me with a problem. On 3 new Centos 5.3 servers running Postgres 8.2.13 query's are taking 3500ms-5000ms to complete, where the same query on an older server (same hardware, older software revisions) the same query on the same data comes back in < 50 ms. After s

Re: [SQL] FW: Help- post gress sql error

"Kota, Prasoona" <[EMAIL PROTECTED]> writes: > I am receiving the following error when trying to execute sql > Error: ERROR: ExecSubPlan: failed to find placeholder for subplan What Postgres version is that? If it's not a current minor release, please try updating, because I seem to remember hav

[SQL] FW: Help- post gress sql error

Hi! I am receiving the following error when trying to execute sql Error: ERROR: ExecSubPlan: failed to find placeholder for subplan result (State:HY000, Native Code: 2C) - My query works fine in Oracle database without any issues. I have sub queries w

Re: [SQL] Fw: How to FindNearest

On Tue, Oct 03, 2006 at 17:35:55 +, [EMAIL PROTECTED] wrote: > > My function will take all the above info about the hole and down_hole_survey > tables, and sample.hole_id and sample.depth_meters and will calculate the > coordinates. I need to put these coordinates into sample.x , sample.y

[SQL] FW: sql copy does not work

Title: FW: sql copy does not work Hi I have a problem with the copy command. I need to write a Perl script where I copy the log files into the database. If I use the copy following syntax in shell script it works, but I need to use it in Perl. In shell script look like this and it work

Re: [SQL] FW: help with serial

Luca Rasconi wrote: CREATE OR REPLACE RULE r1 AS ON INSERT TO TABLE_A DO INSERT INTO TABLE_B (uid) VALUES ((new.uid)); how is it possible, why in a table 37 and in the other 37 + 1? This is almost certainly the "nextval() evaluated twice" issue that catches everyone out from time t

[SQL] FW: help with serial

Hi all, There’s a behaviour I can’t understand There is table TABLE_A with 3 columns (uid, id, session) where uid is the pk and the data type is serial. There is table TABLE_B with 3 columns (uid, ts_col, col) where uid is the pk.   On table TABLE_A I have a rule on insert like thi

[SQL] FW: How to install Postgres that supports 64-bit integer/date-time.

Continued…..   I solved this problem by myself.   A. For 64 bit development you need these packages installed on Solaris server: SUNWarcx, SUNWbtoox, SUNWdplx, SUNWscpux, SUNWsprox, SUNWtoox, SUNWlmsx, SUNWlmx, SUNWlibCx   Pls confirm these using the following command. pkginfo S

Re: [SQL] FW: Working with XML.

Theo Galanakis wrote: Hi, I have copied all the files manually from http ://developer.postgresql.org/docs/pgsql/contrib/ for the xml2 contribution. However I have the following issue when I attempt to compile with gmake: gcc -I/usr/includ

Re: [SQL] FW: "=" operator vs. "IS"

I'm just curious - why is it not possible to use the "=" operator to compare values with NULL? I suspect that the SQL standard specified it that way, but I can't see any ambiguity in an expression like "AND foo.bar = NULL". Is it because NULL does not "equal" any value, and the expression should b

Re: [SQL] Fw: postgres logging [SOLVED]

Stef mentioned : => The postmaster logs to a separate log file, but at the moment => it's impossible to tell which sql comes from which connection. => Is there an easy way to accomplish this? Erm... sorry , It appears my postgresql.conf is not an original. It didn't have the log_pid option in. :)

[SQL] Fw: postgres logging

Hi all, I've switched on log_statement in postgresql.conf for debugging purposes. I tried logging connections and pids as well, but I need to match up the logged statements to specific connections. The postmaster logs to a separate log file, but at the moment it's impossible to tell which sq

Re: [SQL] Fw: Error message during compressed backup

On Thursday 23 October 2003 10:57, Kumar wrote: > Dear Friends, > > While doing compressed backup for one of the database running at Postgres > Server 7.3.4 on RH Linux 7.2, I got the following error., but it backup > other items > > --Command to backup > $ $ pg_dump -h 192.xxx.x.xxx -p 5432 -v tes

[SQL] Fw: Error message during compressed backup

  Dear Friends,   While doing compressed backup for one of the database running at Postgres Server 7.3.4 on RH Linux 7.2, I got the following error., but it backup other items   --Command to backup $ $ pg_dump -h 192.xxx.x.xxx -p 5432 -v testdb -f /home/db_repository/testdb20031023.sql.tar.gz

Re: [SQL] Fw: Transactions ID

On Friday 05 September 2003 08:10, Yaroslav Ulyanov wrote: > Hello. > > You may prompt me, what get the identifier to transactions? Not entirely sure what you're after, but does chapter 2.2 of the manuals - "System Columns" help you? -- Richard Huxton Archonet Ltd -

[SQL] Fw: Transactions ID

  Hello.   You may prompt me, what get the identifier to transactions?   Best regards,Yaroslav Ulyanov[EMAIL PROTECTED]

Re: [SQL] Fw: Change column data type

On Tue, 2 Sep 2003, Kumar wrote: > > Dear Friends, > > Using Postgres 7.3.4 over the linux server 7.3. > > Is it possible to alter/change the data type of a existing table's > column, with out dropping and recreating a column of same name. Only for certain types, and only by hacking the syst

[SQL] Fw: Change column data type

  Dear Friends,   Using Postgres 7.3.4 over the linux server 7.3.   Is it possible to alter/change the data type of a existing table's column, with out dropping and recreating a column of same name.   Thanks for ur knowledge sharing.     Regards Kumar  

Re: [SQL] Fw:

Your ipc-daemon-version is to old. Download the newest version first. (1.13) Daniel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] Fw:

pgsql-sql >hello: > > when instal postgresql7.3.2 .in windows2000 >I have install Cygwin 1.3.3 >postgresql-7.3.1-1 >cygipc-1.09-2 > >all successful >but when I initdb -D /usr/local/pgsql/data >I got the following messages: >User > Posted: 2003-02-13 15:19 > > The files belonging to this da

[SQL] Fw:

pgsql-sql >hello: > > when instal postgresql7.3.2 .in windows2000 >I have install Cygwin 1.3.3 >postgresql-7.3.1-1 >cygipc-1.09-2 > >all successful >but when I initdb -D /usr/local/pgsql/data >I got the following messages: >User > Posted: 2003-02-13 15:19 > > The files belonging to this da

Re: [SQL] FW: query problem "server sent binary data ... without

Stephan Szabo <[EMAIL PROTECTED]> writes: > On Thu, 26 Sep 2002 [EMAIL PROTECTED] wrote: >> the query runs for 10 minutes or so, then outputs: > How much data is that sending? The client library is going to try to > buffer the entire result set. And, in fact, this is the typical behavior when i

Re: [SQL] FW: query problem "server sent binary data ... without

On Thu, 26 Sep 2002 [EMAIL PROTECTED] wrote: > I'm having a problem with postgres on HPUX. My version is: > > VERSION = 'PostgreSQL 7.2.2 on hppa2.0w-hp-hpux11.11, > compiled by aCC -Ae' > > I'm trying to do a query and it consistently gives the > followi

[SQL] FW: query problem "server sent binary data ... without prior row description ..."

Not sure if this is a repeat request, sorry if you receive it twice. Thanks, Bill Jones Systems Architect Middleware Services Wells Fargo Services Company Office --415.222.5226 PCS -- 415.254.3831 ([EMAIL PROTECTED]) Views expressed are mine. Only in unusual circumstances are they shared by my

[SQL] Fw: Server Performance and Scalability Killers

Server Description: MIDI audio

[SQL] Fw: !

<>

[SQL] FW: RESTORE A TABLE

I have serveral tables that they have more then 2 millions, I want dump they out from one server and then restore them back on another server every day, the questions are: 1. What is the fastest way to dump/restore my data, I am try to use: pg_dump -aRt mytable -Fc -f mytable dbname pg_res

[SQL] Fw: C function for use from PLpgSQL trigger

Hello all, I posted this (see below) Friday to the interfaces list with no response. Does anyone know if what I'm trying to do is possible, or should I just write the entire thing in a C function trigger? The latter would be unfortunate because I think it would be nice to be able to extend PLpgSQ

[SQL] Fw: Optimization recommendations request

Well, this message never made it through, but I managed to answer my own question -- I never ran vacuum analyze which caused a table scan instead of an index scan. After running vacuum analyze the query returns immediately. I would still be interested in any hints to optimize performance for very

[SQL] Fw: lo_import & lo_export

Hi, I am trying to develop a client application in Visual Basic that will run on MS Windows98 and will communicate via ODBC with a Postgres server on Solaris. I need to store Blob's from the client drive (images) to the Postgres DB. I tried to use lo_import/lo_export but apparently they only

[SQL] FW: Sharing Databases

-Original Message- From: Yoghini Thevarajah [mailto:[EMAIL PROTECTED]] Sent: Monday, November 27, 2000 1:06 PM To: [EMAIL PROTECTED] Subject: FW: Sharing Databases Need help desperately. Is is possible at all to share tables between different databases. In SELECT query need to make

[SQL] FW: Sharing Databases

Need help desperately. Is is possible at all to share tables between different databases. In SELECT query need to make join to two tables in different databases. Thanks a million in advance. Yoghini