[SQL] indexing arrays in pgaccess's query interface is failing

2001-07-03 Thread David Stanaway 

Hi there, I am having some difficulties with using arrays in pgaccess 
relating to arrays.

Here is an example schema:

CREATE TABLE arraykeys (
akID int,
akName varchar(12),
PRIMARY KEY(akID)
);

CREATE TABLE items (
itID serial,
itProperties bool[],
PRIMARY KEY(itID)
);

--.. And some Data

INSERT INTO arraykeys VALUES (1,'Active');
INSERT INTO arraykeys VALUES (2,'Overdue');
INSERT INTO arraykeys VALUES (3,'Local');

INSERT INTO items (itProperties) VALUES ( '{1,0,1}');
INSERT INTO items (itProperties) VALUES ( '{0,1,1}');



--.. And now the query that I am having problems with.

SELECT itID, itProperties[akID], akName
FROM items, arraykeys;



In the readline client psql, the above select statement works perfectly
scratch-# FROM items, arraykeys;
  itid | itproperties | akname
--+--+-
 1 | t| Active
 1 | f| Overdue
 1 | t| Local
 2 | f| Active
 2 | t| Overdue
 2 | t| Local
(6 rows)


However

In pgaccess,
when I try to execute the same query in query builder,
I get the tcl error dialogue:
Error: invalid command name "akID"


Is there an alternate way indexing arrays in queries that I should be 
using?
Or is pgaccess just not suitable for this class of queries!


--
Best Regards
David Stanaway
.-
Technology Manager  -  Australia's Premier Internet Broadcasters
[EMAIL PROTECTED] Office +612 9357 1699
'-

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

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

[SQL] cumulative sum in aggregate query.

2001-07-10 Thread David Stanaway 

Hi there,

I have a query that gives me x/y data for a graph, and at the moment, 
the y data is relative.

EG:

x   |  y
1.2 | +1
1.9 | 0
3.4 | +4
5.2 | -2
6.7 | -1
9.3 | 0
11.3| -1

Now, I want to convert this result into a result like this:
x   |  y
1.2 | 1
1.9 | 1
3.4 | 5
5.2 | 3
6.7 | 2
9.3 | 0
11.3| 1

Does anyone have any suggestions as to how to do this?



--
Best Regards
David Stanaway
.-
Technology Manager  -  Australia's Premier Internet Broadcasters
[EMAIL PROTECTED] Office +612 9357 1699
'-

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

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

Re: [SQL] example of [outer] join

2001-07-20 Thread David Stanaway 
 On Friday, July 20, 2001, at 08:22  PM, Gary Stainburn wrote:

My view so far is:
CREATE view member_dets as
select *,
getphone(m.mphone) as phone,
getphone(m.mfax) as fax,
getphone(m.mmobile) as mobile,
getunitno(m.mid) as munitno
from members m,
address a,
teams t,
emails e
where m.madd = a.aid and
m.memail = e.eid and
m.mteam = t.tid;

Try

CREATE VIEW member_dets AS
SELECT *,
getphone(m.mphone) AS phone,
getphone(m.mfax) AS fax,
getphone(m.mmobile) AS mobile,
getunitno(m.mid) AS munitno
FROM members m
-- every member has an address
JOIN  address a ON m.madd = a.aid
-- not everyone has an email address
LEFT JOIN emails e ON m.memail = e.eid
-- every member belongs to a team
JOIN team t ON m.mteam = t.tid;

You will need postgresql 7.1 for this syntax, otherwise, refer to the docs on how do do outer joins using unions (Pretty messy esp if you have more than one...  One very good reason to upgrade to 7.1 if you are still using an earlier version of postgres)

==
David Stanaway
Personal: [EMAIL PROTECTED]
Work: [EMAIL PROTECTED]

Re: [SQL] nextval on insert by arbitrary sequence

2001-07-20 Thread David Stanaway 

Have you looked at the serial type?

This type creates an explicity sequence with a predictable name:
tblname_rowname_seq

and has a default value that selects the next val from that sequence.
You can get the value of the most recently inserted row in your session 
with
CurrVal('tblname_rowname_seq')

Hope this helps :)

