[SQL] Simple SQL-syntax

[Fredrik Thunberg]

Hello.
 
I have a very simple sql-question from an 
sql-beginner:
 
Suppose I have Table A looking something like 
this:
 
Index    Text    
NrA
 
And Table B like this:
 
NrA    NrB
 
Then I want to change all occurences of NrA in 
Table A to NrB...
 
I've tried:
 
UPDATE tableA Set tableA.nrA = B.nrB From 
tableB B Where tableA.nrA = B.nrA;
 
But that doesn't seem to work...
 
Best regards
 
/Fredrik Thunberg

[SQL] pg_ctl start hangs

[Christophe Labouisse]

When restarting postgres this morning I notice the pg_ctl start hangs
forever while the database seems to be up and working. Eventually I
found out that the start process involves a "psql -l" which waits
for me to enter a password since I have "local all password" in my
pg_hba.conf. I change back to trust but since I have users with shell
access on the server I'd would like to switch back to "password".

I used the following command to start the database:

/usr/local/bin/pg_ctl start -s -w -l /usr/local/pgsql/errlog -o "-i"

My configuration:

FreeBSD 4.3, Postgresql 7.1.2

-- 
Le cinéma en Lumière : http://www.lumiere.org/
Fingerprint : 4721 651D D309 B302 93E4  5D73 CC52 DF3A E7C7 86CF

---(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] Temp tables being written to disk. Avoidable?

[Tom Lane]

Paul McGarry <[EMAIL PROTECTED]> writes:
> Can I avoid having the temp table written to disk

Not at present --- temp tables are not different from real tables,
except for some naming shenanigans.  So creation of a temp table will
involve some disk hits.

Do you really *need* a temp table, as opposed to writing a
sub-SELECT-in-the-FROM-clause?  ISTM that that feature takes care
of most of the simple notational reasons for wanting a temp table.

regards, tom lane

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

[SQL] Sequential select queries...??

[Mark Mikulec]

Hello,

At first I thought what I was trying to do was simple and could be done
easily - but alas, I've spent way too much time and could not figure out
how to get the results in question.

Let's say I have a table T comprised of  id of type integer, name and
description both of type text.

What i'd like to do is the following:

Select id from T where name = 'bleh';

and

Select id from T where description = 'bleh';

and result both results in the same result set. That is, duplicate id's
if they appear. So then I could do a GROUP BY and a COUNT to see how
many appeared in only one, and how many appeared in both.

Could someone help me? I've tried countless different sql queries, can't
seem to get one to work. If I can just get those duplicate id's in the
query.. then I'd be laughing and then I can complete my task.

Thanks in advance,

Mark


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

[SQL] SQL Statement too long

[Jayson Callaway]

I am using Postgres 7.1.2 under linux with the JDBC drivers and Iam trying
to do an insert into a text field with about a 9K string of text. Does
anyone know why I would be getting an error back that says "The SQL
Statement is too long?

At first I though it was because I was creating the SQL statement as a
string used through the Statement class, so I tried using a
PreparedStatement, but I am still getting the same problem.

--
Jayson Callaway



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

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

[SQL] Strange DISTINCT !

[Jean-Christophe Boggio]

Hello, can someone explain to me why this query is so slow :

select distinct t.idmembre,p.datecrea
from   tmp_stat t,prefs p
where  p.idmembre=t.idmembre
limit  5;

And this one is so fast :

select t.idmembre,p.datecrea
from   (select distinct idmembre from tmp_stat) as t,
   prefs p
where  p.idmembre=t.idmembre
limit 5;
 
(I currently have idmembre as an index on tmp_stat and prefs)

How does DISTINCT work ? Is this a bug or a misconfigured index ?

--
Jean-Christophe Boggio   
[EMAIL PROTECTED]   -o)
Independant Consultant and Developer /\\
Delphi, Linux, Perl, PostgreSQL _\_V


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

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

[SQL] Re: `postgresql.conf' has wrong permissions???

[fimbulvetr]

[EMAIL PROTECTED] (Tom Lane) wrote in message news:<[EMAIL PROTECTED]>...
> <[EMAIL PROTECTED]> writes:
> > FATAL 1:  configuration file `postgresql.conf' has wrong permissions
>  
> > One thing to note is that before I rebooted linux I did this:
> > chmod 755 -R /usr/local/pgsql
> 
> That was a bad idea.  700 is the most permission you should have on the 
> /usr/local/pgsql/data directory.  I am not sure why the code is so picky
> about postgresql.conf and not about the data directory as a whole ---
> but it will refuse to start up if postgresql.conf has more than 744
> permissions.
> 
>   regards, tom lane
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

