Re: [SQL] hierarchical order equivalent

[Karel Zak]

On Sun, Mar 18, 2001 at 06:56:17PM -0500, Grant Furick wrote:
> Is there an equivalent way to do this Oracle query in Postgres?
> 
>  Select category_id, parent_category_id, category_name
>  FROM Category
>  START WITH category_id = 6
>  CONNECT BY PRIOR category_id = parent_category_id
>  Order by category_name
> 

 The PostgreSQL hasn't implemented Oracle's "walk tree" feature (yet??? 
-- speculate about it anyone?). If I good remember some discussion about 
hierarchy system in tables was before now, see archives at postgresql.org.

Karel


-- 
 Karel Zak  <[EMAIL PROTECTED]>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

---(end of broadcast)---
TIP 3: 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

[SQL] MultiByte strings

[Tubagus Nizomi]

Hello,

i have an error 
"psql: ERROR:  MultiByte strings (MB) must be enabled to use this function"

where i must enable MultiByte strings (MB)  ??

Nizomi

---(end of broadcast)---
TIP 3: 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

[SQL] postgres DB temporary on NT

[juerg . rietmann]

Hi there

For testing, I have a Microsoft Windows NT Server with IIS 4.0 running in
my DMZ. Currently it is not possible to migrate this server to LinuX. Is
there a way to move an existing postgreSQL 7.0.3 db (with views/sequences)
from a Linux Server to this NT Web server. I have coldfusion on NT, all I
need is the postgres DB or a 100% compatible on NT.

Any ideas ??  Thanks in advance ... jr

PFISTER + PARTNER, SYSTEM - ENGINEERING AG
Juerg Rietmann
Grundstrasse 22a
6343 Rotkreuz
Switzerland

phone: +4141 790 4040
fax: +4141 790 2545
mobile: +4179 211 0315



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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] MultiByte strings

[Richard H]

On 3/19/01, 10:13:40 AM, Tubagus Nizomi <[EMAIL PROTECTED]> wrote 
regarding [SQL] MultiByte strings:

> Hello,

> i have an error
> "psql: ERROR:  MultiByte strings (MB) must be enabled to use this 
function"

> where i must enable MultiByte strings (MB)  ??

Assuming you enabled this at the ./configure stage you need to use the -E 
encoding flag when you run createdb. See the Administrator's Guide / 
Localisation section for details.

- Richard Huxton

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] handling special characters in sql strings

[Markus Wagner]

Hi,

I need to insert and retrieve strings containing special characters (e.
g. "'") and I want a C function to prepare my strings automatically. Is
it ok to just insert a "\" before each special character? And what range
of characters will need this handling?

Thanks,

Markus

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] handling special characters in sql strings

[[EMAIL PROTECTED]]

Markus,

Here are comments from a C function which does what you
need. You won't have the regex functions available, so the
function itself would be useless. You get the idea though.


1. First change backslashed backslashes back to single backslashes. 

2. Find backslash single quote combinations (i.e. "\'") and convert them to 
   single quotes (i.e. "'"). Use a while loop to be sure.
   Now there should be no single quotes preceded by backslashes left.

3. Find all backslashes and convert them to double backslashes.
   Now all characters preceded by a backslash should be in their
   original state, while all backslashed single quotes are
   just single quotes. E.g. "\1ABC'ABC\2\q"

5. Find all single quotes and backslash them.
   (Note: There are no single quotes preceded by a backslash, so
   it is not possible to have a backslash which would hide a
   backslash preceding a single quote.)

That's it.



Troy


> 
> Hi,
> 
> I need to insert and retrieve strings containing special characters (e.
> g. "'") and I want a C function to prepare my strings automatically. Is
> it ok to just insert a "\" before each special character? And what range
> of characters will need this handling?
> 
> Thanks,
> 
> Markus
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] MultiByte strings

[[EMAIL PROTECTED]]

Nizomi,

