[SQL] small problem
hi to all I am doing a webpage and i manipulate data from a postgres database. I need to do the same thing with a oracle database. I am using pg_dump to transfer the database data from postgres to oracle but there are a few problems with same data types in a table when i try to import it to a oracle database. create table (...) (...) duration interval default 7 hourDaytimestamp with time zome (...) if i replace this two data types with varchar2(50) it works but i would like if there's a better choice thanks for your time and replay _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(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] small proble
hi to all I am doing a webpage and i manipulate data from a postgres database. I need to do the same thing with a oracle database. I am using pg_dump to transfer the database data from postgres to oracle but there are a few problems with same data types in a table when i try to import it to a oracle create table (...) (...) duration interval default 7 hourDaytimestamp with time zome (...) if i replace this two data types with varchar2(50) it works but i would like if there's a better choice thanks for your time and replay _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(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] small problem
create table (...) (...) duration interval default 7 do you really think it's worth spending 12 bytes for your intervals? i usually use either timestamps or integers for intervals. btw, as i know default 7 would be 7 msecs :) hourDaytimestamp with time zome consider using it without a time zone maybe. if you really mean time of a day use time [with time zone] (...) if i replace this two data types with varchar2(50) it works but i would like if there's a better choice it seems oracle still has some probs with sql date/time data types... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] bigger problem
in postgres database i created a table with a field like create table (...) (...) var text default '' not null (...) now i need to import the data from this table into a oracle database i used pg_dump I altered the field data type to (...) var varchar2(50) default '' not null (...) but when i was importing the the table into the oracle database all the rows with the field var='' were not inserted. Is there a way to work this around thanks for your time and replay _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Generating a cross tab (pivot table)
> I saw something that might somewhat a bit more > flexible solution using SQL. I don't know if it works > in PostgreSQL. I saw it at the MySQL site. > > The following is the URL: > http://www.mysql.com/articles/wizard/index.html > > Has anyone tried this on a PostgreSQL database ? No, not me. But as far as I can tell the SQL statements can quite easily be re-written in PostgreSQL: e. g. mysql> SELECT location, SUM(IF(gender='M',1,0)) AS M, SUM(IF(gender='F',1,0)) AS F -> FROM locations INNER JOIN employees USING (loc_code) GROUP BY location; becomes SELECT location, SUM(CASE WHEN gender='M' THEN 1 ELSE 0 END) AS "M", SUM(CASE WHEN gender='F' THEN 1 ELSE 0 END) AS "F", FROM locations LEFT JOIN employees ON (locations.loc_code=employees.loc_code) GROUP BY location; And this goes for the perl script as well. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Generating a cross tab II (pivot table)
Based on the e-mails on "Generating a cross tab (pivot table)",
I can give you a PLpgSQL procedure to automatically generate a
cross tab from any relation now.
It's my first steps in PLpgSQL. I am pretty sure this is not the
best way to implement, but I wanted to get some experience, so I
did it this way.
For all, who missed it last week, again the objective:
There is a relation "sales",
holding the sales of different products of different vendors.
The task is to generate a report which shows the sales
of every vendor and every product.
Consider the following table populated with some data:
CREATE TABLE sales (
product TEXT,
vendor TEXT,
sales INTEGER
);
INSERT INTO sales VALUES ( 'milk' , 'mr. pink' , 12 ) ;
INSERT INTO sales VALUES ( 'milk' , 'mr. brown' , 8 ) ;
INSERT INTO sales VALUES ( 'honey' , 'mr. green' , 2 ) ;
INSERT INTO sales VALUES ( 'milk' , 'mr. green' , 34 ) ;
INSERT INTO sales VALUES ( 'butter', 'mr. pink' , 17 ) ;
INSERT INTO sales VALUES ( 'butter', 'mr. brown' , 2 ) ;
INSERT INTO sales VALUES ( 'honey' , 'mr. pink' , 19 ) ;
The following query generates the report:
CREATE VIEW sales_report AS
SELECT product,
SUM(CASE vendor WHEN 'mr. pink' THEN sales ELSE 0 END) AS "mr. pink ",
SUM(CASE vendor WHEN 'mr. brown' THEN sales ELSE 0 END) AS "mr. brown",
SUM(CASE vendor WHEN 'mr. green' THEN sales ELSE 0 END) AS "mr. green",
SUM(sales) AS "sum of sales"
FROM sales GROUP BY product ;
SELECT * FROM sales_report ;
product | mr. pink | mr. brown | mr. green | sum of sales
-+---+---+---+--
butter |17 | 2 | 0 | 19
honey |19 | 0 | 2 | 21
milk|12 | 8 |34 | 54
(3 rows)
It's obvious this approach is most inflexible.
As soon as there is a new vendor, one has to re-write the query and add
SUM(CASE vendor WHEN 'mr. new' THEN ... ,
So what we need is a tool to automatically adapt the view to new vendors
resp. new products.
Here it is (choosing good mnemonics is not my favourite discipline):
CREATE OR REPLACE FUNCTION
create_pivot_report(TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS INTEGER AS '
DECLARE
pg_views_rtype pg_views%ROWTYPE;
vname_paramALIAS FOR $1;
pivot_column ALIAS FOR $2;
select_column ALIAS FOR $3;
pivot_tableALIAS FOR $4;
aggregate_func ALIAS FOR $5;
aggr_columnALIAS FOR $6;
pivot_record RECORD;
create_viewTEXT;
BEGIN
SELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param;
IF FOUND THEN
EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ;
END IF;
create_view :=
''CREATE VIEW '' || quote_ident(vname_param) ||
'' AS SELECT '' || quote_ident(select_column) ;
FOR pivot_record IN
EXECUTE ''SELECT DISTINCT CAST(''
|| quote_ident(pivot_column)
|| '' AS TEXT) AS col1 FROM ''
|| quote_ident(pivot_table)
|| '' order by '' || quote_ident(pivot_column)
LOOP
create_view :=
create_view || '','' || aggregate_func ||
''(CASE '' || quote_ident(pivot_column) ||
'' WHEN '' || quote_literal(pivot_record.col1) ||
'' THEN '' || quote_ident(aggr_column) ||
'' ELSE 0 END) AS "'' || pivot_record.col1 || ''"'' ;
END LOOP;
create_view :=
create_view || '','' || aggregate_func ||
''('' || quote_ident(aggr_column) || '') AS "'' || aggregate_func ||
'' of '' || aggr_column || ''" FROM '' || quote_ident(pivot_table) ||
'' GROUP BY '' || quote_ident(select_column);
EXECUTE create_view ;
RETURN 0;
END;
' LANGUAGE 'plpgsql' ;
-- where
-- vname_paramALIAS FOR $1; -- the view's name to create
-- pivot_column ALIAS FOR $2; -- the pivot column (entries to be CASEd)
-- select_column ALIAS FOR $3; -- the select column (entries to be grouped)
-- pivot_tableALIAS FOR $4; -- the name of the table to work on
-- aggregate_func ALIAS FOR $5; -- the name of the aggregate function
-- aggr_columnALIAS FOR $6; -- the aggregate column (entries to be aggregated)
First try:
SELECT create_pivot_report
('sales_report2','vendor','product','sales','sum','sales');
SELECT * FROM sales_report2 ;
gives you 'sales_report2' as a copy of 'sales_report'.
Now add another data set:
INSERT INTO sales VALUES ( 'butter', 'mr. blue' , 11 ) ;
Re-write the view by:
SELECT create_pivot_report
('sales_report2','vendor','product','sales','sum','sales');
And here we go
SELECT * FROM sales_report2 ;
product | mr. blue | mr. brown | mr. green | mr. pink | sum of sales
-+--+---+---+--+--
butter | 11 | 2 | 0 | 17 | 30
honey |0 | 0 | 2 | 19 | 21
milk|0 | 8 |34 | 12 | 54
(3 rows)
More examples:
SELECT create_pivot_report
('sales_report3','vendor','product','sales','avg','sales');
SELECT create_pivot_report
('sales_report4'
Re: [SQL] Permission on insert rules
On Fri, 2002-11-08 at 21:40, Bruce Momjian wrote: > Josh Berkus wrote: > > > > Luis, > > > > > Just a question. > > > I'm writing some rules to insert/update some data in my database, and I > > > gave all the privileges on that view to the user, and only select on the > > > tables. > > > When that user inserts data using the view, I thought that was user > > > postgres that will do the rest ! But I got permission denied on those > > > tables. > > > The idea was to create a layer, with the views, giving to that user > > > permission on views to insert and update, and not to tables. > > > Is this possible ? > > > > This is a known problem. > > > > I know that permissions for Functions has been addressed in 7.3. However, I > > am not sure about permissions for updatable views. Tom, Bruce? > > Views have always had their own permissions. > If the functions can fire as there creator instead of there caller, then I would think as long as the creator has insert/update views on the base table, you should be able to do updateable rules and give only permissions to the view for the caller. (Though maybe you have to use triggers rather than rules to do this?) Does that sound right? Robert Treat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Permission on insert rules
Robert, > If the functions can fire as there creator instead of there caller, > then > I would think as long as the creator has insert/update views on the > base > table, you should be able to do updateable rules and give only > permissions to the view for the caller. (Though maybe you have to use > triggers rather than rules to do this?) Does that sound right? I don't know. Can you test it? -Josh ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] bigger problem
Carlos, > in postgres database i created a table with a field like > > create table (...) > (...) > var text default '' not null > (...) > > now i need to import the data from this table into a oracle database > i used pg_dump > > I altered the field data type to > > (...) > var varchar2(50) default '' not null > (...) > > but when i was importing the the table into the oracle database all > the rows with the field var='' were not inserted. > Is there a way to work this around This is a problem with your configuration of Oracle import. See your Oracle documentation; we cannot help you here. -Josh Berkus ---(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] Generating a cross tab (pivot table)
Christoph, Lud, > > I saw something that might somewhat a bit more > > flexible solution using SQL. I don't know if it works > > in PostgreSQL. I saw it at the MySQL site. > > > > The following is the URL: > > http://www.mysql.com/articles/wizard/index.html > > > > Has anyone tried this on a PostgreSQL database ? Actually, I'm rather annoyed with the article author. He first claims that Joe Celko reccommends expensive add-on software for crosstabs (Joe does not) and then goes on to use one of Joe's own solutions. However, given the author's thouroughness otherwise, I'm sure the innaccuracy is due to some kind of misunderstanding. There are, in fact, 3 simple SQL-based solutions to the crosstab problem. Which one you use depends on the shape of your data. I am not going to cover them in detail here (I'll save that for an article) but to sum up: 1) The SUM(CASE()) statement method, as outlined in the article, which is good for crosstabs expecting small numbers of columns. Or, in the case of this article, good for RDBMS which do not support subselects. 2) The LEFT JOIN + Sub-Select method, which is good for crosstabs with lots of columns but not that many rows in the crosstabbed table. 3) The "crosstab reference grid" method, which is good for large tables and crosstabs with lots of columns, but requires setup and maintainence by trigger. Joe Celko covers these 3 types in "SQL for Smarties". I will write a PostgreSQL implementation in a later article. -Josh Berkus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] bigger problem
Carlos Sousa writes: > (...) > var varchar2(50) default '' not null > (...) > > but when i was importing the the table into the oracle database all the rows > with the field var='' were not inserted. > Is there a way to work this around Yes, don't use Oracle. In Oracle, '' is the same as null, so the constraint is not satisfied. -- Peter Eisentraut [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] Permission on insert rules
Luis, > Just a question. > I'm writing some rules to insert/update some data in my database, and I > gave all the privileges on that view to the user, and only select on the > tables. > When that user inserts data using the view, I thought that was user > postgres that will do the rest ! But I got permission denied on those > tables. > The idea was to create a layer, with the views, giving to that user > permission on views to insert and update, and not to tables. > Is this possible ? I just checked this. It works fine in 7.2.3. I think that you are missing a step. If you want to have an updatable view, then you need to define a Rule for updating it, such as: kitchen=# create rule update_password as on update to user_password kitchen-# do instead update "user" set "password" = NEW."password" kitchen-# where user_id = OLD.user_id; See the online docs, under Server Programming, for how to use the RULES system. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] pg_dump / dates:
Question for Tom Lockhart: pg_dump is generating some dates that look like: '2002-09-09 02:11:60.00' or '2002-10-31 23:59:60.00'. psql -f doesn't like these dates. I can sed the :60 to a :59 but -- in the latter case it moves the date backwards a day, which doesn't work for my app. I understand from reading some of the list threads that there's a good reason to allow for "second" field values greater than 59, and per an email thread I had with Tom Lane, this has been addressed -- that it was a rounding error issue on some platforms. I'm running postgres 7.2.2 on slackware 8.1 (kernel 2.4.18). Is there a workaround on this? Tks and kind regards, Martin Crundall ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL]
HI, Can someone pls help us with the following problem? Any help would be appreciated. Regards -Naren. Is stored procedures will return muliple rows in postgresql. If so a sample example is needed. This message is proprietary to Hughes Software Systems Limited (HSS) and is intended solely for the use of the individual to whom it is addressed. It may contain privileged or confidential information and should not be circulated or used for any purpose other than for what it is intended. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you are notified that you are strictly prohibited from using, copying, altering, or disclosing the contents of this message. HSS accepts no responsibility for loss or damage arising from the use of the information transmitted by this email including damage from virus. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
