[GENERAL] Relation "tablename" does not exist

[Johan van der Merwe]

I have posted this before without receiving replies

My code is as follows:

Public dbRemote As New Connection
Public rsData As New Recordset

dbRemote.Properties("Data Source").Value = "DSM"
dbRemote.Properties("Initial Catalog").Value = "BCM_DSM"
dbRemote.Properties("User ID").Value = "johan"
dbRemote.Properties("Password").Value = "johan"
dbRemote.Open

rsData.Open "tblSuburb", dbRemote, , , adCmdTable


I receive the error "Relation "tblSuburb" does not exist"

Cheers

Johan van der Merwe
Ballenden & Robb Consulting Engineers
Tel (043) 743 3809
Fax (043) 743 9321
Cell 082 5530445

"I Don't want to be a human being, I want to be a human doing"

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.4/363 - Release Date: 2006/06/13
 


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] how pg_hba.conf

[shyju c.k]

hai 
 
   have to solve one problem
 
 
    i have two mechine in diffrent network range , one is main database server(n/w is 10.1.2.0) and other is back bone server(n/w 10.1.7.0) 
    i need to take pg_dump from back bone server .
 
    what configuration needed in pg_hba.conf 
 
 
thx

Re: [GENERAL] Out of memory error in 8.1.0 Win32

[Qingqing Zhou]

""Relyea, Mike"" <[EMAIL PROTECTED]> wrote
> I've just started receiving an out of memory error with my most complex
> query.

Can you post the memory usage log after the error the server reports?

Regards,
Qingqing



---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] how to test my stored procedures?

[Yavuz Kavus]

hi everybody.
 
i am writing stored procedures.
lots of them have out parameters.
 
i dont know how to test them 
from an sql query tool(ex: pgAdmin query tool).
i am trying to test them from my program, but it is not so efficient.
 
i want to test from a query tool.
i can do with procedures without out paramters,
but how to achive with out parameters?
can i declare a variable out of procedure and 
then send it as an out parameter and see it is value?
 
thanks for your help.

Re: [GENERAL] tsearch2

[John DeSoi]

On Jun 14, 2006, at 6:29 PM, Chris Hoover wrote:

How do you set it up to search for "how now brown cow" vs how now  
brown cow?  The first example is looking for the exact phrase of  
words, while the second is just looking for all of the works.


Your select would have two conditions, one to find rows with the  
words using the tsearch2 index and then a filtering condition (e.g.  
AND like '%how now brown cow%'). How effective this is depends a lot  
on your data, but it works well as long as the indexed search can  
return a reasonably small set for the sequential scan on the  
filtering condition.


There is an example in the documentation if you need more details.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] table has many to many relationship with itself - how to implement?

[Jim C. Nasby]

On Wed, Jun 14, 2006 at 10:53:36AM +0200, Daniel McBrearty wrote:
> Hi all,
> 
> I have a table "phrases" that looks like this :
> 
> create table phrases(
> id serial ,
> language integer references langauges(id),
> content text
> );
 
Might want to avoid bareword 'id' as a field name... it's a great way to
lead to confusion.
 
> insert into table translations ... insert what?
 
INSERT INTO translations VALUES(default) should work. Worst case, you
could always just select from the appropriate sequence.

> The other way to do this that I see is to lose the link table
> translations_to_phrases, and then make translations
> 
> create table translations (
> id serial primary key,
> phrases integer[]
> );
> 
> 
> but it seems that I can no longer make postgre aware that the integers
> in translations(phrases) are references.

BTW, it's "PostgreSQL" or "Postgres" if you must.

> What is the best solution?

I'd just have a sequence for translation_id and grab from it manually
every time you create a translation, then just use that value when you
insert into translation_phrase.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] DEFAULT_STATISTICS_TARGET

[John D. Burger]

You can use ALTER DATABASE.


Argh!  I somehow missed that despite reading the Run-time Configuration 
section three times ... Thanks, folks!


- John D. Burger
  MITRE


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully implementing

[Chris Browne]

kleptog@svana.org (Martijn van Oosterhout) writes:

> On Tue, Jun 13, 2006 at 05:23:56PM -0400, Christopher Browne wrote:
>> > [3] 
>> > http://www.intelligententerprise.com/010327/celko_online.jhtml;jsessionid=NDIHEWXGL4TNKQSNDBNSKHSCJUMEKJVN
>> 
>> The sample problem in [3] is one that shows pretty nicely a
>> significant SQL weakness; it's very painful to build SQL to do complex
>> things surrounding cumulative statistics.
>
> I havn't managed to wrap my brain around them yet, but this seems like
> something that SQL WINDOW functions would be able to do. For each row
> define the window frame to be all the preceding rows, do a SUM() and
> divide that over the total. Or perhaps the PERCENT_RANK() function does
> this already, not sure.
>
> Mind you, postgres doesn't support them yet, but it's interesting that
> it may be possible at all...

Yes, you are exactly right; I have seen a couple references to OVER
and PARTITION BY which look as though they are the relevant SQL
additions...

http://blogs.ittoolbox.com/database/technology/archives/olap-sql-part-5-windowing-aggregates-8373
http://www.sqljunkies.com/HowTo/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk
http://sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk
http://www.experts-exchange.com/Databases/Oracle/Q_21793507.html

