[SQL] pg_restore
Hi all, I have two databases db1 and db2 with no network connection. I want to take backup of few master tables from db1 and restore it to db2 at frequent intervals.. I do not want to delete data from the tables and restores the fresh data as this data is getting referenced in other tables. I can do that by creating scripts on db1 and executing the same on db2. But I want to know if I can use pg_restore command to overwrite existing data of these master tables Thanks, Jyoti
[SQL] backup
Hi all, Can we take backup of specific data of a table (using where clause)? Thanks, Jyoti
Re: [SQL] backup
I tried using /copy command. It is not giving any error but also not creating any file. Thanks Jyoti -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Jasen Betts Sent: Tuesday, June 09, 2009 6:03 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] backup On 2009-06-09, Jyoti Seth wrote: > This is a multipart message in MIME format. > > --=_NextPart_000_003C_01C9E916.43A8D460 > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: 7bit > > Hi all, > > > > Can we take backup of specific data of a table (using where clause)? in psql: /copy (select * from table WHERE condition ) to 'FILENAME' requires postgres version >= 8.2 IIRC. you can use any query. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] backup
Thanks it worked when I changed the command '/copy' to '\copy' -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Jasen Betts Sent: Tuesday, June 09, 2009 6:03 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] backup On 2009-06-09, Jyoti Seth wrote: > This is a multipart message in MIME format. > > --=_NextPart_000_003C_01C9E916.43A8D460 > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: 7bit > > Hi all, > > > > Can we take backup of specific data of a table (using where clause)? in psql: /copy (select * from table WHERE condition ) to 'FILENAME' requires postgres version >= 8.2 IIRC. you can use any query. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] dynamic columns in a query
Hi All, Is there any way in postgres to write a query to display the result in matrix form. (where column names are dynamic) For eg. Employee Name Client1 Client2 Client3 Client4 Emp1100 102 90 23 Emp256 0 23 98 Emp334 45 76 0 Here Client1, Client2... are the values from the database. Thanks, Jyoti -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] SQL File in encrypted form
Hi All, I want to send the sql script file to a client but wants to hide the details in it. Can I send the sql script file in encrypted form which they can execute but can't view the details. Thanks, Jyoti
[SQL] Disable Constraints in Postgresql 8.4
I want to execute a sql file for that I need to disable constraints. In postgres version 8.3, I was executing the script by doing the following: 1. Disabling triggers by updating “reltriggers” field of pg_class to 0 . 2. Executing sql file 3. Then updating “reltriggers” field of pg_class by “select count(*) from pg_trigger where pg_trigger.tgrelid=pg_class.oid” In postgres version 8.4, “pg_class” table does not have “reltriggers” field. How can I disable and enable constraints in Postgres Version 8.4? -- View this message in context: http://www.nabble.com/Disable-Constraints-in-Postgresql-8.4-tp25659252p25659252.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Pg_Restore with --clean option
I want to restore data of a single table. Before restoring the data I disabled all the triggers and constraints on that table. I used the restore command with --clean option so that data gets deleted from that table and then fresh data get inserted. But this is throwing error: duplicate key value violates unique constraint. How can I restore the data to a table that already has data? Thanks, -- View this message in context: http://www.nabble.com/Pg_Restore-with---clean-option-tp25693180p25693180.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] setof or array as input parameter to postgresql 8.2 functions
Hi, I have to pass a set of values and arrays in postgresql 8.2 functions. But I am not getting any help on that. Please let me know if any one has idea. Thanks, Jyoti
Re: [SQL] setof or array as input parameter to postgresql 8.2 functions
Thanks for the solution. With this I am able to pass arrays and multidimensional arrays in postgresql functions. One of my problem is still left I want to pass set of values with different datatypes.For eg: I want to pass following values to the function: 1 ajay 1000.12 2 rita 2300.24 3 leena 1230.78 4 jaya 3432.45 As the values have different data types I have to create three different arrays. Is there any way with which I can pass this as a single setof values. Thanks, Jyoti -Original Message- From: Pavel Stehule [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 13, 2007 6:25 PM To: Jyoti Seth Cc: [EMAIL PROTECTED]; pgsql-sql@postgresql.org Subject: Re: [SQL] setof or array as input parameter to postgresql 8.2 functions Hello maybe: create function foo(varchar[][]) returns void as $$ begin end$$ language plpgsql; postgres=# select foo(array[array[1,2], array[2,2]]::varchar[][]); foo - (1 row) Regards Pavel Stehule 2007/6/13, Jyoti Seth <[EMAIL PROTECTED]>: > > > > > Hi, > > > > I have to pass a set of values and arrays in postgresql 8.2 functions. But I > am not getting any help on that. Please let me know if any one has idea. > > > > Thanks, > > Jyoti ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] setof or array as input parameter to postgresql 8.2 functions
Thanks a lot. Regards, Jyoti -Original Message- From: Pavel Stehule [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 11:27 AM To: Jyoti Seth Cc: [EMAIL PROTECTED]; pgsql-sql@postgresql.org Subject: Re: [SQL] setof or array as input parameter to postgresql 8.2 functions 2007/6/14, Jyoti Seth <[EMAIL PROTECTED]>: > Thanks for the solution. With this I am able to pass arrays and > multidimensional arrays in postgresql functions. > > One of my problem is still left I want to pass set of values with different > datatypes.For eg: > I want to pass following values to the function: > 1 ajay 1000.12 > 2 rita 2300.24 > 3 leena 1230.78 > 4 jaya 3432.45 > > As the values have different data types I have to create three different > arrays. Is there any way with which I can pass this as a single setof > values. > > You have to wait for 8.3 where arrays on composite types are supported. Currently in one our application we use array of arrays where different types are too, and we cast it to text. Regards Pavel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] calling webservice through postgresql function
Hi all, I want to call webservice from my postgresql database. Please let me know if anyone has idea. Thanks, Jyoti Seth
Re: [SQL] calling webservice through postgresql function
Thanks a lot for ur quick response. I have searched a lot on net for code to call a webservice through tcl/tk or any other scripting lang that is supported by postgresql, but couldn't find one. If u can provide me some code to call a web service through postgresql it would be a great help. Thanks, Jyoti Setj -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Friday, July 13, 2007 12:57 PM To: Jyoti Seth Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] calling webservice through postgresql function Jyoti Seth wrote: > Hi all, > > I want to call webservice from my postgresql database. Please let me know if > anyone has idea. You can use any of the "untrusted" procedural languages to access resources outside the database (e.g. pl/perlu), or of course C. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [OBORONA-SPAM] [SQL] calling webservice through postgresql function
Hello Dmity, As suggested by Richard Huxton in this forum we can use any of the "untrusted" procedural languages to access resources outside the database (e.g. pl/perlu). You can get the code to call the webservice from the postgresql function from this url. http://www.pgsql.cz/index.php/PL/Perlu_-_Untrusted_Perl_(en) Thanks, Jyoti -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dmitry Turin Sent: Monday, July 16, 2007 6:49 PM To: pgsql-sql@postgresql.org Subject: Re: [OBORONA-SPAM] [SQL] calling webservice through postgresql function Good day, Jyoti. JS> I want to call webservice from my postgresql database. Please let me know if anyone has idea. Idia or proper tool? If idea, I offer to append some code into source of pg. I already raised this question in topic "We all are looped on Internet", in which i read, that the most widespread transport protocol is HTTP, and the most general format for data is XML (XHTML). Idea is so: if we made create table a ( id num primary key, data float ); create table b ( id num primary key, ref num references a(id), data float ); create table c ( id num primary key, link num references b(id), data float ); insert into a values (1, 12.3); insert into b values (10, 1, 23.4); insert into b values (20, 1, 34.5); insert into b values (30, 1, 45.6); insert into c values (100,10,56.7); insert into c values (101,10,67.8); insert into c values (200,20,78.9); insert into c values (201,20,89.1); insert into c values (300,30,91.2); we need simple request 'a.b.c' to get Request 'a.b.c' allow to avoid php,etc, that is very necessary for unskilled users! Details are on: http://sql40.chat.ru/site/sql40/en/author/introduction_eng.htm http://sql40.chat.ru/site/sql40/en/author/determination_eng.htm http://sql40.chat.ru/site/sql40/en/author/inout_eng.htm Dmitry Turin HTML6 (6.1.2) http://html60.chat.ru SQL4 (4.1.2) http://sql40.chat.ru Unicode2 (2.0.0) http://unicode2.chat.ru Computer2 (2.0.3) http://computer20.chat.ru ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Database Synchronization
Hello , I have two postgres databases on different linux servers. Postgres database on one server has some tables that needs to be synchronized from the other postgres database . What should be the best method for this. Please let me know if someone has idea. Thanks, Jyoti Seth
Re: [SQL] Database Synchronization
Thanks a lot for your suggestions. I have ony single master. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Huxton Sent: Wednesday, July 18, 2007 9:13 PM To: Chris Browne Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Database Synchronization Chris Browne wrote: > [EMAIL PROTECTED] (Richard Huxton) writes: >> >> http://www.slony.info/ > But it is worth noting one thing about the synchronization... > > Slony-I's strategy is pretty simple: One node is considered the > "master," and the other node is forcibly made to conform to what is on > the master. > > If you want to synchronize back and forth (e.g. - multimaster > replication of some sort), Slony-I is not suitable... Except in the case when you can have separate tables for the data. If you have a london_sales table only updated in London and a paris_sales table only updated in Paris then you have something inching towards multi-master. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Database Synchronization
Hello, When I am installing slony-I from rpm's available at the following url:http://main.slony.info/downloads/1.2/rpm/ it is giving an error: There are no installable providers of postgresql-slony1-engine. In our system postgresql has been installed through YAST. So when we try to install and configure slony-I through source, it gives the message please make sure tp build and install postgresql from the sources first. I am new to both linux and postgres. Please help me in installing and configuring slony-I on my suse linux m/c. Thanks, Jyoti Seth -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chris Browne Sent: Wednesday, July 18, 2007 8:28 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Database Synchronization [EMAIL PROTECTED] (Richard Huxton) writes: > Jyoti Seth wrote: >> Hello , >> I have two postgres databases on different linux servers. Postgres >> database >> on one server has some tables that needs to be synchronized from the other >> postgres database . What should be the best method for this. > > The slony replication package lets you choose what tables you > replicate. On the destination server the tables will be read-only. > > http://www.slony.info/ > > Take a little time to read through the documentation and experiment > with a test system first. It works fine, but it's got a lot of > complicated options. But it is worth noting one thing about the synchronization... Slony-I's strategy is pretty simple: One node is considered the "master," and the other node is forcibly made to conform to what is on the master. If you want to synchronize back and forth (e.g. - multimaster replication of some sort), Slony-I is not suitable... -- "cbbrowne","@","linuxdatabases.info" http://www3.sympatico.ca/cbbrowne/unix.html Culus thinks we should go to trade shows and see how many people we can kill by throwing debian cds at them ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Database Synchronization
Hello, Thanks Andrew for the solution. I have successfully installed following rpm << postgresql-slony1-engine-1.1.5-1_PG8.1.4.i686.rpm >> available at http://main.slony.info/downloads/1.1/rpm/. But when I am trying to set a cluster its throwing a following error: :4: PGRES_FATAL_ERROR load '$libdir/xxid'; - ERROR: could not access file "$libdir/xxid": No such file or directory :4: Error: the extension for the xxid data type cannot be loaded in database 'dbname=contactdb host=localhost user=postgres' :4: ERROR: no admin conninfo for node 134590736 I have also tried changing the value of libdir to the directory where xxid.so file is there but couldn't resolve the problem. Thanks, Jyoti Seth -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan Sent: Monday, July 23, 2007 11:43 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Database Synchronization On Mon, Jul 23, 2007 at 02:55:21PM +0530, Jyoti Seth wrote: > In our system postgresql has been installed through YAST. So when we try to > install and configure slony-I through source, it gives the message please > make sure tp build and install postgresql from the sources first. > You _might_ be able to get this to work by installing whatever extra bits the YAST installation tool offers (probably something with -dev- or -src- in it). A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Database synchronization
Hi, I have two postgres databases "Db1" and "Db2" on different linux servers. "DB1" is my master server and its data updates very frequently. My application access DB2 database, which in turn requires data from Db1 database. The same can be achieved in either of the following ways: 1. I can have the same tables from Db1 in the Db2 database and use some replication process to update these tables say using Slony-I. And then our application accesses the data from the single database. 2. Or I can use dblink to fetch the data from the master database only. My problem is my master database changes frequently and I always require the latest information. Please suggest which is the right approach in this situation. Thanks, Jyoti Seth ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Database synchronization
My master database contains prices of some products that vary after few hours and many other details that are also reqd in Db2 which doesn't change that frequently. My slave database contains the details of queries sent by the customers. These queries are to find the price of the product and other details. (Some part of the info reqd from Db1 varies frequently where some information is a kind of master data that doesn't vary frequently). So should I use all the ways i.e #2 or #3 to get price info and #1 (slony) for other details or there is some other better option Thanks, Jyoti -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 25, 2007 3:54 PM To: Jyoti Seth Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Database synchronization Try not to start a new thread (question) by replying to an old one - it can mean some people don't see your question. I will take care in future. Jyoti Seth wrote: > Hi, > > I have two postgres databases "Db1" and "Db2" on different linux servers. > "DB1" is my master server and its data updates very frequently. > > My application access DB2 database, which in turn requires data from Db1 > database. OK > The same can be achieved in either of the following ways: > 1. I can have the same tables from Db1 in the Db2 database and use some > replication process to update these tables say using Slony-I. And then our > application accesses the data from the single database. Yes > 2. Or I can use dblink to fetch the data from the master database only. Yes also: 3. Write a layer in your application / between the application & database which routes queries to the correct database. > My problem is my master database changes frequently and I always require the > latest information. Well, if by "latest" you mean you can't have any delay at all you'll have to use #2 or #3. However, that could be slow if you need to join a lot of data from DB1 to DB2. Can you provide more details of what each contains? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Database synchronization
Thanks a lot for your suggestion. It will help me in taking the decision. -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 25, 2007 6:10 PM To: Jyoti Seth Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Database synchronization Jyoti Seth wrote: > My master database contains prices of some products that vary after few > hours and many other details that are also reqd in Db2 which doesn't change > that frequently. My slave database contains the details of queries sent by > the customers. > These queries are to find the price of the product and other details. (Some > part of the info reqd from Db1 varies frequently where some information is a > kind of master data that doesn't vary frequently). > > So should I use all the ways i.e #2 or #3 to get price info and #1 (slony) > for other details or there is some other better option If you can live with a time-lag measured in seconds, then slony will work just fine. If you can't then you'll want dblink. One other thing you might consider - if you attach "valid_from" time to each price then you can put them in place in advance, and make sure they are replicated before needed. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Database synchronization
Hello, I have one query related to slony-I. Can I replicate table data in different schemas. Say if have table 'country' in database 'stock' and schema 'stocksch'. And I want to replicate the data in table 'country' of database 'smsdb' and schema 'smssch'. Here, the country table is present in two different schemas. Can I replicate the country table data from schema stocksch to schema smssch or I have to create a schema with the same name for replication. Thanks, Jyoti Seth On 7/26/07, Jyoti Seth <[EMAIL PROTECTED]> wrote: > > Thanks a lot for your suggestion. It will help me in taking the decision. > > -Original Message- > From: Richard Huxton [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 25, 2007 6:10 PM > To: Jyoti Seth > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Database synchronization > > Jyoti Seth wrote: > > My master database contains prices of some products that vary after few > > hours and many other details that are also reqd in Db2 which doesn't > change > > that frequently. My slave database contains the details of queries sent > by > > the customers. > > These queries are to find the price of the product and other details. > (Some > > part of the info reqd from Db1 varies frequently where some information > is > a > > kind of master data that doesn't vary frequently). > > > > So should I use all the ways i.e #2 or #3 to get price info and #1 > (slony) > > for other details or there is some other better option > > If you can live with a time-lag measured in seconds, then slony will > work just fine. If you can't then you'll want dblink. > > One other thing you might consider - if you attach "valid_from" time to > each price then you can put them in place in advance, and make sure they > are replicated before needed. > > -- > Richard Huxton > Archonet Ltd > >
[SQL] Does postgresql8.2 supports multithreading?
Hi , Does postgresql8.2 supports multithreading? Thanks, Jyoti Seth
[SQL] Multiple postgresql functions in a single transaction
Hi, I have two postgresql functions. One function is calling another function for certain value. I want that these two functions work under single transaction so that even if the value gets generated in the second function and the first function that calls the second function fails. Then the value generated in the second function should also roll back. Please let me know if we can execute two functions of postgresql in a single transaction. Thanks, Jyoti Seth ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Multiple postgresql functions in a single transaction
Thanks. I am not using sequences. I tried an example and it worked. From: Robins Tharakan [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 06, 2008 12:54 PM To: Jyoti Seth Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Multiple postgresql functions in a single transaction It can be done, but it depends on how you are generating the value in the first function. If you sequences though you may have to take care of reverting it yourself. Robins -- Forwarded message -- From: Jyoti Seth <[EMAIL PROTECTED]> Date: Feb 6, 2008 11:51 AM Subject: [SQL] Multiple postgresql functions in a single transaction To: pgsql-sql@postgresql.org Hi, I have two postgresql functions. One function is calling another function for certain value. I want that these two functions work under single transaction so that even if the value gets generated in the second function and the first function that calls the second function fails. Then the value generated in the second function should also roll back. Please let me know if we can execute two functions of postgresql in a single transaction. Thanks, Jyoti Seth ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Packages
Hi, Is there any concept of Package in postgresql to group functions ? Thanks, Jyoti Seth
[SQL] postgresql function not accepting null values in select statement
Hi, If I pass null value as the parameter of postgresql function, which is used in the where clause of select statement is not functioning properly. I have also changed the value of transform_null_equals = on in the conf file and restarted postgresql. But it is still giving error. Is there any possible solution. Thanks, Jyoti
Re: [SQL] postgresql function not accepting null values in select statement
Hi, I have a the following procedure CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) RETURNS SETOF t_functionaries AS $BODY$ DECLARE rec t_functionaries%ROWTYPE; begin FOR rec IN SELECT f.functionaryid, f.category,f.description FROM functionaries f where f.statecd=p_statecd LOOP return next rec; END LOOP; return; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; In the functionaries table statecd is a null field. When I pass some integer value to the above procedure it works correctly but if I pass null value in p_statecd it doesn't show anything whereas it has values and if I write the select statement separately it gives values Thanks, Jyoti -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Friday, February 22, 2008 2:35 PM To: Jyoti Seth Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] postgresql function not accepting null values in select statement Jyoti Seth wrote: > > If I pass null value as the parameter of postgresql function, which is used > in the where clause of select statement is not functioning properly. Either: 1. You're talking about frooble(), in which case it's supposed to do that. or 2. You'll need to tell us what function it is, how you're using it and what you think should happen. My guess is that you're getting a null as the result and that's not doing what you'd expect in your where clause. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] postgresql function not accepting null values inselect statement
I have tried this, but it is showing following error: ERROR: syntax error at or near "DISTINCT" SQL state: 42601 Thanks, Jyoti -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of johnf Sent: Friday, February 22, 2008 10:01 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] postgresql function not accepting null values inselect statement On Friday 22 February 2008 01:35:47 am Bart Degryse wrote: > Can you try this... > > CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) > RETURNS SETOF t_functionaries AS > $BODY$ > DECLARE > rec t_functionaries%ROWTYPE; > BEGIN > FOR rec IN ( > SELECT f.functionaryid, f.category, f.description > FROM functionaries f > WHERE f.statecd IS NOT DISTINCT FROM p_statecd) > LOOP > return next rec; > END LOOP; > return; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > a newbie question. Could you explain why yours works? I don't understand how it works if p_statecd = NULL -- John Fabiani ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] unique constraint on views
Hi All, Is it possible to add unique constraint on updateable views in postgres? Thanks, Jyoti Seth -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] unique constraint on views
I want to put unique constraint on columns of more than one table. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of A. Kretschmer Sent: Wednesday, December 10, 2008 6:04 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] unique constraint on views In response to Jyoti Seth : > Hi All, > > Is it possible to add unique constraint on updateable views in postgres? Add the constraint to the base-table. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] backup and restore
Hello, I have two databases db1 and db2 with the same database structure on different systems with no network connection. In the first system with the db1 database user updates the master data. At the end of every day, the user needs to take the backup of updated data of master tables on db1 and update the data on another system with db2 database. We can't use WAL as in this as we want to take incremental backup of few tables only and can't use slony as there is no network connection between the systems. Please suggest some solution. Thanks, Jyoti -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Creation of file from postgresql function
Hi, I want to create a file on a disk through postgres function. I am trying to use large objects functions (lo_create, lo_open and lo_write) for this. In this, lo_open command is throwing error "invalid mode" or "Syntax error whereas I have given the value of INV_WRITE from libpq-fs.h as mode. libpq-fs.h File #define INV_WRITE 0x0002 #define INV_READ0x0004 If anyone has idea about this problem or know any other alternative to create a file from postgres function, please let me know. Thanks, Jyoti -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