When you compile postgres, you specify the option at that time.

If you installed postgres as a binary (tar, rpm), you would not 
have seen this option.


Troy



> 
> Hello,
> 
> i have an error 
> "psql: ERROR:  MultiByte strings (MB) must be enabled to use this function"
> 
> where i must enable MultiByte strings (MB)  ??
> 
> Nizomi
> 
> ---(end of broadcast)---
> TIP 3: 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
> 


---(end of broadcast)---
TIP 3: 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: [SQL] postgres DB temporary on NT

[Ross J. Reedstrom]

On Mon, Mar 19, 2001 at 11:23:45AM +0100, [EMAIL PROTECTED] wrote:
> Hi there
> 
> For testing, I have a Microsoft Windows NT Server with IIS 4.0 running in
> my DMZ. Currently it is not possible to migrate this server to LinuX. Is
> there a way to move an existing postgreSQL 7.0.3 db (with views/sequences)
> from a Linux Server to this NT Web server. I have coldfusion on NT, all I
> need is the postgres DB or a 100% compatible on NT.

Install the NT version of PostgreSQL, and do a dump/restore. 
Some instructions are here:

http://www.sevainc.com/PostgreSQL_running_on_NT.html

Worked for me.  Granted, I've never stressed the NT version very much
(just did it as proof of concept), but it does work. (I also pulled the
source code, and built it under cygwin on NT: it's a bit more complicated
than your typical Windows software install, but it works)

Ross

---(end of broadcast)---
TIP 3: 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: [SQL] What do I do with this error?

[Josh Berkus]

Tom, Stephan:

I was hoping that the error would be reproduceable with a simplified
example; however, it looks like I'll have to supply you folks with the
full function an tabledefs.  I hesitated to do so, because the function
involves doing a pattern-matching search based on a complex view linked
to an IN search on a subtable.  It's no doubt this combination of
factors that's making things blow up.

BTW, I found an easy workaround for the problem; I'm pursuing this just
as a beta issue for PgSQL 7.1.

Can either of you supply me with syntax to pg_dump the full function &
view decarations to a text file?  I'm using pgaccess mostly for
development, but it doesn' let me cut-and-paste.

-Josh Berkus

---(end of broadcast)---
TIP 3: 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: [SQL] What do I do with this error?

[Tom Lane]

"Josh Berkus" <[EMAIL PROTECTED]> writes:
> Can either of you supply me with syntax to pg_dump the full function &
> view decarations to a text file?

If you're sure it doesn't require any data, you can do "pg_dump -s dbname"
to get just the database schema.  I'd start with that and then trim away
unrelated stuff, if you feel the urge to trim.

regards, tom lane

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

Re: [SQL] What do I do with this error?

[Tom Lane]

"Josh Berkus" <[EMAIL PROTECTED]> writes:
> I was hoping that the error would be reproduceable with a simplified
> example; however, it looks like I'll have to supply you folks with the
> full function an tabledefs.  I hesitated to do so, because the function
> involves doing a pattern-matching search based on a complex view linked
> to an IN search on a subtable.  It's no doubt this combination of
> factors that's making things blow up.

Possibly, or it could be dependent on platform or configuration factors.
What platform/configure options/locale/etc are you using?

regards, tom lane

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

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] serial type question

[postgresql]

I have a table that I want to add a serial type column. Is there a way 
to add it or do I have to create a new table and insert into it. I have 
experimented with:

insert into newdb (name) select name from olddb order by jobno;

however, pg does not allow the 'order by' during an insert/select

I am on version 7.0.3

Maybe I don't need to do this. What I am trying to accomplish is to 
have PG create job numbers. Today, I only have 2 workstations that 
push jobs into the server and PG tracks the job number. However, I 
have been informed that in the next 6-8 months the number of job 
creation workstations will grow to 8 - 10. 

I would like to migrate to a job number created when the insert is 
done.