I'm not sure the degree to which these are standardized, but they are
available in some form or another in late-breaking versions of Oracle,
DB2, and Microsoft SQL Server.

I'm not quite sure how to frame this so as to produce something that
should go on the TODO list, but it looks like there's a possible TODO
here...
-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/sap.html
"The newsreader abuse likely  stems from more fundamental, than merely
just the  UI, design disagreements. Requests from  Unix programmers to
replicate  Free Agent  rightfully so  should trigger  the  throwing of
sharp heavy objects at the requesting party."
-- [EMAIL PROTECTED] (jedi)

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] DEFAULT_STATISTICS_TARGET

[Tom Lane]

"John D. Burger" <[EMAIL PROTECTED]> writes:
> Is there any way to set DEFAULT_STATISTICS_TARGET on a permanent basis 
> for a particular database?  SET DEFAULT_STATISTICS_TARGET is only per 
> session, apparently, and I'm not sure I want to set it for the whole 
> cluster in the config file.  I know I can fix it per-column with ALTER 
> TABLE, but that's a bit of a pain.
> I'm running 7.4.  Thanks for any info.

ALTER DATABASE SET, same as most other GUC variables ...

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] DEFAULT_STATISTICS_TARGET

[Michael Fuhr]

On Wed, Jun 14, 2006 at 06:21:35PM -0400, John D. Burger wrote:
> Is there any way to set DEFAULT_STATISTICS_TARGET on a permanent basis 
> for a particular database?

You can use ALTER DATABASE.  New sessions will get the new value;
you'll need to reconnect to see the effect.

http://www.postgresql.org/docs/7.4/interactive/sql-alterdatabase.html

-- 
Michael Fuhr

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] table has many to many relationship with itself - how

[John D. Burger]

[EMAIL PROTECTED] wrote:


Starting with this:

create sequence languages_seq increment by 1;
create table languages (
  id integer primary key default nextval('languages_seq'),
  language_name varchar(100)
);


(I like specifying my own sequence names, instead of using "serial",  
plus

using a default this way lets me insert an integer directly, when
necessary, or letting it default, but you can use serial, if you want).


You can always insert your own value into a SERIAL column.  From the  
8.1 docs:


The data types serial and bigserial are not true types, but merely a  
notational convenience for setting up unique identifier columns  
(similar to the AUTO_INCREMENT property supported by some other  
databases). In the current implementation, specifying


CREATE TABLE tablename (
colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);


(http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE- 
SERIAL)


Your example above differs only in the sequence name (plus you have a  
PK constraint, but you can do this on a SERIAL column too).


- John D. Burger
  MITRE


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] tsearch2

[Chris Hoover]

I have some general questions on tsearch2.  How do you set it up to search for "how now brown cow" vs how now brown cow?  The first example is looking for the exact phrase of words, while the second is just looking for all of the works.
Secondly, if you want to split a text field based on multiple delimiters (i.e. a text line with "johnny bravo,123 bravo ln,apple), do you have to write the parser, or is there a way to pass the parser a ' ' and a ',' as the word delimiters?
Thanks,Chris

[GENERAL] DEFAULT_STATISTICS_TARGET

[John D. Burger]

Is there any way to set DEFAULT_STATISTICS_TARGET on a permanent basis 
for a particular database?  SET DEFAULT_STATISTICS_TARGET is only per 
session, apparently, and I'm not sure I want to set it for the whole 
cluster in the config file.  I know I can fix it per-column with ALTER 
TABLE, but that's a bit of a pain.


I'm running 7.4.  Thanks for any info.

- John D. Burger
  MITRE


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] table has many to many relationship with itself - how

[Daniel McBrearty]

thanks Susan for your idea.

I thought that was it for a moment, then I saw a problem (I think) -
it works, but gets quite inefficient.

when you have 2 phrases which are a translation, that is just one entry

when you have 3, that is 3
4 => 6

and so on.

In practice we might have 15, 20 languages in a translation. It is
unlimited many-to-many.

So the problem is a bit to do this while keeping the solution efficient.

I am almost sold on just using

create table translaton (
 id serial,
 phrases integer[]
);

but afaik there is no way to tell pg that the array contains refs to
another table. I could still live with this though, if noone has a
better way.

regards

Daniel


--
Daniel McBrearty
email : danielmcbrearty at gmail.com
www.engoi.com : the multi - language vocab trainer
BTW : 0873928131

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Performance Question

[Terry Lee Tucker]

On Wednesday 14 June 2006 03:57 pm, Terry Lee Tucker <[EMAIL PROTECTED]> thus 
communicated:
--> Hello List:
-->
--> I've been told that an update to a record is equivalent to a delete and
 insert --> operation. We have a utility written in Perl that brings into
 sync certain --> elements of 50 thousand records on 8 structurally identical
 databases. We --> threw together the script and decided to just delete the
 record and re-insert --> it with the data that was brought into sync. Now
 the question: Is it just as --> fast to do it this way, or is there some
 hidden advantage to performing an --> update?
-->
--> Just curious.
-->
--> TIA

Thanks for the answers. This list is a BIG help to us all :o]

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] table has many to many relationship with itself - how

[SCassidy]

Starting with this:

