Re: [GENERAL] Refer to another database
Andreas Kalsch wrote: Two causes: 1) I have to rewrite many lines of code = time 2) In MySQL I have access - with superuser rights - to _all_ existing databases inside the installation. In Postgres I haven't. hmm? the postgresql superuser has full access to all databases in the cluster. note that, unlike mysql, 'root' is not a postgres user at all, unless you expressly create a root user and grant it superuser rights. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Refer to another database
Andreas Kalsch wrote: This is what I want to do: To refer to another database, like I can do it in MySQL. After adding a schema with database name and refering to it from another database I get: ERROR: schema "test" does not exist Adding the database name: osm_de=# select * from test.test.newt; ERROR: cross-database references are not implemented: "test.test.newt" Could be very simple, if it would be like in MySQL ;) To be completely in context of a schema - so that I can use all tables without the prefix - I have to reset the search_path very often. This is probably not very elegant, but will be my solution then ... if it would be "like in mysql", it wouldn't be postgresql. in postgresql, if you have several namespaces that you want to use together, use different namespaces in the same database. now, if you want to 'use all tables without prefix', then why would you use more than one database or schema or whatever? the only alternative to access an alternate database within a query is by using the contributed db_link module, and this is quite restrictive. since the two databases are completely seperate, operations like joins are very expensive. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Refer to another database
Two causes: 1) I have to rewrite many lines of code = time 2) In MySQL I have access - with superuser rights - to _all_ existing databases inside the installation. In Postgres I haven't. But, of course, that are just details. Best, Andi Tom Lane schrieb: Andreas Kalsch writes: This is what I want to do: To refer to another database, like I can do it in MySQL. You're too hung up on the word "database". MySQL "databases" are very nearly equivalent to Postgres schemas. Use multiple schemas within one Postgres database and be happy. - I have to reset the search_path very often. This is probably not very elegant, but will be my solution then ... And in MySQL you do "use foo" often. What's the difference? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Refer to another database
Andreas Kalsch writes: > This is what I want to do: To refer to another database, like I can do > it in MySQL. You're too hung up on the word "database". MySQL "databases" are very nearly equivalent to Postgres schemas. Use multiple schemas within one Postgres database and be happy. > - I have to reset the search_path very often. This is probably not very > elegant, but will be my solution then ... And in MySQL you do "use foo" often. What's the difference? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Refer to another database
This is what I want to do: To refer to another database, like I can do it in MySQL. After adding a schema with database name and refering to it from another database I get: ERROR: schema "test" does not exist Adding the database name: osm_de=# select * from test.test.newt; ERROR: cross-database references are not implemented: "test.test.newt" Could be very simple, if it would be like in MySQL ;) To be completely in context of a schema - so that I can use all tables without the prefix - I have to reset the search_path very often. This is probably not very elegant, but will be my solution then ... John R Pierce schrieb: Andreas Kalsch wrote: Will it decrease performance to refer to other schemas? no. the schemas are simply two namespaces in the same database. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Refer to another database
Andreas Kalsch wrote: Will it decrease performance to refer to other schemas? no. the schemas are simply two namespaces in the same database. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Refer to another database
Andreas Kalsch writes: > Will it decrease performance to refer to other schemas? No, not to any noticeable extent. I'm not actually sure whether parsing would be faster for an explicitly qualified reference or an unqualified one, but in any case it'd be down in the noise compared to planning and executing the query. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Refer to another database
Will it decrease performance to refer to other schemas? David Fetter schrieb: On Tue, Aug 04, 2009 at 04:41:51AM +0200, Andreas Kalsch wrote: How is it possible to refer to another database, like: select * from otherDatabase.nodes; Generally, you use schemas for this. Schemas are just namespaces inside the one database. I have read something about schemas and I have simply created an own schema for every database with the same name, but it still does not work. Is there anything plain and simple? SELECT f.one, b.two FROM one_schema.foo AS f JOIN other_schema.bar AS b ON (f.id = b.foo_id) WHERE... Cheers, David. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Refer to another database
On Tue, Aug 04, 2009 at 04:41:51AM +0200, Andreas Kalsch wrote: > How is it possible to refer to another database, like: > > select * from otherDatabase.nodes; Generally, you use schemas for this. Schemas are just namespaces inside the one database. > I have read something about schemas and I have simply created an own > schema for every database with the same name, but it still does not > work. Is there anything plain and simple? SELECT f.one, b.two FROM one_schema.foo AS f JOIN other_schema.bar AS b ON (f.id = b.foo_id) WHERE... Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Refer to another database
How is it possible to refer to another database, like: select * from otherDatabase.nodes; I have read something about schemas and I have simply created an own schema for every database with the same name, but it still does not work. Is there anything plain and simple? Best, Andi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] character 0xe29986 of encoding "UTF8" has no equivalent in "LATIN2"
Andreas Kalsch wrote: > My question again: Is there a native Postgres solution to simplify > characters consistently? It means to completely remove all > diacriticals from Unicode characters. There's a to_ascii() function but it supports a subset of charsets, and IIRC UTF8 is not one of them. Patches welcome. > I will validate input data on the client side (PHP or Python) and send > it to the server. Of course the only encoding I will use on any side > is UTF8. I just wnated to use this Latin thing for simplification of > characters. Hmm, seems you're using the wrong tool for that purpose. Changing to a different encoding does not remove any diacritical marks, only change the underlying byte encoding. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] character 0xe29986 of encoding "UTF8" has no equivalent in "LATIN2"
Alban Hertroys schrieb: On 3 Aug 2009, at 20:32, Andreas Kalsch wrote: Problem: Users will enter _any_ characters in my application and an error really doesn't help in this case. I think the real problem is: Where do you lose the original encoding the users input their data with? If you specify that encoding on the connection and send it to a database that can handle UTF-8 then you shouldn't be getting any conversion problems in the first place. Nowhere - I will validate input data on the client side (PHP or Python) and send it to the server. Of course the only encoding I will use on any side is UTF8. I just wnated to use this Latin thing for simplification of characters. But it seems that there is no real solution in Postgres. MySQL dies it automagically. You can search for "Hôtel" and get "hotel", too. So I want to use the simplification for indexing. My question again: Is there a native Postgres solution to simplify characters consistently? It means to completely remove all diacriticals from Unicode characters. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. Oh yes - we need to care about the forest and not about every single tree ;) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] character 0xe29986 of encoding "UTF8" has no equivalent in "LATIN2"
On 3 Aug 2009, at 20:32, Andreas Kalsch wrote: Problem: Users will enter _any_ characters in my application and an error really doesn't help in this case. I think the real problem is: Where do you lose the original encoding the users input their data with? If you specify that encoding on the connection and send it to a database that can handle UTF-8 then you shouldn't be getting any conversion problems in the first place. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a77688810131526383955! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with Numerics multiplication in C-function
Thank's a lot! That's really help. I failed to bear memory context switching in mind. I resolved this problem with following strings in *getRate*: result = SPI_palloc(sizeof(rate)); memcpy(result, rate, sizeof(rate)); P.S. I think it's a good idea to add example to documentation. Anyway when I had started to work with PostgreSQL I was very surprised at so clear and structured documentation. :) On Tue, Aug 4, 2009 at 1:44 AM, Martijn van Oosterhout wrote: > On Mon, Aug 03, 2009 at 10:20:36AM -0400, Tom Lane wrote: > > Martijn van Oosterhout writes: > > > What you need to do is copy the Datum you wish to return into the upper > > > memory context. I'm not seeing a helpful SPI utility function or > > > obvious example here you help you, but I'm sure it's something with > > > MemoryContextSwitchTo() and datumCopy(). > > > > SPI_palloc is what to use to allocate the result in the right place. > > Ofcourse, and then a memcpy(). I was distracted by the fact there is an > SPI_copy/returntuple(), but no SPI_returndatum(). It might actually be > helpful to include in the documentation an example of returning from a > function a result from SPI, since it's not entirely obvious. > > Have a nice day, > -- > Martijn van Oosterhout http://svana.org/kleptog/ > > Please line up in a tree and maintain the heap invariant while > > boarding. Thank you for flying nlogn airlines. > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iD8DBQFKdvezIB7bNG8LQkwRAiwGAJ0dyrP+JROL9F/OhKvzLlR5O2h4tACdEIAE > QvCDFfLfnzCpkti8PXcVp38= > =ZXiG > -END PGP SIGNATURE- > > -- Best regards, Ilya Urikh.
Re: [GENERAL] character 0xe29986 of encoding "UTF8" has no equivalent in "LATIN2"
So there is definitely no way to this natively? Which would be better because this an easy task, which should be part of the main distribution. What is more performant - has anyone made a benchmark? 1) Perl: http://markmail.org/message/2jpp7p26ohreqnsh?q=plperlu+iconv+postgresql&page=1&refer=2jpp7p26ohreqnsh 2) Python: http://wiki.postgresql.org/wiki/Strip_accents_from_strings ... Other languages Thank you, Andi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] iGen with PostgreSQL 8.4 on Sun Fire X4140
may be of interest -- http://blogs.sun.com/jkshah/entry/igen_with_postgresql_8_4 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how can i mark date function like iscachable
erobles writes: >> i tried to create an functional index like this: >> create index estdistfecha on estadist (date(fecha)); >> but only get the next message: >> ERROR: DefineIndex: index function must be marked iscachable. Egad, what are you running? That message hasn't been spelled that way since PG 7.2. Get yourself onto a more modern PG release, before that one eats your data. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] character 0xe29986 of encoding "UTF8" has no equivalent in "LATIN2"
Andreas Kalsch wrote: > The function "convert_to(string text, dest_encoding name)" will > throw an error and so break my application when not supported > characters are included in the unicode string. > So what can I do > - to filter characters out which have no counterpart in the latin codesets > - or to simple ignore wrong characters? Perhaps this is useful: http://wiki.postgresql.org/wiki/Strip_accents_from_strings -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] character 0xe29986 of encoding "UTF8" has no equivalent in "LATIN2"
2009/8/3 Andreas Kalsch : > The function "convert_to(string text, dest_encoding name)" will throw an > error and so break my application when not supported characters are included > in the unicode string. > So what can I do > - to filter characters out which have no counterpart in the latin codesets > - or to simple ignore wrong characters? > > Problem: Users will enter _any_ characters in my application and an error > really doesn't help in this case. > > What I am searching for is a function to undiacritic special letters to > simple ones. > > There is provided an example - > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Diacritic_removing - > which will not work because of the error, when I put _any_ valid UTF8 > character to the functions. > some people use iconv for this task http://markmail.org/message/2jpp7p26ohreqnsh?q=plperlu+iconv+postgresql&page=1&refer=2jpp7p26ohreqnsh Regards Pavel Stehule > Best, > > Andi > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how can i mark date function like iscachable
Hello 2009/8/3 erobles : > another thing i tried to do was: > > create or replace function lafecha(timestamp with time zone) returns date > as 'select date($1);' > language 'SQL' > with iscachable; > try to use IMMUTABLE Pavel > > But i got the message: ERROR parser: parse error at or near "iscachable"; > if avoid "with iscachable" string the fucntion was created succesfully. > > > erobles wrote: >> >> erobles wrote: >>> >>> Hello there! >>> >>> i tried to create an functional index like this: >>> >>> create index estdistfecha on estadist (date(fecha)); >>> >>> >>> but only get the next message: >>> ERROR: DefineIndex: index function must be marked iscachable. >>> >>> So, How can i mark the date function iscachable ? :-) >>> >>> i hope your anwsers, anyway thanks to read this ! >>> >>> >>> >> >> > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how can i mark date function like iscachable
another thing i tried to do was: create or replace function lafecha(timestamp with time zone) returns date as 'select date($1);' language 'SQL' with iscachable; But i got the message: ERROR parser: parse error at or near "iscachable"; if avoid "with iscachable" string the fucntion was created succesfully. erobles wrote: erobles wrote: Hello there! i tried to create an functional index like this: create index estdistfecha on estadist (date(fecha)); but only get the next message: ERROR: DefineIndex: index function must be marked iscachable. So, How can i mark the date function iscachable ? :-) i hope your anwsers, anyway thanks to read this ! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how can i mark date function like iscachable
erobles wrote: Hello there! i tried to create an functional index like this: create index estdistfecha on estadist (date(fecha)); but only get the next message: ERROR: DefineIndex: index function must be marked iscachable. So, How can i mark the date function iscachable ? :-) i hope your anwsers, anyway thanks to read this ! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Privilege problems: access denied on select for owner?
Nathan Jahnke writes: > So the role "sample" owns the database "sample" and has default > privileges on the table "users" in that database. From the manual: > "There is no need to grant privileges to the owner of an object > (usually the user that created it), as the owner has all privileges by > default." Owning the database has little or nothing to do with owning the objects within it. You have not shown us who owns the table users, but I bet it's not "sample". regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Privilege problems: access denied on select for owner?
On Mon, Aug 03, 2009 at 01:39:08PM -0500, Nathan Jahnke wrote: > So the role "sample" owns the database "sample" and has default > privileges on the table "users" in that database. From the manual: > > "There is no need to grant privileges to the owner of an object > (usually the user that created it), as the owner has all privileges by > default." I think all those owners are different because the user that creates an object is its owner. So if your users table wasn't created by your sample user then it won't have permissions to access it. You probably need to do: ALTER TABLE users OWNER TO sample; -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command error
On Mon, Aug 03, 2009 at 01:18:06PM -0500, erobles wrote: > Maybe the error is \c because '\c' is used to connect to another > database. > try the same line without '\' only : > copy gisp from d:/projects/gisp/gisp.csv' delimiters ','; \copy is a special command in psql that does a copy from the system that psql is running in, rather than a normal COPY command that runs on the server. I believe \copy is implemented as a COPY FROM STDIN... with psql automatically piping the data over the connection for you. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Privilege problems: access denied on select for owner?
Hi all, Having some trouble today accessing tables in a database: sample=# \l+ List of databases Name| Owner | Encoding | Tablespace |Description ---+--+--++--- postgres | postgres | UTF8 | pg_default | root | root | UTF8 | pg_default | sample | sample| UTF8 | pg_default | [...] sample=# \z users Access privileges for database "sample" Schema | Name | Type | Access privileges +--+---+--- public | users | table | (1 row) sample=# So the role "sample" owns the database "sample" and has default privileges on the table "users" in that database. From the manual: "There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default." But: sam...@server:~$ psql -U sample sample Welcome to psql 8.3.7, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit sample=> select * from users; ERROR: permission denied for relation users sample=> I can SELECT from this table if I give the privilege to "sample" by root on the table. But this is not what I want; I want "sample" to have all privileges on all tables in the database "sample". Any insight would be much appreciated. Thanks, Nathan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] character 0xe29986 of encoding "UTF8" has no equivalent in "LATIN2"
The function "convert_to(string text, dest_encoding name)" will throw an error and so break my application when not supported characters are included in the unicode string. So what can I do - to filter characters out which have no counterpart in the latin codesets - or to simple ignore wrong characters? Problem: Users will enter _any_ characters in my application and an error really doesn't help in this case. What I am searching for is a function to undiacritic special letters to simple ones. There is provided an example - http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Diacritic_removing - which will not work because of the error, when I put _any_ valid UTF8 character to the functions. Best, Andi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command error
On Mon, Aug 03, 2009 at 01:25:56PM -0400, Mark Watson wrote: > Andrew Maracini wrote: > >GISCI# \copy gisp from 'd:/projects/gisp/gisp.csv' delimiter ',' > > If this is the case, exporting the > City/state field wrapped in quotation marks should do the trick You'll want to use the real CSV parser then, the code in psql would look like this: \copy gisp FROM 'd:/projects/gisp/gisp.csv' WITH CSV PG and MS Excel have an almost identical definition of what a CSV file should look like, opening the file in Excel is always a good quick check as to why PG isn't liking the file. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command error
Mark Watson wrote: *>De :* pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] *De la part de* Andrew Maracini >*Envoyé :* 3 août 2009 11:46 >*À :* pgsql-general@postgresql.org >*Objet :* [GENERAL] \copy command error > >hi, > >I can't seem to get the \copy command to work. > >Here's my syntax: > >GISCI# \copy gisp from 'd:/projects/gisp/gisp.csv' delimiter ',' > >ERROR extra data after last expected column Line 1 > > >It is taking all of the fields and grouping them into one field, the .csv has about 4700 records, here is what Maybe the error is \c because '\c' is used to connect to another database. try the same line without '\' only : copy gisp from d:/projects/gisp/gisp.csv' delimiters ','; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command error
>De : pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] De la part de Andrew Maracini >Envoyé : 3 août 2009 11:46 >À : pgsql-general@postgresql.org >Objet : [GENERAL] \copy command error > >hi, > >I can't seem to get the \copy command to work. > >Here's my syntax: > >GISCI# \copy gisp from 'd:/projects/gisp/gisp.csv' delimiter ',' > >ERROR extra data after last expected column Line 1 > > >It is taking all of the fields and grouping them into one field, the .csv has about 4700 records, here is what the first two look like: >Jon,JF,Doe,GIS Coordinator,Miami University,Ohio,Oxford,OH,United States,10/25/02 >Janet,,Doe,PhD Student and Professional Geologist,York University,Toronto,ON,Canada,9/25/07 >The table has 9 fields mostly varchar and one date field. >I'm running 8.3 on Windows Vista Ultimate > >thanks. > >Andy Hi Andy, Your CSV lines have 10 fields (10 comma separated values). Probably, in the data you are exporting, the City/State is one field, but the comma is being exported, thus giving you 10 fields. If this is the case, exporting the City/state field wrapped in quotation marks should do the truck, or use a different delimiter, like Tab Mark
Re: [GENERAL] using generate_series to iterate through months
On Mon, Aug 03, 2009 at 11:15:25AM -0400, Bill Reynolds wrote:
> Here is what I am using in the from clause (along with other tables) to
> generate the series of numbers for the number of months. This seems to
> work:
> generate_series( 0, ((extract(years from age(current_date,
> DATE('2008-05-01')))*12) + extract(month from age(current_date,
> DATE('2008-05-01'::INTEGER) as s(a)
I doubt you're using it, but the generate_series in 8.4 knows how to
handle dates and intervals, for example you can do:
SELECT generate_series(timestamp '2001-1-1','2004-1-1',interval '1 month');
to go from 2001 to 2004 in one month steps. If not, I'd be tempted to
bung the above into a function at that does the same. Something like
this should work OK for series with only a few thousand rows, but don't
use it to generate a microsecond spaced series covering several years:
CREATE FUNCTION generate_series(timestamp,timestamp,interval)
RETURNS SETOF timestamp
LANGUAGE plpgsql
IMMUTABLE AS $$
DECLARE
_c timestamp := $1;
BEGIN
WHILE _c < $2 LOOP
RETURN NEXT _c;
_c := _c + $3;
END LOOP;
END;
$$;
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] \copy command error
hi, I can't seem to get the \copy command to work. Here's my syntax: GISCI# \copy gisp from 'd:/projects/gisp/gisp.csv' delimiter ',' ERROR extra data after last expected column Line 1 It is taking all of the fields and grouping them into one field, the .csv has about 4700 records, here is what the first two look like: Jon,JF,Doe,GIS Coordinator,Miami University,Ohio,Oxford,OH,United States,10/25/02 Janet,,Doe,PhD Student and Professional Geologist,York University,Toronto,ON,Canada,9/25/07 The table has 9 fields mostly varchar and one date field. I'm running 8.3 on Windows Vista Ultimate thanks. Andy -- Andrew Maracini, GISP/AICP Superior GIS Solutions LLC 3309 N.Casaloma Dr. #114 Appleton, WI 54913 http://www.superiorgissolutions.com 920-574-2090 906-361-4132 (cell)
Re: [GENERAL] using generate_series to iterate through months
Ok, it is Monday -:) Thanks Tom!
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Monday, August 03, 2009 11:44 AM
To: Bill Reynolds
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] using generate_series to iterate through months
"Bill Reynolds" writes:
> Ok, I'm a bit stumped on getting my group by query to work which
> iterates through a number of months that basically a generate_series
> provides for me.
> ...
> select DATE('2008-05-01') + interval (s.a??? ' months') as Month_of
No, you're confusing a syntax that's meant to handle literal constants
with something that's appropriate for computation. What you want is
> select DATE('2008-05-01') + s.a * interval '1 month' as Month_of
that is, multiply the sequence output by a suitable interval constant.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] using generate_series to iterate through months
Bill Reynolds wrote:
> Ok, I'm a bit stumped on getting my group by query to work which
> iterates through a number of months that basically a generate_series
> provides for me.
>
> Here is what I am using in the from clause (along with other tables) to
> generate the series of numbers for the number of months. This seems to
> work:
> generate_series( 0, ((extract(years from age(current_date,
> DATE('2008-05-01')))*12) + extract(month from age(current_date,
> DATE('2008-05-01'::INTEGER) as s(a)
>
> Next, I want to group by and compare against dates in my table. These
> are the two parts I can't get to work:
>
> In the select part:
> select DATE('2008-05-01') + interval (s.a??? ' months') as Month_of
The trick is easy:
test=*# select current_date + s.a * '1 month'::interval from (select
generate_series(0,10) as a) as s;
?column?
-
2009-08-03 00:00:00
2009-09-03 00:00:00
2009-10-03 00:00:00
2009-11-03 00:00:00
2009-12-03 00:00:00
2010-01-03 00:00:00
2010-02-03 00:00:00
2010-03-03 00:00:00
2010-04-03 00:00:00
2010-05-03 00:00:00
2010-06-03 00:00:00
(11 Zeilen)
I think, you can solve your problem now.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Partition tables
Michael Gould wrote: 2. can you come up with a pattern you can use to segment your data into smaller chunks (like weeks, months, ids) to create partitions reasonably big but not huge Yes, each location has their own specific location code. good! how many rows you will have per each location code? how do you use the data? most of your reporting would read from one location only or from multiple ones? 3. how do you populate your db? inserts? copy? if ever you create partitions, will you write to many or just one (the most recent one) Everything will be done via inserts either via online entry or from external processes such as EDI processing. It would depend on how the partition is setup. If we set them up by location, there would be one insert or more for each record entered and it would to just the location files. If we did it by date range then everyone would be entering data into a single set of tables based on date range instead of by location. do you read tables in the same time when you insert into it? how many inserts per sec/min/hour you may have? approximate.. Yes, it can be useful archiving old 'locations'. In that case I'd suggest to create dedicated datafile and put it on (slower=cheaper) disks and move your older partitions there... but again, it's not the main reason why you could consider partitioning. >From our current platform, disk space isn't a issue. Our db is highly normalized and we've had about 200 locations over the past 10 years and the db is currently about 4 gig of total data. That being said, the only way to reclaim space with this db is to do a complete unload/reload which we don't do very oftern and it appears that the Postgres vaccuum all process is much easier to use. ok, in that case why do you want to archive the data in the first place? you have no space constraints and you still need to read the data from time to time... Regards, foo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] using generate_series to iterate through months
"Bill Reynolds" writes:
> Ok, I'm a bit stumped on getting my group by query to work which
> iterates through a number of months that basically a generate_series
> provides for me.
> ...
> select DATE('2008-05-01') + interval (s.a??? ' months') as Month_of
No, you're confusing a syntax that's meant to handle literal constants
with something that's appropriate for computation. What you want is
> select DATE('2008-05-01') + s.a * interval '1 month' as Month_of
that is, multiply the sequence output by a suitable interval constant.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Partition tables
Michael Gould wrote: I am considering whether or not to partition a few tables in our system. The guide lines I read said that this could help when the size of a table exceeds the amount of system memory. I'm wondering if this statement should be table or database. The way our system operates is that each individual location is actually a separate operation owned by another party. In most cases the data from one location should not be seen by others. There are several tables which are global in nature which hold mainly lookup information, but the actual processing tables are by location. I am wondering if partitioning would be a good way to isolate the information for each location. Each database would be created by location number. Out db servers is a dual quad Intel with 4 Gigs of RAM running Windows 2000 Enterprise Server. All Client machines are running Quad core servers with 8-16 gig of RAM partitioned using Windows 2003 and accessed by Citrix. The total size of our database with 5 years worth of data is about 3.4 gig. In the business we are in, we open about 5-7 new locations each year and close 2-3. I was also thinking that if each partition was by location it would be easier to disconnect the partion tables to use for historial use when we close a location. We probably would get 10 or so queries on the closed locations 6 months after closing. Does this seem like an appropriate use of table partitioning? Hi - partitioning by definition should be used to get your data faster and, in most of the cases, should be transparent to your client. Questions I'd suggest to ask first: 1. do you have performance issues reading from your db? 2. can you come up with a pattern you can use to segment your data into smaller chunks (like weeks, months, ids) to create partitions reasonably big but not huge 3. how do you populate your db? inserts? copy? if ever you create partitions, will you write to many or just one (the most recent one) Yes, it can be useful archiving old 'locations'. In that case I'd suggest to create dedicated datafile and put it on (slower=cheaper) disks and move your older partitions there... but again, it's not the main reason why you could consider partitioning. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax Regards, foo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] using generate_series to iterate through months
Ok, I'm a bit stumped on getting my group by query to work which
iterates through a number of months that basically a generate_series
provides for me.
Here is what I am using in the from clause (along with other tables) to
generate the series of numbers for the number of months. This seems to
work:
generate_series( 0, ((extract(years from age(current_date,
DATE('2008-05-01')))*12) + extract(month from age(current_date,
DATE('2008-05-01'::INTEGER) as s(a)
Next, I want to group by and compare against dates in my table. These
are the two parts I can't get to work:
In the select part:
select DATE('2008-05-01') + interval (s.a??? ' months') as Month_of
and in the where clause:
and DATE(sometimestamp) >= DATE('2008-05-01') || 's.a??? months' -
someOffsetVariable - 30
and DATE(sometimestamp) < DATE('2008-05-01') + s.a + 1 || 'months'
The point is I want to use interval (s.a 'months') to iterate through
them. I can obviously get DATE('2008-05-01') + interval '3 months' to
work, but I want the '3' part to be generated with a series. I have
tried various type casting to no avail. Any help with this syntax would
be appreciated.
Thanks in advance for insight on how to do this.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Partition tables
I am considering whether or not to partition a few tables in our system. The guide lines I read said that this could help when the size of a table exceeds the amount of system memory. I'm wondering if this statement should be table or database. The way our system operates is that each individual location is actually a separate operation owned by another party. In most cases the data from one location should not be seen by others. There are several tables which are global in nature which hold mainly lookup information, but the actual processing tables are by location. I am wondering if partitioning would be a good way to isolate the information for each location. Each database would be created by location number. Out db servers is a dual quad Intel with 4 Gigs of RAM running Windows 2000 Enterprise Server. All Client machines are running Quad core servers with 8-16 gig of RAM partitioned using Windows 2003 and accessed by Citrix. The total size of our database with 5 years worth of data is about 3.4 gig. In the business we are in, we open about 5-7 new locations each year and close 2-3. I was also thinking that if each partition was by location it would be easier to disconnect the partion tables to use for historial use when we close a location. We probably would get 10 or so queries on the closed locations 6 months after closing. Does this seem like an appropriate use of table partitioning? Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with Numerics multiplication in C-function
Martijn van Oosterhout writes:
> On Mon, Aug 03, 2009 at 10:20:36AM -0400, Tom Lane wrote:
>> SPI_palloc is what to use to allocate the result in the right place.
> Ofcourse, and then a memcpy(). I was distracted by the fact there is an
> SPI_copy/returntuple(), but no SPI_returndatum().
Yeah, I was just thinking that that seems like an oversight.
plpgsql does this:
if (!fcinfo->isnull && !func->fn_retbyval)
{
Sizelen;
void *tmp;
len = datumGetSize(estate.retval, false, func->fn_rettyplen);
tmp = SPI_palloc(len);
memcpy(tmp, DatumGetPointer(estate.retval), len);
estate.retval = PointerGetDatum(tmp);
}
but it seems like it'd be reasonable to provide SPI_datumcopy or
something like that to encapsulate this a bit more conveniently.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with Numerics multiplication in C-function
On Mon, Aug 03, 2009 at 10:20:36AM -0400, Tom Lane wrote: > Martijn van Oosterhout writes: > > What you need to do is copy the Datum you wish to return into the upper > > memory context. I'm not seeing a helpful SPI utility function or > > obvious example here you help you, but I'm sure it's something with > > MemoryContextSwitchTo() and datumCopy(). > > SPI_palloc is what to use to allocate the result in the right place. Ofcourse, and then a memcpy(). I was distracted by the fact there is an SPI_copy/returntuple(), but no SPI_returndatum(). It might actually be helpful to include in the documentation an example of returning from a function a result from SPI, since it's not entirely obvious. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] synchronous_commit=off doesn't always return immediately
On Mon, Aug 3, 2009 at 2:14 AM, tomrevam wrote: > > > > Tom Lane-2 wrote: >> >> tomrevam writes: >> It might help to increase wal_buffers. >> > > Hi, > > I increased the wal_buffers to 2 MB. Initially this improved the performance > very much, but after 4 days of continuous operation the system is back to > very long inserts and updates (at least as bad as it was before). > Is there a way to know what resource the DB is running out of? I can almost guarantee you that you are i/o bound. synchronous commit helps, but is not magic: your single sata disk can only deliver about 100-200 iops and you are expecting more than that. I think you have only two options: add more/faster disks (and consider separating wal from the data volume) or disable sync completely with fsync=off (which is of course quite dangerous). This can be confirmed with an iostat, noting the tps on each volume and the iowait. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with Numerics multiplication in C-function
Martijn van Oosterhout writes: > What you need to do is copy the Datum you wish to return into the upper > memory context. I'm not seeing a helpful SPI utility function or > obvious example here you help you, but I'm sure it's something with > MemoryContextSwitchTo() and datumCopy(). SPI_palloc is what to use to allocate the result in the right place. My guess is that the reason the function appeared to work (most of the time) before is that it wasn't being tested in an assert-enabled build. CLOBBER_FREED_MEMORY is exposing the mistake. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Out of Office AutoReply: Never thought he would be able
Thank you for your email, Chris Black has left the ACEA - the Victoria/ Tasmania office will be unattended until the 17th November when Chris will be replaced by Cathryn Ellis During this period please contact ACEA NSW on 02 9966 4966 or email. elizab...@acea.com.au or pa...@acea.com.au Kind Regards ACEA
Re: [GENERAL] Problem with Numerics multiplication in C-function
On Mon, Aug 03, 2009 at 01:45:53PM +1100, Ilya Urikh wrote: > Hi, I have a strange problem with Numeric multiplication in C-function. > > There are 2 functions getRate and getVAT which return Numeric. In 3rd > function calculateService I try to multiply the results of getRate and > getVAT. After execution I have two types of error, some time without > messages and with message "Invalid memory alloc ... ". > If I initialize the Numeric variables inside calculateService and multiply, > function numeric_mul works fine. Not entirly sure, but I think it's because SPI_finish destroys the SPI memory context, including the data in it, which is the data you are trying to return. See this page in the documentation: http://www.postgresql.org/docs/8.3/interactive/spi-memory.html What you need to do is copy the Datum you wish to return into the upper memory context. I'm not seeing a helpful SPI utility function or obvious example here you help you, but I'm sure it's something with MemoryContextSwitchTo() and datumCopy(). Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
