Re: [SQL] Migrating database from postgresql 7.4.6 to postgresql
O A. Kretschmer έγραψε στις Nov 9, 2005 : > am 08.11.2005, um 23:18:35 -0800 mailte Louise Catherine folgendes: > > Hi, > > I would like to migrating my database from postgresql > > 7.4.6 to postgresql 8.0.3. > > Why not 8.1.0? > > > > Are there any problem during migrating database? a > > casting problem or sintax problem, or other problem? > > I dont know about problems. Install the new database (on the same > machine, use a different port), and use the pg_dump from the newer > version to dump the old db in the new db. Or better consult HISTORY. It is in the root of the tarball. > > > HTH, Andreas > -- -Achilleus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] migratation of database from oracle9i to postgreSQL8.0.3
Hi Can anyone help me out How can i migrate database from oracle9i to postgreSQL8.0.3 where the table structure may differ in certain way... Jaime Casanova <[EMAIL PROTECTED]> wrote: On 11/8/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]>wrote:> 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>>Extracted from:http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT--- begin extracted text ---weekThe number of the week of the year that the day is in. By definition(ISO 8601), the first week of a year contains January 4 of that year.(The ISO-8601 week starts on Monday.) In other words, the firstThursday of a year is in week 1 of that year. (for timestamp valuesonly)Because of this, it is possible for early January dates to be part ofthe 52nd or 53rd week of the previous year. For example, 2005-01-01 ispart of the 53rd week of year 2004, and 2006-01-01 is part of the 52ndweek of year 2005.SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');Result: 7--- end extracted text -regards,Jaime Casanova(DBA: DataBase Aniquilator ;)---(end of broadcast)---TIP 6: explain analyze is your friend Yahoo! FareChase - Search multiple travel sites in one click.
[SQL] Como ejecutar una funcion insert en plpgsql....
Hola a todo... necesito ejecutar una consulta Insert que realice en pgsql para ver si inserta correctamente en la tabla asociada, pero con execute me da un erroralguien me puede decir como lo hago yo trabajo con el editor postgresql manager pro.. Gracias... Ahh otra cosa: en otra consulta que realice pero que es un select al principio me daba un error porque no encontraba la columna "ItemID" la cual porsupuesto existia, entonces a la columna le quite las mayusculas de su nombre quedando "itemid" y ya no me salio el error.en postgres los nombres de tablas y columnas deben ser siempre con minuscula
Re: [SQL] Como ejecutar una funcion insert en plpgsql....
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Fernando lo que pasa es que postgres siempre pasa a minusculas los nombres de los campos o tablas excepto si estos se escriben entre comillas. es decir algo como: select CAMPO1 ... postrgres lo pasara a: select campo1 .. y algo como select "CamPo1" ... postgres lo mantendra igual. Sobre la ejecucion de la instruccion: la tienes en una funcion? o solo estas probando que te inserta algun dato de prueba, si es asi no tienes que usar el execute. Mauricio Fernández A. Ingeniero de Sistemas U. Autónoma de Manizales -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Fernando Garcia Enviado el: miércoles, 09 noviembre, 2005 15:22 Para: pgsql-sql@postgresql.org Asunto: [SQL] Como ejecutar una funcion insert en plpgsql Hola a todo... necesito ejecutar una consulta Insert que realice en pgsql para ver si inserta correctamente en la tabla asociada, pero con execute me da un erroralguien me puede decir como lo hago yo trabajo con el editor postgresql manager pro.. Gracias... Ahh otra cosa: en otra consulta que realice pero que es un select al principio me daba un error porque no encontraba la columna "ItemID" la cual porsupuesto existia, entonces a la columna le quite las mayusculas de su nombre quedando "itemid" y ya no me salio el error.en postgres los nombres de tablas y columnas deben ser siempre con minuscula -BEGIN PGP SIGNATURE- Version: PGPfreeware 6.0.2i iQA/AwUBQ3IUEc4irmZP1c6WEQKdagCg86HUkABRxiUkwoX0KxatXgBrVIMAnRl6 Fejv6bN92riROQl+LG+pA6zN =+KQq -END PGP SIGNATURE-
[SQL] RETURNS SETOF table; language 'sql'
When I issue something like this: SELECT * FROM ads WHERE id=1004; i get: id | vpn_id | service_id | ignore_length | start_time | end_time| ad_text --+++---+++ 1004 | 1 |106 | f | 2005-01-01 00:00:00+01 | 2005-12-31 00:00:00+01 | Probna reklama numera una! Now, I create a function that does that: CREATE FUNCTION get_ads(int4) RETURNS SETOF ads AS 'SELECT * FROM ads WHERE id=$1' LANGUAGE 'sql' When I do: SELECT * FROM get_ads(1004); i get: ERROR: query-specified return row and actual function return row do not match Why is that? Mike P.S. That's run on Postgres 7.4. -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(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: [SQL] RETURNS SETOF table; language 'sql'
Mario Splivalo <[EMAIL PROTECTED]> writes: > When I do: > SELECT * FROM get_ads(1004); > i get: > ERROR: query-specified return row and actual function return row do not > match The example as given works fine for me in 7.4.9. Maybe you need an update, or maybe you're not telling us the whole truth about what you did. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Design question: Scalability and tens of thousands of
I have thought of this as a possible solution. Unfortunately I want to do reporting (sum, avg, ect.) queries on this data (for each user) and I can't imagine that being feasible with casting that many columns. Zack -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of george young Sent: Tuesday, November 08, 2005 6:15 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Design question: Scalability and tens of thousands of On Thu, 3 Nov 2005 09:58:29 -0800 "zackchandler" <[EMAIL PROTECTED]> threw this fish to the penguins: > I'm designing an application that will allow users to create custom objects > on the application level. A custom object can have zero or more attributes. > Attributes can be one of 5-10 types (ex. String, Number, List, Date, Time, > Currency, etc...). This will allow users to track anything exactly as they > want. My first thought on how to structure this is to make a custom table > in the db for each custom object. The attributes would map to fields and > everything would work just dandy. > > The problem is I am very concerned about scalability with having a different > table created for each custom object. I want to design to site to handle > tens of thousands of users. If each user has 3-5 custom objects the > database would have to handle tens of thousands of tables. > > So it would appear that this is a broken solution and will not scale. Has > anyone designed a similar system or have ideas to share? This is a sticky problem. My app is a bit similar. Trying something like your solution, I found that zillions of tables, constantly created and destroyed by users, to be terrible to manage. Now I use one table that defines objects' attributes, e.g. something like: create table fields(owner text, obj text, name text, type text, seq int) create table objs(owner text, name text) create table values(owner text, obj text, name text, val text) That is, the values are stored in text type, not the native type. Yes, this takes a performance hit for conversion of values, but the simplicity of schema really wins for me. I suggest you seriously consider it unless you need blinding performance in all 20,000 applications... -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] RETURNS SETOF table; language 'sql'
On Wed, 2005-11-09 at 13:49 -0500, Tom Lane wrote: > Mario Splivalo <[EMAIL PROTECTED]> writes: > > When I do: > > SELECT * FROM get_ads(1004); > > i get: > > ERROR: query-specified return row and actual function return row do not > > match > > The example as given works fine for me in 7.4.9. Maybe you need an > update, or maybe you're not telling us the whole truth about what you > did. Oh, me, big liar :) I dunno what else could be wrong... Maybe because the id column in table ads has default value taken from a sequence? Here are the statements for creating the table, and INSERTS for the data. That's very same amount of data as I have on my server. I just removed the default value for the id column, and foreign keys to some other tables: CREATE TABLE ads ( id int4 NOT NULL, vpn_id int4 NOT NULL, service_id int4, ignore_length bool NOT NULL, start_time timestamptz NOT NULL, end_time timestamptz, ad_text varchar(1024) NOT NULL ) WITHOUT OIDS; INSERT INTO ads VALUES (1004, 1, 106, false, '2005-01-01 00:00:00+01', '2005-12-31 00:00:00+01', 'Probna reklama numera una!'); INSERT INTO ads VALUES (1005, 1, 106, false, '2005-01-01 00:00:00+01', '2005-12-31 00:00:00+01', 'Probna reklama numera una!'); INSERT INTO ads VALUES (1006, 1, 106, false, '2005-01-01 00:00:00+01', '2005-12-31 00:00:00+01', 'Probna reklama numera dua!'); INSERT INTO ads VALUES (1008, 1, 106, false, '2005-01-01 00:00:00+01', NULL, 'ProbaNull'); INSERT INTO ads VALUES (1007, 1, 106, false, '2005-01-01 00:00:00+01', '2006-01-01 00:00:00+01', 'ProbaNull'); Now, here is the code for the function: CREATE FUNCTION get_ad(int4) RETURNS SETOF ads AS ' SELECT * FROM ads WHERE id=$1 ' LANGUAGE 'sql'; I'm doing all of this on Postgres 7.4.9: pulitzer2=# select version(); version -- PostgreSQL 7.4.9 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.2 20050808 (prerelease) (Ubuntu 4.0.1-4ubuntu9) (1 row) Now, I created fresh database on the same database cluster, and executed the CREATE for the table, INSERTs for the data, and CREATE for the function. Then I run the function: fun_test=# select * from get_ad(1004); id | vpn_id | service_id | ignore_length | start_time | end_time| ad_text --+++---+++ 1004 | 1 |106 | f | 2005-01-01 00:00:00+01 | 2005-12-31 00:00:00+01 | Probna reklama numera una! (1 row) Works fine. I remind you again, this is on a newly created database. So, I go back to the database I'm developing, and I create function get_ad_test (I created get_ad using plpgsql, ended up with adding some more logic): CREATE FUNCTION get_ad_test(int4) RETURNS SETOF ads AS ' SELECT * FROM ads WHERE id=$1 ' LANGUAGE 'sql'; When I run it, again the same:pulitzer2=# select * from get_ad_test(1004); ERROR: query-specified return row and actual function return row do not match pulitzer2=# I went to some other database I have in that same cluster, recreated the table, and everything works fine. I haven't tried droping the ads table in the pulitzer database (the developing database the issue appeared). Maybe I could provide some more detailed log files, or something? Mike ---(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] RETURNS SETOF table; language 'sql'
Mario Splivalo <[EMAIL PROTECTED]> writes: > Works fine. I remind you again, this is on a newly created database. Yup, works fine for me too. > When I run it, again the same:pulitzer2=# select * from > get_ad_test(1004); > ERROR: query-specified return row and actual function return row do not > match > pulitzer2=# Ah, I bet I know the problem: alter table ads add column z int; << function still works ... >> alter table ads drop column z; << function no longer works... >> 7.4 isn't too good about coping with dropped columns in rowtypes. This problem is fixed in 8.0 and up. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] RETURNS SETOF table; language 'sql'
On Wed, 2005-11-09 at 17:05 -0500, Tom Lane wrote: > Mario Splivalo <[EMAIL PROTECTED]> writes: > > Works fine. I remind you again, this is on a newly created database. > > Yup, works fine for me too. > > > When I run it, again the same:pulitzer2=# select * from > > get_ad_test(1004); > > ERROR: query-specified return row and actual function return row do not > > match > > pulitzer2=# > > Ah, I bet I know the problem: > > alter table ads add column z int; > << function still works ... >> > alter table ads drop column z; > << function no longer works... >> > > 7.4 isn't too good about coping with dropped columns in rowtypes. > This problem is fixed in 8.0 and up. So, I should drop the table, recreate it, and then the function should work ok? I'll try ASAP, I'll just jump to a gas station for a beer :) Mike ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Como ejecutar una funcion insert en plpgsql....
Fernando -- I am not sure about the first question -- my spansih is rusry. postgres does force all column, table and schema names (I think) to lower case (there have been recent long discussions about this on this list IIRC). If you want to preserve case put the column name in double quotes: "ItemID" = ItemID ItemID = itemid This applies both to the creation of a table and when referring to that table's columns. HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Fernando Garcia Sent: Wed 11/9/2005 6:22 AM To: pgsql-sql@postgresql.org Cc: Subject:[SQL] Como ejecutar una funcion insert en plpgsql Hola a todo... necesito ejecutar una consulta Insert que realice en pgsql para ver si inserta correctamente en la tabla asociada, pero con execute me da un erroralguien me puede decir como lo hago yo trabajo con el editor postgresql manager pro.. Gracias... Ahh otra cosa: en otra consulta que realice pero que es un select al principio me daba un error porque no encontraba la columna "ItemID" la cual porsupuesto existia, entonces a la columna le quite las mayusculas de su nombre quedando "itemid" y ya no me salio el error.en postgres los nombres de tablas y columnas deben ser siempre con minuscula !DSPAM:437202c363872044317020! ---(end of broadcast)--- TIP 6: explain analyze is your friend