create sequence languages_seq increment by 1;
create table languages (
  id integer primary key default nextval('languages_seq'),
  language_name varchar(100)
);
insert into languages (id, language_name) values (1, 'English');
insert into languages (id, language_name) values (2, 'French');
insert into languages (id, language_name) values (3, 'Spanish');
insert into languages (id, language_name) values (4, 'Italian');

create table phrases(
 id serial primary key,
 language integer references languages(id),
 content text
);
insert into phrases (language, content) values (1, 'the book');
insert into phrases (language, content) values (2, 'le livre');
insert into phrases (language, content) values (3, 'el libro');
insert into phrases (language, content) values (4, 'il libro');
insert into phrases (language, content) values (1, 'the room');
insert into phrases (language, content) values (4, 'la stanza');
insert into phrases (language, content) values (4, 'la camera');


For your translations table, I would go with something like this:


create sequence translations_seq increment by 1;
create table translations (
 translation_id integer primary key default nextval('translations_seq'),
 lang1_id integer references phrases(id),
 lang2_id integer references phrases(id)
);


(I like specifying my own sequence names, instead of using "serial", plus
using a default this way lets me insert an integer directly, when
necessary, or letting it default, but you can use serial, if you want).

That lets you insert rows for multiple to/from pairs.  Also, some words
have multiple meanings, or more than one word has the same meaning.  For
example, the English word "room" can be either "camera" or "stanza" in
Italian.

testdb1=> select * from phrases;
 id | language |  content
+--+---
  1 |1 | the book
  2 |2 | le livre
  3 |3 | el libro
  4 |4 | il libro
  5 |1 | the room
  6 |4 | la stanza
  7 |4 | la camera
(7 rows)

testdb1=> insert into translations (lang1_id, lang2_id) values (1, 2);
INSERT 666949 1
testdb1=> insert into translations (lang1_id, lang2_id) values (1, 3);
INSERT 666950 1
testdb1=> insert into translations (lang1_id, lang2_id) values (1, 4);
INSERT 666953 1
testdb1=> insert into translations (lang1_id, lang2_id) values (5, 6);
INSERT 666954 1
testdb1=> insert into translations (lang1_id, lang2_id) values (5, 7);
INSERT 666955 1

Then, you can do this:
select p.content from phrases p where p.id in (select lang2_id from
translations where lang1_id = 5);
  content
---
 la stanza
 la camera
(2 rows)

I assume that this is a fairly simple "phrasebook" type of data set.
Partly, the structure depends on how you intend to access the data after
you build it.

Just an idea.

Susan



  
   "Daniel McBrearty"   
  
  <[EMAIL PROTECTED]To:   
pgsql-general@postgresql.org   
  com>   cc:
  
   Sent by:  Subject:  [GENERAL] table 
has many to many relationship with itself - how to 
  implement?
  

  
  [EMAIL PROTECTED] |---|   

  tgresql.org | [ ] Expand Groups | 
  
  |---| 
  

  
   06/14/2006 01:53 
  
  AM
  

  

  




Hi all,

I have a table "phrases" that looks like this :

create table phrases(
 id

Re: [GENERAL] Performance Question

[Alan Hodgson]

On Wednesday 14 June 2006 13:24, Greg Stark <[EMAIL PROTECTED]> wrote:
> One way it would be unequal is if you can do your DELETE as a single
> query and the insert operation as using a single large COPY FROM. 

This is definitely the fastest way to update tens of thousands of rows if 
you know they all need to be replaced.  It saves on index lookups and also 
network latency to the feeding app.

I have also had measurable success COPYing data into a temp table and then 
using joins against that to delete,update,or insert only the rows that 
actually need to be processed in the real table (saving unnecessary index 
updates).

-- 
In a truly free society, "Alcohol, Tobacco and Firearms" would be a
convenience store chain.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Performance Question

[Greg Stark]

Terry Lee Tucker <[EMAIL PROTECTED]> writes:

> Hello List:
> 
> I've been told that an update to a record is equivalent to a delete and 
> insert 
> operation. We have a utility written in Perl that brings into sync certain 
> elements of 50 thousand records on 8 structurally identical databases. We 
> threw together the script and decided to just delete the record and re-insert 
> it with the data that was brought into sync. Now the question: Is it just as 
> fast to do it this way, or is there some hidden advantage to performing an 
> update?

If you're doing the whole DELETE/INSERT as a single transaction then it should
be roughly comparable. The UPDATE operation tries to keep the records on the
same page which makes it a faster operation all else being equal, but all else
is rarely equal.

One way it would be unequal is if you can do your DELETE as a single query and
the insert operation as using a single large COPY FROM. Even if you issue 50
thousand INSERTs and a single big DELETE that would be better than issuing 50
thousand separate UPDATEs that have to use index lookups to track down the
tuples being updated. 

Just be sure not to be issuing 50 thousand separate transactions, that will be
*much* slower.

-- 
greg


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Performance Question

[Douglas McNaught]

Terry Lee Tucker <[EMAIL PROTECTED]> writes:

> Hello List:
>
> I've been told that an update to a record is equivalent to a delete
> and insert operation. We have a utility written in Perl that brings
> into sync certain elements of 50 thousand records on 8 structurally
> identical databases. We threw together the script and decided to
> just delete the record and re-insert it with the data that was
> brought into sync. Now the question: Is it just as fast to do it
> this way, or is there some hidden advantage to performing an update?

UPDATE will probably be somewhat faster because it's only one SQL
statement to parse, plan and execute.

-Doug

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] Performance Question