Thanks for  your  help.

Ted Petrosky



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

[SQL] request for tutorial on plsql

[Sarathi, Vijai (CAP)]

please send me all the tutorial material or the links where i can get the
notes.


thank u.

Vijai.

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

http://www.postgresql.org/search.mpl

Re: [SQL] What do I do with this error?

[Tom Lane]

BTW, are you certain that your failure case actually does involve a
NULL, and not an empty string?

regression=# select 'foo' ~* null;
 ?column?
--

(1 row)

regression=# select 'foo' ~* '';
ERROR:  regcomp failed with error empty (sub)expression

This is correct according to the POSIX definition of regular expressions
("A (modern) RE is one or more non-empty branches ...").

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Invalid (null) int8, can't convert to float8

[Tim Pizey]

Hi, 

I have been lurking for a while and am very appreciative of the effort 
put in by the answers on this list, and for psql itself. 

I am using Postgres version 7.0.2

I have a table defined thus:

CREATE TABLE "chapter" (
"id" int4 NOT NULL,
"book" int4 NOT NULL,
"sequence" int4 NOT NULL,
"textid_old" int4,
"indexdate" timestamp,
"title" text NOT NULL,
"path" text NOT NULL,
"filename" text NOT NULL,
"part" text,
"lastencached" timestamp,
"longtitle" text,
"layout" int4,
"messageboard" int4,
"textid" int8
);
CREATE UNIQUE INDEX "chapter_id_index" on "chapter" using btree ( "id"
"int4_ops" );
CREATE  INDEX "chapter_book_index" on "chapter" using btree ( "book"
"int4_ops" );
CREATE  INDEX "chapter_sequence_index" on "chapter" using btree (
"sequence"
"int4_ops" );
CREATE UNIQUE INDEX "chapter_textid_index" on "chapter" using btree (
"textid" "int8_ops" );   


If there is a a record with a Null textid in the table then psql reports
the error:

Invalid (null) int8, can't convert to float8

to a query of the form 

select id from chapter where textid = 9057599501;

It does seem as though the textid in the query needs to be large to
produce the error.

Can anyone suggest what is going on?

I have, temporary, fixed the problem by eliminating all records with a
null textid, but 
null is a sensible value, so it will re-occur. 

thanks in advance
timp

-- 
Member of http://www.paneris.org/

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Invalid (null) int8, can't convert to float8

[Tom Lane]

Tim Pizey <[EMAIL PROTECTED]> writes:
> If there is a a record with a Null textid in the table then psql reports
> the error:
> Invalid (null) int8, can't convert to float8
> to a query of the form 
> select id from chapter where textid = 9057599501;

> It does seem as though the textid in the query needs to be large to
> produce the error.

This is actually being interpreted as

select id from chapter where textid = 9057599501::float8;

and then the parser decides it needs to convert textid to float8 and
perform a float8 '=' (which among other things means this query won't
use the index).  This happens because the parser is going to interpret
that undecorated numeric constant as either int4 or float8, and it's
too big for int4, so float8 gets picked.

We have had some discussions about teaching the parser to be smarter
about choosing the type of numeric constants depending on context,
but for now you need to force the issue:

select id from chapter where textid = 9057599501::int8;

If you want the index to be used then you'd better do this all the
time, not only for values that are too big to be int4.

BTW, the inability to convert an int8 NULL to float8 is a bug;
it's fixed in 7.1.

regards, tom lane

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

http://www.postgresql.org/search.mpl

Re: [SQL] What do I do with this error?

[Tom Lane]

"Josh Berkus" <[EMAIL PROTECTED]> writes:
> Tom,
>> regression=# select 'foo' ~* '';
>> ERROR:  regcomp failed with error empty (sub)expression
>> 
>> This is correct according to the POSIX definition of regular
>> expressions
>> ("A (modern) RE is one or more non-empty branches ...").