I experienced this problem as well, I changed permissions on
/usr/local in order to get our samba share to work, and the next day
the database would not start.
744 will allow the postmaster to start again.
Dan

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

[SQL] user defined function question

[Joseph Syjuco]

how do i return a resultset from a user defined function. pls show a simple
structure


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

[SQL] Re: Simple SQL-syntax

[Jeff Eckermann]

UPDATE TableA SET nrA = TableB.nrB WHERE nrA = 
TableB.nrA;
 
UPDATE statements don't take FROM 
clauses.  I don't think they like aliasing, either.

  - Original Message - 
  From: 
  Fredrik 
  Thunberg 
  To: [EMAIL PROTECTED] 
  Sent: Monday, August 20, 2001 6:15 
  AM
  Subject: Simple SQL-syntax
  
  Hello.
   
  I have a very simple sql-question from an 
  sql-beginner:
   
  Suppose I have Table A looking something like 
  this:
   
  Index    Text    
  NrA
   
  And Table B like this:
   
  NrA    NrB
   
  Then I want to change all occurences of NrA in 
  Table A to NrB...
   
  I've tried:
   
  UPDATE tableA Set tableA.nrA = B.nrB From 
  tableB B Where tableA.nrA = B.nrA;
   
  But that doesn't seem to work...
   
  Best regards
   
  /Fredrik 
Thunberg

Re: [SQL] SQL Statement too long

[Josh Berkus]

Jayson,

> I am using Postgres 7.1.2 under linux with the JDBC drivers and Iam
> trying
> to do an insert into a text field with about a 9K string of text.
> Does
> anyone know why I would be getting an error back that says "The SQL
> Statement is too long?

You need to post this question on the pgsql-jdbc list.  It is unlikely
that anyone on this list can help you, as this sounds like a JDBC
problem.

-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] Sequential select queries...??

[Robby Slaughter]

>Hello,

>At first I thought what I was trying to do was simple and could be done
>easily - but alas, I've spent way too much time and could not figure out
>how to get the results in question.

I think there's cause for excitement, because unless I'm interpreting you
incorrectly, it IS easy to do what you want to do:

Use the UNION functionality. If your table T is this

  id   name   description
 -
   1   abc   def
   2   bcd   abc
   3   def   ghi
   4   jkl   bcd
   5   hij   hij

And you do this:

  SELECT id FROM T WHERE name = 'abc' 
 UNION
  SELECT id FROM T WHERE desc = 'abc';

You'll get:

   id
  
1
2

That ought to do it for you!

Hope this helps,
Robby Slaughter

---(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] Sequential select queries...??

[Josh Berkus]

Mark,

> What i'd like to do is the following:
> 
> Select id from T where name = 'bleh';
> 
> and
> 
> Select id from T where description = 'bleh';
> 
> and result both results in the same result set. That is, duplicate
> id's
> if they appear. So then I could do a GROUP BY and a COUNT to see how
> many appeared in only one, and how many appeared in both.

What you want is UNION ALL.  You also want to go out and purchase "SQL
for Smarties" after you finish reading my e-mail.

SELECT id FROM T WHERE name = 'bleh'
UNION ALL
SELECT id FROM T WHERE description = 'bleh';

This gives you both result sets, once right after the other.  If you
didn't want to see duplicate values (i.e. only one instance of each
"id"), you would use simply UNION without the "ALL".

This means that it is possible to get both your desired rowcounts out of
a *single* query, using subselects.  "SQL for Smarties" can help you
learn to build this kind of query.

-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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Finding table constraints

[Stephen Patterson]

Is there any way to find out what constraints have been set on a table
or on columns in a table?

-- 
Stephen Patterson http://home.freeuk.net/s.patterson/
[EMAIL PROTECTED] (remove spam to reply)
ICBM address 54-22-0N 0-28-0W

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

RE: [SQL] Simple SQL-syntax

[Michael Ansley (UK)]

Title: RE: [SQL] Simple SQL-syntax





-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


Probably something like:
 
UPDATE tableA SET tableA.nrA = (SELECT B.nrB FROM tableB B WHERE
tableA.nrA = B.nrA);
 
Let me know...
 
 
MikeA
 
 