[Terry Lee Tucker]

Hello List:

I've been told that an update to a record is equivalent to a delete and insert 
operation. We have a utility written in Perl that brings into sync certain 
elements of 50 thousand records on 8 structurally identical databases. We 
threw together the script and decided to just delete the record and re-insert 
it with the data that was brought into sync. Now the question: Is it just as 
fast to do it this way, or is there some hidden advantage to performing an 
update?

Just curious.

TIA

master=# select version();
   version
--
 PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 
20030502 (Red Hat Linux 3.2.3-49)

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Partitioning... -> solved

[Milen Kulev]

Thanks Tom,
OFFSET is even better solution for me .

Regards. Milen 

-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 14, 2006 3:57 PM
To: Milen Kulev
Cc: 'Jim C. Nasby'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Partitioning... 


"Milen Kulev" <[EMAIL PROTECTED]> writes:
> Thanks Jim, the "trick" worked for me !

>> You might need to add an ORDER BY to the subquery to ensure 
>> PostgreSQL doesn't pull it into the main query.

Actually, the usual trick is "OFFSET 0", which works just as well as an 
optimization fence and doesn't force any
significant extra work. Of course, if you *need* an ORDER BY then that's what 
to use, but it's overkill if you just want
to prevent flattening the subquery.

regards, tom lane


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] Out of memory error in 8.1.0 Win32

[Relyea, Mike]

I've just started receiving an out of memory error with my most complex
query.  It has been running fine for the past 9 months.  It's a snapshot
materialized view that I update every night using the functions from
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html

The error I'm receiving when I try to execute SELECT
refresh_matview('"tblSummary"'); is
ERROR:  out of memory
DETAIL:  Failed on request of size 344.
CONTEXT:  SQL statement "INSERT INTO "tblSummary" SELECT * FROM
"qrySummary""
PL/pgSQL function "refresh_matview" line 13 at execute statement

I'd post the results from EXPLAIN INSERT INTO "tblSummary" SELECT * FROM
"qrySummary"; but it's just over 700 lines

qrySummary is a view based on a number of other views and a few tables.
It is defined as

=
 SELECT "qryPQSetPages"."MachineID", "qryPQSetPages"."PrintCopyID",
"tblColors"."Color", "tblSuppliers"."Supplier", "tblZones"."Zone",
"tblPrinters"."Name" AS "Printer", "qryYield"."Yield",
"qryPrintCopyComments"."PrintCopyComments",
avg("qryPhotopicDensity"."PhotopicDensity") AS "AvgPhotopicDensity",
avg("qryCRMS"."CRMS_Value") AS "AvgCRMS", avg("qryLStar"."AvgOfLstar")
AS "AvgLstar", avg("qryAStar"."AvgOfAstar") AS "AvgAstar",
avg("qryBStar"."AvgOfBstar") AS "AvgBstar", avg("qryABRatio"."ABRatio")
AS "AvgABRatio", max("qryGhosting"."MaxGhosting") AS "MaxGhost",
max("qryBackground"."Background_DeltaE") AS "MaxBkgdDeltaE",
avg("qryMottle_NMF"."Mottle_NMF") AS "AvgMottle_NMF",
max("qryVBS_Horizontal"."VBS_Horizontal") AS "MaxVBS_H",
max("qryVBS_Vertical"."VBS_Vertical") AS "MaxVBS_V",
max("qryReload"."DeltaE") AS "MaxReloadDeltaE"
   FROM "tblColors"
   JOIN ("qryPQSetPages"
   LEFT JOIN "qryCRMS" ON "qryPQSetPages"."ColorID" =
"qryCRMS"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryCRMS"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryCRMS"."MachineID"
   LEFT JOIN "qryGhosting" ON "qryPQSetPages"."ColorID" =
"qryGhosting"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryGhosting"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryGhosting"."MachineID"
   LEFT JOIN "qryVBS_Horizontal" ON "qryPQSetPages"."ColorID" =
"qryVBS_Horizontal"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryVBS_Horizontal"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryVBS_Horizontal"."MachineID"
   LEFT JOIN "qryBStar" ON "qryPQSetPages"."ColorID" =
"qryBStar"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryBStar"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryBStar"."MachineID"
   LEFT JOIN "qryBackground" ON "qryPQSetPages"."ColorID" =
"qryBackground"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryBackground"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryBackground"."MachineID"
   LEFT JOIN "qryReload" ON "qryPQSetPages"."ColorID" =
"qryReload"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryReload"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryReload"."MachineID"
   LEFT JOIN "qryMottle_NMF" ON "qryPQSetPages"."ColorID" =
"qryMottle_NMF"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryMottle_NMF"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryMottle_NMF"."MachineID"
   LEFT JOIN "qryAStar" ON "qryPQSetPages"."ColorID" =
"qryAStar"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryAStar"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryAStar"."MachineID"
   LEFT JOIN "qryABRatio" ON "qryPQSetPages"."ColorID" =
"qryABRatio"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryABRatio"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryABRatio"."MachineID"
   LEFT JOIN "qryLStar" ON "qryPQSetPages"."ColorID" =
"qryLStar"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryLStar"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryLStar"."MachineID"
   LEFT JOIN "qryYield" ON "qryPQSetPages"."ColorID" =
"qryYield"."ColorID" AND "qryPQSetPages"."MachineID" =
"qryYield"."MachineID"
   LEFT JOIN "qryPrintCopyComments" ON "qryPQSetPages"."ColorID" =
"qryPrintCopyComments"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryPrintCopyComments"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryPrintCopyComments"."MachineID"
   LEFT JOIN "qryVBS_Vertical" ON "qryPQSetPages"."ColorID" =
"qryVBS_Vertical"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryVBS_Vertical"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryVBS_Vertical"."MachineID"
   LEFT JOIN "qryPhotopicDensity" ON "qryPQSetPages"."ColorID" =
"qryPhotopicDensity"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryPhotopicDensity"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryPhotopicDensity"."MachineID") ON "tblColors"."ColorID" =
"qryPQSetPages"."ColorID"
   LEFT JOIN "tblZones" ON "qryYield"."ZoneID" = "tblZones"."ZoneID"
   LEFT JOIN "tblPrinters" ON "qryYield"."PrinterID" =
