[SQL] problem with casting
I have a table that has a 'date' column. In psql I tried to do a select proofdate::text from openjobs where jobno = '10625'; responce: can not cast type 'date' to 'text' Did i miss-read this section. I thought I could cast the date to text. has anyone seen this or am I missreading the docs? thanks Ted
[SQL] problem with dates
This is a rather generic question about "date" and "time". I seem to be beating my head on the wall. I was trying to use a set up a table with a 'date' and 'time' field. I wanted to keep the two separate. Can someone explain if there is a difference between a time field and a timestamp. I don't mean the visual date and time as a single element. I mean in concept. I have been pouring through the docs and it appears from my reading that a 'date' field is supposed to act like the date portion of a timestamp. But it doesn't and I just don't understand why. if there is some document somewhere that explains this just point me to it. Thanks again. Ted
[SQL] syntax prob
I am away from my server for the weekend and I need a little help. when doing updates of multiple fields there commas between the elements? I mean update table set cname = 'Bill', caddress = '2nd floor' where acode = 'AVAN'; I refer to the space between 'Bill' and caddress. if I could get to the server I would just try it. what happens if you have only 1 field to update and you add a comma, like this update table set cname = 'Bill', where acode = 'AVAN'; I am trying to concatenate an update string and I would love to not have to worry about the comma. I guess I could figure out how many things have changed then add commas... but I could also wish for it to be easier. Ted
[SQL] underscore problem
Is there anywhere in the docs that says we should not use an underscore in a table name? table FC_Client_Info exists with a record where jobno 1234 has info: select acode from FC_Client_Info where jobno = '1234'; fails with a warning that fc_client_info does not exist notice the lower case. If I quote the table select acode from "FC_Client_Info" where jobno = '1234'; the select works fine What's the problem here? This should be easy. Ted
[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 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] creating "job numbers"
I have been working with PG for about 2 months now. I am creating a job tracking system for my company. I have written a front end on the workstations (all macintoshes) that seems to be working quite well. However, I have a problem with a concept. In my current setup I have only one workstation that is actually inputting new jobs. So, I took the expedient way to create the job number. Ask PG to count the rows, add a magic number and insert this data. This all happens in one connection. What are the odds of two people hitting the db at the same time? In the current set up nil. There is only one entry computer. I want to change the system to use a job number generated by PG. I created a test table and I am playing with inserting and the sequence function works great. However, I am at a loss of how to pick up this next (last) job. I have read the docs and I still am confused. I can not first ask with the number will be, and asking for the previous oid after the fact can also lead to the same problem. so that leaves me with, 1 ask for that last oid from this workstation ip, or 2 since a job is inserted with data, I could do a select of this data after the insert (not very elegant). How are you professionals handling this problem? I like the ability to insert and have the system give me the number. As I grow into more workstations inputting the jobs I won't have to worry about chasing the next highest number. Thanks, Ted P. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] creating "job numbers"
Jan,
Thanks, I must be missing something here. Bear with me, I am
trying to form an intelligent question.
Using the serial data type... I don't understand when the backend
would skip a number.
If the db is assigning the number with the insert, then if two (or
more) clients are trying to insert into the db at the exact same time,
only those that are successful should get a number. I am trying to
envision a situation where two clients hit at the same time and
because of problem with the insert, one aborts and the serial data
number is skipped. I would have assumed that the aborted insert is
just skipped no harm done.
I guess that I could then break the insert down into two parts. Insert
only the client name in order to grab the next job number then
update the row. I think I should be able to reduce the number of
aborted inserts to 1 in a couple of thousand were the abort is due to
client input error. (I wanted to say in a million but that just seemed
too far fetched) The only reason that I can think of that would cause
an abort would be that data was currupted in transit to the db. Or the
front end crashed and sent bad data.
Is this reasonable? I feel that people with more expierence might
shed a little light here.
Thanks for your time.
Ted
-Original Message-
From: Jan Wieck <[EMAIL PROTECTED]>
To: postgresql <[EMAIL PROTECTED]>
Date: Thu, 22 Mar 2001 14:48:19 -0500 (EST)
Subject: Re: [SQL] creating "job numbers"
>
> Two possible ways:
>
> 1. If you can live with gaps in the job numbers, you can use
> the serial data type. That is, you create your table like
>
> CREATE TABLE jobs (
> job_id serial PRIMARY KEY,
> employee_id integer REFERENCES staff,
> ...
> );
>
> Now your application can INSERT a row not specifying an
> explicit value for the job_id like
>
> INSERT INTO jobs (employee_id, ...)
> VALUES (4711, ...);
>
> and reading the PostgreSQL assigned job_id back with
>
> SELECT currval('jobs_job_id_seq');
>
> Even if there are other processes doing the same
> concurrently, the assigned job_id is guaranteed to be
> unique and the currval() given back by your database
> connection isn't affected by it.
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
RE: [SQL] creating "job numbers"
Thanks Micheal,
I guess what I am trying to figure out is, 'how important is this in
reality, in a working setup'. I know that at times I want things to be
perfect, and it is not necessary.
I was thinking about how I do this manually.
I would take a sheet of paper, write numbers down the left column.
make a couple of sheets with increasing numbers. As jobs enter the
shop, I populate the meta data. If a job gets killed, it is marked as
dead but the number does not go away. It could be used again.
What I really need to do is:
Insert "blank" into openjobs;
SELECT currval('jobs_job_id_seq');
UPDATE openjobs "job meta data" where jobno= "returned number
from prev select"
Even if this job fails, I have the empty row. I can choose to put a job
in there later if I want. Or leave it blank. I feel that all the numbers
have to be accounted for as used or null. But it should not skip.
Thanks,
Ted
-Original Message-----
From: Michael Ansley
<[EMAIL PROTECTED]>
To: 'postgresql' <[EMAIL PROTECTED]>
Date: Fri, 23 Mar 2001 13:22:09 -
Subject: RE: [SQL] creating "job numbers"
> The procedure is something more like this:
>
> The first backend grabs the sequence number, say 1, and tries to
> insert.
> Whether or not this insert succeeds, the number 1 is gone from
the
> sequence.
> Then backend two tries to insert, and grabs the number 2 from the
> sequence.
> After this, the first backend rolls back, and doesn't insert. The next
> backend will get number 3. And so number 1 is lost. If the
session is
> caching sequence number, then even more numbers may be lost.
Anyway,
> the
> principle is that sequences do not roll back. Once you have a
number,
> it's
> gone, whether or not you use it. This is because keeping track of
> numbers
> to keep them contiguous is a time-consuming exercise, and
causes
> locking
> problems. So, the principle is that a sequence will always give you
a
> distinct number, but not necessarily the next number.
>
> Hope this helps...
>
>
> MikeA
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] serial type; race conditions
How does currval work if you are not inside a transaction. I have been experimenting with inserting into a table that has a sequence. If the insert fails (not using a transaction) because of bad client input then the next insert gets the proper next number in the sequence. given sequence 1,2,3,4,5 exists insert into table date 1/111/01 (obviously wrong) insert fails... try again with good data, insert succeeds and gets number 6 in the sequence. i'm getting what I want. A sequence number that does not increment on a failed insert. However, how do I get the assigned sequence number with currval when I am not using a transaction? What happens when multiple users are inserting at the same time? I am trying to create a sequence with out any "missing" numbers. If there is a failure to insert, and a sequence number is "taken". I want the empty row. Thanks, it is getting clearer Ted -Original Message- From: Bruce Momjian <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Date: Mon, 26 Mar 2001 16:47:37 -0500 (EST) Subject: Re: [SQL] serial type; race conditions > > Hi, > > > > I'm using serial fields to generate IDs for almost all object in my > > database. I insert an empty row, get the CURRVAL() of the sequence > > and then update to that value. > > > > I had understood (and now, I can't find the reference to back this > up) > > that serial is implemented in such a way that race conditions between > > DB connections can't happen. > > > > Is this true? > > Safe. See FAQ item. currval is for your backend only. > > -- > 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 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) ---(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] serial type; race conditions
Micheal,
Thanks, I was thinking that something like this should work.
However, I am having a problem with it. here is what I am doing.
begin;
INSERT INTO table (serial_col) (SELECT nextval('seq_serial_col'));
commit;
first I needed to add parens around the select statement. The
results are not what I expected. If I executed this a few times, when I
looked at the table what I saw was:
serial_col | seq_serial_col
1 | 2
3 | 4
5 | 6
etc.
I had thought I would do the insert, grab the currval of transaction
passing it back to my app. commit, then do an update. I can not
seem to get the seq to work.
Ted
-Original Message-
From: Michael Fork <[EMAIL PROTECTED]>
To: postgresql <[EMAIL PROTECTED]>
Date: Thu, 29 Mar 2001 10:04:46 -0500 (EST)
Subject: Re: [SQL] serial type; race conditions
> If you are looking to have every number accounted for, something
like
> this
> will work:
>
> INSERT INTO table (serial_col) SELECT nextval('seq_serial_col');
>
> UPDATE table SET foo = 'bar' , ... WHERE serial_col = (SELECT
> currval('seq_serial_col'));
>
> then, if the update fails, the number will be accounted for in the
> table (Note that you could not use not null on any of the columns).
>
> Michael Fork - CCNA - MCP - A+
> Network Support - Toledo Internet Access - Toledo Ohio
>
> On Thu, 29 Mar 2001, postgresql wrote:
>
> > How does currval work if you are not inside a transaction. I have
> > been experimenting with inserting into a table that has a
sequence.
> > If the insert fails (not using a transaction) because of bad client
> input
> > then the next insert gets the proper next number in the
sequence.
> >
> > given sequence 1,2,3,4,5 exists
> > insert into table date 1/111/01 (obviously wrong) insert fails...
> > try again with good data, insert succeeds and gets number 6 in
the
> > sequence.
> >
> > i'm getting what I want. A sequence number that does not
increment
> > on a failed insert. However, how do I get the assigned sequence
> > number with currval when I am not using a transaction? What
> > happens when multiple users are inserting at the same time?
> >
> > I am trying to create a sequence with out any "missing" numbers.
If
> > there is a failure to insert, and a sequence number is "taken". I
> want
> > the empty row.
> >
> > Thanks, it is getting clearer
> >
> > Ted
> >
> >
> > -Original Message-
> > From: Bruce Momjian <[EMAIL PROTECTED]>
> > To: [EMAIL PROTECTED]
> > Date: Mon, 26 Mar 2001 16:47:37 -0500 (EST)
> > Subject: Re: [SQL] serial type; race conditions
> >
> > > > Hi,
> > > >
> > > > I'm using serial fields to generate IDs for almost all object in
> my
> > > > database. I insert an empty row, get the CURRVAL() of the
> > sequence
> > > > and then update to that value.
> > > >
> > > > I had understood (and now, I can't find the reference to back
> this
> > > up)
> > > > that serial is implemented in such a way that race conditions
> > between
> > > > DB connections can't happen.
> > > >
> > > > Is this true?
> > >
> > > Safe. See FAQ item. currval is for your backend only.
> > >
> > > --
> > > 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 2: you can get off all lists at once with the unregister
> command
> > > (send "unregister YourEmailAddressHere" to
> > > [EMAIL PROTECTED])
> >
> >
> >
> > ---(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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] has anyone tried running in MAC OS X
I guess the subject line says ithas anyone tried running PostgreSQL in MAC OS X. I see that in the product brochure, Apple specifically mentions Mysql. I run a shop that has all mac computers. I am runnng just 1 linux box (SUSE Linux on a Max G3) to run the job tracking system. Please, I don't want to start a "my Os is better than yours" war. It is just a question Ted ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] ports in use question
I have started my pg installation using port 5444 (with the -p 5444 switch). Everything is working fine inside my installation with the internal ip number (a 192.168 number). I created a tunnel in my firewall to point to the linux box running pg with the port 5444 open. I can not seem to get to the server from outside. does anything else need to be opened to talk to the pg machine? is pg talking on some other port? thanks a million, Ted ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] cascading delete - recursivity
Hi, I have a table of categories (it's pseudo-sql...) : CATEGORIES ( id_category PRIMARY KEY, id_category_parent (FOREIGN KEY ? CONSTRAINT ??) , cat_text ) There is recursivity : id_category_parent is -1 (= no parent cat) or the id_category of a category in this table. What is the SQL statements to create such a table so that when you delete a category, all sub-categories are automatically deleted ? I've tried many things like CONSTRAINT... but the query fails ! Thanks ! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] need help please on triggers..
Hi, How to create a foreign key in postgresql ? need triggers ? Thanks, Fred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] calling user defined function with parameters..
Hi, I've created a function like this : CREATE FUNCTION tester(INT4) RETURNS BOOL AS ' DECLARE r RECORD; p ALIAS FOR $1; BEGIN SELECT INTO r id_dpt FROM dpts WHERE id_dpt=p; IF NOT FOUND THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END; ' LANGUAGE 'plpgsql'; All is ok at creation. But now , how can i use my function ? i would like a thing like : SELECT tester(14) AS ok; But is make a Postgresql parser error How to do then ?? Thanks for any help or links (other than the postgresql.org website...) ! ;) Regards, Fred ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] finding current oid
Is there a way to return the current oid of a transaction? I am trying to: begin; insert into db something; select current session oid commit; I will only ever be inserting one row during the transaction. Is this doable? Thanks for your time. Ted ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] problem connecting to pg.
I am experimenting with running PG 7.1.2 on Mac OSX. I seem to have a problem with getting my users to connect. This works beautifully when connect to my linux box. Any ideas? [localhost:/etc] postgres% /usr/local/pgsql/bin/postmaster -i -p5444 -D /users/postgres/pgdata DEBUG: database system was shut down at 2001-08-14 09:03:12 EDT DEBUG: CheckPoint record at (0, 2571948) DEBUG: Redo record at (0, 2571948); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 750; NextOid: 35106 DEBUG: database system is in production state Unable to connect to Ident server on the host which is trying to connect to Postgres (IP address 192.168.0.2, Port 113). errno = Connection refused (61) IDENT authentication failed for user 'postgres' ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] just checking
At times things seem just too easy, so I want to confirm that this is the
correct syntax.
I am looking for the interval (aging) for these invoices. I guess I am
concerned because I guessed at the syntax. Is there a better way to do
this?
billdate is type 'date'
SELECT ('now'-billdate) FROM fcinvoiced WHERE client = 'smith';
Ted
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL]
I had a suspicion that JOIN was going to be part of the answer. I guess I had not found documentation that really helped. I have been searching for more information. I found this: 11.2. Controlling the Planner with Explicit JOINs I guess I am just slow It mentions 'inner joins', outer joins, left joins. I get lost. Where can I get more info. I really want to understand. Thanks for your time. I guess it's back to the bookstore. -Original Message- From: "Joseph Syjuco" <[EMAIL PROTECTED]> To: "postgresql" <[EMAIL PROTECTED]> Date: Fri, 7 Sep 2001 08:50:20 +0800 Subject: RE: [SQL] > select i.cname from fc_client_info i inner join fc_communication c on i.acode=c.acode where c.contactdate='090601' order by cname > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of postgresql > Sent: Friday, September 07, 2001 1:12 AM > To: PgSQL-SQL > Subject: [SQL] > > > I am basically self taught with sql. I find that I keep re-reading the > postgresql tutorials tryin to add a little more to my understanding. > > so my question is... Is this the best way to word this querry? > I have two tables, fc_client_info and fc_communication. I keep reading > the section on JOINs and wonder if I should querry this differently. > for > some reason I have been shying away from JOINs (I haven't figures them > out). > > SELECT i.cname FROM fc_client_info i, fc_communication c WHERE i.acode > = > c.acode AND c.contactdate = '09/06/2001' ORDER BY lower(cname); > > Thank for your great software I am really beginning to like SQL... > > Ted ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] serial data type
CREATE TABLE tablename (colname SERIAL);
okay, but how do I set the beginning number. So far the only thing I
have been able to do is directly after creating the serial column, I do:
select setval('tablename_colname_seq', 15753);
Is this the only way to set the beginning number of a sequence?
Thanks for all your help guys,
Ted Petrosky
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] more information on JOINs
Thanks for this reference, I have been here. I guess my problem is that I don't understand a need for a 'join' that is returning NULLs. If I understandJOINs correctly, they are returning columns that contain NULLs. An example please where I would want this result. I have created a job tracking system that includes invoicing, collections reporting, aging. When I first learned to do the INNER JOIN ON, I replaced the processing that I was doing in my client app and let Postgres do it. So now I am examining and studying the other joins. I just can not figure out why I would EVER want one. Which leads me to think that I just don't understand them. Please, if someone has a good example.. Ted [EMAIL PROTECTED] -Original Message- From: Francesco Casadei <[EMAIL PROTECTED]> To: postgresql <[EMAIL PROTECTED]> Date: Sat, 22 Sep 2001 17:40:57 +0200 Subject: Re: [SQL] more information on JOINs > On Sat, Sep 22, 2001 at 08:11:03AM -0400, postgresql wrote: > > Where can I get more information and examples on using JOINs. I > > have been reading over the Chapter 2 on from the interactive user > > docs but I need a little more in the way of examples. I am having a > > problem understanding when I would need a LEFT or RIGHT JOIN. > > > > Please, a 'point' in the right (no pun intended) direction would be > > appreciated. > > > > Ted > > > > > > > > ---(end of > broadcast)--- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > end of the original message > > Take a look at the reference manual, part I "SQL Commands": > > http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/sql-s elect > .html > > Francesco Casadei ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] more information on JOINs
Where can I get more information and examples on using JOINs. I have been reading over the Chapter 2 on from the interactive user docs but I need a little more in the way of examples. I am having a problem understanding when I would need a LEFT or RIGHT JOIN. Please, a 'point' in the right (no pun intended) direction would be appreciated. Ted ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL]
Please can someone help I tried to subccribe to pgsl-admin but I have been unable. I just upgraded to Mac osx 10.1. When I try to configure --with-tcl I get an error that 'wish' can not be found. Where do I get this? (this is not so important but I would like to use pgaccess) 2. below is the error I am getting from 'make' (included the output from make -v) any ideas (I know that I can go back to osx version 10.0.4 but 10.1 is much faster writing to the screen. I apologize for posting this here. I just can not seem to get the confirmation to the other groups. Ted cc -traditional-cpp -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -bundle -undefined suppress -bundle -undefined suppress fe-auth.o fe-connect.o fe-exec.o fe-misc.o fe-print.o fe-lobj.o pqexpbuffer.o dllist.o pqsignal.o -o libpq.so.2.1 /usr/bin/ld: -undefined error must be used when -twolevel_namespace is in effect make[3]: *** [libpq.so.2.1] Error 1 make[2]: *** [all] Error 2 make[1]: *** [all] Error 2 make: *** [all] Error 2 [localhost:/Users/postgres/postgresql-7.1.3] root# make -v GNU Make version 3.79, by Richard Stallman and Roland McGrath. Built for powerpc-apple-darwin1.4 Copyright (C) 1988, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. Report bugs 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])
[SQL] Table Copy.
HI! I'm new to postgres. I need to have a table as a copy of another one. Example: CREATE TABLE one ( fileda INTEGER, filedb INTEGER, filedc INTEGER ); CREATE TABLE two ( fileda INTEGER, filedb INTEGER, filedc INTEGER ); As on insert to table one I should get the same insert on table two. As on delete to table one I should get the same delete on table two. As on update to table one I should get the same update on table two. Can someone provide the examples i can study ? Thanks in advance. Alex ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] DATE TIME INDEX ?
I have been tring to use index on timestamps: I have a table with a timestamp filed with index on it. I nned to extract all the ids of the table with datarx >= a date <= a date or between 2 dates I found that the only way to force postgres to use index is: explain select id,datarx::date from docs where datarx between '2002-11-13' and '2002-11-14' ; Index Scan using idx_documenti_datarx on documenti (cost=0.00..7.86 rows=2 width=12) In other cases the index is not used: explain select id,datarx::date from docs where datarx >= '2002-11-16'; Seq Scan on documenti (cost=0.00..12.01 rows=107 width=12) explain select id,datarx::date from docs where datarx::date between '2002-11-13' and '2002-11-13' ; Seq Scan on documenti (cost=0.00..16.02 rows=36 width=12) I found that the only way to force postgres to use index is: explain select id,datarx::date from docs where datarx between '2002-11-13' and '2002-11-14' ; Index Scan using idx_documenti_datarx on documenti (cost=0.00..7.86 rows=2 width=12) Do I need to use some other functons o trick? Thanks in advance Alex ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] INDEX PROBLEMS ?
On postgres 7.2.3 I have found what follows: explain select * from documents where iddoc>1; Seq Scan on lotti (cost=0.00..831.79 rows=26783 width=98) EXPLAIN explain select * from documents where iddoc=1; Index Scan using lotti_pkey on lotti (cost=0.00..2.26 rows=1 width=98) Why index is not used for operators > and < Alex ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Trigger calling a function HELP ME!
Hi all, first of all, let me explain what I'm trying to do. I have a table with a fairly complicated trigger. In this trigger I have a specific set of codelines that can be executed in more than 50 places that works on the new.* fields in order to fix/clean them. In order to improve readability, I created a function that manages this small set of codelines, but I'm stuck on the following error: --- ERROR: return type mismatch in function returning tuple at or near "imp_test_to_out_test" CONTEXT: compile of PL/pgSQL function "imp_test_trigger" near line 2 --- as a model, I've created this run-down example: --- CREATE TABLE public.imp_test ( id int8, value text ) WITHOUT OIDS; CREATE OR REPLACE FUNCTION public.imp_test_to_out_test(imp_test) RETURNS imp_test AS 'begin return new; end;' LANGUAGE 'plpgsql' STABLE; CREATE OR REPLACE FUNCTION public.imp_test_trigger() RETURNS trigger AS 'begin return imp_test_to_out_test(new); end;' LANGUAGE 'plpgsql' STABLE; CREATE TRIGGER imp_test_trigger_001 BEFORE INSERT OR UPDATE ON public.imp_test FOR EACH ROW EXECUTE PROCEDURE public.imp_test_trigger(); --- Whenever I run the following select, I get the a.m. result: --- insert into imp_test (id, value) values(1, 'A'); --- Can somebody help me? regards, = Riccardo G. Facchini ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Trigger calling a function HELP ME! (2)
Sorry. I realize I slipped an error in my code: the code is: --- CREATE TABLE public.imp_test ( id int8, value text ) WITHOUT OIDS; CREATE OR REPLACE FUNCTION public.imp_test_to_out_test(imp_test) RETURNS imp_test AS 'begin return $1; end;' LANGUAGE 'plpgsql' STABLE; CREATE OR REPLACE FUNCTION public.imp_test_trigger() RETURNS trigger AS 'begin return imp_test_to_out_test(new); end;' LANGUAGE 'plpgsql' STABLE; CREATE TRIGGER imp_test_trigger_001 BEFORE INSERT OR UPDATE ON public.imp_test FOR EACH ROW EXECUTE PROCEDURE public.imp_test_trigger(); --- regards, = Riccardo G. Facchini ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Bug#960: WAS: Trigger calling a function HELP ME! (2)
Ok. I think I found the problem is related to this Bug. is there anywhere to check the status of this bug? regards, = Riccardo G. Facchini ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] PL/pgSQL and PHP 5
I'm having this problem inserting data from my form using PL/pgSQL.
Here is the simplified version of my table and function (this example
does not work, also ):
CREATE TABLE theirry.sample (
staff_id serial PRIMARY KEY NOT NULL,
firstname varchar(100),
lastname varchar(150),
username varchar(35),
identifier varchar(40),
address2 varchar(180),
activated boolean,
activated_keys varchar(32)
);
CREATE OR REPLACE FUNCTION insert_staff_b
(insert_firstname varchar)
RETURNS VOID AS
$$
DECLARE
BEGIN
INSERT INTO theirry.sample
(firstname)
VALUES
(insert_firstname);
RETURN;
END;
$$
LANGUAGE plpgsql;
I have a form with a value firstname then call the query in php
select insert_staff_b('$_POST['firstname']::varchar)
Still I get this error:
Warning: pg_query(): Query failed: ERROR: function
insert_staff_b(character varying) does not exist HINT: No function
matches the given name and argument types. You may need to add explicit
type casts.
Suggestions or maybe a place to read up on this problem.
Thanks in advance,
J
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] [PHP] PL/pgSQL and PHP 5
Thanks for the catch. I've tried:
$connection->execute("SELECT
insert_staff_b('$staff[insert_firstname]'::varchar)");
$connection->execute("SELECT
insert_staff_b('".$staff['insert_firstname']."'::varchar)");
None work... I'm scratching my head on this one.
Thanks,
J
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] PL/pgSQL and PHP 5
Tom Lane wrote: PostgreSQL Admin <[EMAIL PROTECTED]> writes: CREATE OR REPLACE FUNCTION insert_staff_b (insert_firstname varchar) RETURNS VOID AS ... Still I get this error: Warning: pg_query(): Query failed: ERROR: function insert_staff_b(character varying) does not exist Sure looks like it oughta work. One possibility is that you created the function in a schema that isn't part of the application's search path. Other than that, look for *really* silly errors, like not creating the function in the same database the application is connected to ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq I never thought of that. I look into it. Thanks, J ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PL/pgSQL and PHP 5 - thanks
Thanks, The search path was the problem. Sometimes it's the simple things. Big thanks, J ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Replace string
Hi, I want to replace a title with dashes and also remove punctuation. e.g, The blue fox's fur. -> The-blue-fox-fur Thanks for any input, J ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Replace string
test=*# select regexp_replace(regexp_replace('The blue fox\'s fur.', ' ', '-',
'g'), '\\.', '');
regexp_replace
The-blue-fox's-fur
(1 row)
Andreas
Thanks for the input.
I came up with this:
REPLACE(regexp_replace('The blue fox\'s fur', '[[:punct:]]', ''), ' ', '-');
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] Add constraint for number of chars
Hi, This is seemly simple, but I'm @ a loss this early in the morning. It's best explained this way SAMPLE --- id | serial| username | varchar(100)| constraint username >=8 and username <=100 The problem is that it's characters not integers or numeric. It my constraint correct? Thanks for the help, J ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] pgmemcache
As anyone used pgmemcache? I would like to look more into a but I'm having problems installing the sql. I'm on OS X 10.4 and the sql there are lines causing errors: (e.g. AS '$libdir/pgmemcache', 'memcache_server_add' LANGUAGE 'C' STRICT;) thanks for any input and also will version 1.2 come out of beta? I'm looking to implement it @ work and they are not happy about using beta releases. Thanks, J ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Constraints for grouping
I have a question that I've thought in my head about using triggers, but I figure to ask people that do SQL more than I. So, I have a table that I want two columns. (1) A featured column which is for only 1 row, once it switched to another row than all other rows must be false title | author_id | categories | featured +---+--+- Thierry Beta Release | 3 | 11 | True Different Approach | 3 | 11 | Ottawa Does Not Heart Hip-Hop | 3 | 11 | (2) A featured column by category and only allow category_feature equal to the number of categories. Is SQL the way to go (and how - ideas), or should I use python for the heavy lifting? Thanks for any input, J ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Constraints for grouping
> --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > >> CREATE UNIQUE INDEX Only_one_row_true >> ON Your_table ( featured ) >>WHERE featured = true; >> >> Or if you want to only allow 1 featured article per catagory then: >> >> CREATE UNIQUE INDEX Only_one_row_true_per_catagory >> ON Your_table ( catigories, featured ) >>WHERE featured = true; >> > > I forgot the unique part of the DDL. > > Regards, > Richard Broersma Jr. > > Thanks for the information. I will check it out and get back to you. Thanks again, J ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Trigger to change different row in same table
I want to write a trigger that updates a different row on the same table. It's pretty basic: before the any row in his table updated I want to set a only row that has the value true to false. I keep getting this error: SQL statement "update theirry.articles set master_featured = false where master_featured = true" PL/pgSQL function "master_featured_maintenance" line 4 at SQL statement My basic trigger: CREATE OR REPLACE FUNCTION theirry.master_featured_maintenance() RETURNS TRIGGER AS $master_featured_maintenance$ DECLARE master_feature boolean; BEGIN update theirry.articles set master_featured = false where master_featured = true; END; $master_featured_maintenance$ LANGUAGE plpgsql; CREATE TRIGGER master_featured_maintenance BEFORE INSERT OR UPDATE ON theirry.articles FOR EACH ROW EXECUTE PROCEDURE theirry.master_featured_maintenance(); Thanks in advance, J ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Trigger to change different row in same table
chester c young wrote: > how are you preventing recursion? > > That could be the problem, suggestions? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Foreign Key for multi PK or design question
I have a table in which people will have a number of questions to answer. I want those pk to be placed in my user table. So if a user answers three question I want those 3 pk's in the user table (fk). What should I be doing? Thanks in advance, J ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Foreign Key for multi PK or design question
This is my layout so far: CREATE TABLE users ( id serial NOT NULL, --question REFERENCES questions(id) ON DELETE CASCADE ## ON REMOVED## ); CREATE TABLE questions ( id serial NOT NULL, questions varchar(450) NOT NULL ); CREATE TABLE answers ( id serial NOT NULL, question_id int REFERENCES questions(id) ON DELETE CASCADE, user_id int REFERENCES users(id) ON DELETE CASCADE, answer varchar(450) NOT NULL, created timestamptz NOT NULL ); Originally I wanted to have a foreign key that would be the pk of the question table. So if the user answered Q2, 5 and 6 - the user.fk would store values 2,5,6 - but I have passed most of logic to the answer table. Does this look correct? or most efficient? J ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Foreign Key for multi PK or design question
No problems with the design - I was not thinking with the DB hat on at first. I have been working on clustering for a while... just adjusting. Thanks everyone. :) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Unclosed connections
We are using this bad piece of the software that does not close connections to the postgres server. Is there some setting for closing dead connections? And not TCP/IP keep alive does not work. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Double query
Simple question - but my brain is not working:
This is my table
Column | Type |
Modifiers
---++-
id| integer| not null default
nextval('emr_clinicschedule_id_seq'::regclass)
clinic_id | integer| not null
date | date |
day | smallint |
status| boolean| not null
open | time without time zone |
close | time without time zone |
reason| character varying(64) |
active| boolean| not null
I want to find any value:
SELECT id FROM schedule WHERE clinic_id = '%s' AND date = '%s'
But I want to make sure the clinic exist first then find if the date
exist 2nd.
How do I do that?
Thanks in advance,
J
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] DB Design
I have a inventory system design in which I would like some help with to
see if it's efficient. The products are broken into:
Product tables
Property tables
Configurable Products - this would include colors (i.e. - black, blue
and green) tied to products
I'm thinking of breaking inventory into 2 tables.
Product Inventory
Inventory Adjustment
Should I include the fk of the Configurable Product in the above tables
or break it out further into more tables?
Product Inventory
Inventory Adjustment
--plus--
Product Property Inventory
Inventory Property Adjustment
Thanks for any input,
J
These are my Product tables:
\d cart_product
Table "public.cart_product"
Column | Type |
Modifiers
---+--+---
id| integer | not null default
nextval('cart_product_id_seq'::regclass)
name | character varying(128) | not null
kind | character varying(40)|
sku | character varying(15)|
short_description | character varying(255) | not null
description | text |
category_id | integer | not null
date_created | timestamp with time zone | not null
active| boolean | not null
in_stock | boolean | not null
featured | boolean | not null
ordering | integer |
Indexes:
"cart_product_pkey" PRIMARY KEY, btree (id)
"cart_product_category_id" btree (category_id)
Foreign-key constraints:
"cart_product_category_id_fkey" FOREIGN KEY (category_id) REFERENCES
cart_category(id) DEFERRABLE INITIALLY DEFERRED
\d cart_propertyvariation
Table "public.cart_propertyvariation"
Column | Type |
Modifiers
---+---+-
id| integer | not null default
nextval('cart_propertyvariation_id_seq'::regclass)
properties_id | integer | not null
name | character varying(42) | not null
value | character varying(20) | not null
order | integer |
Indexes:
"cart_propertyvariation_pkey" PRIMARY KEY, btree (id)
"cart_propertyvariation_properties_id" btree (properties_id)
Check constraints:
"cart_propertyvariation_order_check" CHECK ("order" >= 0)
Foreign-key constraints:
"properties_id_refs_id_73bc0a59" FOREIGN KEY (properties_id)
REFERENCES cart_property(id) DEFERRABLE INITIALLY DEFERRED
\d cart_configurableproduct
Table "public.cart_configurableproduct"
Column | Type |
Modifiers
-+--+---
id | integer | not null default
nextval('cart_configurableproduct_id_seq'::regclass)
product_id | integer | not null
variation_id| integer | not null
price_change| numeric(8,2) |
weight_change | integer |
quantity_change | integer |
active | boolean | not null
Indexes:
"cart_configurableproduct_pkey" PRIMARY KEY, btree (id)
"cart_configurableproduct_product_id" btree (product_id)
"cart_configurableproduct_variation_id" btree (variation_id)
Foreign-key constraints:
"cart_configurableproduct_product_id_fkey" FOREIGN KEY (product_id)
REFERENCES cart_product(id) DEFERRABLE INITIALLY DEFERRED
"cart_configurableproduct_variation_id_fkey" FOREIGN KEY
(variation_id) REFERENCES cart_propertyvariation(id) DEFERRABLE
INITIALLY DEFERRED
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Different type of query
I have a table like this: usda=# \d nutrient_data Table "public.nutrient_data" Column | Type | Modifiers -+---+--- ndb_no | integer | not null nutrient_no | integer | not null nutrient_value | double precision | not null data_points | double precision | not null std_error | double precision | src_cd | integer | not null derivation_code | character varying(5) | ref_ndb_no | integer | add_nutr_mark | character varying(2) | num_studies | integer | min | double precision | max | double precision | df | numeric | low_eb | double precision | up_eb | double precision | stat_cmt| character varying(15) | cc | character varying(5) | Indexes: "nutrient_data_pkey" PRIMARY KEY, btree (ndb_no, nutrient_no) Foreign-key constraints: "nutrient_data_derivation_code_fkey" FOREIGN KEY (derivation_code) REFERENCES derivation_code(derivation_code) ON UPDATE CASCADE ON DELETE CASCADE "nutrient_data_ndb_no_fkey" FOREIGN KEY (ndb_no) REFERENCES food_description(ndb_no) ON UPDATE CASCADE ON DELETE CASCADE "nutrient_data_nutrient_no_fkey" FOREIGN KEY (nutrient_no) REFERENCES nutrient_definitions(nutrient_no) ON UPDATE CASCADE ON DELETE CASCADE "nutrient_data_src_cd_fkey" FOREIGN KEY (src_cd) REFERENCES source_code(src_cd) ON UPDATE CASCADE ON DELETE CASCADE when I run this query: select ndb_no, nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473; it produces: ndb_no | nutrient_no | nutrient_value +-+ 13473 | 203 | 24.18 13473 | 204 | 15.93 13473 | 205 | 0 13473 | 207 |1.1 13473 | 208 |247 13473 | 221 | 0 13473 | 255 | 57.78 13473 | 262 | 0 13473 | 263 | 0 13473 | 268 | 1033 13473 | 269 | 0 13473 | 291 | 0 13473 | 301 | 5 13473 | 303 | 3.35 13473 | 304 | 24 13473 | 305 |199 13473 | 306 |302 13473 | 307 | 67 13473 | 309 | 4.67 13473 | 312 | 0.131 13473 | 315 | 0.015 13473 | 317 | 10.9 13473 | 318 | 0 13473 | 319 | 0 13473 | 320 | 0 13473 | 321 | 0 13473 | 322 | 0 13473 | 323 | 0.18 13473 | 334 | 0 13473 | 337 | 0 13473 | 338 | 0 13473 | 401 | 0 13473 | 404 | 0.101 I want only certain nutrient_no (say 8 of them) and the nutrient values by ndb_no. how would I write that query. BIG THANKS in advance as I'm lost on this one. J -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Different type of query
Steve Crawford wrote: PostgreSQL Admin wrote: I have a table ... when I run this query: select ndb_no, nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473; it produces: ndb_no | nutrient_no | nutrient_value +-+ 13473 | 203 | 24.18 ... I want only certain nutrient_no (say 8 of them) and the nutrient values by ndb_no. Not entirely sure I understand the question. Do you mean that for a given nutrient_no, you want the complete list of nutrient values? If so, it's just: --Example for nutrient_no 203: SELECT ndb_no, nutrient_value from nutrient_data where nutrient_no=203; Cheers, Steve I would like to have multiple values nutrient_no: ndb_no | nutrient_no | nutrient_value +-+ 13473 | 203 | 24.18 13473 | 204 | 15.93 13473 | 205 | 0 13473 | 207 |1.1 13473 | 208 |247 13473 | 221 | 0 I'm thinking: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 and (nutrient_no = '203' or nutrient_no = '204' or nutrient_no = 208); Now is that the most efficient SQL query? Thanks, J -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Different type of query
Mark Roberts wrote: On Wed, 2008-06-11 at 14:41 -0400, PostgreSQL Admin wrote: I would like to have multiple values nutrient_no: ndb_no | nutrient_no | nutrient_value +-+ 13473 | 203 | 24.18 13473 | 204 | 15.93 13473 | 205 | 0 13473 | 207 |1.1 13473 | 208 |247 13473 | 221 | 0 I'm thinking: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 and (nutrient_no = '203' or nutrient_no = '204' or nutrient_no = 208); Now is that the most efficient SQL query? Thanks, J It seems that you'd want to do something like: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 and nutrient_no in (203, 204, 208..) You could also grab the most significant 8 nutrients by doing something like: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 order by nutrient_value desc limit 8 -Mark Thanks Mark! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Tsearch
this is a small sample of the data: short_desc | long_desc --+ CHICKEN,BROILERS OR FRYERS,LEG,MEAT&SKN,CKD,FRIED,BATTER | Chicken, broilers or fryers, leg, meat and skin, cooked, fried, batter Is the best method of search through this data full text search via tsearch or some other method. I'm running version 8.3 say I want to search for chicken skin? Thanks for the advice, J -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Design and Question
Hi,
I have BOTH a sql AND db design question.
I'm creating a cookbook DB with have broken the table into this:
RECIPE TABLE
Column | Type | Modifiers
---+--+--
id | integer | not null default nextval('recipes_id_seq'::regclass)
title | character varying(150) | not null
description | text | not null
servings | integer |
instruction | text | not null
photo | character varying(100) | not null
difficulty | integer |
cuisine | smallint |
course | smallint |
season | smallint |
dietary | smallint |
technique | smallint |
published_date | timestamp with time zone | not null
publishing_rights | boolean | not null
credits | character varying(100) | not null
approved | boolean | default false
cooktime | integer |
preptime | integer |
and this:
RECIPE DIET INFO TABLE
Column | Type | Modifiers
---+--+---
id | integer | not null default nextval('recipes_diet_id_seq'::regclass)
recipe_id | integer | not null
diet | character varying(1) |
RECIPE SEASON TABLE
Column | Type | Modifiers
---+--+-
id | integer | not null default nextval('recipes_season_id_seq'::regclass)
recipe_id | integer | not null
season | character varying(1) |
I can perform is query ->
select title from recipes where id in (select recipe_id from
recipes_season where season in ('P', 'W'));
title
---
ButterFlied Chicken Fillets with Lime
Balsamic Vinegar Chicken with Beans
(2 rows)
select title from recipes where id in (select recipe_id from
recipes_diet where diet in ('P'));
title
---
ButterFlied Chicken Fillets with Lime
How do I combine the two in a query?
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