> That sounds like what I'm seeing!  And now that you point it out, there
> is a bug in the PHP that calls this function that passes an empty string
> ('') instead of a NULL.  Thanks!

> Any possibility that that error message could be made more informative?
> (i.e. ERROR:  regcomp failed due to empty string in regular expression)

You're right, this isn't a very transparent message.  Perhaps

ERROR: Invalid regular expression (empty expression or subexpression)

Comments, better wordings?

regards, tom lane

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

http://www.postgresql.org/search.mpl

Re: [SQL] serial type question

[Stephan Szabo]

On Mon, 19 Mar 2001, postgresql wrote:

> I have a table that I want to add a serial type column. Is there a way 
> to add it or do I have to create a new table and insert into it. I have 
> experimented with:
> 
> insert into newdb (name) select name from olddb order by jobno;
> 
> however, pg does not allow the 'order by' during an insert/select
> 
> I am on version 7.0.3

The type of query above seems to work in 7.1, but...

> Maybe I don't need to do this. What I am trying to accomplish is to 
> have PG create job numbers. Today, I only have 2 workstations that 
> push jobs into the server and PG tracks the job number. However, I 
> have been informed that in the next 6-8 months the number of job 
> creation workstations will grow to 8 - 10. 
> 
> I would like to migrate to a job number created when the insert is 
> done.

Because serial only is a column with a default gathered from a sequence,
if you can just use the jobno's from the old table, I'd say you
just might want to make it, load the jobno's you already have and set the
sequence value higher than those, new rows will get new automatic jobno's
and the old ones will keep their values.



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] What do I do with this error?

[Josh Berkus]

Tom,

> regression=# select 'foo' ~* '';
> ERROR:  regcomp failed with error empty (sub)expression
> 
> This is correct according to the POSIX definition of regular
> expressions
> ("A (modern) RE is one or more non-empty branches ...").

That sounds like what I'm seeing!  And now that you point it out, there
is a bug in the PHP that calls this function that passes an empty string
('') instead of a NULL.  Thanks!

Any possibility that that error message could be made more informative?
(i.e. ERROR:  regcomp failed due to empty string in regular expression)

-Josh Berkus



__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

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

Re: [SQL] What do I do with this error?

[Josh Berkus]

Tom,

> You're right, this isn't a very transparent message.  Perhaps
> 
>  ERROR: Invalid regular expression (empty expression or
> subexpression)
> 
> Comments, better wordings?

Nope.  That's great.

-Josh Berkus


__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Postgres & XML

[Najm Hashmi]

Hi all, I was just wondering if there is  way or some sort of utility to
incorporate XML in postgrres.
Thanks in advance.


begin:vcard 
n:Hashmi;Najm
x-mozilla-html:FALSE
org:Mondo-Live.com;www.flipr.com
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
x-mozilla-cpt:;6144
fn:Najm Hashmi
end:vcard



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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Postgres & XML

[clayton cottingham]

Najm Hashmi wrote:
> 
> Hi all, I was just wondering if there is  way or some sort of utility to
> incorporate XML in postgrres.
> Thanks in advance.
> 
>   
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html



ive done some simple processing using tables and xml::xslt

basically generating a xml page based on db info and then parsing to
html
using the xslt's

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

http://www.postgresql.org/search.mpl

Re: [SQL] Postgres & XML

[Peter Eisentraut]

Najm Hashmi writes:

> Hi all, I was just wondering if there is  way or some sort of utility to
> incorporate XML in postgrres.

This question is about as generic as "Is there a way to incorporate ASCII
in Postgres?"  Depending on what you want you might find Cocoon useful.
See .

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Manual Trigger Creation

[Josh Berkus]

Tom, Stephan. List folks:

