Re: [SQL] Problems when copy data from dump file
Klas Stockhem wrote: > Does the php engine interpret the big P like a small one? It's not PHP, but PostgreSQL itself. All names in PostgreSQL are case insensitive unless you double-quote them. PostgreSQL actually translates everything to lower-case internally. CREATE TABLE1 ...;-- gives "table1" CREATE "TABLE2" ...; -- gives "TABLE2" SELECT * FROM table1 -- works SELECT * FROM TABLE1 -- works SELECT * FROM TaBlE1 -- works SELECT * FROM "table1" -- works SELECT * FROM "TABLE1" -- FAILS, actually "table1" SELECT * FROM table2 -- FAILS, looks for "table2" SELECT * FROM TABLE2 -- FAILS, still looking for "table2" SELECT * FROM "TABLE2" -- works So - if you double-quote a table-name (or function or schema name) when you create it, you should always double-quote it when using it. I'm guessing something added double-quotes for you when you created the schema. > Is it recommended to always have small letters in schema names? Personally, I do. I think it looks neater. > 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. There is a variable called "search_path" which controls what schemas are checked for tables, functions etc. If you have two tables with the same name but different schemas you'll need to use the . format though. SET search_path = public2,public; ALTER USER myuser SET search_path = ... ALTER DATABASE mydb SET search_path = ... -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problems when copy data from dump file
Klas Stockhem wrote: > The data in the dump file are structured and I want to execute this > automatically (copy+paste it) in the EMS software. > I still get the syntax error even if I use the command you send me: > COPY artikkel (id, tittel, tekst) FROM stdin; 1Title oneSome > text \. Why not just load the dump file into PostgreSQL? Why are you trying to copy+paste if you want the entire dump? -- Richard Huxton Archonet Ltd -- 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
On Wednesday 05 August 2009 10:21:08 pm A. Kretschmer wrote: > 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 Thanks - the sessionid's in fact do match. It's just that I can have more than two (2) classes per sessionid. So mytable might look like: select * from mytable 1 2009/01/01 2101 2 2009/01/02 2101 3 2009/02/05 2101 4 2009/02/15 2101 5 2009/02/25 2101 I will try to use your solution. I was also looking at using an array aggregate. I'm not sure how I use it but it might work. Also I'm using 8.3.7 if that helps. Johnf -- 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
John wrote: > 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 > Can you give a more precise example please? I don't get what you really need. What I understand is that you want 1 record back for each sessionid with the earliest and latest class_date. I've done the following: lem=# select * from mytable; pkid | class_date | sessionid --+-+--- 1 | 2009-01-01 00:00:00 | 2101 2 | 2009-01-02 00:00:00 | 2101 3 | 2009-01-01 00:00:00 | 2102 4 | 2009-01-02 00:00:00 | 2102 5 | 2009-01-01 00:00:00 | 2103 6 | 2009-01-02 00:00:00 | 2103 7 | 2009-01-03 00:00:00 | 2103 (7 rows) and then: lem=# select min(pkid) as pkid lem-# ,min(class_date) as class_date1 lem-# ,max(class_date) as class_date2 lem-# ,sessionid lem-# from mytable lem-# group by sessionid; pkid | class_date1 | class_date2 | sessionid --+-+-+--- 5 | 2009-01-01 00:00:00 | 2009-01-03 00:00:00 | 2103 3 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 | 2102 1 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 | 2101 (3 rows) Is this what you need or is there something else? Can you give more sample data and the result you expect from it? Cheers, Leo -- 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 : > Thanks - the sessionid's in fact do match. It's just that I can have more > than two (2) classes per sessionid. So mytable might look like: > select * from mytable > 1 2009/01/01 2101 > 2 2009/01/02 2101 > 3 2009/02/05 2101 > 4 2009/02/15 2101 > 5 2009/02/25 2101 Can you show/explain, which rows in your example contains now the values for the new row? Regards, 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
Re: [SQL] two records per row from query
On Thursday 06 August 2009 06:42:34 am Leo Mannhart wrote: > John wrote: > > 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 > > Can you give a more precise example please? I don't get what you really > need. What I understand is that you want 1 record back for each > sessionid with the earliest and latest class_date. > > I've done the following: > > lem=# select * from mytable; > pkid | class_date | sessionid > --+-+--- > 1 | 2009-01-01 00:00:00 | 2101 > 2 | 2009-01-02 00:00:00 | 2101 > 3 | 2009-01-01 00:00:00 | 2102 > 4 | 2009-01-02 00:00:00 | 2102 > 5 | 2009-01-01 00:00:00 | 2103 > 6 | 2009-01-02 00:00:00 | 2103 > 7 | 2009-01-03 00:00:00 | 2103 > (7 rows) > > and then: > > lem=# select min(pkid) as pkid > lem-# ,min(class_date) as class_date1 > lem-# ,max(class_date) as class_date2 > lem-# ,sessionid > lem-# from mytable > lem-# group by sessionid; > pkid | class_date1 | class_date2 | sessionid > --+-+-+--- > 5 | 2009-01-01 00:00:00 | 2009-01-03 00:00:00 | 2103 > 3 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 | 2102 > 1 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 | 2101 > (3 rows) > > Is this what you need or is there something else? Can you give more > sample data and the result you expect from it? > > > Cheers, Leo I'm sorry I was attempting to simplify the problem. I will attempt to provide more info: OVERVIEW: "mytable" contains the dates of the classes a student will attend along with fields to identify the student (not really it's normalized). One row per class. In general the student signs up for a session. A session has many classes that run for some length of time. Normally, a few months. Classes maybe on some set schedule or not. Maybe on each Saturday and Sunday for two months - maybe a total of 16 classes. What I need is a way to gather the classes two (maybe three) at a time into one row. I need this because the report writer processes the data one row at a time. And I need the report writer to print two class dates on one line of the report. So the output would look similar to the follows on the report: Your class schedule is as follows: Saturday 01/03/2009 Sunday 01/04/2009 Saturday 01/10/2009 Sunday 01/11/2009 Saturday 01/17/2009 Sunday 01/18/2009 And of course the schedule will continue until all the classes are print. Also note that the dates are in order from left to right and then down. THE PROBLEM: Since the classes are in a single row per class I need a way to get two classes into a single row to allow the report writer to print two classes per row. I don't know how too! In general the sessionid will be the same but it is not the only thing I'm using to find the student. The "essess" table is the available sessions. The "esclass" contains the classes and any reschedule classes with a FK into essess The 'esenroll' has the student, the session. This is converted from an old Visual Fox Pro program. The actual tables in question The sessions: CREATE TABLE essess ( pkid serial NOT NULL, sessionid_do_not_use integer, courseid integer, instrid integer, sequenceid integer, began date, ended date, cancelled boolean, name_1 character varying(35), locationid integer, facility character varying(35), availseats numeric(5), depart integer, stop_close boolean DEFAULT false, langid integer, monday boolean DEFAULT false, tuesday boolean DEFAULT false, wedesday boolean DEFAULT false, thursday boolean DEFAULT false, friday boolean DEFAULT false, saturday boolean DEFAULT false, sunday boolean DEFAULT false, end_time character varying(10), start_time character varying(10), note character varying, eligiblecourses text, topic integer, total_hours numeric(5,1) DEFAULT 0.0, total_classes integer DEFAULT 0, CONSTRAI
Re: [SQL] Problems when copy data from dump file
Klas Stockhem wrote: > Does the php engine interpret the big P like a small one? It's not PHP, but PostgreSQL itself. All names in PostgreSQL are case insensitive unless you double-quote them. PostgreSQL actually translates everything to lower-case internally. CREATE TABLE1 ...;-- gives "table1" CREATE "TABLE2" ...; -- gives "TABLE2" SELECT * FROM table1 -- works SELECT * FROM TABLE1 -- works SELECT * FROM TaBlE1 -- works SELECT * FROM "table1" -- works SELECT * FROM "TABLE1" -- FAILS, actually "table1" SELECT * FROM table2 -- FAILS, looks for "table2" SELECT * FROM TABLE2 -- FAILS, still looking for "table2" SELECT * FROM "TABLE2" -- works So - if you double-quote a table-name (or function or schema name) when you create it, you should always double-quote it when using it. I'm guessing something added double-quotes for you when you created the schema. > Is it recommended to always have small letters in schema names? Personally, I do. I think it looks neater. > 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. There is a variable called "search_path" which controls what schemas are checked for tables, functions etc. If you have two tables with the same name but different schemas you'll need to use the . format though. SET search_path = public2,public; ALTER USER myuser SET search_path = ... ALTER DATABASE mydb SET search_path = ... -- Richard Huxton Archonet Ltd > > > Yes It works fine when I execute the SET command and the ALTER USER command in the EMS software! Now my web application return data from the database tables that contains some data. (I have manually put data in some tables from the EMS software). I have "a lot of" data still left in the dump file to put in the database. I can manually put the data in the tables but this will take some times. The data in the dump file are structured and I want to execute this automatically (copy+paste it) in the EMS software. I still get the syntax error even if I use the command you send me: COPY artikkel (id, tittel, tekst) FROM stdin; 1Title oneSome text \. This is the error I get : "ERROR: syntax error at or near "1" at character 47". It feels like it is some simple error but I can't find it out. I would help me a lot if I get a solution. //Klas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] trigger problem
Hi,
If I try this to run in a trigger function
'perform dblink_connect('myconnect','dbname=postgres password=uzleuven');
perform dblink_exec('myconnect', 'insert into test (uid) values (' ||
quote_literal(NEW.pat_id) || ')');
return new;
perform dblink_disconnect('myconnect');'
I get the message
'ERROR: duplicate connection name
SQL state: 42710
Context: SQL statement "SELECT dblink_connect('myconnect','dbname=postgres
password=uzleuven')"
PL/pgSQL function "test_update_trigger" line 2 at perform'
This happens only in one of my two databases, anyone an idea?
Regards,
Jan
[SQL] Problems when copy data from dump file
> The data in the dump file are structured and I want to execute this > automatically (copy+paste it) in the EMS software. > I still get the syntax error even if I use the command you send me: > COPY artikkel (id, tittel, tekst) FROM stdin; 1Title oneSome > text \. Why not just load the dump file into PostgreSQL? Why are you trying to copy+paste if you want the entire dump? -- Richard Huxton Archonet Ltd > > > I have tried to load the dump file also but get same error. I have check the sql dump file in Ultra editor 32 where you can see the hex value of each character in the file I see the both the original dump file and my copy+paste text contain a tab (hex value 09). Note that I have "CREATE TABLE" script in the dump file and this was executed good in the server. This was copy+pasted also. Maybe you can send me an create table script and one row of a copy command and I can execute this and see if it works? //Klas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] FW: trigger problem
Hi,
I keep looking for myself and tried as well the following code:
'perform dblink_connect('myconnect','dbname=postgres password=uzleuven');
create view remote as
select *
from dblink('myconnect','select
uid from test')
as t1(pat_id text);
perform * from remote where pat_id like '|| query_literal(NEW.pat_id) ||';
return new;
perform dblink_disconnect('myconnect');'
And again I get the same error message as below..
Is this because it tries to make connection for each row in the column??
Thanks,
Jan
From: Jan Verheyden
Sent: Thursday, August 06, 2009 11:09 AM
To: '[email protected]'
Subject: trigger problem
Hi,
If I try this to run in a trigger function
'perform dblink_connect('myconnect','dbname=postgres password=uzleuven');
perform dblink_exec('myconnect', 'update test set uploaded = 1 where uid =' ||
quote_literal(NEW.pat_id) || ' ');
return new;
perform dblink_disconnect('myconnect');'
I get the message
'ERROR: duplicate connection name
SQL state: 42710
Context: SQL statement "SELECT dblink_connect('myconnect','dbname=postgres
password=uzleuven')"
PL/pgSQL function "test_update_trigger" line 2 at perform'
This happens only in one of my two databases, anyone an idea?
Regards,
Jan
