Re: [SQL]
You might also consider using ecpg which allows a syntax like: int a, b, c; EXEC SQL INSERT INTO mytable ( :a, :b, :c ); See http://www.postgresql.org/docs/current/interactive/ecpg.html for details. HTH Jürgen Viorel Dragomir <[EMAIL PROTECTED]> schrieb am 29.01.2004, 14:11:44: > So, you are an artist. Isn't it? :) > Your query is actually a string. > This is your string: INSERT into table values(a,b,c,d) > > You must change your string to actually use values of tha a, b... > You can make this string with sprintf > sprintf(string, "INSERT into table values(%d,%d,%d,%d)", a, b, c, d); > > Then you launch: > res=PQexecute(Conn, string); > > Why don't you use PHP? Is easier. > > Have a nice day > - Original Message - > From: MUKTA > To: [EMAIL PROTECTED] > Sent: Thursday, January 29, 2004 15:08 > Subject: [SQL] > > > Hi I have an urgent problem > I want to insert values into a table using the C syscalls provided by the libpq > library, but i find that i can not insert into the table when i use variables > instead of values...like so: > int a,b,c,d; > using the C function > > res=PQexecute(Conn,"INSERT into table values(a,b,c,d)); > > executing above statement with plain integers does fine and inserts them into > table.. > > Is there some special way to insert variables rather than plain values? do i have > to build functions (in sql) or somehting?help! > Thanx ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] benchmarks
MTcW: Pick the database which allows your programmers to get the job done. If the system is too slow, find out if there are ways you could speed it up, and then if that doesn't make you happy, get a faster server. In my opinion it's not worth making the programmer's life more difficult to go with a database which is difficult to work with. Also, make sure the database you choose is reliable. One of the mySQL, mSQL (I confuse the two) servers has the problem that they lock the entire db when working, so only one client can connect at a time. Seems like a problem to me. Troy > > > hi all, > > lately at work there has been a debate over > mysql versus postgres > > im just looking for independent benchmarks > > i personally love postgres > at work they like mysql > > currently we are investigating other possible db solutions > > and they are looking at oracle, i think we could save a lot of dollarsz > if we decided to go to postgres > > > i was wondering if anyone can share links to any current independent > benchmarks > > as i would like some real data on these > > or at the very least give me a how to so i can do my own testing! > > thanks! > > > > > >
Re: [SQL] Large Object dump ?
Large objects are not dumped. It should be in the documentation for large objects. You need to write a script which writes them to disk and then imports them back in after you have installed your dbs. Troy > > > I need to move a DB from Pg 6.5 to 7 haw can i export Large Object to the > new DB ? > > Pg_dumpall seems non to export LArge Object. > > Please Help > > Alex > > >
[SQL] Meaning of "REMINDER from pgsql..."
What's the meaning of the "REMINDER from pgsql ..." message? I checked the page, and apparently everybody on the list got the message. Should we accept or reject the action? Troy
Re: [SQL] 7.0.3 BUG
Just a wild guess, but I would imagine a corrupt (old) index on the userid field would cause this kind of behavior. You could test this by dropping the index and then rebuilding it. Troy > > "pgsql-sql" <[EMAIL PROTECTED]> writes: > > migrate=# select userid from users where userid = '[EMAIL PROTECTED]'; > > userid > > - > > [EMAIL PROTECTED] > > admin > > (2 rows) > > That's a tad, um, startling :-( > > However, you haven't given us nearly enough information to have a shot > at figuring out what's going on. > > regards, tom lane >
Re: [SQL] Help: Using a regular expression match as a value
Richard,
Please correct me if I am wrong.
You want to do something like:
select $1 from captives where firstname ~ '^(R[^ \t,]*d)$';
And get as result:
lastname
Richard
Richard
Richard
Ricard
Richard
Rolland
Richard
In this case, the above query is same as:
select firstname from captives where firstname ~ '^R[^ \t,]*d$';
If the firstname field contains entries like "Richard, Elvis" and "Richard Amadeus,"
then you want something like:
select $1 from captives where firstname ~ '^(R[^ \t,]*d)[ \t,]';
which would return the first name in the field but not subsequent
names.
I am afraid there is no built in solution to get around these scenarios.
You could do:
select substring(firstname from 1 for (position(' ' in firstname)-1))
from captives where firstname ~ '^R[^ ]*d ' union select firstname
from captives where firstname ~ '^R[^ ]*d$';
This would get you what you wanted, for this one scenario.
I know it's not what you hoped, but you might be able to
make do with this.
Troy
>
> I am just getting started with pgsql and have read available docs I can
> find.
>
> I know I can match a row in a where clause using a regular expression.
>
> How can I use what was regexp matched (e.g. perl $1,$2, etc...) as a column
> assignment ?
>
> I'm looking for something like this?
> select ...
> ...
> $1 of firstname, /^R.*d/ as name_starts_with_r_and_ends_with_d
> ...
>
> TIA
>
> Richard DeVenezia
>
>
Re: [SQL] Select 'Sunday' in month ??
E.g.
create table mytable (created datetime);
insert into mytable values ('01-01-2001');
...
insert into mytable values ('01-31-2001');
select created from mytable where date_part('dow', created) = 7 and date_part('month',
created) = 1;
Troy
>
> i want to select date in january where day='Sunday'
>
> any idea ??
>
> please help me
>
> Nizomi
>
Re: [SQL] How can i escape a '+' or a '+' in a regexp ?
Gabriel, Two backslashes. > select * from areas where titulo ~ '\\+' or titulo ~ '\\*' Troy > > Hi fellows, > > I'm trying to the following query: > > select * from areas where titulo ~ '+' or titulo ~ '*' > > and the answer is: > > ERROR: regcomp failed with error repetition-operator operand invalid > > I have tried to escape the '+' and the '*' with a backslash, as > follows: > > select * from areas where titulo ~ '\+' or titulo ~ '\*' > but the answer is the same. > > If I use the LIKE operator, then I have the problem with '%' and '_' > :-) > > As long as the values in the field can contain either '+' or '*' or '%' > or '_' I need to escape these characters. How can i do it ? > > I'm using PostgreSQL 6.5.3 on Linux Red Hat 6.2. > > Thanks, > > Gabi :-) > > > > >
Re: [SQL] handling special characters in sql strings
Markus, Here are comments from a C function which does what you need. You won't have the regex functions available, so the function itself would be useless. You get the idea though. 1. First change backslashed backslashes back to single backslashes. 2. Find backslash single quote combinations (i.e. "\'") and convert them to single quotes (i.e. "'"). Use a while loop to be sure. Now there should be no single quotes preceded by backslashes left. 3. Find all backslashes and convert them to double backslashes. Now all characters preceded by a backslash should be in their original state, while all backslashed single quotes are just single quotes. E.g. "\1ABC'ABC\2\q" 5. Find all single quotes and backslash them. (Note: There are no single quotes preceded by a backslash, so it is not possible to have a backslash which would hide a backslash preceding a single quote.) That's it. Troy > > Hi, > > I need to insert and retrieve strings containing special characters (e. > g. "'") and I want a C function to prepare my strings automatically. Is > it ok to just insert a "\" before each special character? And what range > of characters will need this handling? > > Thanks, > > Markus > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] MultiByte strings
Nizomi, When you compile postgres, you specify the option at that time. If you installed postgres as a binary (tar, rpm), you would not have seen this option. Troy > > Hello, > > i have an error > "psql: ERROR: MultiByte strings (MB) must be enabled to use this function" > > where i must enable MultiByte strings (MB) ?? > > Nizomi > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Re: Still don't know how to build this string ? how to concat ??
If I understand corrently, the idea is to get a comma
delimited list as a result.
here is a modified function with a slightly different set of names
for the table. The commands include commands to add and drop the
needed tables.
CREATE TABLE emps (username text, userid int4);
INSERT INTO emps VALUES ('User 1', 1);
INSERT INTO emps VALUES ('User X', 2);
INSERT INTO emps VALUES ('User 2', 2);
INSERT INTO emps VALUES (null, 2);
INSERT INTO emps VALUES ('something', null);
CREATE FUNCTION com_delim(int4) RETURNS text AS '
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := '''';
comstr := '''';
FOR rec IN SELECT username FROM emps WHERE userid = $1 AND NOT username
ISNULL LOOP
str := str || comstr || rec.username;
comstr := '','';
END LOOP;
RETURN str;
END;
' LANGUAGE 'plpgsql';
SELECT com_delim(2) FROM emps;
DROP FUNCTION com_delim(int4);
DROP TABLE emps;
Troy
>
> Hello Andy,
>
> Tuesday, March 27, 2001, 3:22:37 PM, you wrote:
>
> AC> Tuesday, March 27, 2001, 10:20:18 AM, you wrote:
>
> jrpc>>
>
> jrpc>> Result:
> jrpc>> 01 1440
> jrpc>> 02 1460
> jrpc>> 03 1398
>
> jrpc>> The values (1440, ...) are the last entries of z_u_umfang for each z_u_typ.
> jrpc>> This is correct since in the function the list:= ... is overwritten until
> jrpc>> the last record is read.
> jrpc>> When I try to concat the list in the manner of list := list ||
> jrpc>> text(rec.z_u_umfang); the zustring is empty !
>
> jrpc>> Thanks for any help ... jr
>
> jrpc>> Query :
> jrpc>> select distinct z_u_typ, buildString(z_u_typ) as zustring from
> jrpc>> zylinder_umfang
>
> jrpc>> Function:
> jrpc>> CREATE FUNCTION buildString(bpchar) RETURNS text AS '
> jrpc>> DECLARE
> jrpc>> list text;
> jrpc>> rec record;
> jrpc>> BEGIN
> jrpc>> FOR rec IN SELECT z_u_umfang FROM zylinder_umfang WHERE z_u_typ
> jrpc>> = $1;
> jrpc>> list := text(rec.z_u_umfang);
> jrpc>> END LOOP;
> jrpc>> RETURN list;
> jrpc>> END;
> jrpc>> ' LANGUAGE 'plpgsql';
>
> AC> You seem to be constantly re-assigning "list", rather than adding to
> AC> it with each iteration of the "for loop".
>
> AC> Would:
> AC> ...
> AC> list := list || ',' || text(rec.z_u_umfang)
> AC> ...
> AC> be what your solution is missing?
>
> I read it again and noticed your comment about having tried || already
> - I must learn to read messages fully...
>
> But, I did wonder if the semicolon ";" at the end of the for loop is
> what is causing your problem? The syntax explanation I have does not
> show the ";", therefore it is possible that the loop is executing a
> null instruction ";", moving on the the list assignment, and then
> finding the unmatched "end loop" which might not throw an error.
>
> Can anyone comment if this is a plausible explanation?
>
> --
> Best regards,
> Andymailto:[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])
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] outer joins
Algirdas, This should do the trick: SELECT a.id,b.name FROM a,b WHERE a.id=b.id UNION SELECT id,null FROM a WHERE id NOT IN (SELECT id FROM b); Troy > > Hi all, > > I'm new to postgre, I've changed my work and consequently now i'm moving > from MS plaform. > In MS SQL there are such constructs left or right outer join, in postgres > there are no such thing > > Can You offer me strategy to make query that selects from table (a) and > joins to it another (b) > on e.g. a.id=b.aid but joins so that in case there is no value in table b > NULL is left in result: > in MS SQL i've used query: > > select a.id, b.name from a left outer join b on a.id=b.aid > > table a table b > > id | aid | name > --- > 11 | Tom > 23 | Sam > > result: > a.id | b.name > - > 1 | Tom > 2 | NULL > > thank you in advance > > Algirdas ©akmanas > IT manager > +370 99 90369 > [EMAIL PROTECTED] > Grafton Entertainment > http://www.tvnet.lt > > > > ---(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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Re: Need help with search-and-replace
I am sure someone already sent this reply and I missed it.
Anyway, if I understand the original problem correctly, you want to
find instances of "\t\t00:00:00" and "\t\t\t\t\t\t\t00:00:00", etc. and
remove them.
I hope this is generic enough so you can change it to fit your needs:
echo "Start c 00:00:00crap here." | sed "s/\([^ ]*\)[
]\+[0-9][0-9]:[0-9][0-9]:[0-9][0-9]\(.*\)/\1\2/g"
This will find an instance of "nn:nn:nn" only when preceded by more than one
tab.
Perl is easier to read, so here is a perlish version:
echo "Start c 00:00:00crap here." | {perlish}
"s/([^\t]*)[\t]+\d\d:\d\d:\d\d(.*)/$1$2/g"
Troy
>
> There are oh-so-many ways, as I am sure people will tell you. regular
> expressions are the most wonderful things for such a task. I am comfortable
> with tcl, so I would read the file into a tcl variable and use 'regsub -all
> {\t700:00:00} $instring {} outstring'.
>
> There are unbelievably simple, unvbelievably fast ways to do this in one line
> from the shell using sed, but I don't speak sed. I suspect someone will hook
> you up with some basic sed.
>
> Try this in Windows. Visual Basic can use regular expressions, but you have to
> instantiate a regular expression object, then execute one of it's methods to do
> anything. Ugh.
>
> Ian
>
> Josh Berkus wrote:
>
> > Folks,
> >
> > I need to strip certain columns out of my pgdump file. However, I
> > can't figure out how to use any Unix-based tool to search-and-replace a
> > specific value which includes a tab character (e.g. replace "{TAB}7
> > 00:00:00" with "" to eliminate the column).
> >
> > RIght now, I'm copying the file to a Win32 machine and using MS Word
> > for the search-and-replace, but I'm sure there's got to be a better way
> > ... *without* learning VI or Emacs. Help?
> >
> > -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 4: Don't 'kill -9' the postmaster
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] Calculating the age of a person
You are probably looking for date_part().
E.g.
SELECT date_part('year',age(birth)),* FROM persons LIMIT 1;
Troy
>
> I have a table containing the birthdays of various persons. The target
> is to compute the age of a persons.
>
> persons=# SELECT age(birth), * FROM persons LIMIT 1;
> age | id | name | birth| gender |
> income
> ---+++++
>
> 31 years 4 mons 16 days 23:00 | 1 | Albert | 1970-01-01 | m |
> 35000
> (1 row)
>
> When I use age() I don't get full years. Is there an easy way to round
> ::reltime off or up without writing a function. Is there any possibility
> to use plain SQL only?
>
> Hans
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go 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] SQL specified sort
Jeff, If you want to receive your data in an order which you specify manually, the easiest way to accomplish this is to add a separate field which contains an int4 value used by you to specify the order. E.g. myval | sortint _ H29| 2 214| 1 200| 3 099| 4 SELECT myval FROM mytable ORDER BY sortint; Troy > > I was wondering if you could help me out with a problem I'm having. Say for > example I have data coming out like: > H29 > 214 > 200 > 099 > > How would I use a sort to be able to make the output look like > > 214 > H29 > 200 > 099 > > I don't believe their is a way to do an ORDER BY in a specific order. Any > help you could give me this problem would be very much appreciated. > > Jeff Morrison > _ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(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] Case Insensitive Queries
select * from account where lower(username) = lower('test');
Troy
>
> Is it possible to execute a query using a where clause that allows case
> insensitive comparison between a field and text.
>
> For example:
>
> select * from account where username = 'test'
>
> where username could be 'Test', which would be a match. As is, this
> compare is case sensitive.
>
> grep'd the source, but stricmp is only used for keywords and not actual
> column data.
>
> Any help would be greatly appreciated.
>
> Thanks,
>
> Mark
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
-------(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Case Insensitive Queries
Can you please explain in little more detail? I am curious.
I haven't noticed any discussion about upper() being different from
lower() when it comes to such comparisons. As far as I know, upper()
and lower() only operate on ascii characters a-z. If you are using the
default locale, neither function should have any impact on characters
in the extended ascii range.
If upper() and lower() operate on characters in 8859-1 and other character
sets when the appropriate locale is set, then a difference in the behavior
of upper() and lower() would seem like a bug.
If you can shed some light on this, I would appreciate it.
Thanks,
Troy
>
> Mark writes:
> > Is it possible to execute a query using a where clause that allows case
> > insensitive comparison between a field and text.
>
> select * from account where upper(username) = upper('test')
>
> (Upper used because, as has been remarked on this list and in other places,
> folding from richer character sets is likely to get better matches this way).
>
> And yes, you can create an index on upper(fieldname).
>
> Dan
>
> ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Case Insensitive Queries
I use a few of them, and in my opinion there is a distinct group of
characters at last in the 8859-1 character set which have a lower and
upper case instance. The ranges are 0xC0 to 0xDD for upper case and 0xE0
to 0xFD for upper (with the exception of 0xD0, 0xD7, 0xF0, and 0xF7).
I haven't examined all the relevant docs, so I might be wrong. The lists
are based on my own observations of the characters in question.
There is probably no harm in sending a few extra bytes, so I am appending
a related function below. If someone finds a flaw with the function,
please tell me; that would be greatly appreciated.
I am also including a list of related characters. This email is going
out with 8859-1 as the charset, so I hope you are able to view them.
UPPER CASE:
192: À (0xc0) 193: Á (0xc1) 194: Â (0xc2) 195: Ã (0xc3)
196: Ä (0xc4) 197: Å (0xc5) 198: Æ (0xc6) 199: Ç (0xc7)
200: È (0xc8) 201: É (0xc9) 202: Ê (0xca) 203: Ë (0xcb)
204: Ì (0xcc) 205: Í (0xcd) 206: Î (0xce) 207: Ï (0xcf)
209: Ñ (0xd1) 210: Ò (0xd2) 211: Ó (0xd3)
212: Ô (0xd4) 213: Õ (0xd5) 214: Ö (0xd6)
216: Ø (0xd8) 217: Ù (0xd9) 218: Ú (0xda) 219: Û (0xdb)
220: Ü (0xdc) 221: Ý (0xdd)
LOWER CASE:
224: à (0xe0) 225: á (0xe1) 226: â (0xe2) 227: ã (0xe3)
228: ä (0xe4) 229: å (0xe5) 230: æ (0xe6) 231: ç (0xe7)
232: è (0xe8) 233: é (0xe9) 234: ê (0xea) 235: ë (0xeb)
236: ì (0xec) 237: í (0xed) 238: î (0xee) 239: ï (0xef)
241: ñ (0xf1) 242: ò (0xf2) 243: ó (0xf3)
244: ô (0xf4) 245: õ (0xf5) 246: ö (0xf6)
248: ø (0xf8) 249: ù (0xf9) 250: ú (0xfa) 251: û (0xfb)
252: ü (0xfc) 253: ý (0xfd)
SKIPPED
208: Ð (0xd0)
215: × (0xd7)
222: Þ (0xde)
240: ð (0xf0)
247: ÷ (0xf7)
254: þ (0xfe)
CREATE FUNCTION lower8859_1 (text) RETURNS text
AS '/usr/include/pgsql/lib/str8859_1.so'
LANGUAGE 'C';
/* No warranty of any kind, use at your own risk. Use freely.
*/
text * lower8859_1 (text * str1) {
text * result;
int32 len1 = 0, i;
unsigned char * p, * p2, c;
unsigned char upper_min = 0xC0;
unsigned char upper_max = 0xDD;
len1 = VARSIZE(str1) - VARHDRSZ;
if (len1 <= 0)
return str1;
result = (text *) palloc (len1 + 2 + VARHDRSZ);
if (! result)
return str1;
memset (result, 0, len1 + 2 + VARHDRSZ);
p = VARDATA(result);
p2 = VARDATA(str1);
for (i=0; i < len1; i++) {
c = p2[i];
if (isupper(c) || (c >= upper_min && c <= upper_max && c != 0xD0 && c != 0xD7))
p[i] = c + 0x20;
else
p[i] = c;
}
VARSIZE(result) = len1 + VARHDRSZ;
return result;
}
Troy
> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> > If upper() and lower() operate on characters in 8859-1 and other character
> > sets when the appropriate locale is set, then a difference in the behavior
> > of upper() and lower() would seem like a bug.
>
> Au contraire ... upper() and lower() are not symmetric operations in
> quite a few non-English locales. I'll let those who regularly work with
> them give specific details, but handling of accents, German esstet (sp?),
> etc are the gotchas that I recall.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Large Objects - lo_export
You must be logged in as the unix user postgres.
(Presuming /home/postgres/ is writable by postgres and
the file doesn't exist.)
Try doing
touch /home/postgres/junk.tgz
from the command line. It should also fail.
Troy
>
>
> Hi to list !
>
> while running psql as 'postgres' user, i executed the following query.
>
> 'select lo_export(img.image, '/home/postgres/junk.tgz') from img where
> data = 'tar file';
>
> The result of the above command is as follows.
>
> ERROR: lo_export: can't open unix file
> "/home/postgres/junk.tgz": Permission denied
>
> i performed the above query as superuser ('postgres'). I hope it' having
> all the superuser previleges.
>
> I am not sure why i am getting the above error.
>
> Any one got any clue for it.
> thanx in advance.
>
> guru.
> bk SYSTEMS (P) Ltd.
> P . N . G U R U P R A S A D
> ---
>
>
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Urgent: How to set autocommit off in postgres.........
Hi , I read one of solution to andreas problem of how to set autocommit off but my problem still persists. I am basically writing a function in plpgsql language which performs a number of update and insert statements but on a failing condition it rollbacks inorder to maintain integrity in the database. I have tried using the following code : create function abc() returns char as' begin begin work; insert into mytable values(1); // pseudo code if (conditions fails) then rollback work; end work; ... .. end; ' language 'plpgsql'; But this code fails miserably by not executing without errors since autocommit is on.Moreover I could not find ~/.psqlrc (or /etc/psqlrc). I am using Red Hat Linux 7.1 and Postgres 7.1.2. I badly in need of help as my deadline closes by to submit my project. Hope u would consider this as SOS call from ur friend. Waiting in anticipation of early reply, Regards, Advid Parmar New Delhi, India ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL]maximum parameters limit to function & manipulating array in plpgsql
Hi,
I have 2 major problems bothering me and probably u
are the best person to help me out :-
1)
Well I read one of ur solutions to sending more
tham 16 parameters to a function in plpgsql. I have changed the value of
FUNC_MAX_ARGS (a/k/a INDEX_MAX_KEYS) in
\usr\local\plpgsql\include\config.h after
stoping the postmaster service.
On restarting the service after having
changed config.h I am atill experiencing the same problem. What should I do
now?
2)
Secondly I am trying to use arrays as below
:
create function ftest(integer[]) returns integer[]
as'declaren
$1%type;begin.
...
n:={1,12,11};
return n;end;'language 'plpgsql';
If I declare n as integer[3] it gives a compilation
error. So I tried it as above. Can I do any array manipulations at all in
plpgsql like assigning,copying etc values in to other arrays in plpgsql. If so
how ??
Do help me out since I have got stuck
with both these problems.
Regards,
Advid
Newgen Software Tech
[SQL] Implicit v Explicit joins...
So I wasn't paying enough attention to postgres upgrades and missed that 7.1 actually has outer joins, so I'm currently happily rebuilding, but I had a few questions about the explicit join syntax and preformance issues. 1. Say i have a tables called "married" (marriedid, husband, wife), "people" (peopleid, firstname, lastname, townid), and "towns" (townid, townname) (not the exact exaple, but should be close enough). I want to get a list of all couples (fullname of both husband/wife with hometown) where the hometown of one equals 'foo'; Would this be the proper way of setting up the query? SELECT h.firstname, h.lastname, h.hometown, w.firstname, w.lastname, w.hometown FROM married m JOIN people h ON (m.husband=h.peopleid) JOIN people w ON (m.wife=w.peopleid) LEFT OUTER JOIN towns ht (h.townid=ht.townid) LEFT OUTER JOIN towns wt (w.townid=wt.townid) WHERE ht.townname='foo' OR wt.townname='foo'; 2. In general is explicit outer join more efficient than the old union select syntax? Is the outer join syntax just syntacic sugar (does it decode into the union selects)? 3. I think I saw someone that explicit joins occur in order, giving the planner less room to optimize, is this correct? I've often heard that you want to preform your inner joins before the other joins in order to limit the size of the tables being used. Will the planner consider putting implicit inner joins before the explicit outers or do all explicits occur first? Thank you. Eric Nielsen ---- mail2web - Check your email from the web at http://mail2web.com/ . ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Update Help
Hi, Let say I have 2 Product table, both of them has columns ProductID and Price What is the update command if I want to update all Prices of first table to be equal with Price in second table? Please Help. Yudie
[SQL] Update help
Hi, Let say I have 2 Product table, both of them has columns ProductID and Price What is the update command if I want to update all Prices of first table to be equal with Price in second table? Please Help. Yudie
[SQL] psql history
Hi everibody, i have installed Postgres 7.2.2 from a tarball, but using psql i can not have the history of the last command. When i used Postgres from rpm this useful element worked very well! Why that? Best Regards Massimo Arnaudo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] create index
i don't know how to create the index about the following statement. SELECT b.screen_id AS screen_id, b.name AS screen_name FROM tbl_showlog AS a CROSS JOIN tbl_screen AS b CROSS JOIN tbl_company AS c WHERE a.screen_id = b.screen_id AND b.company_id = c.company_id AND c.company_id = 1 AND c.is_active = 1 GROUP BY b.screen_id, b.name ORDER BY b.screen_id many thx! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] can i decrease the query time?
i created index already. how can i decrease the query time more. number of record is over 1 million. the following is the query plan. Group (cost=34082.59..34085.62 rows=61 width=112) -> Sort (cost=34082.59..34082.59 rows=607 width=112) -> Nested Loop (cost=0.00..34054.51 rows=607 width=112) -> Nested Loop (cost=0.00..125.64 rows=2 width=108) -> Nested Loop (cost=0.00..8.84 rows=1 width=22) -> Index Scan using tbl_member_pkey on tbl_member d (cost=0.00..5.14 rows=1 width=18) -> Index Scan using tbl_company_pkey on tbl_company c (cost=0.00..3.68 rows=1 width=4) -> Index Scan using tbl_adpage_pkey on tbl_adpage b (cost=0.00..112.65 rows=332 width=86) -> Index Scan using tbl_showlog_adpage_id_idx on tbl_showlog a (cost=0.00..20370.75 rows=5466 width=4) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] error in copy table from file
hi i'm using postgreSQL 7.3 b1. when i try to populate my tables with the files that contain all the data, i have some troblues: for example, my table has the following fields: -date (date) -id_street (int2) -flux_h_0_1 (float8) -flux_h_1_2 (float8) . . . -flux_h_23_0 (float8) the input file has semicolon as delimiters and it't like this 2000-01-01;25;325.236;-0.123; and so on ;1.2 2000-01-01;26;323.45;-1.23; 1.66 if i execute the command copy table from 'path/file' using delimiters ';' it returns the following: 'RROR: copy: line 1, Bad float8 input format '-0.123 i can't understand why it's saying it's wrong or another error in importing other tables can be: ": can't parse "ne 1, pg_atoi: error in "125 and 125 is the last number of a row. what can i do to solve this problem? thanx in advance for your help massimo ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] save data from views
first of all, thanx for anyone who answered my previous question... i've understood what was wrong... now, i've got another question, i know it could sound stupid, but i have not such a great practice with postgres. i'm asking you: is it that a way to save values from a view? i'm using postgres to calculate pollutant emissions by cars, and i make some queries and create views. i would like to save the results from the views but i can't find a way to export them. once again thanx in advance for your help, massimo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] save data from views
On Mon, 23 Dec 2002, [iso-8859-1] [EMAIL PROTECTED] wrote: > > > > first of all, thanx for anyone who answered my previous question... i've understood >what was wrong... > now, i've got another question, i know it could sound stupid, but i have not such a >great practice with postgres. i'm asking you: is it that a way to save values from a >view? > i'm using postgres to calculate pollutant emissions by cars, and i make some queries >and create views. i would like to save the results from the views but i can't find a >way to export them. > once again thanx in advance for your help, massimo You could write a program in your favourite language that selects and prints the rows of your views. Also you could # create table tempviewdata as select * from ; Why would you want to save the values of your views?? thanx for your answer. i'm developing a database that outputs pollutant emissions that has to be imported by the GRASS _ GIS program. in that way i can produce vector maps of the city i'm studying with pollutant emissions as attributes. to do this, i had to put the COPERT emissions model in the database, and create views to display all the results. but i also need to save this results, if i want to open them in other programs!!! massimo ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] database broken ?
hi all the pg_dump utilitie return the follow message. Database version: PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC 2.96 Archiver (db) version:7.1.2 Proceeding deespite mistmatch. getTables():SELECT (for PRIMARY KEY) failed on table AnomaliasEstacion. Exlanation from backend: ERROR: Attribute 'oid' not found Any ideas ? What's going wrong? The power failure on the server several times ago ,is posible couse this problem ? thanks in advance. Marcelo Diaz Cortez --- ¿Todavía no navegás con Keko? Hacé click aquí: http://www.keko.com.ar ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] date_trunc for 5 minutes intervals
Hi All, I am wanting to perform the equivalent of date_trunc to 5/10/15 minute intervals. As this does mnot seem to be natively supported by date_trunc, can anyone point me in the right direction to possible write the SQL ro acheive the desired outcome? Tia, Darren ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] date_trunc for 5 minutes intervals
Hi,
Thanks for the several replies both on and off the list.
To be more specific, I am wanting to aggregate data to a 5/10/15 min
interval. Currently, I am aggregating data that falls in hour / day /
month / year periods for both count() and sum(). The sql I am currently
using is:
SELECT count(id) AS count, sum(conn_bytes) AS
sum, hisec_port, conn_protocol,
date_trunc('hour'::text, datetime) AS date_trunc
FROM trafficlogs
WHERE (conn_outbound = false)
GROUP BY date_trunc('hour'::text, datetime),
conn_protocol, hisec_port
HAVING (count(*) = ANY (
SELECT count(*) AS count
FROM trafficlogs
GROUP BY hisec_port, date_trunc('hour'::text, datetime)
ORDER BY count(*) DESC)
);
Which produces:
count sum hisec_portconn_protocol date_trunc
12192 5,050 2003/09/17 00:00:00
11176 5,050 2003/09/17 01:00:00
12192 5,050 2003/09/17 02:00:00
11176 5,050 2003/09/17 03:00:00
10160 5,050 2003/09/17 04:00:00
- if you know of a more efficient way to do this than the sql above, pls
let me know
In my previous post I should have said I wanted to aggregating data in
5/10/15 min intervals in a similar manner to the above
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] INHERITS and Foreign keys
Hi
I have some problem with INHERITS and foreign keys. I do not know if I have
not got the clue or not. Anyway I have tried to simplify the problem so
that you can guide me on the right track.
I have two slightly different object A and B where 95% is common both of
data and operations. Among those are the primary key. I then put the common
stuff into P and let A and B inherit from P. It seems to work fine and
behaves like I assumed, except for foreign keys. I have a different object
L that links to P through P's PRIMARY KEY. But when I try do make an insert
it fails with foreign key constraint failure.
Below is a compressed code example illustrating the problem. If anyone can
tell me if I am trying the impossible, it is a bug, a todo, a never do, or
a patch, I would be very happy. I did search through the mailing lists to
see if I could find the answer. I found some articles about inheritance and
foreign keys, but I could not see the relevance to my problem.
My assumption is that everything you add to A and B will be seen in P. The
results of the selects indicates that I am correct. However the behavior of
the FOREIGN KEY indicates that that is not true. The references can see ONLY P.
I am using version postgresql 7.4.
Code:
CREATE TABLE p(id SERIAL PRIMARY KEY);
CREATE TABLE a(a char(2)) INHERITS(p);
CREATE TABLE b(b char(2)) INHERITS(p);
INSERT INTO p(id) VALUES(default);
INSERT INTO p(id) VALUES(default);
INSERT INTO a(a) VALUES('a1');
INSERT INTO a(a) VALUES('a2');
INSERT INTO b(b) VALUES('b1');
INSERT INTO b(b) VALUES('b2');
CREATE TABLE l(id SERIAL PRIMARY KEY, pRef bigint REFERENCES p(id));
INSERT INTO l(pRef) VALUES(1);
INSERT INTO l(pRef) VALUES(2);
INSERT INTO l(pRef) VALUES(3); --- This one fails ! You see it is there on
the select * from p.
psql:testInheritRef.sql:25: ERROR: insert or update on table "l" violates
foreign key constraint "$1"
DETAIL: Key (pref)=(3) is not present in table "p".
Here is result from SELECT.
SELECT * FROM p;
id
1
2
3
4
5
6
(6 rows)
SELECT * FROM ONLY p;
id
1
2
(2 rows)
SELECT * FROM a;
id | a
+
3 | a1
4 | a2
(2 rows)
SELECT * FROM b;
id | b
+
5 | b1
6 | b2
(2 rows)
---(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] INHERITS and Foreign keys
At 20:55 12/13/2003, you wrote: On Sat, 13 Dec 2003, [EMAIL PROTECTED] wrote: > Hi > > I have some problem with INHERITS and foreign keys. I do not know if I have > not got the clue or not. Anyway I have tried to simplify the problem so > that you can guide me on the right track. Foreign keys, unique and primary key constraints do not meaningfully inherit currently. At some point in the future, that's likely to change, but for now you're pretty much stuck with workarounds (for example, using a separate table to store the ids and triggers/rules on each of the tables in the hierarchy in order to keep the id table in date.) Is there a time frame for this ? a patch, a month, a year, 7.5 or a beta version ? Thank you for the quick response. Henning ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] query not using index for descending records?
Hi All, I have this table; id - Serial datetime - timestamp without timezone with the index as index idx_trafficlogs_datetime_id on trafficlogs using btree (datetime,id); When performing the following query: explain select datetime,id from trafficlogs order by datetime,id limit 20; QUERY PLAN Limit (cost=0.00..2.31 rows=20 width=12) -> Index Scan using idx_trafficlogs_datetime_id on trafficlogs (cost=0.00..1057.89 rows=9172 width=12) (2 rows) however, I am wanting to return the last 20 records. I have been using: explain select datetime,id from trafficlogs order by datetime,id desc limit 20; QUERY PLAN - Limit (cost=926.38..926.43 rows=20 width=12) -> Sort (cost=926.38..949.31 rows=9172 width=12) Sort Key: datetime, id -> Seq Scan on trafficlogs (cost=0.00..322.72 rows=9172 width=12) as you can see, a sequential scan is performed. How do I get pg to use an index scan for this query. The table in a production environment grows by approx 150,000 records daily, hence long term performance is a major factor here - for each additional day of data, the above query takes an additional 6-8 secs to run. Tia, Darren ---(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] query not using index for descending records?
Hi, | You probably don't want to do that. The DESC only applies to the | one expression it follows. What you want is probably: | explain select datetime,id from trafficlogs order by | datetime desc,id desc limit 20; This is exactly what I was after - worked a treat! Thanks. Darren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] a wierd query
hi
i have a wierd problem and i require an equally weird query.
1) backgound
Table test:
CREATE TABLE main_table (
string_A varchar( 20),
string_B varchar( 20),
);
-- both columns are identical in nature and usage
INSERT INTO main_table VALUES('abcd','qrst');
INSERT INTO main_table VALUES('efgh','efgh');
INSERT INTO main_table VALUES('ijkl','abcd');
INSERT INTO main_table VALUES('abcd','ijkl');
INSERT INTO main_table VALUES('qrst','uvwx');
2) problem:
i require a query that gives me a result set of the form
'abcd'
'efgh'
'ijkl'
'qrst'
'uvwx'
that is i require the dictinct values from (visualizing each column
result as a set) the union of the two columns
3) questions
a) is a query like this possible that can give me the desired result
b) if so what would it be.
4) remarks
i can get the solution using a temporary table and with repeated
"insert into temporary select $column from main_table"
thanks in advance
ashok
mail2web - Check your email from the web at
http://mail2web.com/ .
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] typecasting numeric(18,4) to varchar/text
hi how can i typecast a numeric(18,4) value (that i'm extracting from a table) into a a varchar (which i'll be inserting into another table) that is: create table a (a numeric(18,4)); create table b (b varchar(25)); insert into a values(12000.43); insert into b select (a.a)::varchar; the above gives the followig error ERROR: Cannot cast type 'numeric' to 'character varying' alternately try (this is my real requirement) insert into b select 'ID'||((a.a)::varchar); there has been no results on google thanks in advance ashok ---- mail2web - Check your email from the web at http://mail2web.com/ . ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] assistance on self join pls
Hi all, I have the following firewall connection data. datetime | protocol | port | inside_ip| outside_ip | outbound_count | outbound_bytes -+--+---++-- --++--- 2004-05-05 05:00:00 |6 |21 | 192.168.11.191 | 205.227.137.53 | 6 | 3881 2004-05-05 05:00:00 |6 | 22326 | 192.168.11.191 | 205.227.137.53 | 1 | 2592 2004-05-05 05:00:00 |6 | 38005 | 192.168.11.191 | 205.227.137.53 | 1 | 51286 2004-05-05 05:00:00 |6 | 51861 | 192.168.11.191 | 205.227.137.53 | 1 | 42460 2004-05-05 05:00:00 |6 | 52095 | 192.168.11.191 | 205.227.137.53 | 1 | 2558 2004-05-05 05:00:00 |6 | 59846 | 192.168.11.191 | 205.227.137.53 | 1 |118 2004-05-05 05:00:00 |6 | 60243 | 192.168.11.191 | 205.227.137.53 | 1 | 2092 2004-05-05 06:00:00 |6 |21 | 192.168.11.185 | 205.227.137.53 | 6 | 3814 2004-05-05 06:00:00 |6 | 29799 | 192.168.11.185 | 205.227.137.53 | 1 |118 2004-05-05 06:00:00 |6 | 30138 | 192.168.11.185 | 205.227.137.53 | 1 | 2092 2004-05-05 06:00:00 |6 | 30215 | 192.168.11.185 | 205.227.137.53 | 1 | 42460 2004-05-05 06:00:00 |6 | 51279 | 192.168.11.185 | 205.227.137.53 | 1 | 1332 2004-05-05 06:00:00 |6 | 52243 | 192.168.11.185 | 205.227.137.53 | 1 | 51286 2004-05-05 06:00:00 |6 | 60079 | 192.168.11.185 | 205.227.137.53 | 1 | 2558 I am wanting to aggregate / collapse each entry to something similar to: datetime | protocol | port | inside_ip| outside_ip | outbound_count | outbound_bytes -+--+---++-- --++--- 2004-05-05 05:00:00 |6 |21 | 192.168.11.191 | 205.227.137.53 | 12 | 104987 2004-05-05 06:00:00 |6 |21 | 192.168.11.185 | 205.227.137.53 | 12 | 103660 I have not had much success - any assistance greatly appreciated Darren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] timestamp precision - can I control precision at select time or set for all time?
I have a database with several tables that use timestamp without time zone type. I upgraded from an older pgsql and have code that does not expect the precision in the select output. Is there a way I can get the effect of zero precision without modifying all the tables? The docs say it usees "default precision" as set in the "timestamp literal". I wasn't able to find docs describing what literals are. If there's a way I can set this to zero precision for everything, that'd save me a bunch of time. with thanks, Royce ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] timestamp precision - can I control precision at select time
thanks, christoph. i did go ahead and retool all the tables, but glad to know about the casting option. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Copy Views From Database?
Hi Guys, I'm using PostGreSQL with a Java project since I'm a proffesional Java developer. I've a database that have about 120 views in PostGreSQL 7.0 that I need to copy into PostGreSQL 8.0. I can copy them one-by-one in pgAdmin but I don't have the time! Is there a maybe a tool that I can use to copy views as-is from one database into another? Kind Regards, Lennie De Villiers Java Developer CorePat Systems (Pty) Ltd www.corepat.com mail2web - Check your email from the web at http://mail2web.com/ . ---(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
[SQL] schema inspection
Hi, supposing to have a small DB: TABLE a ( id SERIAL PRIMARY KEY ); TABLE b ( id SERIAL PRIMARY KEY, idA INTEGER NOT NULL REFERENCES a(id) ); How can I inspect pg_schema/information_schema to "detect" the relation between "b" and "a" via "idB"? TIA Roberto Colmegna Tiscali ADSL 4 Mega Flat Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95 € al mese! Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
R: R: Re: [SQL] schema inspection
> pg_catalog.pg_constraint is your (only?) friend. I have already examintated this table without results. Seem not to be a "human-readable" table :( TIA Roberto Colmegna Tiscali ADSL 4 Mega Flat Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95 € al mese! Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
R: Re: R: R: Re: [SQL] schema inspection
> >SELECT c1.relname,c2.relname from pg_constraint cons,pg_class c1, pg_class >c2 where cons.conrelid=c1.oid and cons.confrelid = c2.oid; > >for column(s) names you will have to do extra homework. Thanks! I have obtained my query! Here is: SELECT (SELECT relname FROM pg_catalog.pg_class WHERE oid=conrelid) AS fromTbl, (SELECT relname FROM pg_catalog.pg_class WHERE oid=confrelid) AS toTbl, (SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid=conrelid AND conkey[1]=attnum) AS viaCol FROM pg_catalog.pg_constraint AS rel WHERE contype='f'; TIA Roberto Colmegna Tiscali ADSL 4 Mega Flat Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95 € al mese! Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
R: Re: R: Re: R: R: Re: [SQL] schema inspection
>Well thats it if you use only *single column* Foreign keys. >In the general case the above will need extra work. Are you sure? I have tested query with 3 table with multiple FK and it works (see below) [as you can see "C" table have two FK to A and B] test=> SELECT test-> (SELECT relname FROM pg_catalog.pg_class WHERE oid=conrelid) AS fromTbl, test-> (SELECT relname FROM pg_catalog.pg_class WHERE oid=confrelid) AS toTbl, test-> (SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid=conrelid AND conkey[1]=attnum) AS viaCol test-> FROM pg_catalog.pg_constraint AS rel WHERE contype='f' ORDER BY fromTbl; fromtbl | totbl | viacol -+---+ b | a | ida c | a | ida c | b | idb TIA Roberto Colmegna Tiscali ADSL 4 Mega Flat Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95 € al mese! Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] join on a like
If you use a btree index on the column, you should be able to use it in the like: http://www.postgresql.org/docs/8.1/interactive/indexes-types.html. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Serial number of a record
Hi List, I have one table something like this Name Age = tom 20 ram 25 shyam 30 balram15 hari 10 Now i want serial number of a record. Suppose if i will fire a query "select Name from tablename where age=30". it will give output "shyam" but i also want record no. other then oid, in this case it is 3. is it possible? if yes then how? plz. help me. Thanks Ashok mail2web.com - Microsoft® Exchange solutions from a leading provider - http://link.mail2web.com/Business/Exchange ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Failing join with set returning function
Please, remove my address from this Mailing List. thanks -- Leggi GRATIS le tue mail con il telefonino i-mode di Wind http://i-mode.wind.it/
[SQL] Problem with now() in function pgsql
Hi, I have a problem in a function pgsql with Reading TIMESTAMP calling the function now() the problem is this: At the begin of function I read timestamp... select now() into TS_BEGIN; the function execute some statement... Many seconds after I reread the timestamp select now() into TS_END; and TS_BEGIN is identical TS_END!!! Can anyone help me? Thanks in advance. Luke. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Problem commit in function
Hi, I need to execute COMMIT in a function pgsql, there is a way? Can I have any example? Thanks in advance. Luke. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Commit every processed record
Hi, I have to execute commit for evey record that i processed during a cursor fetch in a function. There is a way to do it? Thanks in advance. Luke. -- 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] case insensitive search
Joern, select myfield from tablea where lower(myfield) = 'mysearch'; or select myfield from tablea where myfield ~* 'mysearch'; Troy > > Hello together, > > how can I handle case insensitive search in a table? > > > > -- > Linux is like wigwam - no windows, no gates, apache inside. > In diesem Sinne > Joern > > >
Re: [SQL] Group BY ...(cont.)
E.g. select workgroup from job group by workgroup; will that do? Troy > > Ok, if I want to use group by, then I have to put every select field > afterwards to group by. But I do want to group by one field. > Hence > select workgroup,id from job group by workgroup,id; > would create groups of 1? > But I want to group by workgroups. > Sorry if my explanation is fuzzy. > A. > > >
Re: [SQL] GROUP by finish&&last day of month
Not quite sure what you mean, but how about this:
select date_part('day', 'Jul 01 00:00:00 2000 PDT'::datetime-1);
You must specify Jul for June, i.e. always the following month.
You could get around that too, but since I don't know why you
need to do what you asked, I'll leave it at this.
Troy
>
> Ok, if I want to get non-aggregat data in groups, I use order by. This
> group by seemed so logical though, but the fruit that u can't have, is
> usually the most sweet.
>
> New question, how to get the last day of month(order data by last day of
> month). And to prevent chain letter from misunderstanding, no I don't
> intend to look it up from calendar and then input it manually.
>
> A.
>
>
>
Re: [SQL] max timestamp
Michael Sterling wrote: i'm trying to get the max time stamp, from each day, of a range of dates, not just the max time stamp for the complete range dates but for each day. I don't trust the news client I was using. So will answer again from the Netscape version; This worked for me: I have a table, sis, with schema: Table "sis" Column | Type | Modifiers +--+--- time | timestamp with time zone | stat10 | integer | ipf10 | real | ipf30 | integer | select date_part ( 'doy',time ) as jday, max ( time ) from sis group by jday (doy means day of year, which I called jday) Boyd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