On Saturday, July 21, 2001, at 10:04  AM, Dado Feigenblatt wrote:

> Josh Berkus wrote:
>
>> Dado,
>>
>> Maybe we're looking at this the hard way.  Have you thought of simply
>> putting in a DATETIME column and sorting by that?
>>
>> -Josh
>>
> Sorting? I might have expressed myself wrong.
> I'm not concerned about sorting.
> I'm concerned about giving rows in a single table an unique, sequential 
> numbered ID
> based on a sequence per project (as in a serial counter, as in 'create 
> sequence specific_project_sequence;')
> e.g.
> rows related to project A get a number from sequence A
> rows related to project B get a number from sequence B
>
> Is it clear now, or is it me who's not understanding what you're saying?
>
> Sorry for the confusion.
>
>
>
>
>
> -- Dado Feigenblatt Wild Brain, Inc.   
> Technical Director   (415) 553-8000 x???
> [EMAIL PROTECTED]   San Francisco, CA.
>
>
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>

--
Best Regards
David Stanaway

Technology Manager
Australia's Premier Internet Broadcasters
Phone: +612 9357 1699
Fax: +612 9357 1169
Web: http://www.netventures.com.au
Support: [EMAIL PROTECTED]

The Inspire Foundation is proudly supported by Net Ventures through the 
provision of streaming solutions for it's national centres.  The Inspire 
Foundation is an Internet-based foundation that inspires young people to 
help themselves, get involved and get online. Please visit Inspire at 
http://www.inspire.org.au


---(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] URGENT: restoring a database

2001-10-25 Thread David Stanaway 


On Friday, October 26, 2001, at 11:22  AM, Oleg Lebedev wrote:

> Hi,
> I think I got a problem here.
> I tried to restore my database from dump.bac file, which was created
> with
> pg_dumpall -o > dump.bac
> This is what I did:
>> createdb replica
>> psql -d replica -f dump.bac
>

Sounds like you didn't read up on pg_dump to closely.

What you probaly should have doe was not use pg_dumpall, but
pg_dump -o -F t livedb > livedb.dump.tar

The use pg_restore

My distribution doesnt have pg_dumpall, but I imagine that unlike pg_dump
where it does

  \connect - someuser

at the top.

It does
\connect origdb someuser

So you probably want to edit a copy of the dump file,
remove the db nmae from the connect statement, then drop the old db
then

You should be able to execute that section of the dump pertaining to the 
db you want to restore on the replacement original, and the new database.

EG:

Your edited snippet of the dump (Checcking all \connect statements to 
ensure they are eiter removed, or refer to currently connected 
database (-) is in dbdump.sql

psql -U username
 > CREATE DATABASE live;
 > \connect live
 > \i dbdump.sql
 > CREATE DATABASE replica;
 > \connect replica
 > \i dbdump.sql
 > \q



> Notice that I have two different databases stored in this file.
> This is what I got:
>
> You are now connected to database template1.
> DELETE 3
> psql:db_10_22_01.bac:7: ERROR:  CREATE USER: permission denied
> psql:db_10_22_01.bac:8: ERROR:  CREATE USER: permission denied
> psql:db_10_22_01.bac:9: ERROR:  CREATE USER: permission denied
> psql:db_10_22_01.bac:11: ERROR:  pg_aclcheck: invalid user id 503
> You are now connected to database template1 as user postgres.
> psql:db_10_22_01.bac:18: ERROR:  CREATE DATABASE: database "webspectest"
>
> already exists
> You are now connected to database webspectest as user postgres.
> CREATE
> DROP
> You are now connected as new user postgres.
> psql:db_10_22_01.bac:48: NOTICE:  CREATE TABLE/PRIMARY KEY will create
> implicit index 'activitytype_pkey' for table 'activitytype'
> psql:db_10_22_01.bac:48: ERROR:  Relation 'activitytype' already exists
> psql:db_10_22_01.bac:65: NOTICE:  CREATE TABLE/PRIMARY KEY will create
> implicit index 'dcr_pkey' for table 'dcr'
>
> Obviously, no database was created. Moreover, I can not access my
> neither of my existing databases anymore.
> When I try:
>> psql webspectest
> I get an error:
> psql: FATAL 1: user "olebedev" does not exist
>
> At this point I am completely stuck.
> Please help.
> thanks,
>
> Oleg
>
>
> ---(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
>
>
--
Best Regards
David Stanaway

Technology Manager
Australia's Premier Internet Broadcasters
Phone: +612 9357 1699
Fax: +612 9357 1169
Web: http://www.netventures.com.au
Support: [EMAIL PROTECTED]

The Inspire Foundation is proudly supported by Net Ventures through the 
provision of streaming solutions for it's national centres.  The Inspire 
Foundation is an Internet-based foundation that inspires young people to 
help themselves, get involved and get online. Please visit Inspire at 
http://www.inspire.org.au


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] how to sort a birthday list ?

2002-06-20 Thread David Stanaway 

On Thu, 2002-06-20 at 13:55, Michael Agbaglo wrote:
> Hi !
> 
> there's a nice query for retrieving the people who have their birthday 
> in the next n days: (taken from Joe Celko's SQL for Smarties, 1st Ed., 
> p. 76)
> 
> SELECT *
> FROM Persons
> WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( 
> CURRENT_DATE+60, dateofbirth ) )
> 
> ... but how do I sort the list ?


How about:
ORDER BY dateofbirth





signature.asc
Description: This is a digitally signed message part

Re: [SQL] is there a way to get hh:mm:ss given seconds

2002-07-09 Thread David Stanaway 

On Mon, 2002-07-08 at 09:28, Narendra A wrote:
> Sir,
> Is there a way in sql such that if I give seconds it should me return me
> hours:mins:seconds
> 
> Eg. Seconds hh:mm:ss
> 422  1:01:02


scratch=# SELECT 422::interval;
 interval 
--
 00:07:02
(1 row)

scratch=# \q
dstanawa@ciderbox:~$ bc -l
7*60+2
422

I don't know where you got 1:01:02 from.


--
David Stanaway



signature.asc
Description: This is a digitally signed message part

[SQL] Is there a better way than this to get the start and end of a month?

2002-09-21 Thread David Stanaway 

Here are the 2 functions I have at the moment. I was wondering if
someone had a better way?

CREATE OR REPLACE FUNCTION month_start (date)
  RETURNS date
  AS '
   DECLARE
day ALIAS FOR $1;
   BEGIN
RETURN day - (extract(''day'' FROM day)||'' days'')::interval + 
''1 day''::interval;  
   END;
  '
  LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION month_end (date)
  RETURNS date
  AS '
   DECLARE
day ALIAS FOR $1;
month int;
year int;
   BEGIN
month := extract(''month'' FROM day);
year  := extract(''year'' FROM day);
IF month = 12 THEN
 month := 1;
 year  := year +1;
ELSE
 month := month +1;
END IF;
    RETURN (''01-''||month||''-''||year)::date - 
''1 day''::interval;
END;
   '
   LANGUAGE 'plpgsql';

-- 
David Stanaway

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

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

[SQL] UPDATE FROM problem, multiple updates of same row don't seem to work

2004-08-09 Thread David Stanaway 
Here is an example:

CREATE TABLE tablea(
 id int PRIMARY KEY,
 flag int
);

CREATE TABLE tableb(
 aid int REFERENCES tablea(id),
 flag int
);

INSERT INTO tablea VALUES(1,0);
INSERT INTO tablea VALUES(2,0);

-- Flags for 1st row of tablea - When ORed, should be 7
INSERT INTO tableb VALUES(1,1);
INSERT INTO tableb VALUES(1,2);
INSERT INTO tableb VALUES(1,4);

-- Flags for 2nd row of tablea - When ORed, should be 5
INSERT INTO tableb VALUES(2,1);
INSERT INTO tableb VALUES(2,4);


UPDATE tablea
SET flag = tablea.flag | tableb.flag
FROM tableb
WHERE tablea.id = tableb.aid;


SELECT * from tablea;
 id | flag
+--
  1 |1
  2 |1

-- Desired output is
 id | flag
+--
  1 |7
  2 |5


Is there a way around this so that I can get the desired output?

-- 
David Stanaway <[EMAIL PROTECTED]>

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

   http://www.postgresql.org/docs/faqs/FAQ.html