I'm having a great deal of trouble with the automated constraint
triggers as implemented in 7.1 beta 3. I find that if I establish a
foriegn key through the CREATE TABLE statement, things start to blow up
if I modify any of the tables involved, and there's no easy way to drop
and re-create the foriegn key.  (PostgreSQL isn't alone in this ... MS
SQL Server is a headache if you want to tinker with the relational
structure).

 As such, I'd like to manually create my own foriegn key triggers
instead, once I'm done tinkering with the DB structure.  Can anyone
provide me with an example of this?  I figure I'll need 5 triggers for
each key:

1. ON UPDATE trigger on Reference List
2. ON DELETE trigger on Reference List
3. ON UPDATE trigger on data table
4. ON DELETE trigger on data table
5. ON INSERT trigger on data table

Am I on the right track?

-Josh



__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Manual Trigger Creation

[Stephan Szabo]

On Mon, 19 Mar 2001, Josh Berkus wrote:

> Tom, Stephan. List folks:
> 
>   I'm having a great deal of trouble with the automated constraint
> triggers as implemented in 7.1 beta 3. I find that if I establish a
> foriegn key through the CREATE TABLE statement, things start to blow up
> if I modify any of the tables involved, and there's no easy way to drop
> and re-create the foriegn key.  (PostgreSQL isn't alone in this ... MS
> SQL Server is a headache if you want to tinker with the relational
> structure).

Yeah, it doesn't play nice with alter table at all. :(
Actually, for recreating -- All you really need to do is kill the
three triggers that it creates (drop trigger should work) and use
alter table to add them again.

>As such, I'd like to manually create my own foriegn key triggers
> instead, once I'm done tinkering with the DB structure.  Can anyone
> provide me with an example of this?  I figure I'll need 5 triggers for
> each key:
> 
> 1. ON UPDATE trigger on Reference List
> 2. ON DELETE trigger on Reference List
> 3. ON UPDATE trigger on data table
> 4. ON DELETE trigger on data table
> 5. ON INSERT trigger on data table

You could do this (4 is unnecessary and 3 and 5 can be combined), although
I think you might be better off using alter table add constraint to do
that.


---(end of broadcast)---
TIP 3: 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: [SQL] Manual Trigger Creation

[Josh Berkus]

Stephan,

> Yeah, it doesn't play nice with alter table at all. :(
> Actually, for recreating -- All you really need to do is kill the
> three triggers that it creates (drop trigger should work) and use
> alter table to add them again.

How can I drop them if they are  triggers?  I've been doing
that by editing pg_trigger, but that just got me into a system table
mess that it took 2 hours to fix ... and lost me half my foriegn keys to
boot.

Is there, perhaps, a way I can name my constraints in the original
CREATE TABLE statement?  Aha! I see ... I never noticed the optional
[CONSTRAINT constraint_name] phrase before.  'S what I get for crossing
over from Transact-SQL without retraining!

> You could do this (4 is unnecessary and 3 and 5 can be combined),
> although
> I think you might be better off using alter table add constraint to
> do
> that.

I'm interested in the approach for another reason.  I have a number of
tables that must match a NON-UNIQUE value in a reference table, and thus
I'd like to test them against a query or view.

-Josh Berkus


__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

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

http://www.postgresql.org/search.mpl

[SQL] count() and multiple tables

[Joseph Shraibman]

I want to select all the entries from d that have at least one
corresponding entry in u that meets my conditions.  The problem is that
count(*) is returning the number of corresponding entries in u, and I
want only the number of entries in d.  How do I do this?


create table d(
   id int  primary key,
  status int default 1
);

create table a(
   key int primary key,
   status int default 1
);

create table u(
dkey int not null,
akey int not null,
b bool DEFAULT false,
status int default 1,
primary key (dkey, akey) 
);

insert into d values (1, 2);

insert into a values (1, 3);
insert into a values (2, 3);
insert into a values (3, 3);

insert into u values(1,1,false,2);
insert into u values(1,2,false,1);
insert into u values(1,3,false,2);

