Re: [SQL] Error message during compressed backup
Senthil Kumar S writes: > $ $ pg_dump -h 192.xxx.x.xxx -p 5432 -v testdb -f > /home/db_repository/testdb20031023.sql.tar.gz -u -F c > WARNING: owner of function "plpgsql_call_handler" appears to be invalid Run select proowner from pg_proc where proname = 'plpgsql_call_handler'; which gives you the ID of the user that owns this function. Then run select * from pg_user; to get the list of valid users. You may want to adjust the owner of the function to a valid user (use UPDATE). -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Regular expression problem
Matias Surdi <[EMAIL PROTECTED]> writes:
> Manuel Sugawara wrote:
> >Use something like '^[a-z]{2}$'
>
> so, is this a bug
No it is not. The sintax you are using is not supported.
Regards,
Manuel.
---(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] Regular expression problem
On 24 Oct 2003, Manuel Sugawara wrote:
> Matias Surdi <[EMAIL PROTECTED]> writes:
>
> > Manuel Sugawara wrote:
> > >Use something like '^[a-z]{2}$'
> >
> > so, is this a bug
>
> No it is not. The sintax you are using is not supported.
It certainly seems to work in Postgresql 7.4 beta 4:
create table test2 (info text);
CREATE TABLE
insert into test2 values ('ab');
INSERT 109169538 1
insert into test2 values ('abc');
INSERT 109169539 1
marl8412=# select * from test2 where info ~ '^[a-z]{2}$';
info
--
ab
Or was there more to that message I wasn't getting?
---(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] Regular expression problem
"scott.marlowe" <[EMAIL PROTECTED]> writes:
> It certainly seems to work in Postgresql 7.4 beta 4:
>
> create table test2 (info text);
> CREATE TABLE
> insert into test2 values ('ab');
> INSERT 109169538 1
> insert into test2 values ('abc');
> INSERT 109169539 1
>
> marl8412=# select * from test2 where info ~ '^[a-z]{2}$';
> info
> --
> ab
>
> Or was there more to that message I wasn't getting?
He was trying to use '[a-z]{2,2}', which doesn't work in PostgreSQL.
Regards,
Manuel.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] extend INSERT by 'INSERT INTO table FETCH ... FROM cursor' syntax
Please CC me, I am not subscribed. An imaginary SQL statement INSERT INTO table FETCH ... FROM cursor; looks almost the same as currently available INSERT INTO table SELECT ...; I tried it because I needed to insert a row in a table after I DELETEd a set of rows, something like this: BEGIN; DECLARE total CURSOR FOR SELECT SUBSTR(datetime,1,7)||'-01 00:00:00' as month, client, SUM(money) FROM stat WHERE SUBSTR(datetime,1,7)='2003-10' GROUP BY month,client; DELETE FROM stat WHERE SUBSTR(datetime,1,7)='2003-10'; INSERT INTO stat FETCH ALL FROM total; COMMIT; but it does not work, chokes on FETCH ALL. I want to sum up all the money by month, delete all the rows (possibly thousands of them) and insert one row per client with monthly totals. Obviously I cannot swap order of INSERT and DELETE here. I hesitate to post this to [EMAIL PROTECTED], do I have to? ;) -- Alexander Vlasenko ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] problem with sql
Hello to everyone, In my own database, I linked customers table with orders table using one-to-many relation. I need to check status of last order for each customer and then set customer's status. I made a query using LAST and GROUP BY to select last order for each customer and I wanted to use it in UPDATE query, but it seems to be impossible. Is here anybody who knows how to solve this problem? (I work with MS Access). Adam ---(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] date_trunc for 5 minutes intervals
+-Le 19/10/2003 23:38 +1000, email lists écrivait : | 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 achieve the desired outcome? maybe (date_part(epoch, field)::integer / 300 * 300)::timetamp for 5 minutes interval... -- Mathieu Arnold ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] naming conventions constraint
I'm at the very beginning of working with postgres. The aim is to replace / build a number of very large and very complex database currently residing in Oracle, MSSQL and Informix. In order to coordinate across a medium size DBA team I instituted a standard constraint naming convention suitable for the above 3 platforms. For example a foreign key would be FK_table#reftable#column. Unfortunately I don't appear to be able to use it with postgres. This is because I can't use the # character in constraint names. Does anybody have any suggestions as to an alternative that is also a single char? (I'm limited to 30 chars cos of oracle and manyof the current names at the limit). Any help appreciated, Julian. ---(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] Regular expression problem
On 24 Oct 2003, Manuel Sugawara wrote:
> "scott.marlowe" <[EMAIL PROTECTED]> writes:
>
> > It certainly seems to work in Postgresql 7.4 beta 4:
> >
> > create table test2 (info text);
> > CREATE TABLE
> > insert into test2 values ('ab');
> > INSERT 109169538 1
> > insert into test2 values ('abc');
> > INSERT 109169539 1
> >
> > marl8412=# select * from test2 where info ~ '^[a-z]{2}$';
> > info
> > --
> > ab
> >
> > Or was there more to that message I wasn't getting?
>
> He was trying to use '[a-z]{2,2}', which doesn't work in PostgreSQL.
Oh, ok. Thanks.
---(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] naming conventions constraint
rbt=# create table bob_is(your_uncle integer, constraint "bob_is#your_uncle" check(true)); CREATE TABLE rbt=# \d bob_is Table "public.bob_is" Column | Type | Modifiers +-+--- your_uncle | integer | Check constraints: "bob_is#your_uncle" CHECK true You need to quote strings which contain special characters. On Thu, 2003-10-23 at 16:30, Julian North wrote: > I'm at the very beginning of working with postgres. > > The aim is to replace / build a number of very large and very complex > database currently residing in Oracle, MSSQL and Informix. > > In order to coordinate across a medium size DBA team I instituted a standard > constraint naming convention suitable for the above 3 platforms. > > For example a foreign key would be FK_table#reftable#column. > > Unfortunately I don't appear to be able to use it with postgres. > > This is because I can't use the # character in constraint names. > > Does anybody have any suggestions as to an alternative that is also a single > char? (I'm limited to 30 chars cos of oracle and manyof the current names at > the limit). > > Any help appreciated, > > > > Julian. > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > signature.asc Description: This is a digitally signed message part
Re: [SQL] Regular expression problem
7.4 uses a completely new regex engine, so comparisons
with that will not be valid anyway.
Pre-7.4 regex behaviour is all documented:
Users Guide -> Functions & Operators -> Pattern
Matching
--- "scott.marlowe" <[EMAIL PROTECTED]> wrote:
> On 24 Oct 2003, Manuel Sugawara wrote:
>
> > Matias Surdi <[EMAIL PROTECTED]> writes:
> >
> > > Manuel Sugawara wrote:
> > > >Use something like '^[a-z]{2}$'
> > >
> > > so, is this a bug
> >
> > No it is not. The sintax you are using is not
> supported.
>
> It certainly seems to work in Postgresql 7.4 beta 4:
>
> create table test2 (info text);
> CREATE TABLE
> insert into test2 values ('ab');
> INSERT 109169538 1
> insert into test2 values ('abc');
> INSERT 109169539 1
>
> marl8412=# select * from test2 where info ~
> '^[a-z]{2}$';
> info
> --
> ab
>
> Or was there more to that message I wasn't getting?
>
>
> ---(end of
> broadcast)---
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to
[EMAIL PROTECTED])
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Referring to derived column name in a RECORD
Hi folks, I know I'm doing something wrong here but cannot make it work no matter how many/few quotes I use I'm trying to reference a column in a RECORD which is not a column name but a derived column. Any suggestions??? Example code below to highlight the problem: DECLARE r_app RECORD ; BEGIN FOR r_app IN SELECT to_char( created_timestamp, 'DDMMYY' ) AS "joined_on", last_name, first_name FROM customer WHERE cust_id = 123 LOOP r_app.last_name ; -- Easy to referenece this value but... r_app.joined_on ; -- HOW do I reference this value?...this does not work ... ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Referring to derived column name in a RECORD
David B wrote: SELECT to_char( created_timestamp, 'DDMMYY' ) AS "joined_on", r_app.joined_on ; -- HOW do I reference this value?...this does not work Try either making that first line: ... AS joined_on, (i.e. without the double quotes) or make the second one: r_app."joined_on"; (i.e. with double quotes) HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] Alias-Error
You have the alias in upper case: "C2", and postgres complains about a lower case "c2". May be this some case sensitivity problem ? Just a thought. HTH, Csaba. On Tue, 2003-10-21 at 09:17, Jost Richstein wrote: > Ok. The exact error message is as follows: > > SQLException Time : Mon Oct 20 13:15:25 CEST 2003 > SQLException ErrorCode : 0 > SQLException SQLState : null > SQLException Message : ERROR: Relation "c2" does not exist > SQLException Connection: 4878867 > > The query is something like this: > > SELECT DISTINCT C2.cmc_mchap, C2.cmc_sort > FROM sis_cmca, sis_cmca C2 > WHERE cm_status != 'U' AND sis_cmca.cmc_name='INTERN2000' AND > C2.cmc_name='INTERN2000' AND sis_cmca.cmc_mchap=C2.cmc_mchap > > Tom Lane wrote: > > > > Jost Richstein <[EMAIL PROTECTED]> writes: > > > I am running a query with alias (a self join) against > > > version 7.3.4 on Suse Linux 7.3 and on FreeBSD (v5?). > > > It runs fine on Linux, but produces an error on > > > FreeBSD: "unknown alias C2". > > > > The string "unknown alias" appears nowhere in the 7.3 sources. > > Are you sure you are talking to a Postgres database? > > > > regards, tom lane > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(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