"tblPrinters"."PrinterID"
   LEFT JOIN "tblSuppliers" ON "qryYield"."SupplierID" =
"tblSuppliers"."SupplierID"
  GROUP BY "qryPQSetPages"."MachineID", "qryPQSetPages"."PrintCopyID",
"tblColors"."Color", "t

[GENERAL] Initdb logging on xp

[Phil Thornhill]

Hi,

Is there a way to log the initdb output when running on Windows XP.

I have created my own Merge Module to install postgresql on XP with my 
.net application MSI project.
It works fine for most installs but occasionally the initdb fails, and 
as it is run in a seperate process under the postgres user account i'm 
having difficulties in finding out why?


Any help would be much appreciated.

Regards

Phil Thornhill



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] new user (need help: fedora core 4)

[Devrim GUNDUZ]

Hi Jasbinder,

On Wed, 2006-06-14 at 12:13 -0400, Jasbinder Bali wrote:
> How do i actually work on the database after starting the postgres
> service at the command line. 

Use pgadmin3: http://www.pgadmin.org 

This is a powerful gui for PostgreSQL. We have a prebuilt RPM for Fedora
Core 4:

http://wwwmaster.postgresql.org/download/mirrors-ftp?file=pgadmin3%
2Frelease%2Fv1.4.1%2Ffedora-core-4%2Frpms%2Fpgadmin3-1.4.1-1.i686.rpm

Regards,
-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] new user (need help: fedora core 4)

[Jasbinder Bali]

Hi,I'm a new user of Postgresql and using it on Fedora core 4. After following all the installation steps and completing them successfully, I'm in a confusion. How do i actually work on the database after starting the postgres service at the command line.
I can sense that i have to use something called webmin. Whats that and how do i use that   on fedora core 4. Please help~Jas

Re: [GENERAL] Problem Connecting to 5432

[Casey, J Bart]

Louis,

Thank you very much for the info on the -h flag.  Somehow I missed that
in the postmaster man pages.  That did the trick!

Regards,

J. Bart Casey

-Original Message-
From: louis gonzales [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 14, 2006 2:42 AM
To: louis gonzales
Cc: Casey, J Bart; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Problem Connecting to 5432

My mistake, the "-h host_IP" explicitly states which IP address to 
listen on.

/usr/bin/postmaster -h your_IP -p 5432 -D /var/lib/pgsql/data -i

I'm not sure if postgresql v7.x.y already used the pg_ctl command which 
is essentially a wrapper for postmaster, if so use,

pg_ctl -w -o "-h your_IP -p your_PORT" -l logfile(if you wish) start

if you use "your_IP = 0.0.0.0" it will listen on all valid TCP/IP 
interfaces, including 127.0.0.1(a.k.a. localhost)



louis gonzales wrote:

> Try using the following format in the pg_hba.conf file:
>
> host all all(or your_user_account) your_IP/32 trust (The 32 is the 
> same as 255.255.255.255 but in CIDR format)
>
> As for the command line you started postmaster with, doesn't the "-i" 
> require an interface such as an IP address too? If you look below in 
> your comments, you specify "-i" after your DATA directory but never 
> give the "-i" an argument?
>
>
>
> Casey, J Bart wrote:
>
>> All,
>>
>> I have read message after message and searched the internet for 
>> hours, yet I still can't get a remote computer to connect to port 
>> 5432 on my Fedora Core 3 system running Postgresql 7.4.7.
>>
>> What I have done:
>>
>> 1) Stopped the iptables service
>>
>> 2) Modified postgresql.conf and added the following lines
>>
>> tcpip_socket = true
>>
>> port = 5432
>>
>> 3) Modified pg_hba.conf and added
>>
>> host all all (my ip address) 255.255.255.255 trust
>>
>> 4) Modified the postgresql startup script to use the -i flag
>>
>> 5) Verified that postmaster is running with the -i flag... ps ax |
grep 
>> postmaster output:
>>
>> 4259 pts/1 S 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
-i
>>
>> 6) Tried to verify that the server was listening on port 5432 only to

