Re: [SQL] Maybe a Bug, maybe bad SQL

[D'Arcy J.M. Cain]

Thus spake Grant
> Why does everyone reply to the person as well as CC to the list when the
> person is on the list anyhow?

Politeness.  You reply to the list so that others benefit from the discussion
and you copy the sender since some people don't get to their mailing lists
as quickly as their regular mail.  One assumes that they have slightly
more interest in the answer since they asked the question.  Any good email
client will filter out the dup.

-- 
D'Arcy J.M. Cain|  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP)   |  what's for dinner.

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

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

[SQL] Complex Query Help

[Brian C. Doyle]

Hello all,

I have a table of work shifts.  It has
  Table "teams"
  Attribute  | Type  | Modifier
+---+--
  team_id| varchar(50)   |
  team_name  | varchar(100)  |
  location   | varchar(100)  |
  department | varchar(100)  |
  shift  | varchar(100)  |
  start_time | numeric(30,6) |
  end_time   | numeric(30,6) |
  mon| boolean   |
  tue| boolean   |
  wen| boolean   |
  thu| boolean   |
  fri| boolean   |
  sat| boolean   |
  sun| boolean   |
  start_day  | char(3)   |
  end_day| char(3)   |

What I need to be able to do is select team_name where team works between a 
time span ie teams that work between 730 and 1600 how would I do this 
What type of field should i sue for start_time and end_time?



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

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

Re: [SQL] Maybe a Bug, maybe bad SQL

[Bruce Momjian]

> version 7.0.3
> 
> binary_data=# select day, date_part('day',  day) AS day from test;
> day | day
> +-
>  02/04/2000 |   2
>  01/04/2000 |   1
>  03/04/2000 |   3
> (3 rows)
> 
> binary_data=#
> 
> Why does everyone reply to the person as well as CC to the list when the
> person is on the list anyhow?

Good question.  They get the reply faster by sending it to them and the
list, and if someone later wants to reply only to the poster, they have
the email address right there.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(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] how do I find which ...

[Cedar Cox]

On Wed, 21 Mar 2001, datactrl wrote:

> I write some functions inPL/pgSQL. If I drop a function B , which is called
> by function A, and create function B again, then function A won't work

If I recall correctly, this is because function A's "compiled" code refers
to the oid of the now non-existent function B.  Aren't functions
"compiled" once per connection (/ backend process)?  If so, reconnecting
should do it.  (Then again, I might be totally wrong ... :]

> unless I drop function A and re-create again. How about if there are lots
> functions call to function B, is there any way to search system table to
> find out those functions call to A?
> 
> JACK

-Cedar


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

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

Re: [SQL] Maybe a Bug, maybe bad SQL

[Bruce Momjian]

> Note also that it's a mailing list cultural thing: many lists operate
> in a 'post only to the list' mode. Those of us on the pgsql lists do the
> 'list and person' thing, in response to direct questions, for the reasons
> Bruce and D'Arcy point out. Note that by knowing the reasons, one may
> then make informed decisions, like my posting of this message directly
> to the list only, since it's a peripheral issue and multiple people
> are involved in the conversation. It's not uncommon, when debugging
> a particular problem, or discussing implementation of a new feature,
> to have a thread of discussion by CC'ing three or four developers,
> plus the HACKERS list for archiving and general interest.

My mailer would have trouble sending just to the list and not to both. 
To do list-only, the mailing list software would have to set the
Reply-To to be to the list.

Marc had it set up that way a few times, but most didn't like it.  In
fact, the big problem with that setup is that you can't easily reply
just to the poster.

Most mailers have a 'reply to user' and 'reply to group' mode.  Reply to
user goes only to the poster, while reply-to group goes to both.

At least that is how I understand the issue.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

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

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

Re: [SQL] Maybe a Bug, maybe bad SQL

[Ross J. Reedstrom]

On Wed, Mar 21, 2001 at 08:20:22AM -0500, Bruce Momjian wrote:
> > version 7.0.3
> > 
> > binary_data=# select day, date_part('day',  day) AS day from test;
> > day | day
> > +-
> >  02/04/2000 |   2
> >  01/04/2000 |   1
> >  03/04/2000 |   3
> > (3 rows)
> > 
> > binary_data=#
> > 
> > Why does everyone reply to the person as well as CC to the list when the
> > person is on the list anyhow?
> 
> Good question.  They get the reply faster by sending it to them and the
> list, and if someone later wants to reply only to the poster, they have
> the email address right there.

Note also that it's a mailing list cultural thing: many lists operate
in a 'post only to the list' mode. Those of us on the pgsql lists do the
'list and person' thing, in response to direct questions, for the reasons
Bruce and D'Arcy point out. Note that by knowing the reasons, one may
then make informed decisions, like my posting of this message directly
to the list only, since it's a peripheral issue and multiple people
are involved in the conversation. It's not uncommon, when debugging
a particular problem, or discussing implementation of a new feature,
to have a thread of discussion by CC'ing three or four developers,
plus the HACKERS list for archiving and general interest.

In short, it's a cultural thing.

Ross

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

Re: [SQL] Maybe a Bug, maybe bad SQL

[Cedar Cox]

> > Why does everyone reply to the person as well as CC to the list when the
> > person is on the list anyhow?
> 
> Good question.  They get the reply faster by sending it to them and the
> list, and if someone later wants to reply only to the poster, they have
> the email address right there.

And some people have the 'nomail' option set so they don't receive mail
from the list but they can still post...


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

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

[SQL] rows equal

[Marios Moutzouris]

Hello

I need a sql query to a postgresql database which can tell me whether the
rows returned ar equal.

i.e

---
Status
---
Order
---
Collected
--


This is two columns. Both are not equal.

Any help?...without having to write code to fetch the result and go
through etc etc.

thanks
marios



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

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

[SQL] Toggle a Bit type ie not field

[Peter Morgan]

Here's the SP that toggles a Bit datatype. How would I make it comething
like
???
update pcpmanufacturers set def = not (def) where ManufID = @ManufID

--
create Procedure pcp_manufToggle
@ManufID int

as

Set nocount on

declare @S as bit
select @s = def from pcpManufacturers where ManufID = @ManufID

if (@s = 1) begin

 update pcpManufacturers set def=0 where manufID = @ManufID
end
else begin
 update pcpmanufacturers set def=1 where manufid = @manufID
end

TIA

Pete




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

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

[SQL] LEFT JOIN

[Grant Furick]

I am trying to output news.  An article can have an image or not sometimes.
Can someone help me get this to work in Postgres?

 select a.article_id, a.title, a.url, a.synopsis, a.publish_date,
 c.parent_category_id, c.category_id, c.category_name,
 i.server_image_name
 from ((article a JOIN article_category_assoc acs ON a.article_id =
acs.article_id)
 JOIN category c ON c.category_id = acs.category_id)
 LEFT OUTER JOIN (image i JOIN article_image_assoc aia ON i.image_id =
aia.image_id)
 ON a.article_id = aia.article_id
 where i.image_type_id = 1
 and a.live_date <=  #CreateODBCDate(Now())#
 and a.active_ind = TRUE
 and a.status_id = 2
 and c.category_id=#intCategoryID#

Thanks



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

[SQL] FULL JOIN

[Ligia Pimentel]

Does anyone know if it is possible to make a FULL OUTER JOIN in Postgres 7?

Thank you for your help,

Ligia





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

[SQL] Error when selecting rows from a temporary table in ODBC

[Ligia Pimentel]

When we execute this query in psql over linux, we don't get any errors

select sum(valor) as totalCR, count(correlativo) as docsCR, caja into tmp_cr
from transaccion as t, codigocaja as c
where c.codigo = t. codigo
and c.tipomov = 'CR'
group by caja;

select sum(valor) as totalDB, count(correlativo) as docsDB, caja into tmp_db
from transaccion t, codigocaja c
where c.codigo = t. codigo
and c.tipomov = 'DB'
group by caja;

select db.caja, totalcr, totaldb, docsdb, docscr
from tmp_db db, tmp_cr cr
where cr.caja  = db.caja;

But when we execute it on PGAdmin (Windows via ODBC) we get the following
error message: ERROR from backend during clear: 'ERROR:  relation_info:
Relation 2300301 not found

Following is the message in psqlodbc.log,

conn=57295424, query='drop table tmp_cr; drop table tmp_db; select
sum(valor) as totalCR, count(correlativo) as docsCR, caja into tmp_cr from
transaccion as t, codigocaja as c where c.codigo = t. codigo and c.tipomov =
'CR' group by caja; select sum(valor) as totalDB, count(correlativo) as
docsDB, caja into tmp_db from transaccion t, codigocaja c where c.codigo =
t. codigo and c.tipomov = 'DB' group by caja; select db.caja, totalcr,
totaldb, docsdb, docscr from tmp_db db, tmp_cr cr where cr.caja = db.caja; '
NOTICE from backend during send_query: 'NOTICE:  mdopen: couldn't open
tmp_db: No such file or directory
'
NOTICE from backend during send_query: 'NOTICE:  mdopen: couldn't open
tmp_cr: No such file or directory
'
NOTICE from backend during send_query: 'NOTICE:  mdopen: couldn't open
tmp_db: No such file or directory
'
NOTICE from backend during send_query: 'NOTICE:  mdopen: couldn't open
tmp_cr: No such file or directory
'
NOTICE from backend during send_query: 'NOTICE:  mdopen: couldn't open
tmp_cr: No such file or directory
'
NOTICE from backend during send_query: 'NOTICE:  mdopen: couldn't open
tmp_cr: No such file or directory
'
NOTICE from backend during clear: 'NOTICE:  mdopen: couldn't open tmp_db: No
such file or directory
'
NOTICE from backend during clear: 'NOTICE:  mdopen: couldn't open tmp_db: No
such file or directory
'
Command response: 'DROP'
Command response: 'SELECT'
Command response: 'SELECT'
ERROR from backend during clear: 'ERROR:  relation_info: Relation 2300301
not found
'
STATEMENT ERROR: func=SC_execute, desc='', errnum=-1, errmsg='Error while
executing the query (non-fatal)'
 ---
-
 hdbc=57295424, stmt=57310092, result=63909376
 manual_result=0, prepare=0, internal=0
 bindings=0, bindings_allocated=0
 parameters=0, parameters_allocated=0
 statement_type=6, statement='drop table tmp_cr; drop table
tmp_db; select sum(valor) as totalCR, count(correlativo) as docsCR, caja
into tmp_cr from transaccion as t, codigocaja as c where c.codigo = t.
codigo and c.tipomov = 'CR' group by caja; select sum(valor) as totalDB,
count(correlativo) as docsDB, caja into tmp_db from transaccion t,
codigocaja c where c.codigo = t. codigo and c.tipomov = 'DB' group by caja;
select db.caja, totalcr, totaldb, docsdb, docscr from tmp_db db, tmp_cr cr
where cr.caja = db.caja; '
 stmt_with_params='drop table tmp_cr; drop table tmp_db;
select sum(valor) as totalCR, count(correlativo) as docsCR, caja into tmp_cr
from transaccion as t, codigocaja as c where c.codigo = t. codigo and
c.tipomov = 'CR' group by caja; select sum(valor) as totalDB,
count(correlativo) as docsDB, caja into tmp_db from transaccion t,
codigocaja c where c.codigo = t. codigo and c.tipomov = 'DB' group by caja;
select db.caja, totalcr, totaldb, docsdb, docscr from tmp_db db, tmp_cr cr
where cr.caja = db.caja; '
 data_at_exec=-1, current_exec_param=-1, put_data=0
 currTuple=-1, current_col=-1, lobj_fd=-1
 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1
 cursor_name='SQL_CUR036A7B8C'
 QResult
Info ---
 fields=63909344, manual_tuples=0, backend_tuples=0,
tupleField=0, conn=0
 fetch_count=0, fcount=0, num_fields=0, cursor='(NULL)'
 message='(NULL)', command='DROP', notice='NOTICE:  mdopen:
couldn't open tmp_cr: No such file or directory
'
 status=6, inTuples=0
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:
relation_info: Relation 2300301 not found
'

henv=63901552, conn=57295424, status=1, num_stmts=16
sock=63901504, stmts=63901424, lobj_type=-999
 Socket Info ---
socket=180, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=57301892, buffer_out=57305992
buffer_filled_in=3, buffer_filled_out=0, buffer_read_in=2


Thank you for

[SQL] Re: Toggle a Bit type ie not field

[Brent Verner]

On 20 Mar 2001 at 23:07 (-), Peter Morgan wrote:
| Here's the SP that toggles a Bit datatype. How would I make it comething
| like
| ???
| update pcpmanufacturers set def = not (def) where ManufID = @ManufID

update pcpmanufacturers set def=(1-def) where ManufID=@ManufID;

hth.
brent

-- 
 - - - - - - - -=(   d a m o n   b r e n t   v e r n e r   )=- - - - - - - - - 
   c e r t i f i e d   n o s o u r c o p h o b i c

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

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

Re: [SQL] CHAR or VARCHAR

[Peter J. Schoenster]

On 21 Mar 2001, at 18:58, Martin A. Marques wrote:

> two questions.
> When should I use one, and when the other?
> Which is the limit on CHAR(n) and VARCHAR(n)?

Okay, here is my more "let's get this thing working" as opposed to 
"after dedicated study of the matter" opinion (which I hope some 
dedicated studier might correct or confirm or extend) (and, ps, I do 
99% of dev on mysql):

I live under the assumption that indexes on CHAR will be "faster" 
but that CHAR may vary well consume more disk space as I 
believe it pads data to fit the size you created for it.  


This page does not help much:

http://www.postgresql.org/docs/user/datatype1066.htm

This page is interesting:

http://www.postgresql.org/docs/user/sql-createindex.htm

> Tip: Indexes are primarily used to enhance database performance. But
> inappropriate use will result in slower performance. 

I rarely index a table if there are many inserts/updates.

So char vs. varchar  

Peter




---
"Reality is that which, when you stop believing in it, doesn't go
away".
-- Philip K. Dick

---(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] Re: psql win32 version

[datactrl]

Hi, Christopher

Thank you for your help, but the URL
http://www.zeos.dn.ua/download/ipgsql-1.6.2.zip is a broken link. Would you
please check again. Thank you very much.

Jack

- Original Message -
From: "Christopher Sawtell" <[EMAIL PROTECTED]>
Newsgroups: comp.databases.postgresql.sql
Sent: Thursday, March 15, 2001 1:19 PM
Subject: Re: [SQL] Re: psql win32 version


> This one worked a minute ago:-
>
> http://www.zeos.dn.ua/download/ipgsql-1.6.2.zip
>
>
> On Thu, 15 Mar 2001 14:14, datactrl wrote:
> > The URL shown below can not get through. Would you please check again.
>
>
> --
> Sincerely etc.,
>
>  NAME   Christopher Sawtell
>  CELL PHONE 021 257 4451
>  ICQ UIN45863470
>  EMAIL  csawtell @ xtra . co . nz
>  CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
>
>  -->> Please refrain from using HTML or WORD attachments in e-mails to me
> <<--
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl


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

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

Re: [SQL] Error when selecting rows from a temporary table in ODBC

[Hiroshi Inoue]

Ligia Pimentel wrote:
> 
> When we execute this query in psql over linux, we don't get any errors
> 
> select sum(valor) as totalCR, count(correlativo) as docsCR, caja into tmp_cr
> from transaccion as t, codigocaja as c
> where c.codigo = t. codigo
> and c.tipomov = 'CR'
> group by caja;
> 
> select sum(valor) as totalDB, count(correlativo) as docsDB, caja into tmp_db
> from transaccion t, codigocaja c
> where c.codigo = t. codigo
> and c.tipomov = 'DB'
> group by caja;
> 
> select db.caja, totalcr, totaldb, docsdb, docscr
> from tmp_db db, tmp_cr cr
> where cr.caja  = db.caja;
> 
> But when we execute it on PGAdmin (Windows via ODBC) we get the following
> error message: ERROR from backend during clear: 'ERROR:  relation_info:
> Relation 2300301 not found
> 
> Following is the message in psqlodbc.log,
> 
> conn=57295424, query='drop table tmp_cr; drop table tmp_db; select
> sum(valor) as totalCR, count(correlativo) as docsCR, caja into tmp_cr from
> transaccion as t, codigocaja as c where c.codigo = t. codigo and c.tipomov =
> 'CR' group by caja; select sum(valor) as totalDB, count(correlativo) as
> docsDB, caja into tmp_db from transaccion t, codigocaja c where c.codigo =
> t. codigo and c.tipomov = 'DB' group by caja; select db.caja, totalcr,
> totaldb, docsdb, docscr from tmp_db db, tmp_cr cr where cr.caja = db.caja; '

Seems a chained query is issued.
Queries in a chained query are parse and analyzed all
together before the chained query's execution. Therefore
SELECT couldn't see tables which didn't exist before
the execution.

regards,
Hiroshi Inoue

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

Re: [SQL] Maybe a Bug, maybe bad SQL

[Tom Lane]

[EMAIL PROTECTED] (D'Arcy J.M. Cain) writes:
> Thus spake Grant
>> Why does everyone reply to the person as well as CC to the list when the
>> person is on the list anyhow?

> Politeness.  You reply to the list so that others benefit from the discussion
> and you copy the sender since some people don't get to their mailing lists
> as quickly as their regular mail.  One assumes that they have slightly
> more interest in the answer since they asked the question.  Any good email
> client will filter out the dup.

Or, if you don't have such a good email client, see the "eliminatecc"
subscription option offered by our majordomo server.

A further tip: if you don't like getting multiple copies of messages
that are cross-posted to multiple lists, you can tell the majordomo
server
set ALL unique

"help set" will extract a pretty useful description of all the options
majordomo supports (there are a lot of 'em these days).

regards, tom lane

---(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] Error when selecting rows from a temporary table in ODBC

[Tom Lane]

Hiroshi Inoue <[EMAIL PROTECTED]> writes:
> Ligia Pimentel wrote:
>> conn=57295424, query='drop table tmp_cr; drop table tmp_db; select
>> sum(valor) as totalCR, count(correlativo) as docsCR, caja into tmp_cr from
>> transaccion as t, codigocaja as c where c.codigo = t. codigo and c.tipomov =
>> 'CR' group by caja; select sum(valor) as totalDB, count(correlativo) as
>> docsDB, caja into tmp_db from transaccion t, codigocaja c where c.codigo =
>> t. codigo and c.tipomov = 'DB' group by caja; select db.caja, totalcr,
>> totaldb, docsdb, docscr from tmp_db db, tmp_cr cr where cr.caja = db.caja; '

> Seems a chained query is issued.
> Queries in a chained query are parse and analyzed all
> together before the chained query's execution. Therefore
> SELECT couldn't see tables which didn't exist before
> the execution.

More to the point, the later SELECTs are probably being parsed with
regard to the previous versions of tmp_cr and tmp_db that existed at
the start of the line.

This is fixed in 7.1, but in previous releases you'd best split up
that sequence of operations into multiple query submissions.

regards, tom lane

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

Re: [SQL] FULL JOIN

[Tom Lane]

"Ligia Pimentel" <[EMAIL PROTECTED]> writes:
> Does anyone know if it is possible to make a FULL OUTER JOIN in Postgres 7?

In 7.1.

regards, tom lane

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