- -Original Message-
From: Fredrik Thunberg [mailto:[EMAIL PROTECTED]]
Sent: 20 August 2001 12:16
To: [EMAIL PROTECTED]
Subject: [SQL] Simple SQL-syntax



Hello.
 
I have a very simple sql-question from an sql-beginner:
 
Suppose I have Table A looking something like this:
 
Index    Text    NrA
 
And Table B like this:
 
NrA    NrB
 
Then I want to change all occurences of NrA in Table A to NrB...
 
I've tried:
 
UPDATE tableA Set tableA.nrA = B.nrB From tableB B Where tableA.nrA =
B.nrA;
 
But that doesn't seem to work...
 
Best regards
 
/Fredrik Thunberg



-BEGIN PGP SIGNATURE-
Version: PGPfreeware 6.5.3 for non-commercial use 


iQA/AwUBO4EtanympNV/C086EQKOMwCbBLcLCUJYK5gWvOROp2IbV0+IkC4An1gN
0Bys7MmGO9C6S6o8nt1YPwRQ
=Vhvj
-END PGP SIGNATURE-




_
This e-mail and any attachments are confidential and may also be privileged and/or copyright 
material of Intec Telecom Systems PLC (or its affiliated companies).  If you are not an 
intended or authorised recipient of this e-mail or have received it in error, please delete 
it immediately and notify the sender by e-mail.  In such a case, reading, reproducing, 
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. 
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free 
from computer viruses or other defects. The opinions expressed in this e-mail and any 
attachments may be those of the author and are not necessarily those of Intec Telecom 
Systems PLC. 

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses. 
__

Re: [SQL] Simple SQL-syntax

[Josh Berkus]

Fredrik,

> I have a very simple sql-question from an sql-beginner:

Sure.  Keep in mind that there is a pgsql-newbie list.  Also quite a few
good books on the topic, including: Database Design for Mere Mortals and
SQL for Smarties.

> Then I want to change all occurences of NrA in Table A to NrB...
> 
> I've tried:
> 
  UPDATE tableA Set tableA.nrA = B.nrB
  From tableB B Where tableA.nrA = B.nrA;

This should work.  What error message are you getting?  And what version
of Postgres are you using?

-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] Simple SQL-syntax

[Robby Slaughter]

Fredrik,

Just for future reference, it's generally a good idea to include a complete
table schema
and some sample data for your tables when asking this kind of question. But
I think I know
what you mean and will try to help you.

>Suppose I have Table A looking something like this:
>IndexTextNrA

>And Table B like this:
>NrANrB

>Then I want to change all occurences of NrA in Table A to NrB...

First of all, your syntax isn't quite right. It's

   UPDATE tablename SET value = value WHERE [conditions];

There's no FROM clause in an update statement. And second, you
need to be careful to make sure all your tokens make sense. You
had an extra "B" in the middle of your statement. Based on this:

  UPDATE tableA,tableB SET tableA.NrA = tableB.NrB WHERE tableA.NrA =
tableB.NrB

should achieve the desired result.

Hope this helps!

-Robby Slaughter


---(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] Sequential select queries...??

[Grigoriy G. Vovk]

I don't knoe, may be I don't understand the question, but for me its
looking like UNION statement. For example:

select id from T where name='bleh'
UNION
select id from T where description='bleh';

Aug 14, 20:40 -0400, Mark Mikulec wrote:

> Hello,
>
> At first I thought what I was trying to do was simple and could be done
> easily - but alas, I've spent way too much time and could not figure out
> how to get the results in question.
>
> Let's say I have a table T comprised of  id of type integer, name and
> description both of type text.
>
> What i'd like to do is the following:
>
> Select id from T where name = 'bleh';
>
> and
>
> Select id from T where description = 'bleh';
>
> and result both results in the same result set. That is, duplicate id's
> if they appear. So then I could do a GROUP BY and a COUNT to see how
> many appeared in only one, and how many appeared in both.
>
> Could someone help me? I've tried countless different sql queries, can't
> seem to get one to work. If I can just get those duplicate id's in the
> query.. then I'd be laughing and then I can complete my task.
>
> Thanks in advance,
>
> Mark
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>

my best regards,

Grigoriy G. Vovk


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

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

Re: [SQL] Strange DISTINCT !

[Oleg Lebedev]

I think this is because if you remove duplicates before joining the tables,
then you would join smaller tables, therefore cutting the cost of the join
(and later sorting and removing duplicates).
Say tmp_stat has the size of 1000 and 10 duplicates on the everage for each
distinct tuple. Also, if the size of prefs is 1000, then the cost of joining
(assuming you have no indexing and just use nested loop joins) these two
tables is 1000 x 1000 = 1,000, 000. After this result of the join needs to
be sorted and duplicates removed.
However, if you remove duplicates before joining, the result (cost) of join
is 100 x 1000 = 100, 000, which also reduces the time of sorting and
duplicates removal.
cheers,

Oleg

Jean-Christophe Boggio wrote:

> Hello, can someone explain to me why this query is so slow :
>
> select distinct t.idmembre,p.datecrea
> from   tmp_stat t,prefs p
> where  p.idmembre=t.idmembre
> limit  5;
>
> And this one is so fast :
>
> select t.idmembre,p.datecrea
> from   (select distinct idmembre from tmp_stat) as t,
>prefs p
> where  p.idmembre=t.idmembre
> limit 5;
>
> (I currently have idmembre as an index on tmp_stat and prefs)
>
> How does DISTINCT work ? Is this a bug or a misconfigured index ?
>
> --
> Jean-Christophe Boggio
> [EMAIL PROTECTED]   -o)
> Independant Consultant and Developer /\\
> Delphi, Linux, Perl, PostgreSQL _\_V
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html


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

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

Re: [SQL] Finding table constraints

[Johannes Grødem]

> Is there any way to find out what constraints have been set on a table
> or on columns in a table?

Take a look at the source for psql.

-- 
johs

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

Re: [SQL] Strange DISTINCT !

[Stephan Szabo]

On Mon, 20 Aug 2001, Jean-Christophe Boggio wrote:

> Hello, can someone explain to me why this query is so slow :
> 
> select distinct t.idmembre,p.datecrea
> from   tmp_stat t,prefs p
> where  p.idmembre=t.idmembre
> limit  5;
> 
> And this one is so fast :
> 
> select t.idmembre,p.datecrea
> from   (select distinct idmembre from tmp_stat) as t,
>prefs p
> where  p.idmembre=t.idmembre
> limit 5;
>  
> (I currently have idmembre as an index on tmp_stat and prefs)

I'd suggest looking at the explain output for the two queries,
however, I believe the first query is likely to result in a sort
and unique step and the second is going to probably use the index
to distinct on tmp_stat.  I think that *possibly* if you used
select distrinct p.idmembre, p.datecrea and made an index on (idmembre,
datecrea) on prefs you could possibly see an improvement but I really
don't know.

In addition, these two queries may not do the same thing.  The first
will unique over both membre and datecrea whereas I think the second 
will not, so if you had two prefs rows with the same idmembre and 
datecrea, I believe the first will give one row and the second two.


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

Re: [SQL] Sequential select queries...??

[Ross J. Reedstrom]

Well, a short answerto your direct question would be:

 Select id from T where name = 'bleh'
 
 UNION ALL
 
 Select id from T where description = 'bleh';
 
But since you described what your trying to do, not just how
your trying to do it, doesn't this do it for you?

SELECT id from T where name = 'bleh' and description = 'bleh';

That should give you just the ones where it appears in both.

Ross



On Tue, Aug 14, 2001 at 08:40:50PM -0400, Mark Mikulec wrote:
> Hello,
> 
> At first I thought what I was trying to do was simple and could be done
> easily - but alas, I've spent way too much time and could not figure out
> how to get the results in question.
> 
> Let's say I have a table T comprised of  id of type integer, name and
> description both of type text.
> 
> What i'd like to do is the following:
> 
> Select id from T where name = 'bleh';
> 
> and
> 
> Select id from T where description = 'bleh';
> 
> and result both results in the same result set. That is, duplicate id's
> if they appear. So then I could do a GROUP BY and a COUNT to see how
> many appeared in only one, and how many appeared in both.
> 
> Could someone help me? I've tried countless different sql queries, can't
> seem to get one to work. If I can just get those duplicate id's in the
> query.. then I'd be laughing and then I can complete my task.
> 
> Thanks in advance,
> 
> Mark
> 
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster

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

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

Re: [SQL] user defined function question

[Josh Berkus]

Joseph,

> how do i return a resultset from a user defined function. pls show a
> simple
> structure

As of version 7.1.x, you cannot return rowsets from a function.  This
feature will be available for future versions.

