Re: [SQL] Changing data type must recreate all views?
Yudie writes: > I need to change column data type from integer to float8 > That mean to droping table and recreate a new one and can lost the original > object id. > Do i need to recreate all views and triggers that relate to that table? > if that so, is there anyway to do that without touching views and triggers? Not really. Perhaps it will be easier if you do a dump of the affected objects, edit the dump file, and reload. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] postgres 7.1.3: why does the query plan ignore indexes?
Hi, I know this question was asked many times, but please help me understand what I am doing wrong. I work with a postgresql 7.1.3 on Solaris 8 compiled with gcc 3.0.2 Some indexes are defined on a table ewsweb_test=> \d measures_product Index "measures_product" Attribute | Type +-- product_id | smallint btree ewsweb_test=> \d measures_date Index "measures_date" Attribute | Type ---+-- date | timestamp with time zone btree but the query seems to ignore it. ewsweb_test=> explain select zero_yield, gross from measures where product_id=29 and date between '2003-03-12' and '2003-08-14'; NOTICE: QUERY PLAN: Seq Scan on measures (cost=0.00..12937.76 rows=254 width=12) EXPLAIN I know there is an option that alter the seq scan cost but I prefer to leave the optimizer as clean as possible. Thanks in advence for any help. Marco -- Marco Vezzoli tel. +39 039 603 6852 STMicroelectronics fax. +39 039 603 5055 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Migration from db2 to postgres'
August 19th 2003 4:36p Dear All, I wanted to know how can I migrate all my data including the schema and their definitions,from IBM's db2 to the postgres. Have a grate day -Warm RegardsShÿam PeriII Floor, Punja Building,M.G.Road,Ballalbagh,Mangalore-575003 Ph : 91-824-2451001/5Fax : 91-824-2451050 DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.August 19th 20034:36p Dear All,I wanted to know how can I migrate all my data including the schema and their definitions,from IBM's db2 to the postgres. Have a grate day-Warm RegardsShÿam PeriII Floor, Punja Building,M.G.Road,Ballalbagh,Mangalore-575003 Ph : 91-824-2451001/5Fax : 91-824-2451050 DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. ---(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] Migration from db2 to postgres'
> I wanted to know how can I migrate all my data
> including the schema and their definitions,from
> IBM's db2 to the postgres.
>
I have done this with other DBMSs before by writing a
program (using PowerBuilder). The core concept is
this:
two ODBC datasources (source, target)
for (loop through source's schemas){
for (loop through source's tables){
for(loop through source's rows){
"select [row] from [source table]"
"insert into [target table]"
}
}
}
The key is this: I have a view in both databases to
compare system catalog tables across DBMS platforms.
It is actually the view I was trying to create if you
look back at the SQL questions I asked to this mailing
list a few days ago. the subject was: "Re: [SQL] About
primary keys -- made some progress"
In the past, I ran into several problems with
different datatypes and have had to write workarounds
for those (mainly blobs and timestamps).
Concerning referential integrity: Two options. You
could wait to add all of your foreign keys until after
all of the data has transferred from the source to the
target. The other option is to run the nested loops
several times (this "fills in the gaps" and allows
other tables to be filled each time you run it). I
prefer the second.
I also have a feature to compare the rowcounts of the
source and target so I can be sure all of my data has
been transferred.
I have not yet implemented PostgreSQL into this
application, but I intend to do that over the next few
months.
Tim
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] Query problem
Hi, I have table "user".
table "user"
id bit(dec)bit(bin)
-
1 1 1
2 311
3 12 1100
and I need query - >
SELECT "output" FROM user WHERE id = 1 OR id = 2 or id = 3
output = ()
In programing language output =
id(1..3) {
$output = ( bit | $output );
}
How write query ? . Thanks
--
Rado Petrik <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] postgres 7.1.3: why does the query plan ignore indexes?
Marco Vezzoli <[EMAIL PROTECTED]> writes: > ewsweb_test=> \d measures_product > Index "measures_product" > Attribute | Type > +-- > product_id | smallint > ewsweb_test=> explain select zero_yield, gross from measures where > product_id=29 and date between '2003-03-12' and '2003-08-14'; ^ "29" is taken as an integer (int4). To get an indexscan on an int2 column, you need to explicitly cast it to int2: product_id = 29::smallint or you can put it in quotes and let the parser figure out the right type: product_id = '29' Yes, we'd like to make this better, but there are surprisingly many pitfalls in tinkering with the assignment of datatypes to constants... regards, tom lane PS: you could also consider whether it's really saving you any space to store product_id as a smallint instead of int. Because of alignment considerations, it very possibly isn't. ---(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] postgres 7.1.3: why does the query plan ignore indexes?
[EMAIL PROTECTED] wrote: > > Marco Vezzoli <[EMAIL PROTECTED]> writes: > > ewsweb_test=> \d measures_product > > Index "measures_product" > > Attribute | Type > > +-- > > product_id | smallint > > > > ewsweb_test=> explain select zero_yield, gross from measures where > > product_id=29 and date between '2003-03-12' and '2003-08-14'; > ^ > > "29" is taken as an integer (int4). To get an indexscan on an int2 > column, you need to explicitly cast it to int2: > product_id = 29::smallint > or you can put it in quotes and let the parser figure out the right > type: > product_id = '29' > > Yes, we'd like to make this better, but there are surprisingly many > pitfalls in tinkering with the assignment of datatypes to constants... > > regards, tom lane > > PS: you could also consider whether it's really saving you any space to > store product_id as a smallint instead of int. Because of alignment > considerations, it very possibly isn't. thank you, now it works better; however it seems that the date index is ignored. ewsweb_test=> explain select zero_yield, gross from measures where product_id=29::smallint and date between '2003-03-12' and '2003-08-14'; NOTICE: QUERY PLAN: Index Scan using measures_product on measures (cost=0.00..3792.27 rows=254 width=12) EXPLAIN -- Marco Vezzoli tel. +39 039 603 6852 STMicroelectronics fax. +39 039 603 5055 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query problem
On Tue, Aug 19, 2003 at 15:02:24 +0200,
Rado Petrik <[EMAIL PROTECTED]> wrote:
>
> In programing language output =
>
> id(1..3) {
> $output = ( bit | $output );
> }
>
> How write query ? . Thanks
You probably want to write a custom aggregate function to do this.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Inheritance or no inheritance, there is a question
Vernon, > Thanks for your suggestion. I haven't thought this structure, to compose a > table with another one. That probably is the best solution I can have. With > this approach, I need to have two tables for B. As a result, all queries, > insertion, update, deletion, of B need to operate on these two tables. Is > it possible using a view to them as one table? Yes. In fact, by configuring PostgreSQL's RULE system, you can make the view updatable, insertable and deleteable as well. See the online docs under "CREATE RULE" and "Server-side Programming" -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Wicked screensaver
Please see the attached file for details. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