select count(*) from d where status = 2 and d.id = u.dkey and u.status =
2 and not u.b and u.akey = a.key and a.status = 3;

/* that returns 2 when I want it to return 1 */

drop table d;
drop table a;
drop table u;


-- 
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio.  http://www.targabot.com

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Manual Trigger Creation

[Tom Lane]

"Josh Berkus" <[EMAIL PROTECTED]> writes:
>As such, I'd like to manually create my own foriegn key triggers
> instead, once I'm done tinkering with the DB structure.  Can anyone
> provide me with an example of this?

pg_dump some tables with foreign keys ...

regards, tom lane

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

http://www.postgresql.org/search.mpl

Re: [SQL] Manual Trigger Creation

[Stephan Szabo]

On Mon, 19 Mar 2001, Josh Berkus wrote:

> Stephan,
> 
> > Yeah, it doesn't play nice with alter table at all. :(
> > Actually, for recreating -- All you really need to do is kill the
> > three triggers that it creates (drop trigger should work) and use
> > alter table to add them again.
> 
> How can I drop them if they are  triggers?  I've been doing
> that by editing pg_trigger, but that just got me into a system table
> mess that it took 2 hours to fix ... and lost me half my foriegn keys to
> boot.

You can use the real trigger name (tgname) and drop trigger, but you
need to double quote the name:
drop trigger "RI_ConstraintTrigger_" on ;
... (for all three)

> Is there, perhaps, a way I can name my constraints in the original
> CREATE TABLE statement?  Aha! I see ... I never noticed the optional
> [CONSTRAINT constraint_name] phrase before.  'S what I get for crossing
> over from Transact-SQL without retraining!

> > You could do this (4 is unnecessary and 3 and 5 can be combined),
> > although
> > I think you might be better off using alter table add constraint to
> > do
> > that.
> 
> I'm interested in the approach for another reason.  I have a number of
> tables that must match a NON-UNIQUE value in a reference table, and thus
> I'd like to test them against a query or view.

Well, if you're going against non-unique values then the referential 
actions aren't really meaningful anymore [you'd have to look at
match partial's semantics, probably], and you'd probably need to
reimplement that part.
In general you can add the constraint triggers the same way pg_dump does.


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

Re: [SQL] count() and multiple tables

[Josh Berkus]

Joseph,

SImple as pie (e.g., easy on your 100th one):

> select count(*) from d where status = 2 and d.id = u.dkey and
> u.status =
> 2 and not u.b and u.akey = a.key and a.status = 3;

Count(*) will always count the number of records in the largest table in
your join.  If you want the number of records in a specific table, then
you have to make sure that that table is the only on in your FROM
clause, and reference all other tables in WHERE:

select count(*) from d
where id IN (select u.dkey from u, a
 where u.akey = a.key
AND u.status = 2 and not u.b
and a.status = 3);

-Josh Berkus

__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Select very slow...

[Joseph Shraibman]

David Olbersen wrote:
> 
> On Sun, 18 Mar 2001, Fernando Eduardo B. L. e Carvalho wrote:
> 
> >   select  p.city,count(*) from sales s, person p where s.doc = p.doc
> > group by p.city;
> >
> >Anyone help-me?
> 
> 1: VACUUM ANALYZE sales
>VACUUM ANALYZE person;
> 
> 2: That 'count(*)' is going to be slow.
>Try counting a column that's indexed (p.doc might work?)
> 
I don't think that is true.


--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio.  http://www.targabot.com

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

http://www.postgresql.org/search.mpl

Re: [SQL] count() and multiple tables

[Joseph Shraibman]