A number of workarounds have been posted to the list in past months.
Browse through and you should find them.

-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

Re: [SQL] Simple SQL-syntax

[Josh Berkus]

Robby,

> There's no FROM clause in an update statement. And second, you
> need to be careful to make sure all your tokens make sense. You
> had an extra "B" in the middle of your statement. Based on this:

Actually, Postgres *does* support UPDATE ... FROM.  I use it quite a
bit.  The syntax can be fussy, however.

The following is perfectly valid in Postgres, although not 100% ANSI-92
kosher:
UPDATE A SET 1 = B.2
FROM B
WHERE A.2 = B.2

Fredrik's problem may be simply the table reference after SET; that's
why I'd like to see his exact parse error message.

-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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

RE: [SQL] user defined function question

[Robert J. Sanford, Jr.]

you can't.

rjsjr

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Joseph Syjuco
> Sent: Tuesday, August 14, 2001 10:34 PM
> To: [EMAIL PROTECTED]
> Subject: [SQL] user defined function question
> 
> 
> how do i return a resultset from a user defined function. pls 
> show a simple
> structure
> 
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 

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

[SQL] Re: Sequential select queries...??

[Jeff Eckermann]

Select id from T where name = 'bleh'
UNION ALL
Select id from T where description = 'bleh';

Will get you the resultset you want, but: I don't believe that you can do a
GROUP BY on it.
If you just want counts, as you describe below, you could do something like:

SELECT 'Only One', (SELECT count (id) from T where name = 'bleh' OR
description = 'bleh') - (SELECT count (id) from T where name = 'bleh' AND
description = 'bleh')
UNION
SELECT 'Both', (SELECT count (id) from T where name = 'bleh' AND description
= 'bleh');
- Original Message -
From: "Mark Mikulec" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, August 14, 2001 7:40 PM
Subject: Sequential select queries...??


> Hello,
>
> At first I thought what I was trying to do was simple and could be done
> easily - but alas, I've spent way too much time and could not figure out
> how to get the results in question.
>
> Let's say I have a table T comprised of  id of type integer, name and
> description both of type text.
>
> What i'd like to do is the following:
>
> Select id from T where name = 'bleh';
>
> and
>
> Select id from T where description = 'bleh';
>
> and result both results in the same result set. That is, duplicate id's
> if they appear. So then I could do a GROUP BY and a COUNT to see how
> many appeared in only one, and how many appeared in both.
>
> Could someone help me? I've tried countless different sql queries, can't
> seem to get one to work. If I can just get those duplicate id's in the
> query.. then I'd be laughing and then I can complete my task.
>
> Thanks in advance,
>
> Mark
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
>


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

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

Re: [SQL] pg_ctl start hangs

[Peter Eisentraut]

Christophe Labouisse writes:

> /usr/local/bin/pg_ctl start -s -w -l /usr/local/pgsql/errlog -o "-i"

Don't use the -w option.  Read the man page.

-- 
Peter Eisentraut   [EMAIL PROTECTED]   http://funkturm.homeip.net/~peter


---(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] split/explode functions

[omid omoomi]

hi ,
Is there any split or explode function which split a whole string to 
seperate parts using any special delimiter ?
TIA
Omid

_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp


---(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] Simple SQL-syntax

[Josh Berkus]

Robby,

> Do you know if, internally there's any difference between
> 
> UPDATE A,B SET a.f1 = b.f1 WHERE a.i = b.i;
> 
>   -and-
> 
> UPDATE A SET a.f1 = b.f2 FROM B WHERE a.i = b.i;
> 
> Just wondering why the SQL standard would be broken in this
> case---and if there's any reason to learn this particular
> aspect of Postgres functionality

Only if you want to use JOINS, sub-selects, and/or aliasing in your
UPDATE statement.  For example, I have a table that caches subtotals of
its detail-level subtable:

UPDATE invoice SET invoice_total = total_of_items
FROM (SELECT invoice_id, sum(item_amount) FROM invoice_items) iit
WHERE iit.invoice_id = invoices.id;

-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

[SQL] quick server c question

[Laurette Cisneros]

Hi all,

My c is rusty and you are all pros!...so...

I want to write a server routine that will return a string so that when "SELECT 
retstr() from tbl;" is executed this string is returned.

What's the best way to do this?

i.e.

create:

CREATE FUNCTION retstr() RETURNS char???
  AS '/tmp/tst.so' LANGUAGE 'c';

