Re: [GENERAL] one-click installer in linux redhat-centos-fedora
On Tue, Jan 13, 2009 at 9:36 AM, m zyzy myz...@gmail.com wrote: I successfully install the .bin installer in linux redhat-centos-fedora. After a few days using pgadmin and postgresql in the one-click ,I decided to go back to old plain postgresql and postGIS (Am I right ? the one click installer not include the PostGIS extension. ) . Is there an option to uninstall the one-click-postgresql-et.al from the binary .bin file. The --help option not much help too. No. I know I would be able to simply delete the Postgresql/8.3/ installation folder straightaway but just not sure that would be a clean uninstall method. This is because when I execute ./uninstall-postgresql it always prompt me Segmentation fault , without the quotes. Urgh - what distro/version exactly? You can just delete the installation directory - but first you should run $INSTALLDIR/installer/server/removeshortcuts.sh You'll also need to remove the postgres user account (if it wasn't there before you installed), /etc/init.d/postgresql-8.3 and /etc/ld.so.conf.d/postgresql-8.3.conf (if that doesn't exist, check for a line in /etc/ld.so.conf). -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] one-click installer in linux redhat-centos-fedora
I successfully install the .bin installer in linux redhat-centos-fedora. After a few days using pgadmin and postgresql in the one-click ,I decided to go back to old plain postgresql and postGIS (Am I right ? the one click installer not include the PostGIS extension. ) . Is there an option to uninstall the one-click-postgresql-et.al from the binary .bin file. The --help option not much help too. I know I would be able to simply delete the Postgresql/8.3/ installation folder straightaway but just not sure that would be a clean uninstall method. This is because when I execute ./uninstall-postgresql it always prompt me Segmentation fault , without the quotes.
[GENERAL] limit and other joined tables
Is the planner/optimiser smart enough to join just after the LIMIT in a similar situation: select [columns from A, B and C] from A join B on A.Aid=B.Bid join C on A.Aid=C.Cid where (conditions on A and B columns) order by [columns from A and B] limit 10; What about a similar situation with subselects? select [columns from A, B, C] from ( select [column from A, B] where (conditions on A and B columns) ) s join C on s.Aid=C.Cid order by [columns from A, B] limit 10; vs select [columns from A, B, C] from ( select [column from A, B] order by [columns from A, B] limit 10 ) s join C on s.Aid=C.Cid; Can postgresql take advantage of the LIMIT even if it is in the outer select? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Trying to create implicit casts to text in PG 8.3
2009/1/13 Aleksander Kmetec aleksander.kme...@intera.si: Hi, everyone. I'm trying to upgrade a database which is used by several hundred installations of an app; with each installation possibly running some custom code and 3rd party extensions. I was hoping that it would be possible to re-add implicit casts to text for bacwards compatibility with 8.2, since that would be much easier and smoother than testing all those installations and/or waiting for bug reports to start pouring in... I tried adding casts found here: http://code.open-bio.org/svnweb/index.cgi/biosql/revision?rev=284 Function calls work fine now, but a new problem appeared with operators: SELECT EXTRACT(DAY FROM NOW()) || 'abc'; ERROR: operator is not unique: double precision || unknown Is there a way to get around this? You add the cast's but not the operator. The cast is in other way (try to compare with a text '13') for example. Regards, Aleksander -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Emanuel Calvo Franco ArPUG / AOSUG Member Postgresql Support Admin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [pgsql-advocacy] [GENERAL] PgUS 2008 end of year summary
Scott Marlowe escribió: In this thread getting a bounce from advocacy won't bother me too much. Having hundreds ot bounce messages in a busy thread, would be much much worse. Yup. I wonder, if all the mailing lists are run by the same software, wouldn't it be easy enough to have a kind of passthrough filter for other mailiing list? You can post to them in addition to the ones you're subscribed to, knowing you'll get the thread back by reply to semantics and no one need get a bounce message. At most a we've secretly moderated your post into pgsql-xyz, let's see if they notice you're not really a member of their mailing list reply. And / or an auto approval message into the group you're posting into? Yeah, there is a way to configure lists this way in Mj2. We only have to get Marc to play along ... -- 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
[GENERAL] Polymorphic setof record function?
Hi list,
I have written a function that returns a setof record. The function has
a table name as a parameter and the resulting records have the same
structure as this table. Is there any easy way to specify this when I
call the function? If the table has many columns then it's annoying to
specify all of them.
I need something like:
select * from myfunc('mytable') as x(like mytable)
or
select * from myfunc('mytable') as x(mytable%TYPE)
Is there any solution for PostgreSQL 8.2?
Regards,
Christian
--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
--
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] Polymorphic setof record function?
2009/1/13 Christian Schröder c...@deriva.de:
Hi list,
I have written a function that returns a setof record. The function has a
table name as a parameter and the resulting records have the same structure
as this table. Is there any easy way to specify this when I call the
function? If the table has many columns then it's annoying to specify all of
them.
I need something like:
select * from myfunc('mytable') as x(like mytable)
or
select * from myfunc('mytable') as x(mytable%TYPE)
Is there any solution for PostgreSQL 8.2?
Can make the function with text parametter (table name) and
search in the catalog for the table?. Then you will have the table
to get %TYPE...
Regards,
Christian
--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Emanuel Calvo Franco
ArPUG / AOSUG Member
Postgresql Support Admin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cast for text-Integer missing in 8.3.5
I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are not there and how can I get them back. I have many stored procedures that rely on those casts
Re: [GENERAL] Polymorphic setof record function?
On Tue, Jan 13, 2009 at 02:50:49PM +0100, Christian Schrrrder wrote: I have written a function that returns a setof record. The function has a table name as a parameter and the resulting records have the same structure as this table. Is there any easy way to specify this when I call the function? If the table has many columns then it's annoying to specify all of them. I'm struggling to understand PG as well. I'd expect to be able to use the normal :: cast operator, but it doesn't seem to function as expected. I came up with the following code: CREATE TEMP TABLE foo ( cola INTEGER, colb TEXT ); INSERT INTO foo (cola, colb) VALUES (1, 'hi'), (2, 'bye'), (3, 'hello'), (4, 'testing'); SELECT (x::foo).cola FROM (SELECT x::record FROM foo x LIMIT 10) x; CREATE FUNCTION bar() RETURNS SETOF RECORD LANGUAGE SQL AS $$ SELECT * FROM foo LIMIT 10; $$; SELECT (x::foo).cola FROM ( SELECT bar()) AS xxx(x); I get cannot cast type record to foo from the final query, which I don't understand at all. It suggests that casting something to a value of type RECORD sometimes keeps the real type information around, and sometimes not. Why? Sam -- 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] Cast for text-Integer missing in 8.3.5
2009/1/13 Nykolyn, Andrew andrew.nyko...@ngc.com: I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are not there and how can I get them back. I have many stored procedures that rely on those casts http://code.open-bio.org/svnweb/index.cgi/biosql/revision?rev=284 here are the functions -- Emanuel Calvo Franco ArPUG / AOSUG Member Postgresql Support Admin -- 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] Trying to create implicit casts to text in PG 8.3
Emanuel Calvo Franco wrote: You add the cast's but not the operator. The cast is in other way (try to compare with a text '13') for example. Thanks. I added both ||(double precision, text) and ||(text, double precision) operators and it works now. But I'm wondering: do I need to do anything else besides creating implicit casts and adding missing operators? And will this introduce any side effects which are not compatible with 8.2 and might break my queries in unpredictable ways? Regards, Aleksander -- 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] Cast for text-Integer missing in 8.3.5
Nykolyn, Andrew wrote: I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are not there and how can I get them back. I have many stored procedures that rely on those casts I'm right in the middle of trying to solve the same problem, myself. I started with this link: http://code.open-bio.org/svnweb/index.cgi/biosql/revision?rev=284 But that wasn't enough. In fact, it broke some things that worked before I added the missing implicit casts. Like this, for example: select 123::double precision || 'abc'; ERROR: operator is not unique: double precision || unknown So I also had to define ||(double precision, text) and ||(text, double precision) operators. I'm not sure how much additional work might be needed, but here's what I have so far for adding backwards compatility for double precision datatype: CREATE FUNCTION pg_catalog.text(double precision) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(float8out($1));'; CREATE CAST (double precision AS text) WITH FUNCTION pg_catalog.text(double precision) AS IMPLICIT; CREATE FUNCTION pg_catalog.compat_textcat(double precision, text) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textcat(CAST($1 AS TEXT), $2);'; CREATE FUNCTION pg_catalog.compat_textcat(text, double precision) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textcat($1, CAST($2 AS TEXT));'; CREATE OPERATOR pg_catalog.|| ( PROCEDURE = compat_textcat, LEFTARG = double precision, RIGHTARG = text ); CREATE OPERATOR pg_catalog.|| ( PROCEDURE = compat_textcat, LEFTARG = text, RIGHTARG = double precision ); Regards, Aleksander -- 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] Trying to create implicit casts to text in PG 8.3
On Tue, Jan 13, 2009 at 05:43:52PM +0100, Aleksander Kmetec wrote: I added both ||(double precision, text) and ||(text, double precision) operators and it works now. But I'm wondering: do I need to do anything else besides creating implicit casts and adding missing operators? And will this introduce any side effects which are not compatible with 8.2 and might break my queries in unpredictable ways? You'll get less errors from PG because you've told it to convert things automatically for you. I'd be tempted to not do this and change the code to make these conversion explicit as there will be cases (maybe when you're writing code in the future) when you want to get an error and not have PG do something unexpected. In the end, any type system is just a tool. It's main job is to find bugs in code by spotting a common class of error (it also allows other optimizations, but that's normally less important). I tend to find it serves a useful purpose and the errors it gives are indications for me to be more explicit with my code. If you find it's too strict you're free to disable it by adding in extra casts/functions/operators. Sam -- 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] Trying to create implicit casts to text in PG 8.3
On Tue, 2009-01-13 at 16:58 +, Sam Mason wrote: In the end, any type system is just a tool. It's main job is to find bugs in code by spotting a common class of error The purpose of the database as a whole is to preserve the integrity of your data. The type system is a key component of that. The main job of the type system is to assist in insuring that your data is correct. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Cast for text-Integer missing in 8.3.5
2009/1/13 Aleksander Kmetec aleksander.kme...@intera.si: Nykolyn, Andrew wrote: I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are not there and how can I get them back. I have many stored procedures that rely on those casts I'm right in the middle of trying to solve the same problem, myself. I started with this link: http://code.open-bio.org/svnweb/index.cgi/biosql/revision?rev=284 But that wasn't enough. In fact, it broke some things that worked before I added the missing implicit casts. Like this, for example: select 123::double precision || 'abc'; ERROR: operator is not unique: double precision || unknown So I also had to define ||(double precision, text) and ||(text, double precision) operators. I'm not sure how much additional work might be needed, but here's what I have so far for adding backwards compatility for double precision datatype: CREATE FUNCTION pg_catalog.text(double precision) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(float8out($1));'; CREATE CAST (double precision AS text) WITH FUNCTION pg_catalog.text(double precision) AS IMPLICIT; CREATE FUNCTION pg_catalog.compat_textcat(double precision, text) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textcat(CAST($1 AS TEXT), $2);'; CREATE FUNCTION pg_catalog.compat_textcat(text, double precision) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textcat($1, CAST($2 AS TEXT));'; CREATE OPERATOR pg_catalog.|| ( PROCEDURE = compat_textcat, LEFTARG = double precision, RIGHTARG = text ); CREATE OPERATOR pg_catalog.|| ( PROCEDURE = compat_textcat, LEFTARG = text, RIGHTARG = double precision ); Regards, Aleksander -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general I forgive to tell you that you must add operators...d'oh! -- Emanuel Calvo Franco ArPUG / AOSUG Member Postgresql Support Admin -- 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] Cast for text-Integer missing in 8.3.5
On Tue, Jan 13, 2009 at 8:23 AM, Nykolyn, Andrew andrew.nyko...@ngc.com wrote: I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are not there and how can I get them back. I have many stored procedures that rely on those casts They were removed because the output was unpredictable. It's far better to spend your time fixing your code than trying to reinstate buggy / questionable behaviour. -- 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] Trying to create implicit casts to text in PG 8.3
On Tue, Jan 13, 2009 at 09:14:15AM -0800, Joshua D. Drake wrote: On Tue, 2009-01-13 at 16:58 +, Sam Mason wrote: In the end, any type system is just a tool. It's main job is to find bugs in code by spotting a common class of error The purpose of the database as a whole is to preserve the integrity of your data. The type system is a key component of that. The main job of the type system is to assist in insuring that your data is correct. I think we're saying the same thing, but just to make sure: it's technically possible (but practically *very* difficult) to preserve the integrity of your data without having any type system. Knowing this, languages have some type system (either statically enforced at compile time, or dynamically checked during interpretation, or some mixture of both) to ask us for clarification when we've written some code that looks a bit dubious. As a side note, modern languages have extended types a long way. Some to the extent that you can program at the type level, giving the user many more tools to constrain the dynamic aspects of their code. One fun experiment I've never really managed to get my head around is Chameleon[1]. Sam [1] http://taichi.ddns.comp.nus.edu.sg/taichiwiki/ChameleonHomePage -- 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] Cast for text-Integer missing in 8.3.5
Scott Marlowe scott.marl...@gmail.com writes: On Tue, Jan 13, 2009 at 8:23 AM, Nykolyn, Andrew andrew.nyko...@ngc.com wrote: I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are not there and how can I get them back. I have many stored procedures that rely on those casts They were removed because the output was unpredictable. It's far better to spend your time fixing your code than trying to reinstate buggy / questionable behaviour. To clarify: those casts are not *gone*, they just don't occur implicitly anymore. You should put explicit casts into your functions in those places where you actually intend an integer to be converted to text or vice versa. Don't be surprised if this turns up some bugs in your code, ie places where you didn't really intend the semantics you were getting. 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] Cast for text-Integer missing in 8.3.5
2009/1/13 Aleksander Kmetec aleksander.kme...@intera.si: Nykolyn, Andrew wrote: I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are not there and how can I get them back. I have many stored procedures that rely on those casts I'm right in the middle of trying to solve the same problem, myself. I started with this link: http://code.open-bio.org/svnweb/index.cgi/biosql/revision?rev=284 But that wasn't enough. In fact, it broke some things that worked before I added the missing implicit casts. Like this, for example: select 123::double precision || 'abc'; ERROR: operator is not unique: double precision || unknown So I also had to define ||(double precision, text) and ||(text, double precision) operators. I'm not sure how much additional work might be needed, but here's what I have so far for adding backwards compatility for double precision datatype: CREATE FUNCTION pg_catalog.text(double precision) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(float8out($1));'; CREATE CAST (double precision AS text) WITH FUNCTION pg_catalog.text(double precision) AS IMPLICIT; CREATE FUNCTION pg_catalog.compat_textcat(double precision, text) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textcat(CAST($1 AS TEXT), $2);'; CREATE FUNCTION pg_catalog.compat_textcat(text, double precision) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textcat($1, CAST($2 AS TEXT));'; CREATE OPERATOR pg_catalog.|| ( PROCEDURE = compat_textcat, LEFTARG = double precision, RIGHTARG = text ); CREATE OPERATOR pg_catalog.|| ( PROCEDURE = compat_textcat, LEFTARG = text, RIGHTARG = double precision ); The above has all worked great to get me past the two issues described so far. Now I am having a problem with: function quote_literal is not unique. Is there anything to make that backward compatible? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Use PSQLFS for photo storage
I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? -- 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] Use PSQLFS for photo storage
On Tuesday 13 January 2009, Jason Long mailing.l...@supernovasoftware.com wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? A perl script using either bytea fields or the lo_ interface via DBD::Pg would work well. -- Alan -- 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] Use PSQLFS for photo storage
bytea was what I was going for. *Does anyone have a script they would share for this purpose? * If not I will probably use Java because this is what I am familiar with. The web app I will write for managing my photos will be written in Java. I want to be able to categorize, label, search, my photos. Alan Hodgson wrote: On Tuesday 13 January 2009, Jason Long mailing.l...@supernovasoftware.com wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? A perl script using either bytea fields or the lo_ interface via DBD::Pg would work well.
[GENERAL] XPath to search for elements in a sequence
With XML similar to:
a
b
c 1 / c
c 2 / c
c 3 / c
/ b
I'm trying to create an xpath expression (for a postgresql query) that will
return if is a particular value and not that is all three values.
What I currently have (which does not work) is:
select * from someTable where xpath ('//uim:a/text()', job, ARRAY[
ARRAY['uim',' http://www.cmpy.com/uim '] ])::text[] IN (ARRAY['1','3']);
If I try with ARRAY['1'] this will not return any values but with
ARRAY['1','2','3'] it will return all three.
How can I select based on a single element in a sequence?
Thanks.
Re: [GENERAL] Use PSQLFS for photo storage
On Jan 13, 2009, at 10:34 AM, Jason Long wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? If it's a filesystem then you'd get the files into the system by copying them there. You wouldn't want to touch the database manually (that'd be like touching the raw disk device on a real filesystem). Conversely, it's just a filesystem. There's not really any use to putting a filesystem on top of a database on top of a filesystem other than the (significant) hack value. In other words, you probably don't really want to do this. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Returning number of matches on a query when using limit
Hi. How would I return the number of matches found by a query, but when I only want to return 30 of them ? In MySQL there is a way of calling SQL_CALC_FOUND_ROWS to do this? Is there something similiar that can be done in PostgreSQL ? Do I have to rerun my query? Thanks / Moe
Re: [GENERAL] Returning number of matches on a query when using limit
On Tuesday 13 January 2009 21:56:56 Mohamed wrote: Hi. How would I return the number of matches found by a query, but when I only want to return 30 of them ? In MySQL there is a way of calling SQL_CALC_FOUND_ROWS to do this? Is there something similiar that can be done in PostgreSQL ? Do I have to rerun my query? Thanks / Moe ... RETURNING COUNT(*) -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- 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] Cast for text-Integer missing in 8.3.5
Nykolyn, Andrew andrew.nyko...@ngc.com writes: The above has all worked great to get me past the two issues described so far. Now I am having a problem with: function quote_literal is not unique. And you're going to have a few other problems after you get past that. You can't just insert implicit casts and expect that it will affect only the situations you want it to affect. The reason we downgraded the implicit casts to begin with was exactly that they kicked in too often. You might try backing off to just having the special || operators and not the implicit casts, and see if that gets you to where you want, or at least close enough that fixing your remaining code properly is not too big a task. Extra operators are not nearly as dangerous as implicit casts. 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] Use PSQLFS for photo storage
Steve Atkins wrote: On Jan 13, 2009, at 10:34 AM, Jason Long wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Once they are in there I can deal with them. My main purpose is to use rsync to get the files into the database. Is there a better way to load 20,000 plus files reliably into Postgres? If it's a filesystem then you'd get the files into the system by copying them there. You wouldn't want to touch the database manually (that'd be like touching the raw disk device on a real filesystem). Conversely, it's just a filesystem. There's not really any use to putting a filesystem on top of a database on top of a filesystem other than the (significant) hack value. In other words, you probably don't really want to do this. Cheers, Steve I just want an easy way to load the files into the DB and their original path they were loaded from. Is possible through SQL to load a file into a bytea column? -- 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] Use PSQLFS for photo storage
On Tue, Jan 13, 2009 at 03:28:18PM -0600, Jason Long wrote: Steve Atkins wrote: On Jan 13, 2009, at 10:34 AM, Jason Long wrote: I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/) to store 100 GB of images in PostgreSQL. Is there a better way to load 20,000 plus files reliably into Postgres? That would imply that they're around 5MB on average? If they're all under, say, 20MB (or maybe even much more) you should be able to handle it by doing the most naive things possible. I just want an easy way to load the files into the DB and their original path they were loaded from. Is possible through SQL to load a file into a bytea column? You'd need to generate the SQL somehow; if you know python it's probably a pretty easy 20 or 30 lines of code to get this working. psycopg seems to be the recommend way of accessing PG with python and you basically want to be doing something like: import psycopg2; filename = myimage.jpeg conn = psycopg2.connect(); conn.cursor().execute( INSERT INTO pictures (filename,data) VALUES (%s,%s);, [filename,psycopg2.Binary(open(filename,rb).read())]); conn.commit(); This seems to do the right thing for me, and obviously needs to be put into a loop of some sort. But it'll hopefully get you started. Sam -- 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] Use PSQLFS for photo storage
Sam Mason wrote:
On Tue, Jan 13, 2009 at 03:28:18PM -0600, Jason Long wrote:
Steve Atkins wrote:
On Jan 13, 2009, at 10:34 AM, Jason Long wrote:
I would like to use PSQLFS(http://www.edlsystems.com/psqlfs/)
to store 100 GB of images in PostgreSQL.
Is there a better way to load 20,000 plus files reliably into Postgres?
That would imply that they're around 5MB on average? If they're all
under, say, 20MB (or maybe even much more) you should be able to handle
it by doing the most naive things possible.
*This is correct. They are all around 5 MB.*
I just want an easy way to load the files into the DB and their original
path they were loaded from.
Is possible through SQL to load a file into a bytea column?
You'd need to generate the SQL somehow; if you know python it's probably
a pretty easy 20 or 30 lines of code to get this working. psycopg seems
to be the recommend way of accessing PG with python and you basically
want to be doing something like:
import psycopg2;
filename = myimage.jpeg
conn = psycopg2.connect();
conn.cursor().execute(
INSERT INTO pictures (filename,data) VALUES (%s,%s);,
[filename,psycopg2.Binary(open(filename,rb).read())]);
conn.commit();
This seems to do the right thing for me, and obviously needs to be put
into a loop of some sort. But it'll hopefully get you started.
Sam
*Never used Python or Perl. I use primarily Java. I was thinking of
doing something like
*
*INSERT INTO pictures (filename,data) VALUES
('filename','/path/to/my/image/img0009.jpg');
But, this syntax doesn't seem to be supported.
Maybe I can use a custom C function to get the contents of the file. Then do
something like
***INSERT INTO pictures (filename,data) VALUES
('**/path/to/my/image/img0009.jpg**',getBinaryFileContents('/path/to/my/image/img0009.jpg'));
Is there some postgres contrib for something like this?**
Re: [GENERAL] Use PSQLFS for photo storage
On Tue, Jan 13, 2009 at 06:22:34PM -0600, Jason Long wrote:
Sam Mason wrote:
You'd need to generate the SQL somehow; if you know python it's probably
a pretty easy 20 or 30 lines of code to get this working.
*Never used Python or Perl. I use primarily Java. I was thinking of
doing something like
*INSERT INTO pictures (filename,data) VALUES
('filename','/path/to/my/image/img0009.jpg');
If you're OK with using large objects, instead of byteas, you can use
the lo_import function. You'd do something like:
CREATE TABLE pics (
path TEXT PRIMARY KEY,
data OID
);
INSERT INTO pics (path,data)
SELECT path, lo_import(path)
FROM (VALUES ('/path/to/my/image/img0009.jpg')) x(path);
This assumes that the files are accessable to the database server (i.e.
the paths are relative to the server daemon, not the psql command line
or whatever JDBC app you're driving this from).
Does that help any more?
Sam
--
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] Use PSQLFS for photo storage
Sam Mason wrote:
On Tue, Jan 13, 2009 at 06:22:34PM -0600, Jason Long wrote:
Sam Mason wrote:
You'd need to generate the SQL somehow; if you know python it's probably
a pretty easy 20 or 30 lines of code to get this working.
*Never used Python or Perl. I use primarily Java. I was thinking of
doing something like
*INSERT INTO pictures (filename,data) VALUES
('filename','/path/to/my/image/img0009.jpg');
If you're OK with using large objects, instead of byteas, you can use
the lo_import function. You'd do something like:
CREATE TABLE pics (
path TEXT PRIMARY KEY,
data OID
);
INSERT INTO pics (path,data)
SELECT path, lo_import(path)
FROM (VALUES ('/path/to/my/image/img0009.jpg')) x(path);
This assumes that the files are accessable to the database server (i.e.
the paths are relative to the server daemon, not the psql command line
or whatever JDBC app you're driving this from).
Does that help any more?
Sam
*They are on the server. I would rather use bytea. Is it possible to
import them as large objects and then use SQL to convert them to bytea?*
Re: [GENERAL] Use PSQLFS for photo storage
On Tue, Jan 13, 2009 at 06:43:06PM -0600, Jason Long wrote: Sam Mason wrote: If you're OK with using large objects, instead of byteas, you can use the lo_import function. *They are on the server. I would rather use bytea. Is it possible to import them as large objects and then use SQL to convert them to bytea?* You can, but it's pretty backward. If you Java you'd probably be better off using it to slurp in the file and do the insert there. If you really want to use large objects to go to byteas have a look through the client side API[1] and then at the functions available to call from SQL by doing \df from psql as they mirror the client calls very closely. Be aware that you'll be creating a lot more garbage that PG will have to clean out, so importing many gigabytes of data this way will take much longer. Sam -- 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] Use PSQLFS for photo storage
On Wed, Jan 14, 2009 at 12:56:42AM +, Sam Mason wrote: If you Java you'd probably be better off using it Hum, it's getting late. That should be If you *know* Java! Bed time for me I think! Sam -- 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] Use PSQLFS for photo storage
Sam Mason wrote: On Wed, Jan 14, 2009 at 12:56:42AM +, Sam Mason wrote: If you Java you'd probably be better off using it Hum, it's getting late. That should be If you *know* Java! Bed time for me I think! Sam Thanks for the advice. I will probably go with Java. In the inventory system I developed I am already storing documents via bytea. I will probably do the same with these images. I will be dealing with them via hibernate so I guess I should import them the same way. I was hoping for a already developed function that could import an entire directory structure recursively. I can do this easily enough in Java.
