Re: [SQL] hierarchical order equivalent

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 b

[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) ?? Nizomi ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriat

[SQL] postgres DB temporary on NT

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

Re: [SQL] MultiByte strings

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 ./co

[SQL] handling special characters in sql strings

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 ---

Re: [SQL] handling special characters in sql strings

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

Re: [SQL] MultiByte strings

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 m

Re: [SQL] postgres DB temporary on NT

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/

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

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

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

"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

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

"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 se

[SQL] serial type question

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

[SQL] request for tutorial on plsql

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?

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 def

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

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, "sequ

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

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 que

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

"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 sou

Re: [SQL] serial type question

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 d

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

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 i

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

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___

[SQL] Postgres & XML

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 en

Re: [SQL] Postgres & XML

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)--

Re: [SQL] Postgres & XML

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

[SQL] Manual Trigger Creation

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 eas

Re: [SQL] Manual Trigger Creation

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 mo

Re: [SQL] Manual Trigger Creation

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 t

[SQL] count() and multiple tables

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

Re: [SQL] Manual Trigger Creation

"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 ... regard

Re: [SQL] Manual Trigger Creation

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

Re: [SQL] count() and multiple tables

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 nu

Re: [SQL] Select very slow...

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 g

Re: [SQL] count() and multiple tables

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 > > wan

Re: [SQL] count() and multiple tables

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.

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

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 te

Re: [SQL] count() and multiple tables

> > 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

Re: [SQL] VACUUM kills Index Scans ?!

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 confus

Re: [SQL] count() and multiple tables

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

Re: [SQL] serial type question

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 'ord