>> find out that it isn't. The netstat output follows:
>>
>> tcp 0 0 127.0.0.1:8438 0.0.0.0:* LISTEN
>>
>> tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
>>
>> tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN
>>
>> tcp 0 0 :::80 :::* LISTEN
>>
>> tcp 0 0 :::22 :::* LISTEN
>>
>> tcp 0 0 :::443 :::* LISTEN
>>
>> As you can see it is only listening on the loopback interface
>>
>> I'm quite certain the issue is how I am starting the service, but 
>> I've added the -i flag.
>>
>> I'm all out of ideas on this one. Any and all help is greatly 
>> appreciated.
>>
>> Regards,
>>
>> Bart
>>
>
>
> ---(end of
broadcast)---
> TIP 4: Have you searched our list archives?
>
>   http://archives.postgresql.org





---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Problem Connecting to 5432

[Tom Lane]

"Casey, J Bart" <[EMAIL PROTECTED]> writes:
> I have read message after message and searched the internet for hours,
> yet I still can't get a remote computer to connect to port 5432 on my
> Fedora Core 3 system running Postgresql 7.4.7.

You need to restart (not just SIGHUP) the postmaster to get it to accept
the tcpip_socket flag.  If you don't see it listening in netstat then
my first guess is you forgot that step.  If it's not apparent what's
happening then you need to look at the postmaster's log output ... which
I think the default configuration in FC3 will deliver to /dev/null :-(
You could tweak the initscript to change that, or start the postmaster
"by hand" from a terminal window to see if it prints any useful messages.

Once you see that it's listening, try "psql -h localhost ..." to see if
a TCP connection actually works.  I am not sure that it will work by
default --- I think that the default iptables firewall configuration on
FC3 disallows connections to 5432.  (Even if they're allowed on local
loopback, they're very likely not allowed remotely.)  So fixing the
iptables configuration will be your second hurdle.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Partitioning...

[Tom Lane]

"Milen Kulev" <[EMAIL PROTECTED]> writes:
> Thanks Jim, the "trick" worked for me !

>> You might need to add an ORDER BY to the subquery to ensure
>> PostgreSQL doesn't pull it into the main query. 

Actually, the usual trick is "OFFSET 0", which works just as well as
an optimization fence and doesn't force any significant extra work.
Of course, if you *need* an ORDER BY then that's what to use, but
it's overkill if you just want to prevent flattening the subquery.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] COLLATE

[Jan Behrens]

Filip Rembiałkowski wrote:

let's assume that we keep Unicode text  data in the column.
sometimes we want to sort it according to specific collation order.
how can we force collation when running a query?


Hi Filip,

I had the same problem you have. As a solution I implemented a wrapper 
function named collkey(), which transforms a Unicode string into a 
sortable collation key using IBM's ICU library. Of course it's not as 
nice as having a standard SQL command for it, but i think it's a nice 
solution, until a good collation support is provided by PostgreSQL itself.


You can find the source here:
http://www.flexiguided.de/publications.pgcollkey.en.html

I can understand that people don't want PostgreSQL being dependent of 
other libraries. The best solution for future would be implementing 
independent collation functions inside of PostgreSQL, which also support 
standard SQL syntax, but that's a lot of work, i think.


Jan Behrens

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Copy content from dbs

[Guido Neitzer]

On 14.06.2006, at 14:12 Uhr, A. Kretschmer wrote:

Is there an easy way to copy the content including the table   
structure,

indexes and so on from one db to another?


Create the new db with the old db as template.


Thanks, but as far as I can see, I have to disconnect all clients  
from the production db to do this and this wasn't an option.


I forgot to mention that. The pg_dump way is fine for me.

cug

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Copy content from dbs

[A. Kretschmer]

am  14.06.2006, um 13:56:06 +0200 mailte Guido Neitzer folgendes:
> Hi.
> 
> Is there an easy way to copy the content including the table  structure, 
> indexes and so on from one db to another?

Create the new db with the old db as template.


Andreas
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47215,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Copy content from dbs

[Guido Neitzer]

On 14.06.2006, at 14:02 Uhr, Thomas Kellerer wrote:


pg_dump: http://www.postgresql.org/docs/8.1/static/app-pgdump.html


Found it. Thanks. I was just blind and thought, pg_dump can only re- 
create the db with the same name.


Thanks,
Guido

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Copy content from dbs

[Thomas Kellerer]

On 14.06.2006 13:56 Guido Neitzer wrote:

Hi.

Is there an easy way to copy the content including the table structure, 
indexes and so on from one db to another?


Let's say I have a production db called db_production and want to create 
a development db called db_dev with exactly the same content on the same 
machine - just with the different name?


pg_dump: http://www.postgresql.org/docs/8.1/static/app-pgdump.html

Thomas


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

[GENERAL] Copy content from dbs

[Guido Neitzer]

Hi.

Is there an easy way to copy the content including the table  
structure, indexes and so on from one db to another?


Let's say I have a production db called db_production and want to  
create a development db called db_dev with exactly the same content  
on the same machine - just with the different name?


cug

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Partitioning...

[Milen Kulev]

Aha !!!
Thanks Jim, the "trick" worked for me !

Regards. Milen 

-Original Message-
From: Jim C. Nasby [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 14, 2006 1:09 AM
To: Tom Lane
Cc: Milen Kulev; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Partitioning...


On Mon, Jun 12, 2006 at 06:58:06PM -0400, Tom Lane wrote:
> "Milen Kulev" <[EMAIL PROTECTED]> writes:
> > What is wrong with random() ?
> 
> Not guaranteed to be stable across the multiple evaluations that the 
> rule will perform ... remember a rule is a macro and has the usual 
> multiple-evaluation gotchas in the face of volatile arguments.

I believe a safe alternative would be...

INSERT INTO ... SELECT * FROM
(SELECT random()*20 FROM ...)
;

You might need to add an ORDER BY to the subquery to ensure PostgreSQL doesn't 
pull it into the main query.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Compiling contrib dblink_tds and oralink

[LLC]

Hi List;

I've downloaded and unzipped the dblink_tds and the oralink contribs from
the pgfoundry.

Once I move them into the contrib directory and try to run make I get
errors about undeclared functions (see the listing below). Any C guru's
have any thoughts/advice/etc??


Thanks in advance for your help...


dblink_tds.c:67: error: 'TDSCOLINFO' undeclared (first use in this function)
dblink_tds.c:67: error: (Each undeclared identifier is reported only once
dblink_tds.c:67: error: for each function it appears in.)
dblink_tds.c:67: error: 'col' undeclared (first use in this function)
dblink_tds.c:68: warning: ISO C90 forbids mixed declarations and code
dblink_tds.c:122: error: too few arguments to function
'tds_process_result_tokens'
dblink_tds.c:151: error: too few arguments to function
'tds_process_result_tokens'
dblink_tds.c:240: error: 'TDSBLOBINFO' undeclared (first use in this
function)
dblink_tds.c:240: error: syntax error before ')' token
dblink_tds.c: In function 'dblink_tds_create_view':
dblink_tds.c:283: error: 'TDSCOLINFO' undeclared (first use in this function)
dblink_tds.c:283: error: 'col' undeclared (first use in this function)
dblink_tds.c:284: warning: ISO C90 forbids mixed declarations and code
dblink_tds.c:331: error: too few arguments to function
'tds_process_result_tokens'
dblink_tds.c:339: error: too few arguments to function
'tds_process_result_tokens'
dblink_tds.c: In function 'dblink_tds_create_rules':
dblink_tds.c:438: warning: unused variable 'tuple'
dblink_tds.c:407: warning: unused variable 'password'
dblink_tds.c:406: warning: unused variable 'user'
dblink_tds.c:405: warning: unused variable 'server'
dblink_tds.c:403: warning: unused variable 'table'
dblink_tds.c: In function 'TDSconnect':
dblink_tds.c:536: error: 'TDSCONNECTINFO' undeclared (first use in this
function)
dblink_tds.c:536: error: 'connection' undeclared (first use in this function)
dblink_tds.c:537: warning: ISO C90 forbids mixed declarations and code
make: *** [dblink_tds.o] Error 1


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Me And My Database

[Martijn van Oosterhout]

On Wed, Jun 14, 2006 at 11:45:03AM +0200, Leif B. Kristensen wrote:
> On Wednesday 14. June 2006 11:38, Martijn van Oosterhout wrote:
> >Why did you put parenthesis there? It looks like you're making a
> > record within a record. You wouldn't have parenthesis there for a
> > normal select statement, would you?
> 
> s**t. When I remove the parentheses, it runs fine.
> 
> This is a little contrary to common programmer philosophy, where putting 
> in extra parentheses for clarity is considered Good Practice[TM].

Except in cases where it changes the meaning, obviously.

I suppose if the SQL standard had chosen something else for row
constructors (like [] or {}) what you typed would've been a syntax
error rather than being valid.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Me And My Database

[Leif B. Kristensen]

On Wednesday 14. June 2006 11:38, Martijn van Oosterhout wrote:
>Have a nice day,

I forgot to say thank you. And a nice day to you too.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Me And My Database

[Leif B. Kristensen]

On Wednesday 14. June 2006 11:38, Martijn van Oosterhout wrote:
>Why did you put parenthesis there? It looks like you're making a
> record within a record. You wouldn't have parenthesis there for a
> normal select statement, would you?

s**t. When I remove the parentheses, it runs fine.

This is a little contrary to common programmer philosophy, where putting 
in extra parentheses for clarity is considered Good Practice[TM].
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Me And My Database

[Martijn van Oosterhout]

On Wed, Jun 14, 2006 at 11:35:12AM +0200, Leif B. Kristensen wrote:
> On Wednesday 14. June 2006 11:09, Martijn van Oosterhout wrote:
> >IIRC, if you just declare src as type "record" you can select any
> >fields you like. AIUI, declaring a row to be of a specific type is
> > only really important if you plan to return it or pass it to another
> > function.
> 
> I tried: 
> 
> CREATE OR REPLACE FUNCTION get_source_text(integer) RETURNS TEXT AS $$
> DECLARE
> src RECORD;
> mystring TEXT;
> BEGIN
> SELECT (source_id, parent_id, large_text) 
> FROM sources INTO src WHERE source_id = $1;

Why did you put parenthesis there? It looks like you're making a record
within a record. You wouldn't have parenthesis there for a normal
select statement, would you?

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Me And My Database

[Leif B. Kristensen]

On Wednesday 14. June 2006 11:09, Martijn van Oosterhout wrote:
>IIRC, if you just declare src as type "record" you can select any
>fields you like. AIUI, declaring a row to be of a specific type is
> only really important if you plan to return it or pass it to another
> function.

I tried: 

CREATE OR REPLACE FUNCTION get_source_text(integer) RETURNS TEXT AS $$
DECLARE
src RECORD;
mystring TEXT;
BEGIN
SELECT (source_id, parent_id, large_text) 
FROM sources INTO src WHERE source_id = $1;
mystring := src.large_text;
IF src.parent_id <> 0 THEN
mystring := get_source_text(src.parent_id) || ' ' || mystring;
END IF;
RETURN mystring;
END;
$$ LANGUAGE plpgsql;

But now I get this error message: 

Query failed: ERROR: record "src" has no field "large_text" CONTEXT: 
PL/pgSQL function "get_source_text" line 7 at assignment

PostgreSQL version 8.0.8.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Me And My Database

[Martijn van Oosterhout]

On Wed, Jun 14, 2006 at 11:04:19AM +0200, Leif B. Kristensen wrote:
> One question: When I have a function like this:
> 
> CREATE OR REPLACE FUNCTION get_source_text(INTEGER) RETURNS TEXT AS $$
> DECLARE
> src sources%ROWTYPE;



> What do you suggest that I write instead of "SELECT * FROM sources INTO 
> src", when src is defined as sources%ROWTYPE? The table sources is 
> defined as:

IIRC, if you just declare src as type "record" you can select any
fields you like. AIUI, declaring a row to be of a specific type is only
really important if you plan to return it or pass it to another
function.

Have a ncie day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Me And My Database

[Leif B. Kristensen]

On Wednesday 7. June 2006 00:10, Jim C. Nasby wrote:

>Also, the commentary about how MySQL is faster isn't very clear. Are
> you using MySQL as some kind of result cache? When you get to running
> actual concurrent access on the website, you could well find yourself
> very disappointed with the performance of MyISAM and it's table-level
> locking. There's probably also some gains to be had on the PostgreSQL
> performance.

I've rewritten that passage to make it clearer what it's about. I've 
also included the complete table definitions, along with my views and 
functions. The article is still at 
.

One question: When I have a function like this:

CREATE OR REPLACE FUNCTION get_source_text(INTEGER) RETURNS TEXT AS $$
DECLARE
src sources%ROWTYPE;
mystring TEXT;
BEGIN
SELECT * FROM sources INTO src WHERE source_id = $1;
mystring := src.large_text;
IF src.parent_id <> 0 THEN
mystring := get_source_text(src.parent_id) || ' ' || mystring;
END IF;
RETURN mystring;
END;
$$ LANGUAGE plpgsql;

What do you suggest that I write instead of "SELECT * FROM sources INTO 
src", when src is defined as sources%ROWTYPE? The table sources is 
defined as:

CREATE TABLE sources (
source_id   INTEGER PRIMARY KEY,
parent_id   INTEGER NOT NULL REFERENCES sources (source_id),
small_text  VARCHAR(50) NOT NULL DEFAULT '',
large_text  TEXT NOT NULL DEFAULT ''
);

I only need (source_id, parent_id, large_text) in the query. The 
small_text column is largely unused, but holds at most 50 chars.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] table has many to many relationship with itself - how to implement?

[Daniel McBrearty]

Hi all,

I have a table "phrases" that looks like this :

create table phrases(
id serial ,
language integer references langauges(id),
content text
);


Simply a word or phrase in some language.

Now I want to express the concept of a "translation". A translation is
a number of phrases from different languages that are a translation of
each other. There is nothing else to say about a translation - though
it does need to be referencable by other tables, so it needs an ID.

One way to do this is with these two tables:

create table translations (
id serial primary key
);

create table translations_to_phrases (
translation_id integer references translations(id),
phrase_id integer references phrases(id),
primary key (translation_id, phrase_id)
);

Now this actually works as a data structure; the translations table is
a bit odd, having only an id, but that is all we really need.

Can I do this though? can I create a row in translations?

insert into table translations ... insert what?

The other way to do this that I see is to lose the link table
translations_to_phrases, and then make translations

create table translations (
id serial primary key,
phrases integer[]
);


but it seems that I can no longer make postgre aware that the integers
in translations(phrases) are references.

What is the best solution?

Thanks

Daniel

--
Daniel McBrearty
email : danielmcbrearty at gmail.com
www.engoi.com : the multi - language vocab trainer
BTW : 0873928131

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully implementing

[Martijn van Oosterhout]

On Tue, Jun 13, 2006 at 05:23:56PM -0400, Christopher Browne wrote:
> > [3] 
> > http://www.intelligententerprise.com/010327/celko_online.jhtml;jsessionid=NDIHEWXGL4TNKQSNDBNSKHSCJUMEKJVN
> 
> The sample problem in [3] is one that shows pretty nicely a
> significant SQL weakness; it's very painful to build SQL to do complex
> things surrounding cumulative statistics.

I havn't managed to wrap my brain around them yet, but this seems like
something that SQL WINDOW functions would be able to do. For each row
define the window frame to be all the preceding rows, do a SUM() and
divide that over the total. Or perhaps the PERCENT_RANK() function does
this already, not sure.

Mind you, postgres doesn't support them yet, but it's interesting that
it may be possible at all...

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature