[SQL] Relation in tables
Hello all... I am starting in Postgresql... And I have a question: I am developing a DB system to manage products, but the products may be separated by departaments (with its respectives coluns)... Like: CREATE TABLE products( id serial primary key, desc valchar(100), ... ); Okay, but the products is typed by "amount departament" and this departament should not have access to other coluns like "values, Money, etc...". The "finances departament" may modify the data into products table, but this departament should not have access to coluns like "amounts, etc...". I' ve tried to create the products table with INHERITS but its not right... look: CREATE TABLE prod_amounts ( amount_min numeric, amount_cur numeric, amount_max numeric, ... ) INHERITS products; CREATE TABLE prod_values ( buy_value money, sen_value money, ... ) INHERITS products; Okay, but the problem is: I can INSERT a prod_amounts normaly and automaticaly the products table will be filled, but when i try to modify the data in prod_amounts (references in products) there is no data I think its not right ( I am sure :/ ). How Can I do it??? How Can I References Between Prod_amounts and Prod_Values automaticaly? remembering the Amounts departament may not access the Values departament data and the Values departament may not access the amounts data... And the products will be registred (typed) in Amount departament Thanks for all. ---(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] Relation in tables
Use a view per department, which show/hide the columns according to your liking. Give each department a schema and put everything related to it inside for cleanliness. Use UPDATE triggers on the views, which in fact write to the products table, so that the departments can only update the columns you like. You can even make some columns readable but not writeable, by raising an exception if a modification is attempted on that column. If you want to reuse your code between departments, you will want all the views to have the same columns, so make them return NULL for the fields that they cannot see. Finally don't forget to make the products table inaccessible the departments. Okay, I will use Triggers to manage the insert and update table and viewers to select records, but need I use more than one table (with inherits)? Or I just use product table with the ALL departaments coluns and check the perms into Triggers and Viewers??? Thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Multiples schemas
Hi, Is there a way to construct a multi schema in my data base? Something like: mysystem.finances.money.tables mysystem.finances.money.functions mysystem.finances.credits.tables mysystem.finances.credits.functions mysystem.amount.products.. Or can I use another database like: createdb DB1 createdb DB2 psql DB1 select * from DB2.schema.table Or i need to construct the tables in the same database and the same schema like: mysystemdb.amount.products mysystemdb.amount.vendors mysystemdb.amount.clients mysystemdb.finances.money Could I create a multi schema into another schema ??? or is there only one level for schema sctructs? Thanks for all ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Table like a field
Hello.
Is there any way to build a table that contain the coluns name for the other
table fields? like this:
create table people(id serial primary key, name varchar(50) );
create table people_fields ( field_name varchar(30) );
insert into people_fields values ('occupation');
insert into people_fields values ('address');
then I create any function or view to get:
SELECT * FROM people; //may return
id - name - | ocuppation - address |
Then if I insert a new record in the people_fields table, the new record will
appear as a new field in the people table.
Of course, its not a new field, but when i select by my function/view i can see
anything like it.
How can I create this function to aggregate the both tables??
Thank you.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] Generic Function
Hi, Can I built a generic function like: CREATE FUNCTION f (text) RETURNS TEXT as $$ return 'select * from $1'; $$ I know its impossible as writed. Also I have looked for EXECUTE procedure but it not run the correct function. Is there a way to construct this clause? Using plpgsql/pltcl/anything ??? Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Generic Function
Oh sorry.
I was not clearly.
I've wanted to create a function that suport to select a "parameter variable"
table. Like: return 'select * from $1'.
The Postgresql does not suport this sql function becouse the $1 variable is
considerate as a table... the Postgresql return an error like: The table "$1"
doesn't exist.
Then there is no way to construct this function as SQL function, but I could
make it in PL/TCL function and execute normaly look:
set search_path to public;
create table tb1(vl text);
insert into tb1 values ('aaa');
create table tb2(vl text);
insert into tb2 values ('bbb');
CREATE or REPLACE FUNCTION select_table(text, text, text) returns text as
$$
set schema_name $1;
set table_name $2;
set field_name $3;
set select_query "select $field_name AS select_table from ";
set point ".";
spi_exec $select_query$schema_name$point$table_name;
return $select_table;
$$ language 'pltcl';
Then:
SELECT select_table('public','tb1','vl');
SELECT select_table('public','tb2','vl');
The spi_exec execute the query as a variable ($select_query$...) and
the return
of this query (select $field_name AS select_table) will be the variable
"select_table" for the pl/tcl function. Then I return this variable (return
$select_table).
Is it right?! Is there a better way to make it?
The Pl/Pgsql can built this function? And the SQL Standard?
Thanks...
Quoting George Weaver <[EMAIL PROTECTED]>:
- Original Message -
From: <[EMAIL PROTECTED]>
To:
Sent: Monday, March 14, 2005 12:15 PM
Subject: [SQL] Generic Function
Hi,
Can I built a generic function like:
CREATE FUNCTION f (text) RETURNS TEXT as
$$
return 'select * from $1';
$$
...
If you show us what you've tried and the results you received we may be able
to help more.
...
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Trigger with parameters
me','$field_name')";
set sql_query [subst -nocommands $system_getcheckfieldvalue];
spi_exec "select $sql_query as result";
return $result;
$$ language 'pltcl';
SELECT system_checkfieldvalue('main','products_codes','internal','COMPUTER-1');
--Will return FALSE (becouse there is a product with internal code COMPUTER-1
SELECT system_checkfieldvalue('main','products_codes','internal','COMPUTER-2');
--Will return TRUE (becouse there is NOT a product with internal code COMPUTER-2
Okay, but look the problem:
When I try to create a Trigger procedure to check (with the
system_checkfieldvalue() function) the Postgresql doesn't support the function
with parameters!!!
CREATE FUNCTION trigger_system_checkfieldvalue(schema_name text, table_name
text) RETURNS trigger AS
$$
BEGIN
select
system_checkfieldvalue(schema_name,table_name,NEW.field_name,NEW.value) as
result;
IF NOT result THEN
RAISE EXCEPTION 'The validate of the system field name is False';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER products_codes_checkfieldvalue BEFORE INSERT OR UPDATE ON
main.products_codes FOR EACH ROW EXECUTE PROCEDURE
trigger_system_checkfieldvalue('main','products_codes');
--- ERROR: function trigger_system_checkfieldvalue() does not exist
But the function trigger_system_checkfieldvalue() EXIST! With (text,text)
parameters.
I can't built the trigger for this table (main.products_codes) using the check
field in main_system.products_codes.
What is wrong???
Sorry for the big text mail message, but I think if I did not put the database
definitions, it will be very difficult to understand.
Thanks (and sorry again)...
Lucas Vendramin,
Brazil
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] New record position
Hello,
I am using Slackware Linux 10, Postgresql 8.0.1.
My computer had a incorrectly power down (last week) and I have executed the
vacuum command:
VACCUM FULL ANALYZE VERBOSE;
to recicle and verify my database.
Before the power-down, all records had inserted into a table have displayed at
LAST record. Like:
SELECT * from tb1;
f1| f2
--|
rec1 | vl1
INSERT into tb1 values ('rec2','vl2');
SELECT * from tb1;
f1| f2
--|
rec1 | vl1
rec2 | vl2
But After the power-down and vacuum, the new records inserted have appeared in
random location (FIRST, between other records, etc...). Ie:
INSERT into tb1 values ('rec3','vl3');
SELECT * from tb1;
f1| f2
--|
rec1 | vl1
rec3 | vl3 <<=
rec2 | vl2
Why it? I can't undestand why the new record location was change. Shouldn't it
apper at the LAST record???
What need I do??
Thank you.
Lucas Vendramin
Brazil
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] New record position
Okay, I will use the "order by" clause.
I was worried about it. I have thought that my database had crashed.
Thank you.
Quoting Oleg Bartunov :
This is a feature of relational databases, you should explicitly specify
ordering if you want persistent order.
btw, why do you bothering ?
Oleg
On Wed, 30 Mar 2005 [EMAIL PROTECTED] wrote:
Hello,
INSERT into tb1 values ('rec3','vl3');
SELECT * from tb1;
f1| f2
--|
rec1 | vl1
rec3 | vl3 <<=
rec2 | vl2
Why it? I can't undestand why the new record location was change.
Shouldn't it apper at the LAST record???
---(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
[SQL] Chield Serial
Hi. Is there a way to make a serial columm that works with father table??? I.e: I have created a table called "vending" and its chield table called "items". The items table have a serial columm, but I need the serial colum starts with 0 for each vending. create table vending ( id serial primary key, --Ok, this serial: 0 - xxx date_ date ); create table items ( vending_id references vending, items_id serial, -- THIS SERIAL NEED to start 0 for each vending_id primary key (vending_id,items_id) ); insert into vending values (1,now()); insert into vending values (2,now()); insert into items values (1); insert into items values (1); insert into items values (2); insert into items values (2); select * from items; vending_id | items_id 1 | 1 1 | 2 2 | 3<<=== Here! The items_id need to be 1 (start again for each vending_id) 2 | 4<<== Thanks all. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Record Log Trigger
Hi all, I am building a database in postgresql and I made a function that returns the system time and the current user... like this: CREATE OR REPLACE FUNCTION generate_idx() returns text as $$ select to_char(now(),'MMDDHHMISSUSTZ')||CURRENT_USER; $$ language 'SQL'; CREATE OR REPLACE FUNCTION TG_idxm() RETURNS trigger AS $$ BEGIN NEW.idxm = generate_idx(); RETURN NEW; END; $$ LANGUAGE plpgsql; And my all tables have the "idxm" field, its something like a log for the record, to know Who and When the record have changed. I.e: CREATE TABLE products( id serial primary key, description varchar(50), ... idxm varchar(100) ); CREATE TRIGGER TG_products_idxm BEFORE INSERT or UPDATE on products FOR EACH ROW EXECUTE PROCEDURE TG_idxm(); Okay, it runs fine... but my question is: Is it right??? In the future (when the database will be bigger with many of millions records) this functions for each table will depreceate my database performance??? Is there any other way to build it??? Thank you ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Function or Field?
Hi. What is the better way to store the last record for a translation??? I.E: The data for the last product vendding. What is better: a) Create a field in "product" table and create a Trigger (before insert or update into vendding table) to alter this field. b) Create a view or function that check the all venddings (in vendding table) for the specified product and return the last vendding information? a) CREATE TABLE products( id serial primary key, description varchar(50), last_vendding date()--Is correct to use this field??? ); CREATE TABLE vendding( id serial primary key, date_ date, product integer references (products) ); CREATE TRIGGER TG_change_products_last_vendding_field on table vendding BEFORE INSERT OR UPDATE FOR EACH ROW EXECUTE procedure change_products_last_vendding(); b) CREATE TABLE products ( id serial primary key, description varchar(50) ); CREATE TABLE vendding( id serial primary key, date_ date, product integer references (products) ); CREATE VIEW last_product_change as SELECT * from vendding order by date_ desc limit 1; --Okay, this view will return the last record and not the last record for a product... but its a example. I am asking it becouse I have used CLIPPER(dbase) for my old programs and in DBASE the view/check function that will check for each select is not functional. And I need to create a field in all table references, but in DBASE this fields allways broken and I need to recheck it. Thank you. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Record Log Trigger
Well... Right, I will use to_timestamp() function instead of now() function. But, what is the performance for those Triggers??? Considering that all tables will have this Trigger and will check for each update or insert. Thanks Quoting CHRIS HOOVER <[EMAIL PROTECTED]>: One change you might want to look at is not using the now() function. According to the docs, the now() function always returns the start of the transaction time. So, if your code is using transaction blocks, the time may not be what you are expecting. This is what I had do to in my trigger to get the current clock time: to_char(to_timestamp(timeofday(),\'Dy Mon DD HH24:MI:SS.US \') HTH, Chris --( Forwarded letter 1 follows )- Date: Mon, 02 May 2005 16:10:46 -0300 To: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Subject: [SQL] Record Log Trigger Hi all, I am building a database in postgresql and I made a function that returns the system time and the current user... like this: CREATE OR REPLACE FUNCTION generate_idx() returns text as $$ select to_char(now(),'MMDDHHMISSUSTZ')||CURRENT_USER; $$ language 'SQL'; CREATE OR REPLACE FUNCTION TG_idxm() RETURNS trigger AS $$ BEGIN NEW.idxm = generate_idx(); RETURN NEW; END; $$ LANGUAGE plpgsql; And my all tables have the "idxm" field, its something like a log for the record, to know Who and When the record have changed. I.e: CREATE TABLE products( id serial primary key, description varchar(50), ... idxm varchar(100) ); CREATE TRIGGER TG_products_idxm BEFORE INSERT or UPDATE on products FOR EACH ROW EXECUTE PROCEDURE TG_idxm(); Okay, it runs fine... but my question is: Is it right??? In the future (when the database will be bigger with many of millions records) this functions for each table will depreceate my database performance??? Is there any other way to build it??? Thank you ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] Function or Field?
Do I have to create another table to put this data??? But, Isn't it redundancy? :-/ The question is: For example: I have a "clients" table and I have a "taxes" table that is a chield of client. Is more efficient put fields into client table that contains: -) the count for paid taxes -) the count for unpaid taxes -) the count for all taxes -) the last tax expiration date Or is more efficient construct a function that will count this field runtime, as a view for example, or a simple function. -) SELECT count(*) from taxes where client=$1 and not nullvalue(dt_pay); -) SELECT count(*) from taxes where client=$1 and nullvalue(dt_pay); -) SELECT count(*) from taxes where client=$1; -) SELECT dt_expiration from taxes where client=$1 order by dt_expiration desc limit 1; While having few records in "taxes" table, the function (runtime) work right and in good time, but when the "taxes" table grows I think the function will run so slow... What is correct??? Construct a Function to count runtime? or Create a Trigger to update the "clients" fields before all action and use those fields in select??? Thanks Quoting Joel Fradkin <[EMAIL PROTECTED]>: You could also make a table with just that data in it so you don't have the field in all the records and you don't have to check all the records to see what is next. I am assuming this is some kind of a flag values used in a batch, if you just need the last id I use max(id)+1. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Monday, May 02, 2005 3:17 PM To: pgsql-sql@postgresql.org Subject: [SQL] Function or Field? Hi. What is the better way to store the last record for a translation??? I.E: The data for the last product vendding. What is better: a) Create a field in "product" table and create a Trigger (before insert or update into vendding table) to alter this field. b) Create a view or function that check the all venddings (in vendding table) for the specified product and return the last vendding information? a) CREATE TABLE products( id serial primary key, description varchar(50), last_vendding date()--Is correct to use this field??? ); CREATE TABLE vendding( id serial primary key, date_ date, product integer references (products) ); CREATE TRIGGER TG_change_products_last_vendding_field on table vendding BEFORE INSERT OR UPDATE FOR EACH ROW EXECUTE procedure change_products_last_vendding(); b) CREATE TABLE products ( id serial primary key, description varchar(50) ); CREATE TABLE vendding( id serial primary key, date_ date, product integer references (products) ); CREATE VIEW last_product_change as SELECT * from vendding order by date_ desc limit 1; --Okay, this view will return the last record and not the last record for a product... but its a example. I am asking it becouse I have used CLIPPER(dbase) for my old programs and in DBASE the view/check function that will check for each select is not functional. And I need to create a field in all table references, but in DBASE this fields allways broken and I need to recheck it. Thank you. ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(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] Duplicated records
Hi. How can I delete the duplicated records with "DELETE FROM TABLE WHERE..." clause?? The problem is becouse I have imported data from Dbase (dbf) file, and this function have not built the Constraint (unique, primary key, ...), and this function is usually executed. select * from table1; --id may be primary key - Table1 - id | field 2 0 | 'aaa' 1 | 'bbb' 2 | 'ccc' 0 | 'aaa' <<== The data is duplicated 1 | 'bbb' 2 | 'ccc' 0 | 'aaa' 1 | 'bbb' 2 | 'ccc' Is there a way to delete the duplicated data without build another table with constraints and copy those data to the new table? Something like "delete from table1 where ...???" Thanks, Lucas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Duplicated records
Hi. Thanks for the article... But, I have read it and the query works very slow... My table have aprox. 180.000 records (correct) and in entire table it has aprox.360.000 records(duplicated)... I tried to execute a query to delete the duplicated records, but it worked very very slow... look: # select * from lanctos order by numos; numos | field1 | field2 | field3 |... 1 | test | T2-2 | 2 |... 1 | test | T2-2 | 2 |... 2 | Blabla | 0 | ABC|... 2 | Blabla | 0 | ABC|... 3 | Ll | Oo | Rr |... 3 | Ll | Oo | Rr |... ... The records is entire duplicated (with all fields having the same data), thinking the "numos" fields as primary key I have executed the query: # DELETE from lanctos where not oid=(select oid from lanctos as l2 where l2.numos=lanctos.numos limit 1); I have tested others querys with EXPLAIN command to examine the performance time, and this query was the best performance I got... but its is slow. Other query is: # DELETE from lanctos where not exists (select '1' from lanctos as l2 where l2.numos=lanctos.numos and not l2.oid=lanctos.oid); Is there a way to delete those duplicated records faster??? Remembering the table have aprox 360.000 records... Is better I create other table and copy those data??? How should I created??? Thanks. Quoting Andreas Kretschmer <[EMAIL PROTECTED]>: am 24.05.2005, um 17:59:31 -0300 mailte [EMAIL PROTECTED] folgendes: Hi. How can I delete the duplicated records with "DELETE FROM TABLE WHERE..." clause?? Please read http://www.gtsm.com/oscon2003/deletetid.html Its a very good article about this problem. ---(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] Duplicated records
Thanks CTAS (Create Table As Select) command works fine!!! With great performance. I think it is the best way to correct the data...(apparently) I didnt know about "select DISTINCT". I am going to read about it. Thank you. Quoting Bricklen Anderson <[EMAIL PROTECTED]>: Is there a way to delete those duplicated records faster??? Remembering the table have aprox 360.000 records... Is better I create other table and copy those data??? How should I created??? for 180k rows (which isn't many) just do a CTAS (Create Table As Select): create table lanctos_distinct as select distinct * from lanctos; truncate table lanctos; alter table lanctos_distinct rename to lanctos; ---(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
[SQL] Sum() rows
Hi. How can I sum a row and show the sum for each row??? For example, in a finances table that have the total movimentation(debit/credit) in the bank. i.e: CREATE TABLE TB1 (id integer primary key, value numeric); insert into tb1 values (1,20); insert into tb1 values (2,2); insert into tb1 values (3,3); insert into tb1 values (4,17); insert into tb1 values (5,-0.5); insert into tb1 values (6,3); I want a query that returns: -id- | --- value --- | --- subtot --- 1 |20.00 | 20.00 2 | 2.00 | 22.00 3 | 3.00 | 25.00 4 |17.00 | 42.00 5 |-0.50 | 41.50 6 | 3.00 | 44.50 The subtot colum will be the "prev. subtot colum"+"value colum". :-/ I dont know how to make the "subtot" colum, I tried to use the sum() function but it not works correctly. Any idea??? Thanks. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Sum() rows
Yes, I tried it. In this table the query works fine, but in a big table (with aprox. 200.000 records) the query performace is very bad. I tried it (in the example table): SELECT *,(select sum(value) from tb1 as tb1_2 where tb1_2.id<=tb1_1.id) as subtot from tb1 as tb1_1 order by id; In a small table it works fine, but in a bigger table it works very slow. I was thinking to create a temporary table and a function to update the value for each row of the query... something like: CREATE table temporary (id serial primary key,value numeric default 0); INSERT into temporary values (1,0); CREATE or replace function temporary_sum(numeric) returns numeric as $$ BEGIN update temporary set value = value+$1 where id=1; return value from temporary where id=1; END; $$ language 'plpgsql'; Then before execute the query I need to update the table's value to 0. UPDATE temporary set value=0; SELECT *,temporary_sum(value) from tb1; It works better than the "sum() subquery", but it not seems correct. What is the better way??? Is there a sum() function that works how I want??? Thanks. Quoting Bruno Wolff III <[EMAIL PROTECTED]>: Since in your example the id field gives the ordering, you can use a subselect to add up the subtotal for rows with and id less than or equal to the value of id for the current row. i.e: CREATE TABLE TB1 (id integer primary key, value numeric); insert into tb1 values (1,20); insert into tb1 values (2,2); insert into tb1 values (3,3); insert into tb1 values (4,17); insert into tb1 values (5,-0.5); insert into tb1 values (6,3); I want a query that returns: -id- | --- value --- | --- subtot --- 1 |20.00 | 20.00 2 | 2.00 | 22.00 3 | 3.00 | 25.00 4 |17.00 | 42.00 5 |-0.50 | 41.50 6 | 3.00 | 44.50 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Sum() rows
Hi. The function works well... I will use your function and rewrite it to accept more than one select, becouse in this case you selected all records from tb1 table. In real case the table is bigger with many fields and I will work with some filters and some ordering (dynamically)... Thank you. [EMAIL PROTECTED] wrote: CREATE TABLE tb1 (id integer primary key, value numeric); CREATE TYPE subtotal_type AS (id integer, value numeric, subtotal numeric); CREATE OR REPLACE FUNCTION subtotal () RETURNS SETOF subtotal_type AS $$ DECLARE tbrowRECORD; sbrowsubtotal_type; BEGIN sbrow.subtotal := 0; FOR tbrow IN SELECT id, value FROM tb1 ORDER BY id LOOP sbrow.id := tbrow.id; sbrow.value := tbrow.value; sbrow.subtotal := sbrow.subtotal + tbrow.value; RETURN NEXT sbrow; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; insert into tb1 (id, value) values (1, 20.0); insert into tb1 (id, value) values (2, 2.0); insert into tb1 (id, value) values (3, 3.0); select * from subtotal(); [EMAIL PROTECTED] wrote: Hi. How can I sum a row and show the sum for each row??? For example, in a finances table that have the total movimentation(debit/credit) in the bank. i.e: CREATE TABLE TB1 (id integer primary key, value numeric); insert into tb1 values (1,20); insert into tb1 values (2,2); insert into tb1 values (3,3); insert into tb1 values (4,17); insert into tb1 values (5,-0.5); insert into tb1 values (6,3); I want a query that returns: -id- | --- value --- | --- subtot --- 1 |20.00 | 20.00 2 | 2.00 | 22.00 3 | 3.00 | 25.00 4 |17.00 | 42.00 5 |-0.50 | 41.50 6 | 3.00 | 44.50 Any idea??? Thanks. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Convert numeric to money
Hi. I have searched in mailing-list archives about converting types, but I couldn't found a function or clause that convert a numeric type to money type. How Can I convert this types? => select '1234'::money; money R$1.234,00 => select '1234'::numeric::money; ERROR: cannot cast type numeric to money The problem is becouse I have a table with "numeric" field, and I need to show it like "money" type (R$ 1.234,00). Is there a function to convert it??? Or is there a function to mask the numeric field to show like money (with R$x.xxx,xx)??? Thanks. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Referencing
Hi. Is there a way to references dynamic tables? I.E: I have a table called "buy" that create some records in "financial" table, but there is other table called "send" that create other records in "financial". "Financial" table have the moneys' movements and needs to be referenciable by "buy or send". IE: create table buy ( id serial primary key, product_id integer,--references value money ); create table send ( id serial primary key, product_id integer, --references... value money ); create table financial( id serial primary key, cred_deb smallint, value money, references integer, --<<-HERE IS THE PROBLEM, it will reference to buy OR send table ); Well, I dont know if I was clean. Thank you. ---(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
Fwd: Re: [SQL] Referencing
Ok, But the problem is becouse the "buy" and "send" tables referencing with other father table, wich is different. I shoud not create a spent table to put the "buy" and "send" values becouse the entire database is more complex than it. look: create table output( id serial primary key, client integer references clientes, fiscal_number varchar(30), print_date date, ... ); create table SEND( id serial primary key, output integer references input, product_id integer,--references value money ); create table input( id serial primary key, supplier integer references suppliers, employee varchar(30), ... ); create table BUY( id serial primary key, input integer references input, product_id integer,--references value money ); ---and--- create table financial( id serial primary key, cred_deb smallint, value money, references integer references ???, --<<-HERE IS THE PROBLEM, it will reference to buy OR send table ); How looked, the "buy" and the "send" table is identical except the father references (INPUT or OUTPUT)... Then I shoud not create ONE table (spent) wich has these informations. And now my question: Is there a way to references (financial) with two diferents tables in the some row? Or need I create two diferents rows??? Thanks. (sorry for my english). Quoting William Leite Araújo <[EMAIL PROTECTED]>: > Maybe you need is a table "spent" that has all fields of > buy/send and one more, a flag to say is the field is a "buy" or a > "send". > > > 2005/10/27, [EMAIL PROTECTED] <[EMAIL PROTECTED]>: >> Hi. >> Is there a way to references dynamic tables? I.E: >> I have a table called "buy" that create some records in "financial" >> table, but >> there is other table called "send" that create other records in "financial". >> "Financial" table have the moneys' movements and needs to be >> referenciable by >> "buy or send". IE: >> create table buy ( >> id serial primary key, >> product_id integer,--references >> value money >> ); >> create table send ( >> id serial primary key, >> product_id integer, --references... >> value money >> ); >> create table financial( >> id serial primary key, >> cred_deb smallint, >> value money, >> references integer, --<<-HERE IS THE PROBLEM, it will reference to >> buy OR send >> table >> ); >> Well, I dont know if I was clean. >> Thank you. >> >> ---(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 >> > > > -- > William Leite Araújo > ---(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
Re: Fwd: Re: [SQL] Referencing
Quoting Daryl Richter <[EMAIL PROTECTED]>: It's hard to say without knowing more precisely what you are trying to model, but I think this push you in the right direction: -- This table takes the place of both SEND and BUY create table activity( id serial primary key, product_id integer, --references value money ); create table financial( id serial primary key, cred_debsmallint, value money, activity_id integer references activity ); create table output( idserial primary key, clientinteger, --references clientes, fiscal_number varchar(30), print_datedate, activity_id integer references activity ); create table input( id serial primary key, supplier integer, -- references suppliers, employee varchar(30), activity_id integerreferences activity ); And then you do the following: create view buy as select a.id, b.id as "input_id", a.product_id, a.value from activity a join inputb on b.activity_id = a.id; Okay, but references between (output/input) and ACTIVITY tables is 1 to N. OUTPUT/INPUT - 1 to ACTIVITY - N. And not N to 1 how the example. Then the reference field need to be on "ACTIVITY (send/buy)" table. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Fwd: Re: [SQL] Referencing
Quoting Daryl Richter <[EMAIL PROTECTED]>: [EMAIL PROTECTED] wrote: > Quoting Daryl Richter <[EMAIL PROTECTED]>: >> It's hard to say without knowing more precisely what you are trying to >> model, but I think this push you in the right direction: >> > Okay, but references between (output/input) and ACTIVITY tables is 1 to N. > OUTPUT/INPUT - 1 > to > ACTIVITY - N. > And not N to 1 how the example. > Then the reference field need to be on "ACTIVITY (send/buy)" table. > Ahh, ok. In that case I reverse it like so: -- This table hold everything in common for inputs/outputs create table transfer( idserial primary key ); Yes, I think it was what I wanted. And how I check if a register in "Transfer" table is only referenciable by ONE table (OR "output" OR "input")?? Would I create a Trigger like: CREATE or REPLACE FUNCTION TG_output_check() RETURNS TRIGGER AS $$ BEGIN IF exists (select 1 from input where transfer_id=NEW.transfer_id) THEN Raise Exception 'This activity (transfer) is alread setted to INPUT'; END IF; RETURN NEW; END; $$ language 'plpgsql'; CREATE TRIGGER TG_output_check BEFORE INSERT or UPDATE on OUTPUT EXECUTE PROCEDURE TG_output_check(); CREATE or REP...--- and the some function to INPUT --- Or is there another way to check it? Thank you again. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] date question
Quoting Judith Altamirano Figueroa <[EMAIL PROTECTED]>: Hi everybody, in Postgres 7.0.2 I have the next query: SELECT * from clientes_proceso where fecha_mod::date <= now() -1; but in version 8.0.1 returns the next error: ERROR: The operator doesn't exist: timestamp with time zone - integer How can drop a day to now()?? Try using "now()::date", or "interval". Like: select * from clientes_proceso where fecha_mod::date <= now()::date -1; or: select * from clientes_proceso where fecha_mod::date <= now() - '1 day'::interval; --- Lucas ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Extract date from week
Hi Looking the e-mail I remembered a question. I saw that "select extract (week from now()::date)" will return the week number of current year. But, how can I convert a week to the first reference date. Ex: select extract(week from '20050105'::date); -- 5 Jan 2005 --Returns-- date_part | 1 | It is the first week of year (2005), and how can I get what is the first date references the week 1? Ex: select week 1 --should return--- date | 20050103 | -- 3 Jan 2005 Thank you. Lucas Vendramin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Default on update
Hi. Is there a way to create "default" constraint on UPDATE query. It's becouse I have a bool field that may NOT support NULL value, but the Front-End calls null for FALSE values. I was thinking something like: create table table1 ( id serial primary key, bv bool default false not null ); I would want to replace "bv" values with FALSE when insert/update NULL value for this field. Or need I create a TRIGGER that check it and replace the itens??? CREATE or REPLACE function TG_table1_check RETURNS trigger as ' BEGIN IF nullvalue(NEW.bv) THEN NEW.bv=FALSE; END IF; END; ' language 'plpgsql'; CREATE TRIGGER TG_table1_check BEFORE UPDATE on table1 for each row execute procedure tg_table1_check(); Other question: I have a lot of triggers in my db system, I have table that has 5/6 triggers, many triggers are simple (like the tg_table1_check), any are complex... Is it a problem??? My tests are with few regs and run fine. Where can I read more about triggers and performance? Thanks for all. --- Lucas Vendramin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Default on update
Quoting Richard Huxton : [EMAIL PROTECTED] wrote: Hi. Is there a way to create "default" constraint on UPDATE query. It's becouse I have a bool field that may NOT support NULL value, but the Front-End calls null for FALSE values. Sounds like your frontend is broken. Yes, it is. But I have no access to the front-end. I will send it to the programmer. I was thinking something like: create table table1 ( id serial primary key, bv bool default false not null ); I would want to replace "bv" values with FALSE when insert/update NULL value for this field. You could do this by having the application insert to a view with a rule that replaces null bv values before redirecting to the base table. Is more functional to create a Rule instead of a trigger? Or need I create a TRIGGER that check it and replace the itens??? CREATE or REPLACE function TG_table1_check RETURNS trigger as ' BEGIN IF nullvalue(NEW.bv) THEN IF NEW.bv IS NULL THEN What is the difference between nullvalue() and IS NULL??? NEW.bv=FALSE; END IF; END; ' language 'plpgsql'; CREATE TRIGGER TG_table1_check BEFORE UPDATE on table1 for each row execute procedure tg_table1_check(); To make the trigger work you'll have to relax the "NOT NULL" on column "bv" otherwise PG's type-checks will raise an error. Oh, and then make sure the trigger is called before INSERT too. Okay. Other question: I have a lot of triggers in my db system, I have table that has 5/6 triggers, many triggers are simple (like the tg_table1_check), any are complex... Is it a problem??? My tests are with few regs and run fine. Where can I read more about triggers and performance? Triggers behave exactly as you'd expect. For every row (or statement) the function gets executed. Difficult to say what effect they'll have on performance without testing with your actual setup. Ok, I will test with more records. Thank you. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Count field in query
Hi all.
Is there any way to build a query with a field that has the IndexCount
of the query.
It's something like the number of the row returned (starting with 1).
Something like:
select * from mytable order by name;
id | name | CountField
7 | KK | 1
98 | LL | 2
5 | ZZ | 3
select * from mytable order by id;
id | name| CountField
5 | ZZ | 1
7 | KK | 2
98 | LL | 3
I was thinking to create a sequence:
create temporary sequence MYSEQUENCE increment 1 MINVALUE 1;
And put this sequence as field "nextval('mysequence')":
select *,nextval('mysequence') from mytable;
But I think its not the best way to do this! Couse I need to set
sequence value to 1 everytime.
Can Someone help me?
Thanks
Lucas Vendramin (Brazil)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] Problem with timestamp field/time function.. (upgrading from 7.0 to 7.2.1)
Hi All: I've googling around, searching the mailinglist archive and reading FAQ's but I haven't find the answer for my question. And I know it is quite commom! I'm trying upgrading to 7.2.1. I'm running postgresql 7.0 with a column like: Table "materia" Column | Type | Modifiers +--+--- materiadata| timestamp with time zone | not null mmateriatitulo | character varying(80)| not null materiasequencial | numeric(30,6)| not null I used to execute this query: select max(time(materiadata)) from materia; or select materiasequencial, materiatitulo, time(materiadata) from materia order by time(materiadata) desc; I've read at PostgreSQL 7.3dev Administrator Guide's Release Notes that time() and timestamp() functions in postgresql 7.2 are deprecated (so in 7.2.1). So, how can I get the same result above without using time() ?? Or if it not possible, how can I extract (yes, I tried with extract() function too) time from a timestamp column? I know it's quite simple question... but I haven't find any clue! Thanks a lot in advance. Bests regards []'s Lucas Brasilino [EMAIL PROTECTED] http://www.recife.pe.gov.br Emprel -Empresa Municipal de Informatica (pt_BR) Municipal Computing Enterprise (en_US) Recife - Pernambuco - Brasil Fone: +55-81-34167078 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problem with timestamp field/time function.. (upgrading
Hi Stephan
>> So, how can I get the same result above without using time() ??
>>Or if it not possible, how can I extract (yes, I tried with extract()
>>function too) time from a timestamp column?
>> I know it's quite simple question... but I haven't find any clue!
>>
>
> In general you could probably use CAST(materiadata as time) I'd guess.
> I believe that at this point you can still use the functions, you just
> need to double quote them ("time"(materiadata)) to differentiate them
> from the type specifiers.
>
Well, it seems to work only with "timestamp without time zone" type. But
worked. Thanks a lot.
For me it's a new approching not using functions such as time, but
"casting" it to a "time" type ( like select materiadata::time from
materia;).
If PostgreSQL development group change it's point of view, I suppose
there's some advantage. Do you know some advantages ??
bests regards
--
[]'s
Lucas Brasilino
[EMAIL PROTECTED]
http://www.recife.pe.gov.br
Emprel -Empresa Municipal de Informatica (pt_BR)
Municipal Computing Enterprise (en_US)
Recife - Pernambuco - Brasil
Fone: +55-81-34167078
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] alter user does not changes password
Hi all: I know it's a silly question, but I've googling around and searching mailist archive with no answer to my question: I'm using self compiled PostgreSQL 7.2.1 in a RH 7.1 box. As described in http://www.postgresql.org/idocs/index.php?sql-alteruser.html I'm trying to change a user's password as: alter user camara with password 'canabis!'; but when I try to connect: $ psql -U camara dbcamara; User "camara" logs with no password If I use -W option of psql It prompts for password and I can enter whatever I want... it accepts! It must be a feature.. not a bug... I know I'm making a mistake.. but.. where?? bests regards -- []'s Lucas Brasilino [EMAIL PROTECTED] http://www.recife.pe.gov.br Emprel -Empresa Municipal de Informatica (pt_BR) Municipal Computing Enterprise (en_US) Recife - Pernambuco - Brasil Fone: +55-81-34167078 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Getting rid of accents..
Hi Mallah, I had this problem once, and put together this bunch of regexes. It's by no means optimal, but should solve 90% and would easily be adapted into a plperl function. Begin perl: $value =~ s/[\xc0-\xc6]/A/g; $value =~ s/[\xc7]/C/g; $value =~ s/[\xc8-\xcb]/E/g; $value =~ s/[\xcc-\xcf]/I/g; $value =~ s/[\xd1]/N/g; $value =~ s/[\xd2-\xd6\xd8]/O/g; $value =~ s/[\xd9-\xdc]/U/g; $value =~ s/[\xdd]/Y/g; $value =~ s/[\xe0-\xe6]/a/g; $value =~ s/[\xe7]/c/g; $value =~ s/[\xe8-\xeb]/e/g; $value =~ s/[\xec-\xef]/i/g; $value =~ s/[\xf1]/n/g; $value =~ s/[\xf2-\xf6\xd8]/o/g; $value =~ s/[\xf9-\xfc]/u/g; $value =~ s/[\xfd\xff]/y/g; On Tuesday, May 27, 2003, at 04:55 PM, <[EMAIL PROTECTED]> wrote: Is there any easy way for converting accented text to closest text without accents in postgresql ? eg: BÂLÂ MORGHÂB to BALA MORGHAB Regds Mallah. - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Getting rid of accents..
Full disclosure on previously posted Perl code: I think I may have cribbed all or part of that previous code from something (Perl cookbook?). In any case, the issue is essentially a mapping of which ascii codes "look like" low-ascii, so I don't think there are any authorship issues. Best, Randall On Tuesday, May 27, 2003, at 04:55 PM, <[EMAIL PROTECTED]> wrote: Is there any easy way for converting accented text to closest text without accents in postgresql ? eg: BÂLÂ MORGHÂB to BALA MORGHAB Regds Mallah. - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(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 4: Don't 'kill -9' the postmaster
Re: [SQL] insert problem with special characters
Hi John, (added to JDBC list) 1. What is your database encoding? Does it support the unicode OK? 2. Are you sure it's getting /stored/ as a question mark rather than just displayed as such? Remember, if it is stored correctly, but you look at it from a terminal that doesn't support the character, you might be seeing an artifact of your terminal, not the underlying representation. I would suggest you try having the cent symbol pulled in via the normal route, and once it's in a java.lang.String, have java pop open a JOptionPane.showMessageDialog and show you what it thinks the String is. If it's not showing you the right character here, then the problem is way upstream of Postgres. If it displays OK here, have Java pull the value back out of the database and show it in another messageDialog; my guess is you'll see one of three things: A. Neither dialog shows the unicode OK. This means you have some encoding/decoding issues upstream of Postgres. B. The first dialog is OK, the second is not. Probably, your encoding for the database is not set right. C. Both dialogs are OK. The problem is merely an artifact of how you're seeing the unicode stuff, not a Java + Postgres problem. Best, Randall On Tuesday, May 13, 2003, at 01:18 PM, jwang wrote: Hi All, I have a web application in JSP/Servlet/PostgreSQL(7.3). I have a text input field where users can enter special cent symbol to represent cent unit just like dollar symbol. So I have to use java.net.URLDecoder.encode(text_input,"UTF-8") to fetch the request input on the server. If I just insert the encoding text into the database, it will store the text in URL-encoding format. But I want to store text in ASCII-readable format (and cent symbol is stored just like dollar symbol), rather then in URL-encoding format. If I am using java.net.URLDecoder.decode(text_input,"UTF-8") to decode the text before I do inserting, the cent symbol is stored as a question mark symbol in database. I tried to find a postgres encoding function without success. Does anyone know what the problem is and how to solve it? Thanks a lot. -John GSP Marketing Technologies, Inc. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] comparing querys
how can i compare two querys' eficiency??? TIA, -- Lucas Lain [EMAIL PROTECTED] #! /Scripting/Manager (??) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] TR: Like and =
Hi Nicholas, CHAR fields, as opposed to VARCHAR, are blank-padded to the set length. Therefore, when you inserted a < 25 character string, it got padded with spaces until the end. Likewise, when you cast '100058' to a CHAR(25) in the = below, it gets padded, so it matches. The LIKE operator takes a pattern, and since your pattern did not specify a wildcard at the end, it didn't exactly match the padded string. This behavior does seem kind of confusing; in any case, it probably argues for using varchar. Best, Randall On Monday, June 23, 2003, at 12:29 PM, Nicolas JOUANIN wrote: Hi, I've got a table , pdi, with a field pro_id defined as char(25). One fied og this table contains the string '100058' plus spaces to fill the 25 length (ie pro_id = '100058 '). When I run: select * from pdi where pdi = '100058' the row is returned. When I run: select * from pdi where pdi like '100058' the row is NOT returned. select length(pro_id) where pdi = '100058' returns: length --- 25 2 Row(s) affected 1) In PostgreSQL documentation, it's said that without % wildcards like operates the same as = , it seems not. 2) Why does the = operator return the row ? it shouldn't because of the trailing spaces. 3) The row was inserted from the COPY command: COPY pdi FROM STDIN NULL as '' DELIMITER as '|'; VOL|100058|0|PART||PART \. Why does my field contain trailing spaces ? Regards and thanks again for your useful help. PS: create table pdi ( pmf_id char(4) not null , pro_id char(25) not null , lng_id char(3) not null , pdi_desc char(50) not null , pdi_instr text, pdi_matchdesc char(50), CONSTRAINT pk_pdi PRIMARY KEY (pro_id,pmf_id,lng_id) ); Nicolas. --- Nicolas JOUANIN - SA REGIE FRANCE Village Informatique BP 3002 17030 La Rochelle CEDEX Tel: 05 46 44 75 76 Fax: 05 46 45 34 17 email: [EMAIL PROTECTED] Web : www.regie-france.com --- ---(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 ---(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] Change the behaviour of the SERIAL "Type"
Wow, I had never actually faced this problem (yet) but I spied it as a possible stumbling block for porting MySQL apps, for which the standard practice is inserting a NULL. As I have made a fairly thorough reading of the docs (but may have not cross-correlated every piece of data yet, obviously), I was surprised to find I hadn't figured this out myself. It /seems/ obvious in retrospect, but it really baked my noodle when I first looked at some ugly MySQL queries. Respectfully, then, I move that a sentence outlining this functionality be added to User Manual section 5.1.4, "The Serial Types." Furthermore, anyone who has written or is writing a MySQL porting guide should include this, if he hasn't. Best, Randall On Thursday, June 26, 2003, at 08:49 AM, Bruno Wolff III wrote: On Thu, Jun 26, 2003 at 14:31:34 +0200, Dani Oderbolz <[EMAIL PROTECTED]> wrote: It was written for MySQL, which can take NULL and then assign an auto_increment. However, in PostgreSQL I am getting problems, because it would not let me insert NULL into a NOT NULL column (which is perfectly sensible from my point of view). But as the author has also left out the column list in the insert, its really tedious to change the code. You can use the keyword DEFAULT instead of NULL and it will do what you want. This way, there would be no possibility to circumvent the Value which comes from the Sequence. You can use a unique constraint to enforce uniqueness. Is there a way to change SERIAL this way? Well you can certainly write your own trigger to do this. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] restoring database
Hi everybody ... i need to restore only one database from a pg_dumpall backup file... how can i do it? Thanks a lot ! ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Cursor need it?
I am a new postgres user I want to get a list of tables from pg_tables where tables are like ‘%wo%’ (for example).. and then query that list …. Select count(*) from tableVARIABLENAMEFROMFIRSTQUERY In SQL SERVER I can do that using cursor but in postgresql I don’t understand how to use cursors Here is what I am trying to do for each table on the list Select tablename, count(*) tables from ( list of tables) tablename tables table1 25 table2 35 Any Idea would be appreciated Thanks Lucas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Select problems
Hi: I've just migrated from Linux/PostgreSQL 7.2 to Windows/PostgreSQL 8.0.3. I have a large view. When I make: "Select . WHERE mydate = 'anydate'" the view lasts 19 seconds to complete. But, when I make: "Select . WHERE mydate >= 'anydate'" the view lasts 7 minutes. With PostgreSQL 7.2 they both last for equal time. I did a vacuum analyze with no success. Please, could you help me? Thanks in advance _ Need software for your hardware? Click here http://www.asg.co.za ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Comparing two tables of different database
Nicholas,
To use the dblink:
1. In your postgres server you should find a file *dblink.sql*.
In my beta installation is in *share/postgresql/contrib*. It is the
installation for the dblink contrib module that usually is already compiled
in. It will create a lot of dblink functions.
2. on database2 create a function nammed db_datbase1() which returns
"dbname=database1" (if you need a login use "dbname=database1 password=xxx",
you can also specify host= port= to connect in a remote postgresql database)
3. now execute the sql:
select * from dblink(db_database1(), 'select "id", "name", "time" from
pr_1') as pr_1("id" integer, "name" text, "time" time)
then you will see the table "pr_1" on the datbase2
--
Lucas Brito
Re: [SQL] Comparing two tables of different database
2009/5/2 Isaac Dover
> i've not tried this in postgres, but using information_schema makes
> comparing structures of databases trivial. i've been using this query for a
> while with MSSQL. Maybe this helps to answer the question.
>
> - isaac
>
> select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, *
> from [database].information_schema.Columns ST
> full outer join [other database].information_schema.Columns DV
> on ST.Table_Name = DV.Table_name
> and ST.Column_Name = DV.Column_Name
> where ST.Column_Name is null or DV.Column_Name is NULL
>
>
Isaac, this query will return "ERROR: cross-database references are not
implemented".
Postgres does not support queries in databases other than current one. Even
a simple select * from otherdatabase.information_schema.columns will not
work and throw the same error.
However this can be done with dblink function like:
select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, *
from information_schema.Columns ST
full outer join dblink('dbname=otherdatabase','select Table_Name,
Column_Name from information_schema.Columns') DV(Table_Name text,
Column_Name text)
on ST.Table_Name = DV.Table_name
and ST.Column_Name = DV.Column_Name
where ST.Column_Name is null or DV.Column_Name is NULL
--
Lucas Brito
Re: [SQL] Configuring Problem on Solaris............
Send the config.log file. On Wed, 20 Aug 2003 23:18:15 +0200 (CEST) Peter Eisentraut <[EMAIL PROTECTED]> wrote: > Chidananda writes: > > > We are trying to install on solaris ver 9.0 we are getting the following > > error so kindly tell us how to over come this problem. > > > > configure: error: > > *** Could not execute a simple test program. This may be a problem > > *** related to locating shared libraries. Check the file 'config.log' > > *** for the exact reason. > > Please check the file 'config.log' for the exact reason. It may be a > problem related to locating certain shared libraries. The archives > contain several instances where this problem is dicussed. > > -- > Peter Eisentraut [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > -- Lucas Lain Gerencia de Ingeniería TechTel Telecomunicaciones [EMAIL PROTECTED] TE. (54-11) 4000-3164 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] restoring database
Hi everybody ... i need to restore only one database from a pg_dumpall backup file... how can i do it? Thanks a lot ! -- Lucas Lain Gerencia de Ingeniería TechTel Telecomunicaciones [EMAIL PROTECTED] TE. (54-11) 4000-3164 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