Stephan Szabo wrote:
> 
> On Mon, 19 Mar 2001, Joseph Shraibman wrote:
> 
> > I want to select all the entries from d that have at least one
> > corresponding entry in u that meets my conditions.  The problem is that
> > count(*) is returning the number of corresponding entries in u, and I
> > want only the number of entries in d.  How do I do this?
> >
> >
> > create table d(
> >id int  primary key,
> >   status int default 1
> > );
> >
> > create table a(
> >key int primary key,
> >status int default 1
> > );
> >
> > create table u(
> > dkey int not null,
> >   akey int not null,
> >   b bool DEFAULT false,
> > status int default 1,
> >   primary key (dkey, akey)
> > );
> >
> > insert into d values (1, 2);
> >
> > insert into a values (1, 3);
> > insert into a values (2, 3);
> > insert into a values (3, 3);
> >
> > insert into u values(1,1,false,2);
> > insert into u values(1,2,false,1);
> > insert into u values(1,3,false,2);
> >
> > select count(*) from d where status = 2 and d.id = u.dkey and u.status =
> > 2 and not u.b and u.akey = a.key and a.status = 3;
> 
> And postgres tries to be helpful again... :(  [I *really* dislike this
> adding to from list thing]  Technically the above should be illegal
> because no from list contains u or a.  Postgres is adding them to the
> from list for you.
> 
I get the same result if I do:
select count(d.id) from d where status = 2 and d.id = u.dkey and
u.status = 2 and not u.b and u.akey = a.key and a.status = 3;

So in standard SQL all the tables you join accross are required to be in
the FROM?

> I think you want something like (untested):
> select count(*) from d where status=2 and
> exists (
>  select * from u, a where u.dkey=d.id and u.status=2 and
>   no u.b and u.akey=a.key and a.status=3
> );

That works, but I thought there might be a better way because it looks
like that will get all the data out of the table and throw it away right
after.

-- 
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio.  http://www.targabot.com

---(end of broadcast)---
TIP 3: 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: [SQL] count() and multiple tables

[Stephan Szabo]

On Mon, 19 Mar 2001, Joseph Shraibman wrote:

> I want to select all the entries from d that have at least one
> corresponding entry in u that meets my conditions.  The problem is that
> count(*) is returning the number of corresponding entries in u, and I
> want only the number of entries in d.  How do I do this?
> 
> 
> create table d(
>id int  primary key,
>   status int default 1
> );
> 
> create table a(
>key int primary key,
>status int default 1
> );
> 
> create table u(
> dkey int not null,
>   akey int not null,
>   b bool DEFAULT false,
> status int default 1,
>   primary key (dkey, akey) 
> );
> 
> insert into d values (1, 2);
> 
> insert into a values (1, 3);
> insert into a values (2, 3);
> insert into a values (3, 3);
> 
> insert into u values(1,1,false,2);
> insert into u values(1,2,false,1);
> insert into u values(1,3,false,2);
> 
> select count(*) from d where status = 2 and d.id = u.dkey and u.status =
> 2 and not u.b and u.akey = a.key and a.status = 3;

And postgres tries to be helpful again... :(  [I *really* dislike this
adding to from list thing]  Technically the above should be illegal
because no from list contains u or a.  Postgres is adding them to the
from list for you.

I think you want something like (untested):
select count(*) from d where status=2 and
exists (
 select * from u, a where u.dkey=d.id and u.status=2 and
  no u.b and u.akey=a.key and a.status=3
);


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Invalid (null) int8, can't convert to float8

[Tim Pizey]

Thanks a lot Tom, 

Tom Lane wrote:
> 
> Tim Pizey <[EMAIL PROTECTED]> writes:
> We have had some discussions about teaching the parser to be smarter
> about choosing the type of numeric constants depending on context,
> but for now you need to force the issue:
> 
> select id from chapter where textid = 9057599501::int8;
> 
> If you want the index to be used then you'd better do this all the
> time, not only for values that are too big to be int4.
> 
This problem is actually happening through jdbc, using
PreparedStatements, 
shouldn't jdbc be able to deal with casting transparently?

I am calling this with a string "chapter = ?" which automatically quotes 
and escapes string values correctly.

Hope this isn't too wrong
timp


-- 
Member of http://www.paneris.org/

---(end of broadcast)---
TIP 3: 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: [SQL] count() and multiple tables

[Stephan Szabo]

> > And postgres tries to be helpful again... :(  [I *really* dislike this
> > adding to from list thing]  Technically the above should be illegal
> > because no from list contains u or a.  Postgres is adding them to the
> > from list for you.
> > 
> I get the same result if I do:
> select count(d.id) from d where status = 2 and d.id = u.dkey and
> u.status = 2 and not u.b and u.akey = a.key and a.status = 3;
> 
> So in standard SQL all the tables you join accross are required to be in
> the FROM?

Basically, yes.  It's more complicated than that probably (what isn't in
SQL), but that's the general idea.

Postgres assumes your query is
select count(*) from d,u,a ...

Because d.id was guaranteed to be unique, you might be able to 
count(distinct d.id) and get the result you want. [I think the 
subquery is a nicer way of representing it]



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

Re: [SQL] VACUUM kills Index Scans ?!

[Joseph Shraibman]

Gerald Gutierrez wrote:
> 
> >There is an undocumented little factoid here: CREATE INDEX will update
> >(some of) the planner stats, but only if it finds some data in the
> >table.  CREATE INDEX on an empty table leaves the initial default
> >numbers alone.  This may be contributing to your confusion, but it was
> >deemed necessary ...
> 
> I understand now; it makes sense. I'll be using a number of tables that are
> initially very small, perhaps 5 or 10 records. But I expect that the tables
> will grow very quickly to several tens (or hundreds) of thousands of
> records. It seems reasonable to me that the table should then be set up to
> use index scan right from the beginning so that as the table grows the
> index scan will become more useful. Thus, the correct sequence for me is
> probably:
> 
> > CREATE TABLE
> > CREATE INDEX
> > load data
> 

no, the correct sequence is to create the index last, which will create
statistics that will tell postgres if it really wants to use an index or
not.  Don't try and second guess postgres.

Even better do a VACUUM ANALYZE.


-- 
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio.  http://www.targabot.com

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] count() and multiple tables

[Josh Berkus]

Stephan, Joseph,

> Because d.id was guaranteed to be unique, you might be able to 
> count(distinct d.id) and get the result you want. [I think the 
> subquery is a nicer way of representing it]

Plus a several SQL implementations don't implement the DISTINCT until
after the COUNT, reuslting in a still-inflated (and sometimes varying!)
COUNT.  It won't work properly in T-SQL or MS Office SQL, for example.

-Josh Berkus


__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] serial type question

