[GENERAL] Using AND in query
Hello every one, I have encountered a problem while working .I have a sample table with the following data TID Date Item T100 8/1/2010 Laptop T100 8/1/2010 Desktop T101 8/1/2010 Laptop T102 8/1/2010 Desktop T103 8/2/2010 Laptop T103 8/2/2010 Desktop T104 8/2/2010 Laptop need the data when a person bought laptop desktop on the sameday.I used a condition in where clause but its not working,it is returning no rows.Can any one please help me to resolve this issue ? condition in where clause : table.date in date() to date() and table.item = laptop and table.item = Desktop
[GENERAL] Query to find list of dates between two dates
Hello guys, can you please help me with the following query I need a query that displays all the dates in between two dates say i give two dates 12/1/2009 and 12/31/2009 The result should be like this 12/1/2009 12/2/2009 12/3/2009 . . . . . 12/31/2009 Note : Assume that there is no table Thanks, Aravind.
[GENERAL] Error while including PQXX library
Hello, I wrote a small program in cpp which uses pqxx API.The file that i included are #include pqxx/connection #include pqxx/transaction After compiling i got the following error : prog29.cpp:5:21: pqxx/connection: No such file or directory prog29.cpp:5:21: pqxx/transaction: No such file or directory I checked the include directory and all these files were there,but i don't know why i got these errors.Can you guys please help me to solve this problem. Thanks, Aravind.
[GENERAL] Data in a table to a csv file
Hello, I have a small problem,I need to export data from a table into a csv file.For this i am using pqxx library to work on it.I can able to retrieve the data from the database but the only problem is I am unable to store it in a csv file.Can you guys please help me how to do that. Thanks, Aravind.
[GENERAL] Problem with pgpool-II tool
Hello, I have encountered a problem while configuring pgpool-II,I encountered a problem while executing the following command. pgpool -n [1] 2796 [achand...@localhost ~]$ pool_config: could not open configuration file (pgpool.conf) pool_config: using default values... pid file found but it seems bogus. Trying to start pgpool anyway... 2009-04-19 12:39:24 ERROR: pid 2796: could not open pid file as /var/run/pgpool/pgpool.pid. reason: Permission denied Following were the default paths in pgpool.conf file # Logging directory logdir = '/tmp' # pid file name pid_file_name = '/var/run/pgpool/pgpool.pid' since there is no pgpool/pgpool.pid file in run folder i created pgpool directory and pgpool.pid file in that directory and gave a value 2678 in pgpool.pid file then i got the above message.Can you guys please help me in this? Thanks, Aravind.
[GENERAL] In memory Database for postgres
Hello, Thanks for your reply,but what I am actually looking for is database should be an in-memory database and at the same i want to store that data into disk so that data won't be lost when the system restarts or in case of power failure. Can you guys tell me the procedure how to do this? your help will he greatly appreciated. Thanks, Avin. From: Emanuel Calvo Franco postgres@gmail.com To: John R Pierce pie...@hogranch.com Cc: postgresql Forums pgsql-general@postgresql.org Sent: Monday, April 13, 2009 11:54:45 AM Subject: Re: [GENERAL] In memory Database for postgres 2009/4/12 John R Pierce pie...@hogranch.com: aravind chandu wrote: I created in-memory database but the problem is all the data will be accessed from main memory .when ever the system is restarted the entire data that is in the tables will lost.Is there any way to dump all the data in to local hard disk before restarting the system or any similar method to save the data in to a permanent storage. memory is volatile, disk is persistent. if you want persistent databases, I recommend storing them on disk. ubuntu=# create table test_ram (i integer, name text) tablespace ram_space; CREATE TABLE ubuntu=# create temp table test_ram_temp (i integer, name text) tablespace ram_space; CREATE TABLE ubuntu=# create temp table test_disk_temp (i integer, name text); CREATE TABLE ubuntu=# create table test_disk (i integer, name text); CREATE TABLE ubuntu=# explain analyze insert into test_ram values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.019..9354.014 rows=100 loops=1) Total runtime: 22836.532 ms (2 rows) ubuntu=# explain analyze insert into test_ram_temp values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..7507.349 rows=100 loops=1) Total runtime: 12773.371 ms (2 rows) ubuntu=# explain analyze insert into test_disk values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..7948.205 rows=100 loops=1) Total runtime: 16902.042 ms (2 rows) ubuntu=# explain analyze insert into test_disk_temp values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.018..8135.287 rows=100 loops=1) Total runtime: 13716.049 ms (2 rows) So, let's see in a brief: standard table on ram: 22836.532 standard table on disk: 16902.042 temp table on ram: 12773.371 temp table on disk: 13716.049 -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) 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] In memory Database for postgres
Hello, I created in-memory database but the problem is all the data will be accessed from main memory .when ever the system is restarted the entire data that is in the tables will lost.Is there any way to dump all the data in to local hard disk before restarting the system or any similar method to save the data in to a permanent storage. Thanks, Avin. From: Blazej bl.oleszkiew...@gmail.com To: avin_frie...@yahoo.com Cc: postgresql Forums pgsql-general@postgresql.org Sent: Monday, November 17, 2008 4:26:46 PM Subject: Re: [GENERAL] In memory Database for postgres Sorry I forgot about create tablespace script - this is the SQL script: CREATE TABLESPACE ram_space LOCATION '/mnt/ram0/pgspace'; And then: CREATE TABLE (...) TABLESPACE ram_space; and table is in memory. Regards, Blazej 2008/11/17 Blazej bl.oleszkiew...@gmail.com: In my opinion very nice solution is building part of PostgreSQL database in memory - below it is instruction how to build PostgreSQL schema in memory in Linux. I tested this with my ROLAP solution for recalculation MOLAP cubes in memory and then join with master cube (this speeds up proces about 10 times!!! - but in other solution may be different). In grub (or other bootloader) you must declare ramdisk and then in OS: mke2fs /dev/ram0 mkdir /mnt/ram0 mount /dev/ram0 /mnt/ram0 mkdir /mnt/ram0/pgspace chown postgres:postgres /mnt/ram0/pgsapce The /mnt/ram0/pgsapce folder must be empty (ERROR: directory /mnt/ram0 is not empty) And then you may create tables (in this schema of course) and write to them. Of course you must delete schema before shutdown PostgreSQL and OS - I dont't now how resolve problem with error when the schema was not deleted? - I have no time to think about it maybe anybody know how to restore db when the in memory schema was damaged? Regards, Blazej 2008/11/17 aravind chandu avin_frie...@yahoo.com: Hello, I guess most of you guys heard about In Memory Database.I have a small question regarding it.I need to create an In Memory Database for postgresql through which I have to perform various operations on postgresql database(queries,procedures,programs using pqxx API etc...).I didn't have any idea of how to start and where to start this issue.Please comment on this issue,so that it will be really helpful to me . Thanks, Avin.
[GENERAL] Parallel DB architechture
Hello, I would like to implement parallel DB Architecture in postgres.I came across various parallel architectures shared memory,shared disk,shared nothing,hierarchical.But I am unaware of how to implement one of these in real time system.Can you guys help me in this aspect?
[GENERAL] Parallel Query Processing
Thanks for your reply guys.My aim is to run multiple queries.Will joins come under multiple queries? or it just mean two or more different queries like one select statement and another select statement? Does postgresql itself supports to run multiple queries or we have to use pgpool-II tool? From: Scott Marlowe scott.marl...@gmail.com To: aravind chandu avin_frie...@yahoo.com Cc: postgresql Forums pgsql-general@postgresql.org Sent: Wednesday, March 25, 2009 4:52:43 PM Subject: Re: [GENERAL] Parallel Query Processing On Wed, Mar 25, 2009 at 2:24 PM, aravind chandu avin_frie...@yahoo.com wrote: Hello, I have a few questions related to the parallel query processing.Can you guys tell me how to implement parallel query processing in postgresql database. Do you mean one query being parallelized, or multiple queries running at once? PostgreSQL provides no capability to parallelize one query into multiple processes. Multiple Queries run in parallel automatically Hello,
[GENERAL] Parallel Query Processing
Hello, I have a few questions related to the parallel query processing.Can you guys tell me how to implement parallel query processing in postgresql database. Thanks, Avin.
Re: [GENERAL] Syntax error with select statement
Hello, Thanks for your help,but i still have some problem.While executing it i encountered an error something like this: terminate called after throwing an instance of 'pqxx::syntax_error' what(): ERROR: unterminated dollar-quoted string at or near $Password$ M^fuo|`sjyo|`so|-?z LINE 1: ... = $Username$ achandana $Username$ and password = $Password$... ^ I just give the query as it is in your previous mail.But still i got some problem can you please tell me. Thank You, Aravind. --- On Thu, 12/18/08, Joshua J. Kugler jos...@eeinternet.com wrote: From: Joshua J. Kugler jos...@eeinternet.com Subject: Re: [GENERAL] Syntax error with select statement To: pgsql-general@postgresql.org Date: Thursday, December 18, 2008, 1:51 AM On Wednesday 17 December 2008, justin said something like: aravind chandu wrote: Hello, I have problem with select statement in c++ program I am using pqxx library to connect to postgresql database.My query is result R(T.exec( select * from dbtable where username = ' +user+ ' and password = ' +st+ ' )); here st is in encrypted format and the string is st = M^fuo|`sjyo|`so|-?z this is the string i stored in the table . The error I was encountered is terminate called after throwing an instance of 'pqxx::syntax_error' what(): ERROR: unterminated quoted string at or near 'M^fuo|`sjyo|`so|-?z LINE 1: ...table where username = 'achandana' and password = 'M^fuo|`sj... ^ I am not able to identify what the actual problem is can you guys please help to solve this problem?Your help is greatly appreciated. Thank You, Aravind Well its telling you in the error the quotes are flaky. It apears that the password portion contains another sing quote. I would move to double dollar quoting when dealing with strings that contain special characters example R(T.exec( select * from dbtable where username = $UserName$ + user + $Username$ and password = $Password$ + st + $Password$ )); see http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html on dollar quoting Better yet, use placeholders and let the driver do the quoting. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Syntax error with select statement
Hello, I have problem with select statement in c++ program I am using pqxx library to connect to postgresql database.My query is result R(T.exec( select * from dbtable where username = ' +user+ ' and password = ' +st+ ' )); here st is in encrypted format and the string is st = M^fuo|`sjyo|`so|-?z this is the string i stored in the table . The error I was encountered is terminate called after throwing an instance of 'pqxx::syntax_error' what(): ERROR: unterminated quoted string at or near 'M^fuo|`sjyo|`so|-?z LINE 1: ...table where username = 'achandana' and password = 'M^fuo|`sj... ^ I am not able to identify what the actual problem is can you guys please help to solve this problem?Your help is greatly appreciated. Thank You, Aravind. ^
[GENERAL] Problem Related to storing the field value in a String
Hello Guys,
The following is my sample program
result R(T.exec(select * from emp));
string L;
stringstream S;
vectorstring v;
int z,i;
z = R.size();
for (i = 0;iz ;i++){
// Write field's string into S
S R[i][0];
// Parse contents of S into L
S L;
v.push_back(L);
}
There is a table emp with single column,while I am working on this module I
encountered a problem,I can able to get the first
row of the table only,I am not able to get the other row values.
Lets say column values are abc,def,ghi.jkl etc There is no problem with
R[i][0] its getting the correct values,
but the problem is while reading field values into 'S' I am able to get only
the table's field value of first row only and the rest as zero's.
I don't know what's the problem.Can you guys please help me to solve this
problem.
If there is another way to store field value into a string please let me know.
Thanks,
Avin.
[GENERAL] In memory Database for postgres
Hello, I guess most of you guys heard about In Memory Database.I have a small question regarding it.I need to create an In Memory Database for postgresql through which I have to perform various operations on postgresql database(queries,procedures,programs using pqxx API etc...).I didn't have any idea of how to start and where to start this issue.Please comment on this issue,so that it will be really helpful to me . Thanks, Avin.
[GENERAL] Need Help for a query
Hello, I am using this pqxx library for postgresql to run programs.The following is the query which i gave to store the data,here data.speed,data.heading are float values and data.ttime is timestamp .If i try to run this statement I end up with an error below this query.Please help me to solve this problem. result R1(T1.exec(insert into saveodtdata values( +data.speed+ , +data.heading+ , ' +data.ttime+ ' ) )); invalid operands of types `const char[33]' and `float' to binary `operator+'make: *** [main2.o] Error 1 Thank You, Avinash.
[GENERAL] Username and Password authentication using pqxx library.
Hello,
I am using pqxx API for postgresql.The following is the part of my
code.The problem is in the query I am selecting the number of rows from the
table since there is only one user name and password row it should return 1 and
if there is no match then it should return 0.But here Even though I give wrong
username or password its getting authenticated.please help me,what should I put
in the if () statement inorder to solve this issue .
result R(T.exec(select count(*) from dbtable where username=+username+ and
password=+password+));
if (R.empty())
{
throw logic_error(No tables found);
*shm = '5';
}
else
{
coutAuthenticated;
*shm='0';
}
Thank You,
Avinash.
[GENERAL] size of a table on postgresql
Hello, The following is the procedure to calculate the disk space occupied by postgresql from a flat file. In this I didn't understood some terms 24 bytes: each row header (approximate) 24 bytes: one int field and one text field + 4 bytes: pointer on page to tuple here row header is taken as 24 bytes and in some sites it is given row header as 40 bytes and in some 32 bytes http://www.sbras.ru/rus/docs/db/postgres/doc/howto/faq-english.shtml#3.6 http://www.softlab.ntua.gr/facilities/documentation/unix/postgres/faq-english.html Is the row header value is constant or it is variable?If so what could be the maximun value? what is that additional +4bytes as pointer on page to tuple. Should all these were fixed or is there any command to find the values for these fields? Please post your comments on it. Thank You, Avin.
[GENERAL] How to calculate number of rows per page in postgresql
Hello, The following is the procedure to calculate the disk space occupied by postgresql from a flat file. http://www.postgresql.org/docs/faqs.FAQ.html#item4.5 In this I didn't understood some terms 24 bytes: each row header (approximate) 24 bytes: one int field and one text field + 4 bytes: pointer on page to tuple what is that first 24 bytes and last 4 bytes represents? I did practically but for a table with two integer columns it takes 185 rows for a page size of 8192 for 186th it changes to 16384.But when I applied this procedure... 8 bytes: each row header 8 bytes : two int fields + 4 bytes: pointer on page to tuple total 20 bytes 8192/20 =410 rows per page (approx.) Can you please tell me how to caluculate number of rows per page size. Thank You, Avin.
[GENERAL] Disk space occupied by a table in postgresql
Hello, I installed postgresql on linux system, I create a table and inserted a large data into the table what I would like to know is how to calculate the disk space occupied by the table .Is there any procedure to find it out or simply a command .Please give me some suggestion. Thank You, Avin.
[GENERAL] should i need to install xml library in postgresql inorder to work on xml file?
Hello, I installed postgresql on my sytem,I need to parse an xml file.When I am working on xpath() it is showing an error that function is notfound and i tried almost all xml functions in postgresql but it is giving me the same result.why so ? should I have to install any xml library inorder to avoid this?Please tell me its very important for me. Thanks, Avin.
[GENERAL] errors while working on xml functions in postgresql
Hello,
I
installed postgresql on my sytem,I need to parse an xml file.When I am
working on xpath() it is showing an error that function is notfound and
i tried almost all xml functions in postgresql but it is giving me the
same result.why so ? should I have to install any xml library inorder
to avoid this?Please tell me its very important for me.
these were the errors which i got
SELECT xpath('/my:a/text()', 'my:a
xmlns:my=http://example.com;test/my:a', ARRAY[ARRAY['my',
'http://example.com']]);
ERROR: function xpath(unknown, unknown, text[]) does not exist at
character 8
HINT: No function matches the given name and argument types. You may need to
add explicit type casts.
STATEMENT: SELECT xpath('/my:a/text()', 'my:a
xmlns:my=http://example.com;test/my:a', ARRAY[ARRAY['my',
'http://example.com']]);
ERROR: function xpath(unknown, unknown, text[]) does not exist
LINE 1: SELECT xpath('/my:a/text()', 'my:a xmlns:my=http://example...
^
HINT: No function matches the given name and argument types. You may need to
add explicit type casts.
Thanks,
Avin.
[GENERAL] How to give input a file for a stored procedure
Hi, I am writing a stored procedure where the input to it is a file.I did not have any idea of how to give input as a file for a stored procedure.could you please help me. Thank You, Avin.
[GENERAL] Getting data from Xml to Postgresql database
Hi, I have some data in XML format and i need to upload in postgresql database using stored procedure. Can someone tell me the step by step procedure of the same as i will be doing it for the first time. Thank You, Avin.
[GENERAL] procedure to load xml file data in postgesql
Hello, I have to load xml file data into postgresql database table using a stored procedure,but I didn't have any idea how to start it.Can you please help me how to do this.Your suggestion is greatly appreciated.Its very important to me. Thanks in advance, Avin. ?xml version=1.0? catalog book id=bk101 authorGambardella, Matthew/author titleXML Developer's Guide/title genreComputer/genre price44.95/price publish_date2000-10-01/publish_date descriptionAn in-depth look at creating applications with XML./description /book book id=bk102 authorRalls, Kim/author titleMidnight Rain/title genreFantasy/genre price5.95/price publish_date2000-12-16/publish_date descriptionA former architect battles corporate zombies, an evil sorceress, and her own childhood to become queen of the world./description /book book id=bk103 authorCorets, Eva/author titleMaeve Ascendant/title genreFantasy/genre price5.95/price publish_date2000-11-17/publish_date descriptionAfter the collapse of a nanotechnology society in England, the young survivors lay the foundation for a new society./description /book book id=bk104 authorCorets, Eva/author titleOberon's Legacy/title genreFantasy/genre price5.95/price publish_date2001-03-10/publish_date descriptionIn post-apocalypse England, the mysterious agent known only as Oberon helps to create a new life for the inhabitants of London. Sequel to Maeve Ascendant./description /book book id=bk105 authorCorets, Eva/author titleThe Sundered Grail/title genreFantasy/genre price5.95/price publish_date2001-09-10/publish_date descriptionThe two daughters of Maeve, half-sisters, battle one another for control of England. Sequel to Oberon's Legacy./description /book book id=bk106 authorRandall, Cynthia/author titleLover Birds/title genreRomance/genre price4.95/price publish_date2000-09-02/publish_date descriptionWhen Carla meets Paul at an ornithology conference, tempers fly as feathers get ruffled./description /book book id=bk107 authorThurman, Paula/author titleSplish Splash/title genreRomance/genre price4.95/price publish_date2000-11-02/publish_date descriptionA deep sea diver finds true love twenty thousand leagues beneath the sea./description /book book id=bk108 authorKnorr, Stefan/author titleCreepy Crawlies/title genreHorror/genre price4.95/price publish_date2000-12-06/publish_date descriptionAn anthology of horror stories about roaches, centipedes, scorpions and other insects./description /book book id=bk109 authorKress, Peter/author titleParadox Lost/title genreScience Fiction/genre price6.95/price publish_date2000-11-02/publish_date descriptionAfter an inadvertant trip through a Heisenberg Uncertainty Device, James Salway discovers the problems of being quantum./description /book book id=bk110 authorO'Brien, Tim/author titleMicrosoft .NET: The Programming Bible/title genreComputer/genre price36.95/price publish_date2000-12-09/publish_date descriptionMicrosoft's .NET initiative is explored in detail in this deep programmer's reference./description /book book id=bk111 authorO'Brien, Tim/author titleMSXML3: A Comprehensive Guide/title genreComputer/genre price36.95/price publish_date2000-12-01/publish_date descriptionThe Microsoft MSXML3 parser is covered in detail, with attention to XML DOM interfaces, XSLT processing, SAX and more./description /book book id=bk112 authorGalos, Mike/author titleVisual Studio 7: A Comprehensive Guide/title genreComputer/genre price49.95/price publish_date2001-04-16/publish_date descriptionMicrosoft Visual Studio 7 is explored in depth, looking at how Visual Basic, Visual C++, C#, and ASP+ are integrated into a comprehensive development environment./description /book /catalog -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] REQUIREMENTS FOR POSTGRESQL DATABASE
Hello, I just need the information related to the requirements of postgresql database like how much size it occupies on the disk (when it is installed) irrespective of tables size and how much RAM is needed for better performance of the database say, I have 4gb ram will it use all the 4gb ram or 2gb is sufficient in terms of usage and performance issues. Thank You, Aravind.
[GENERAL] SPACE FOR POSTGRESQL DATABASE
Hello, Can you please how much space does postgresql database occupies? Thank You, Aviansh
[GENERAL] information related to blobs
Hello, Blobs stores large amount can you please tell me what is the limit i.e hw many kb of data can it store ? say 4000k like that. For suppose if a 4000kb data can be stored using blob and the data stored in blob is 3600k what about the remaining data? will it allocates all the 4000kb data or will it allocates only 3600kb data? Like char(100) it will allocates all 100 bytes in memory and varchar(100) which is variable memory?
[GENERAL] predefined animation and pre-recorded sounds in postgresql
Hello, I just need to load pre-defined animations and pre-recorded sounds in postgresql.Can you please tell how to do this.I appreciate your help. Thank You, Aravind.
[GENERAL] To store and retrive image data in postgresql
Hello, I need to store an image in postgresql database and after that i need to retrive the image back.Can you please help me how to do this? Thank You, Avinash.
[GENERAL] xml and postgresql
Hi folks, I need to load xml data in to database can you tell me they way how do I import xml data into postgresql database. lets dat this is the xml file bookstore book category=CHILDREN titleHarry Potter/title authorJ K. Rowling/author year2005/year price29.99/price /book book category=WEB titleLearning XML/title authorErik T. Ray/author year2003/year price39.95/price /book /bookstore so finally the table name should be bookstore and column names are category,title, author,year,price and all the information in the xml file should be ported to the table can you please tell me how to do this its a bit confusing to me as i am just a beginner to this. Thanks in advance, Aravind.
[GENERAL] does postgresql works on distributed systems?
Hi, nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; My question is nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Microsoft sql server 2005 cannot be shared on multiple systems i,e in a network environment when it is installed in one system it cannot be accessed one other systems.One can access only from a system where it is already installed but not on the system where there is no sqlserver.Is postgresql similar to sql server or does it supports network sharing i,e one one can access postgresql from any system irrespective on which system it is installed. nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; If there is any weblink for this kindly provide that also. nbsp;nbsp;nbsp; Thank You, nbsp; nbsp;nbsp; Avinash nbsp;nbsp;nbsp;