c code:

#include postgres.h
char *  
 retstr()
{
do something
return string;
}

Thanks for the help,

-- 
Laurette


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

Re: [SQL] Simple SQL-syntax

[Josh Berkus]

Folks,

Ooops!  Let me correct that example:

 UPDATE invoice SET invoice_total = total_of_items
 FROM (SELECT invoice_id, sum(item_amount) AS total_of_items
FROM invoice_items) iit
 WHERE iit.invoice_id = invoices.id;

Sorry to lead people astray.

-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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] exists

[Joseph Shraibman]

I want to select a boolean if there exists a row in another table that matches this 
one. 
So I did select ..., (select count(*) from table2 where ...) > 0 ...
but that count(*) was taking forever.  I know there is a better way to do it, but 
whenever 
I try to use EXISTS I get a syntax error.  What is the proper way?

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

[SQL] database location question

[Carolyn Lu Wong]

I'm using V6.5.3.

Is there a way to move the database to another directory location
instead in /var/lib/pgsql? As this is on the root file system on the
server and it's using to much spaces.

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

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

Re: [SQL] exists

[Stephan Szabo]

On Mon, 20 Aug 2001, Joseph Shraibman wrote:

> I want to select a boolean if there exists a row in another table that matches this 
>one. 
> So I did select ..., (select count(*) from table2 where ...) > 0 ...
> but that count(*) was taking forever.  I know there is a better way to do it, but 
>whenever 
> I try to use EXISTS I get a syntax error.  What is the proper way?

Hmm, on current sources I can do:
select ..., exists (select * from table2 where ...) from table1;

I don't know if that's new though...


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

[SQL] is it possible to use arrays in plpgsql function??

[Bhuvan A]

hi all,

Is it possible to use arrays of any datatypes in plpgsql function? 

If yes, how??

thankx.

 ==
   Every absurdity has a champion who will defend it.

 ==
Regards,
Bhuvaneswar.


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

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

[SQL] Primary vs Unique Index

[Gonzo Rock]

Why would one need a Primary Key... which can only be declared at table creation if 
one can create a Unique Index post table creation?

ie: I deleted my primary key... is that a big deal? What's the purpose of the Primary 
Key if it's function can be duplicated with another Unique Index?

thanks,




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

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

[SQL] How to execute a system file in procedure?

[Raymond Chui]

I would like execute a system command or my program or my shell
script in procedure. How do I do that?
For example,

CREATE FUNCTION myname() RETURN OPAQUE AS '
BEGIN
IF count(NEW.aColumn) >= 600
THEN RAISE EXCEPTION ''Hello, it is time to re-index the
table.'';
END IF;
RETURN NEW;
END; '
LANGUAGE 'plpgsql';

CREATE TRIGGER AFTER INSERT OR UPDATE ON mytable
FOR EACH ROW EXECUTE PROCEDURE myname();

But I prefer to execute a system command like mail

mail -s "Re-Index" [EMAIL PROTECTED] < `echo "It is time to re-index
the table"`



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

[SQL] Re: [GENERAL] Query Approach and performance

[Martijn van Oosterhout]

On Fri, Aug 17, 2001 at 03:29:36AM -0400, Morgan Curley wrote:
> Hey everyone,
> On average, are multiple simple queries better performance-wise than joins?
> i.e.
> select A.col1 from table1 A
> select B.col2 from table2 B where B.col1 = A.col1
> etc
> 
> vs
> 
> select A.col1, B.col2 from table1 A, table2 B where B.col1 = A.col1
> 
> Are joins better for small/large numbers of tables?
> Is there a diff?

Letting the database do joins is always better than doing them yourself.

> My approach to date has been to keep queries as simple as possible, and 
> when I see a need for complicated joins, I create a view and then do simple 
> queries against that.

I don't think it makes that much difference. Views are precompiled to some
extend.

> Does pg cache queries like Oracle does so that repeated queries don't need 
> to go through the compile phase and run faster? Is this configurable?

Hmm, I've never noticed that query compilation actually took any noticable
time. No, postgres doesn't do that but I'm not convinced it would make a
difference.

HTH,
-- 
Martijn van Oosterhout <[EMAIL PROTECTED]>
http://svana.org/kleptog/
> It would be nice if someone came up with a certification system that
> actually separated those who can barely regurgitate what they crammed over
> the last few weeks from those who command secret ninja networking powers.

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