[Richard Huxton]

postgresql wrote:
> 
> I have a table that I want to add a serial type column. Is there a way
> to add it or do I have to create a new table and insert into it. I have
> experimented with:
> 
> insert into newdb (name) select name from olddb order by jobno;
> 
> however, pg does not allow the 'order by' during an insert/select

Doesn't make sense on an insert - you want an alter table followed by an
update. An insert will add new rows, not just add values to a column.

> I am on version 7.0.3
> 
> Maybe I don't need to do this. What I am trying to accomplish is to
> have PG create job numbers. Today, I only have 2 workstations that
> push jobs into the server and PG tracks the job number. However, I
> have been informed that in the next 6-8 months the number of job
> creation workstations will grow to 8 - 10.

A serial type is basically nothing more than a sequence with a column
that uses the sequence as a default value. There's more on this in the
docs (a couple of lines in the notes I wrote off
techdocs.postgresql.org) but the simplest way to see how it works is to
define a table foo with a serial in it and do a \d or pg_dump it to see
how it works.

> I would like to migrate to a job number created when the insert is
> done.

Once you see how it works, pg_dump the database, edit the file and
re-import the data. Nice clean solution and easy to cope with if
something goes wrong.

- Richard Huxton

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

http://www.postgresql.org/users-lounge/docs/faq.html