Re: [SQL] Question #4 about PL/PGSQL
Tom, > Damn! When is the "holy grail" of PostgreSQL going to be > stable enough to use? Beta3 still has a "not advisable > for > production" warning, and I'm being tied up in knots by > the > number of things I need in 7.1. Ooops! That may have sounded a little harsh. I am a bit desperate, but that's hardly your fault. Thank you so much for all of your hard work as our "database engine" guru, and for staying up late to answer our questions! -Josh
[SQL] Yet one more question
Folks, ALTER TABLE won't work until 7.1. CUrrently, I have a table that needs one small change, but it's refrenced as a foriegn key by 7 other tables. Any suggestions on how I can make the table change without having to drop and re-create 8 tables? -Josh Berkus
Re: [SQL] Question #4 about PL/PGSQL
Tom, > Not only varchar --- any other parameters, period. And > not only that, > but the result is taken as NULL no matter what you try to > return. Not quite. I tried the following: Parameters: $1=integer, $2=NULL, $3=varchar And I had the function test for nulls. It read the first parameter, but not $3. It did return the string telling me it had found nulls, however. > This is a longstanding deficiency that is fixed by the > new function > manager in 7.1. Damn! When is the "holy grail" of PostgreSQL going to be stable enough to use? Beta3 still has a "not advisable for production" warning, and I'm being tied up in knots by the number of things I need in 7.1. Would it help if I sent more money? :-) -Josh
Re: [SQL] Question #4 about PL/PGSQL
"Josh Berkus" <[EMAIL PROTECTED]> writes: > 4. If I pass a NULL to any of the parameters of a PL/PGSQL > function, any (other) VARCHAR parameters are set to NULL as > well. Not only varchar --- any other parameters, period. And not only that, but the result is taken as NULL no matter what you try to return. This is a longstanding deficiency that is fixed by the new function manager in 7.1. regards, tom lane
[SQL] Question #4 about PL/PGSQL
Folks, Oh, yes, one more: 4. If I pass a NULL to any of the parameters of a PL/PGSQL function, any (other) VARCHAR parameters are set to NULL as well. Thanks! -Josh
[SQL] Three questions regarding PL/PGSQL
Folks, 1. While I am able to use the %TYPE declaration within PL/PGSQL functions, I am unable to use this declaration in the parameters for the function -- I get 'Parse Error at or near "."' 2. When I have a PL/PGSQL function return a custom message using a VARCHAR return value, I get backslashes in front of all of the spaces in the message. 3. Given the odd/weak exception handling within the current Postgres database engine, has anyone developed strategies to make certain that their PL/PGSQL functions do not perform inconsistent updates? If so, can you give some examples? Anybody (Jan?) who can shed some light on the above will receive my enthusiastic gratitude in ASCII text. -Josh Berkus P.S. I'm using Postgres 7.0.2 on SuSE 7.0 and use pgaccess extensively for function editing. P.P.S. My most heartfelt gratitude to Jan Wieck for writing some decent compile error text into the PL/PGSQL compiler, and to Constantin Teodorescu for putting a terrific function editor into pgaccess!
Re: [SQL] pg_dump error
Tatsuo Ishii <[EMAIL PROTECTED]> writes: Can not create pgdump_oid table. Explanation from backend: 'ERROR: cannot create pgdump_oid Is there a way to repair this? I would guess that this means there is a file named "pgdump_oid" hanging around in the database directory, no doubt leftover from some previous pg_dump cycle. Manually removing the file should get you up and running again. However, that just begs the question of why the file is there... regards, tom lane
Re: [SQL] pg_dump error
> >Can not create pgdump_oid table. Explanation from backend: 'ERROR: > >cannot create pgdump_oid > > > >Is there a way to repair this? > > > > If there was an earlier crash while running pg_dump, the table may already > exist. If so, try deleting it. Also, can you confirm that the username > under which you run pg_dump has the rights to create tables? I have observed it several times on 7.0.x while using pg_dumpall. Since pg_dumpall would create pgdump_oid as many times as the number of databases, I suspect there might be an internal cache problem in the backend. However this problem was hard to reproduce, I have not tackled it yet. If someone has a reproducible data, please let us know... -- Tatsuo Ishii
Re: [SQL] Querying date interval
> > create table testdate (field1 date);
> > insert into testdate values ('2000-09-30');
> > insert into testdate values ('2000-10-20');
> > insert into testdate values ('2000-11-25');
> > select * from testdate where field1 between '2000-10-01' and
> > '2000-11-30' ;
>
> >field1
> >
> > 2000-09-30 < why is it here??
> > 2000-10-20
> > 2000-11-25
> > (3 rows)
>
> Curious. I can't reproduce this error in EST5EDT timezone, using
> either 7.0.2 or current.
>
> What PG version are you using, and with what timezone setting?
> Is 2000-09-30 or 2000-10-01 a daylight-savings transition date in your
> timezone?
I'm using 7.0.2 version on debian 2.2. Standard timezone here is GMT-3,
but we´re currently in DST since 8 Oct 2000.
Never changed datestyle setting - I'm only using ISO8601 notation to
manipulate values.
And now I've noticed something even weird - leaving psql interface and
entering it again, that query show me the right result! But if I drop the
table, create the same structure with the same data then I get wrong
result again!?
Well, anyway, I'll try upgrading to 7.0.3...
--
Renato
Sao Paulo - SP - Brasil
[EMAIL PROTECTED]
Re: [SQL] pg_dump error
At 22:13 15/01/01 +0200, Johann Spies wrote: > >Can not create pgdump_oid table. Explanation from backend: 'ERROR: >cannot create pgdump_oid > >Is there a way to repair this? > If there was an earlier crash while running pg_dump, the table may already exist. If so, try deleting it. Also, can you confirm that the username under which you run pg_dump has the rights to create tables? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
[SQL] Re: [INTERFACES] outer join in PostgreSql
Mauricio Hipp Werner writes: > I need help, which is the symbol used in postgreSql to carry out the outer > join. > > in oracle the is used (+) > in sybase the is used * and > in postgreSql? No symbol, just words. http://www.postgresql.org/devel-corner/docs/postgres/sql-select.htm -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] pg_dump error
Version 6.5.3 on Debian Potato My daily cron job reported : Can not create pgdump_oid table. Explanation from backend: 'ERROR: cannot create pgdump_oid Is there a way to repair this? Johann -- J.H. Spies - Tel. 082 782 0336 "For I know that my redeemer liveth, and that he shall stand at the latter day upon the earth" Job 19:25
[SQL] Weird script problems solved...
Hi, About a week back I'd posted a message asking for help with a script that as far as I could tell was well formed. The problem was with 2 INSERT statements directly after the CREATE TABLE statement of the table that I was trying to insert data into. I'd used C single/multi line comments of the form: /* woo */ and /* foo * bar * baz */ throughout the script. In order to determine what the problem was I ran the script through a C preprocessor to remove the comments. Without comments the script worked as expected. I replaced the C style comments with SQL '--' comments and the script also worked as expected. Unfortunately, I didn't maintain a copy of the C commented script to help PSQL developers but thought this important to mention anyway. Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6999 North Vancouver, BC, V7M 2J5
[SQL] Adding a parameter to a trigger
Hi together, I am still trying to implement my "userlog". At the moment I can insert a message like "User xy added". What I want to have is, that I can insert something like "User xy added by z". Can anyone tell me, how to parse a parameter to the trigger (I think, only the application knows what value the paramete should have!) Thanx Joern -- Linux is like a wigwam - no Windows, no Gates and Apache inside
[SQL] Sort by relevance
Hello,I am trying to use a perl script to break an input string
into keywords andpull out all the records that match any of them:my
(@chips)= split(' ', $f_name);foreach my $chip (@chips)
{ next if ($stoppers=~/ $chip / || length($chip) <
3); if ($query) {$query .= " OR name ~* '.*$chip.*'
";} else {$query = "SELECT * FROM info WHERE name ~*
'.*$chip.*' ";}}this works fine for pulling the records but I would
like to sort them by howmany of the "chips" matched.On a set
like:name-Bill SmithTed SmithTed
Brownan input of : Ted Smithwould give me a query string
of: SELECT * FROM info WHERE name
~* '.*Ted.*' or name~*'.*Smith.*'which returns all records - which is
what I want - but I would like to haveTed Smith in the first row followed by
the other records grouped by name andsorted on another column (city or
state in the real table)Thanks for your help,Chad
McKay
Re: [SQL] How to display a unixtimestamp from a timestamp record?
> select user_name, date_part( 'epoch' , timestamp 'acct_timestamp') from
> tbacct limit 2;
>
> it said ERROR: Bad timestamp external representation 'acct_timestamp'
> how should i represent date_part( 'epoch' , timestamp 'acct_timestamp')
> to work?
select user_name, date_part ('epoch', acct_timestamp) from tbacct limit 2;
should work... the single quotes are making a literal string value, so
your query is saying take the epoch of the timestamp represented by the
literal 'acct_timestamp' rather than the value of the field.
[SQL] How to display a unixtimestamp from a timestamp record?
Hello! again i was faced to a problem i was trying to do create view in wich i had the next fields: CREATE TABLE "tbacct" ( "user_name" character varying(32), "nas_identifier" character varying(15), "nas_port" int4, "acct_session_id" character varying(15), "caller_id" character varying(16), "called_station_id" character varying(16), "framed_ip_address" character varying(15), "acct_disconnect_cause" character varying(40), "acct_session_time" int4, "acct_timestamp" timestamp, "realm" character varying(64) ); and i need view in wich acct_timestamp is a int8 USERNAME IP-ADDRESS 74 000B2E5B NULL 6572 IP-ADDRESS NULL 4 2000-11-13 14:19:45-03 REALM and i want it to be displayed something like this: USERNAME IP-ADDRESS 74 000B2E5B NULL 6572 IP-ADDRESS NULL 4 974135985 REALM i know it is possible using date_part select date_part( 'epoch' , timestamp '2000-11-13 14:30:40'); gave the right answer . but when i executte select user_name, date_part( 'epoch' , timestamp 'acct_timestamp') from tbacct limit 2; it said ERROR: Bad timestamp external representation 'acct_timestamp' how should i represent date_part( 'epoch' , timestamp 'acct_timestamp') to work? Thanks in Advance -- Marcelo Bartsch R. [EMAIL PROTECTED] PSINet Chile /O /Ot Minimize execution speed (default) Microsoft C/C++ Compiler Documentation, 'Enviroment and Tools', p531 Telefono : +56-2-3979000 Numero de Fax: +56-2-3979090 Numero de eFax : (815) 366-3177
Re: [SQL] Querying date interval
- Original Message - From: "Renato De Giovanni" <[EMAIL PROTECTED]> > Hi, > > Is there any SQL workaround to get the right results from the select > statement bellow? Or am I doing something wrong?? > > select * from testdate where field1 between '2000-10-01' and > '2000-11-30' ; > >field1 > > 2000-09-30 < why is it here?? > 2000-10-20 > 2000-11-25 > (3 rows) > Someone else mentioned DATESTYLE - the other thing to check is that you haven't got a local summertime adjustment on 30th Sep or 1st Oct - that caused issues in some previous versions of postgres IIRC (have a rummage in the archives) - Richard Huxton
Re: [SQL] improve performance
Alexaki Sofia <[EMAIL PROTECTED]> writes: > Initially I loaded all data in one transaction. Subsequently, I increased > the number of buffers and disabled fsync() (-o -F) and I loaded the > data again but the performance was almost unchanged. Does it make sense?? > How can I improve performance? To note that no indexes are created on the > tables and that I load both "insert into" and "create table" statements. If you can load the data with a COPY command, instead of individual INSERTs, it'll go a lot faster. See also http://www.postgresql.org/devel-corner/docs/postgres/populate.htm > I want to reduce the space (8 Kb) allocated by > DBMS when more space is required to load the data in a table. I reduced > the parameter BLCKSZ but the space allocated remains the same, that is 8 > KB. > How can the allocated space be reduced? If the space was reduced > would loading time and query time increase? I've heard lots of people want to increase BLCKSZ, but you're the first one who ever wanted to reduce it. You sure you want to do this? It's going to make the maximum row length uncomfortably short. Anyway, you probably forgot to do a full rebuild after changing config.h. "make clean" before "make all" is the only way to be sure the configuration change propagates to all the code. Don't forget you will have to do an initdb, also, so back up your data with the old code first. regards, tom lane
Re: [SQL] Querying date interval
Renato De Giovanni <[EMAIL PROTECTED]> writes:
> create table testdate (field1 date);
> insert into testdate values ('2000-09-30');
> insert into testdate values ('2000-10-20');
> insert into testdate values ('2000-11-25');
> select * from testdate where field1 between '2000-10-01' and
> '2000-11-30' ;
>field1
>
> 2000-09-30 < why is it here??
> 2000-10-20
> 2000-11-25
> (3 rows)
Curious. I can't reproduce this error in EST5EDT timezone, using
either 7.0.2 or current.
What PG version are you using, and with what timezone setting?
Is 2000-09-30 or 2000-10-01 a daylight-savings transition date in your
timezone?
regards, tom lane
[SQL] Re: Querying date interval
On Mon, Jan 15, 2001 at 12:19:56 -0200, Renato De Giovanni wrote: > select * from testdate where field1 between '2000-10-01' and > '2000-11-30' ; > >field1 > > 2000-09-30 < why is it here?? > 2000-10-20 > 2000-11-25 It works fine for me (7.0.3, Debian GNU/Linux "unstable"). I suspect your problem has to do with the "DATESTYLE" setting you're using; does it help if you do "SET DATESTYLE TO 'ISO'" prior to INSERTing your test values? HTH, Ray -- [Open Source] is the finest expression of the free market. Ideas are encouraged to proliferate and the best thinking wins. By contrast, most corporations today operate in a central planning straitjacket. http://www.thestandard.com/article/display/0,1151,15772,00.html
[SQL] Querying date interval
Hi,
Is there any SQL workaround to get the right results from the select
statement bellow? Or am I doing something wrong??
create table testdate (field1 date);
insert into testdate values ('2000-09-30');
insert into testdate values ('2000-10-20');
insert into testdate values ('2000-11-25');
select * from testdate where field1 between '2000-10-01' and
'2000-11-30' ;
field1
2000-09-30 < why is it here??
2000-10-20
2000-11-25
(3 rows)
Thanks in advance!
--
Renato
Sao Paulo - SP - Brasil
[EMAIL PROTECTED]
[SQL] improve performance
Hello, A) I am going to load a huge amount of data in the DBMS using JDBC and I want to reduce as much as possible the required loading time. Initially I loaded all data in one transaction. Subsequently, I increased the number of buffers and disabled fsync() (-o -F) and I loaded the data again but the performance was almost unchanged. Does it make sense?? How can I improve performance? To note that no indexes are created on the tables and that I load both "insert into" and "create table" statements. -- B) I want to reduce the space (8 Kb) allocated by DBMS when more space is required to load the data in a table. I reduced the parameter BLCKSZ but the space allocated remains the same, that is 8 KB. How can the allocated space be reduced? If the space was reduced would loading time and query time increase? Thank you in advance for your help Sofia Alexaki
[SQL] using pg as a db backend in Access
Hi, I recently posted a problem with connecting to a pg db server from access. Now I found that the pg/odbc driver under windows lets choose between three protocols, 6.x, 6.y, 6.z. I am using pg 7.x as the db server. Could this be the problem? I managed to get the tables linked and shown, but I cannot insert new data in access. Please help, Markus
