[SQL] Problem while using lo_import and lo_export
Hi,
I have PostgresSQL database connection from server. Server
ip is 172.16.5.179
Now I want use lo_import and lo_export function for storing
images into the database.
This images is located in my system itself.
When I am going using the following query…
Insert into image
values('chanukya',lo_import('D:/Vivek/Personal/PICS/IN/chanukya.jpg'))
….it rises error ‘no such file or
directory’
But the same query executing successfully in my local
database…
Could please tell me to resolve this problem…
Thanks & Regards
Penchal reddy | Software Engineer
Infinite Computer Solutions | Exciting Times…Infinite Possibilities...
SEI-CMMI level 5 | ISO 9001:2000
IT SERVICES |
BPO
Telecom | Finance
| Healthcare | Manufacturing
| Energy & Utilities | Retail
& Distribution | Government
Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com
Information transmitted by this e-mail is
proprietary to Infinite Computer Solutions and/ or its Customers and is
intended for use only by the individual or entity to which it is addressed, and
may contain information that is privileged, confidential or exempt from
disclosure under applicable law. If you are not the intended recipient or it
appears that this mail has been forwarded to you without proper authority, you
are notified that any use or dissemination of this information in any manner is
strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] How to use table from one database to another
Hai all, I have 2 databases namee PAO and CAS. PAO contains 3 schemas named Public,pao,sts CAS contains 4 schemas named Public,cao,sts,reports Now i am in PAO database..now i want access table 'activity' in schema 'cas' in CAS database. How it is posible. 2nd thing is... i have 2 servers access i.e local and mainserver. How access table from one server to another server? please tel me...because we need this one Please give me full details with examples… because I am new to Postgres but I know oracle very well Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] How to cal function in one another
hai, In mainfun,the query returns more than two rows,if execute query outside. I have to remove limit keyword in mainfun defination...even it returns 1st row values only..But i need the multful rows? so how can i do that...please help me... select subfun1(22); subfun1 - (swarna,23) select mainfun(22); (2600,10) query result outside.. select sal,dept into return_rec from emp where mgr=23 -- here i removed the limit keyword in mainfun definition 2600,10 3500,20 6520,10 I am waiting for ur positive reply... thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] alter column type from boolean to char with default doesn't work
Hi,
I basically want to change a boolean column to char. The boolean
column has a default of true. The char column should have 'f' for
false and 't' for true. I think that an SQL statement like the
following should work, but it doesn't:
blog=> select version();
version
PostgreSQL 8.0.7 on i486-pc-linux-gnu, compiled by GCC
gcc-4.0.gcc-opt (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu1)
(1 запись)
blog=> \d posts
Таблица "public.posts"
Колонка | Тип | Модификаторы
--+-+---
id | integer | not null default
nextval('public.posts_id_seq'::text)
title| text|
body | text|
created | timestamp without time zone |
modified | timestamp without time zone |
deleted | boolean | default false
Индексы:
"posts_pkey" PRIMARY KEY, btree (id)
blog=> alter table posts alter column deleted type char(1) using (case
when deleted then 't' else 'f' end), alter column deleted set default
'f';
ERROR: default for column "deleted" cannot be cast to type "pg_catalog.bpchar"
blog=> alter table posts alter column deleted set default 'f', alter
column deleted type char(1) using (case when deleted then 't' else 'f'
end);
ERROR: default for column "deleted" cannot be cast to type "pg_catalog.bpchar"
How is this supposed to work? Note that I don't need a solution for
this special case, but rather want to know, how it is supposed to work
- am I doing it wrong or is it not possible to do it like that (and is
that because the SQL standard or because of how postgresql implements
it?)
Thanks
Markus Bertheau
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] i need informarion regarding functions and arrays...
Hi good evening to all…….. Rcid value 1 yes 2 no 3 yes 4 yes 5 no 6 yes 7 no 8 yes 9 no 10 yes 11 no 12 no this is the front end screen they will enter all these 12 in values… then they will press save button… these values have to be store in rnt table… can u tell me how to store all these 12 values at a time in rnt table… but I written one function for this but it is accepting only one value at a time to insert… this is the function… create or replace function rentfunc(in_rcid int4,in_value varchar) as ‘ begin insert into rnt(racid,auditorvalue) values ($1,$2);end;‘language’plpgsql’; If any one knows how to store all these 12 values in a rnt table… let me know ….. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] How to use table from one database to another
> I have 2 databases namee PAO and CAS. > PAO contains 3 schemas named Public,pao,sts CAS contains 4 schemas > named Public,cao,sts,reports > Now i am in PAO database..now i want access table 'activity' in > schema 'cas' in CAS database. > How it is posible. > 2nd thing is... > i have 2 servers access i.e local and mainserver. > How access table from one server to another server? > please tel me...because we need this one > Please give me full details with examples... because I am new to > Postgres but I know oracle very well For this functionality to work you will need the db-link add on for postgresql. http://pgfoundry.org/projects/snapshot/ Also for other useful addons check out the project tree. http://pgfoundry.org/softwaremap/trove_list.php Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] alter column type from boolean to char with default doesn't work
"Markus Bertheau" <[EMAIL PROTECTED]> writes: > I basically want to change a boolean column to char. The boolean > column has a default of true. The char column should have 'f' for > false and 't' for true. I think that an SQL statement like the > following should work, but it doesn't: Hmm ... the way I would have expected to work is alter table posts alter column deleted drop default, alter column deleted type char(1) using (case when deleted then 't' else 'f' end), alter column deleted set default 'f'; but that does not work either --- you have to do it in more than one command: begin; alter table posts alter column deleted drop default; alter table posts alter column deleted type char(1) using (case when deleted then 't' else 'f' end), alter column deleted set default 'f'; commit; We could fix this by tweaking ATPrepCmd to schedule drop-default subcommands in an earlier pass than alter-type, and set-default subcommands afterwards. However I think the way it's done now was chosen to avoid surprising behavior in corner cases like alter table foo alter column bar set default ..., alter column bar drop default; You'd expect this to leave you with no default, but with the change the DROP part would be re-ordered to occur first. So maybe the cure is worse than the disease. OTOH that's a pretty silly example, whereas wanting to ALTER TYPE and fix the default in a single command is quite reasonable. Thoughts? regards, tom lane ---(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] alter column type from boolean to char with default
On Wed, 2006-08-02 at 09:19 -0400, Tom Lane wrote: > "Markus Bertheau" <[EMAIL PROTECTED]> writes: > > I basically want to change a boolean column to char. The boolean > > column has a default of true. The char column should have 'f' for > > false and 't' for true. I think that an SQL statement like the > > following should work, but it doesn't: > > Hmm ... the way I would have expected to work is > > alter table posts > alter column deleted drop default, > alter column deleted type char(1) > using (case when deleted then 't' else 'f' end), > alter column deleted set default 'f'; Perhaps it is easiest to allow the user to specify the new default after USING? USING already indicates that a direct conversion may not be what the user wants to do with it? alter table posts alter column deleted type char(1) using (case when deleted then 't' else 'f' end) default 'f'; Actually, perhaps it would be best to wrap the old default with the USING expression, evaluating when the default was a scalar and keeping the entire equation otherwise? The below SQL would give a new default of 'f' (evaluated scalar): create table posts (deleted bool default false); alter table posts alter column deleted type char(1) using (case when deleted then 't' else 'f' end); This would result in the default (case when somefunc() then 't' else 'f' end): create table posts (deleted bool default somefunc()); alter table posts alter column deleted type char(1) using (case when deleted then 't' else 'f' end); > but that does not work either --- you have to do it in more than one > command: > > begin; > alter table posts > alter column deleted drop default; > alter table posts > alter column deleted type char(1) > using (case when deleted then 't' else 'f' end), > alter column deleted set default 'f'; > commit; > > We could fix this by tweaking ATPrepCmd to schedule drop-default > subcommands in an earlier pass than alter-type, and set-default > subcommands afterwards. However I think the way it's done now > was chosen to avoid surprising behavior in corner cases like > > alter table foo > alter column bar set default ..., > alter column bar drop default; > > You'd expect this to leave you with no default, but with the change > the DROP part would be re-ordered to occur first. So maybe the > cure is worse than the disease. OTOH that's a pretty silly example, > whereas wanting to ALTER TYPE and fix the default in a single command > is quite reasonable. Thoughts? > > regards, tom lane > > ---(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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] viewing the description of tables from python DB-API
On Tue, 2006-08-01 at 18:14, Rodrigo De Leon wrote: > On 8/1/06, Daniel Joo <[EMAIL PROTECTED]> wrote: > > Hi all, > > > > > > > > Is there a way to view the list of all tables from python (or any other > > languages for that matter) DB-API? What I'm looking for is a command > > similar to the meta-command '\d' that works with the psql client. > > > > > > > > Thanks very much! > > > > > > > > Dan > > There's also the INFORMATION_SCHEMA: > > http://www.postgresql.org/docs/8.1/static/information-schema.html Quick note. The INFORMATION_SCHEMA should stay pretty static from one pgsql version to the next, while the queries that psql -E shows will likely change from one version to another. ---(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] alter column type from boolean to char with default
Rod Taylor <[EMAIL PROTECTED]> writes: > On Wed, 2006-08-02 at 09:19 -0400, Tom Lane wrote: >> Hmm ... the way I would have expected to work is >> >> alter table posts >> alter column deleted drop default, >> alter column deleted type char(1) >> using (case when deleted then 't' else 'f' end), >> alter column deleted set default 'f'; > Perhaps it is easiest to allow the user to specify the new default after > USING? He already did --- I don't want to add some random new syntax for this. Maybe we could hack things so that if both an ALTER TYPE and a SET DEFAULT operation are present, we implicitly add a DROP DEFAULT at the start. But leave the timing of any explicitly specified DROP DEFAULT as-is. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Joining a result set from four (4) tables
On 8/1/06, John Tregea <[EMAIL PROTECTED]> wrote: Because the logic structure of this software is in the front endapplication rather than the database there is a strong need to keep thenaming of fields generic rather than specific, I am not usingpre-defined foreign keys at all. If I was building the database with a purpose specific goal I would be doing what you say. I have found thoughthat when I label elements at different levels of the back end for onepurpose, they are less transportable in the long run. In this case the naming conventions are actually stored in another table and applied asaliases when needed. That way I can change the names and labels (for anew client or industry) while the underlying structure remains the same. I hope to increase interoperability in this way as well. We all find ourselves in different situations and because of that, what works for one person, doesn't work for another - so I understand. Good luck with the application and future queries. Maybe you can use the existing structure of your application to help create a query builder so you can have it write a lot of your joins for you. That should help avoid a lot of simply typos or mix-ups when hand writing your queries. I am glad the queries worked for you.-Aaron== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] Problem while using lo_import and lo_export
On 8/2/06, Penchalaiah P. <[EMAIL PROTECTED]> wrote:
Hi,
I have PostgresSQL database connection from server. Server
ip is 172.16.5.179
Now I want use lo_import and lo_export function for storing
images into the database.
This images is located in my system itself.
When I am going using the following query…
Insert into image
values('chanukya',lo_import('D:/Vivek/Personal/PICS/IN/chanukya.jpg'))
….it rises error 'no such file or
directory'Looks like you are running on a Windows machine. Did you try:Insert into image
values('chanukya',lo_import('D:\Vivek\Personal\PICS\IN\chanukya.jpg'));Windows uses \, not /. I am not sure if PostgreSQL will translate for you like Java does.==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] How to cal function in one another
On 8/2/06, Penchalaiah P. <[EMAIL PROTECTED]> wrote: hai, In mainfun,the query returns more than two rows,if execute query outside. I have to remove limit keyword in mainfun defination...even it returns 1st row values only..But i need the multful rows? so how can i do that...please help me... select subfun1(22); subfun1 - (swarna,23) select mainfun(22); (2600,10) query result outside.. select sal,dept into return_rec from emp where mgr=23 -- here i removed the limit keyword in mainfun definition 2600,10 3500,20 6520,10 I am waiting for ur positive reply...Please provide the code for your functions so we can see what you are doing. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] i need informarion regarding functions and arrays...
On 8/2/06, Penchalaiah P. <[EMAIL PROTECTED]> wrote: Hi good evening to all…….. Rcid value 1 yes 2 no 3 yes 4 yes 5 no 6 yes 7 no 8 yes 9 no 10 yes 11 no 12 no this is the front end screen they will enter all these 12 in values… then they will press save button… these values have to be store in rnt table… can u tell me how to store all these 12 values at a time in rnt table… but I written one function for this but it is accepting only one value at a time to insert… this is the function… create or replace function rentfunc(in_rcid int4,in_value varchar) as ' begin insert into rnt(racid,auditorvalue) values ($1,$2);end; 'language'plpgsql'; If any one knows how to store all these 12 values in a rnt table… let me know …..Why not just do a loop and run 12 separate inserts (or 12 separate calls to the function)? == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] Joining a result set from four (4) tables
Hi Aaron, The query builder to generate joins that you mentioned is exactly what I am going to do doing this time. That is one of the reasons I have the generic field names for the primary and foreign key fields. Then I only have to pass the search criteria the table names and which direction I want to do the joins. Thanks again for your help. Regards John T Aaron Bono wrote: We all find ourselves in different situations and because of that, what works for one person, doesn't work for another - so I understand. Good luck with the application and future queries. Maybe you can use the existing structure of your application to help create a query builder so you can have it write a lot of your joins for you. That should help avoid a lot of simply typos or mix-ups when hand writing your queries. I am glad the queries worked for you. -Aaron ---(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] Problem while using lo_import and lo_export
On 8/2/06, Aaron Bono <[EMAIL PROTECTED]> wrote:
On 8/2/06, Penchalaiah P. <[EMAIL PROTECTED]> wrote:
> Hi,
>
>
>
> I have PostgresSQL database connection from server. Server ip is
172.16.5.179
>
>
>
> Now I want use lo_import and lo_export function for storing images into
the database.
>
>
>
> This images is located in my system itself.
>
>
>
> When I am going using the following query…
>
>
>
> Insert into image
values('chanukya',lo_import('D:/Vivek/Personal/PICS/IN/chanukya.jpg'))
>
>
>
>
> ….it rises error 'no such file or directory'
Looks like you are running on a Windows machine. Did you try:
Insert into image
values('chanukya',lo_import('D:\Vivek\Personal\PICS\IN\chanukya.jpg'));
Windows uses \, not /. I am not sure if PostgreSQL will translate for you
like Java does.
I believe PostgreSQL accepts slashes as a separator on Win32 (I used
it that way with COPY), but if you want to use backslashes, you have
to escape them:
INSERT INTO IMAGE
VALUES ('chanukya', lo_import('D:\\Vivek\\Personal\\PICS\\IN\\chanukya.jpg'));
Also, I'm not sure if Penchalaiah means that "Server" = "my system
itself", double check on that.
Regards,
Rodrigo
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem while using lo_import and lo_export
On 8/2/06, Rodrigo De León <[EMAIL PROTECTED]> wrote:
On 8/2/06, Aaron Bono <[EMAIL PROTECTED]> wrote:> On 8/2/06, Penchalaiah P. <[EMAIL PROTECTED]> wrote:
> > Hi,> >> > I have PostgresSQL database connection from server. Server ip is> 172.16.5.179> >> > Now I want use lo_import and lo_export function for storing images into
> the database.> >> > This images is located in my system itself.> >> > When I am going using the following query…> >> > Insert into image> values('chanukya',lo_import('D:/Vivek/Personal/PICS/IN/chanukya.jpg'))
> >> > ….it rises error 'no such file or directory'>> Looks like you are running on a Windows machine. Did you try:>> Insert into image> values('chanukya',lo_import('D:\Vivek\Personal\PICS\IN\chanukya.jpg'));
>> Windows uses \, not /. I am not sure if PostgreSQL will translate for you> like Java does.I believe PostgreSQL accepts slashes as a separator on Win32 (I usedit that way with COPY), but if you want to use backslashes, you have
to escape them:INSERT INTO IMAGEVALUES ('chanukya', lo_import('D:\\Vivek\\Personal\\PICS\\IN\\chanukya.jpg'));Also, I'm not sure if Penchalaiah means that "Server" = "my systemitself", double check on that.
Good point. I presume that PostgreSQL will look at the path relative to the server, not the client. I personally keep my files in CVS and just put references to the files in PostgreSQL. Of course, I had to write the code that automatically checks the files into CVS but it works quite well.
== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
