[SQL] two records per row from query
mytable pkid class_date. sessionid select * from mytable 1 2009/01/01 2101 2 2009/01/02 2101 I would like an SQL that would produce newtable pkid, class_date1, class_date2, sessionid1, sessionid2 Select * from newtable 1 2009/01/01 2009/01/02 2101 2101 I have a list of classes that is perfect for our needs. However, I need to create the second table (from a query) to feed to a report writer so it can write out a single line of text for two records. Like: Your class dates are as follows Date Date 01/01/2009 01/02/2009 01/08/2009 01/10/2009 03/31/2009 04/05/2009 and will continue until the all the classes are printed. The problem of course is the table has a row per class and the report writer needs two class dates per row. I have no idea how to do this using SQL. Thanks in advance, Johnf -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] question about pgagent
Hi All,
First I will explain what I try to do:
I'd like to synchronize more or less two databases, in a third database. I
mean, I want to book keep when an object has arrived in both databases. (In one
database I am allowed to use trigger function but not in the other)
1) Postgres (maintenance database)
2) Database1
3) Database3
How can I set up in a cron job (pgagent), that I check if a certain value f.e.
id is present in both database1 database2 and then flag this in the maintenance
database?
Select_connect('myconnect', 'dbase=database1');
Select_connect('myconnect2','dbase=database2');
Using
select dblink('myconnect',
Thanks in advance!
Jan
Re: [SQL] Problems when copy data from dump file
-Ursprungligt meddelande- Från: Richard Huxton [mailto:[email protected]] Skickat: den 4 augusti 2009 15:27 Till: Klas Stockhem Kopia: PostgreSQL Ämne: Re: SV: [SQL] Problems when copy data from dump file Klas Stockhem wrote: > Thanks for your mail! Remember to cc: the mailing list too. Just hit "reply to all". Oh, and perhaps don't top-quote. It makes it difficult for others to follow the message. > 1. Yes, the 5 and other text are at line 2 (a new line). I tried to put > the whole command on the first line and execute it but I get same error: > > > ERROR: syntax error at or near "1" > LINE 1: COPY artikkel (id, tittel, tekst) FROM stdin 1 test test; No, the data needs to start on the line below COPY, but your first email had a blank line there. Your message had: Line 1: COPY artikkel (id, tittel, tekst) FROM stdin; Line 2: (blank) Line 3: 5 Slitasje og vedlikehold PRAKTISKE OPPLYSNINGER What you want is something like: COPY artikkel (id, tittel, tekst) FROM stdin; 1Title oneSome text 2Title twoSome text \. Where the marks are real tabs. > I have also separated the 1 and the "test-text" with tab. Is the syntax > for the line 1 correct? I have read some about using DELIMITER. Do you > have a suggestion how a can use this in the command? > > 2. Yes I have opened the dump file on in a windows system and copy+paste > it into the phppgadmin web interface. Hmm - reading the phppgadmin documentation, the FAQ says the following: "Only uploaded SQL scripts can contain COPY commands and for this to work, you must have PHP 4.2 or higher." So - it looks like you should save the COPY script to a text-file and upload it. I don't know how you do that, but it should be in the manual. -- Richard Huxton Archonet Ltd > > > ok, I have downloaded and installed EMS SQL query 2007 local on my computer and it works fine to connect to my database server from this software. (It did'nt work with the phpPGadmin web interface.) I have also inserted data into my tables ansql query it works fine. I have just one problem left: I must type the schema name and table name in all sql queries to get some outputs. E.g Schema2.table This makes some problems in my web applications. When I execute my php files in the web browser the sql server returns "schema "public2" does not exist". It reads the sql query in my web application like a small p but I have typed big P in the php file. I can run this in the EMS software and there it works fine. When I try to just type the table name in sql query like "SELECT * FROM tablename" I get error "relation "tablename" does not exist". Does the php engine interpret the big P like a small one? Is it recommended to always have small letters in schema names? How do I make so I not must type the schema name in every sql query? The best would be if I just can type the table name. //Klas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create table command fails with permission denied
Hi Tom, Thanks a lot. What you suspected is correct. Permissions are modified on the underlying OS directory of this database. Once the permissions are rectified, I'm able to create tables without any issues. Thanks to all for your support. Venkat -Original Message- From: Tom Lane [mailto:[email protected]] Sent: Wednesday, August 05, 2009 6:46 AM To: Venkateswara Rao Bondada Cc: Rob Sargent; [email protected] Subject: Re: [SQL] Create table command fails with permission denied Venkateswara Rao Bondada writes: > camd=# \c cms postgres > You are now connected to database "cms" as user "postgres". > cms=# create table test(id character varying(80)); > ERROR: could not create relation "test": Permission denied Actually, what that is complaining about is that the operating system refused its attempt to create a filesystem file to hold the table. It's got nothing to do with permissions inside the database --- there is something wrong with the filesystem permissions of the data directory. Or maybe you started the postmaster as the wrong user (not the one that owns the data directory). regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LOG: unexpected EOF on client connection
Hello, A client application written in C# + npgsql 2.0.6 causes reproducable log entries like this: LOG: unexpected EOF on client connection LOG: could not receive data from client: Die Verbindung wurde vom Kommunikationspartner zurückgesetzt(*) (*) Connection reset by peer The responsable Programmer claims autovacuum caused the malfunction. But after disabling autovacuum the same error occured. Another program, written Python + psycopg2, which invokes exactly the same function resulting the above connection reset in the C# program runs without any problem while inserting far more than 10 times the amount of data the C# program should do. Even running 2 parallel instances of the Python program does not result in any error. Server is version 8.4 + Postgis 1.4 (both compiled with gcc 4.3), OS is Debian Lenny running on a Quadcore-Xeon Dell Precision 690 with 2 GB RAM. When the above error occurs the C# program is the only client connecting to the server. What may be the most probable cause of that? Bernd -- No time toulouse -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LOG: unexpected EOF on client connection
Bernd Nawothnig writes: > A client application written in C# + npgsql 2.0.6 causes reproducable > log entries like this: > LOG: unexpected EOF on client connection > LOG: could not receive data from client: Die Verbindung wurde vom > Kommunikationspartner zurückgesetzt(*) This just indicates that the client application closed the connection without sending a Terminate message. It's harmless, but if the log chatter annoys you, you should fix the application to close its PG connection cleanly before it quits. > The responsable Programmer claims autovacuum caused the malfunction. Nonsense ... regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] two records per row from query
In response to John : > mytable > pkid > class_date. > sessionid > > select * from mytable > 1 2009/01/01 2101 > 2 2009/01/02 2101 > > I would like an SQL that would produce > > newtable > pkid, > class_date1, > class_date2, > sessionid1, > sessionid2 > > Select * from newtable > > 1 2009/01/01 2009/01/02 2101 2101 I will try, but i'm not sure if i understand you correctly. Your table contains only 2 rows and both rows contains the same sessionid. Can i use that sessionid to find the rows that belongs together? Okay, my table: test=*# select * from mytable ; pkid | class_date | sessionid --++--- 1 | 2009-01-01 | 2101 2 | 2009-01-02 | 2101 3 | 2009-02-01 | 2102 4 | 2009-02-02 | 2102 5 | 2009-03-01 | 2103 6 | 2009-03-02 | 2103 (6 rows) As you can see, there are 3 different sessionid's. test=*# select distinct on (sessionid1,sessionid2) pkid, classdate1, classdate2, sessionid1, sessionid2 from (select least(a.pkid, b.pkid) as pkid, least(a.class_date, b.class_date) as classdate1, greatest(a.class_date, b.class_date) as classdate2, a.sessionid as sessionid1, b.sessionid as sessionid2 from mytable a inner join mytable b on (a.sessionid=b.sessionid)) foo order by sessionid1, sessionid2,pkid; pkid | classdate1 | classdate2 | sessionid1 | sessionid2 --++++ 1 | 2009-01-01 | 2009-01-01 | 2101 | 2101 3 | 2009-02-01 | 2009-02-01 | 2102 | 2102 5 | 2009-03-01 | 2009-03-01 | 2103 | 2103 (3 rows) Hope that helps... 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
