Re: [SQL] insert values
On Thu, Feb 22, 2001 at 11:01:11PM -0500, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
> > I have the following type :
> > ...
> > How can I insert a value in this table ?
>
> INSERT INTO entiers VALUES('1234') should work fine.
>
I have tried and I have this message :
ex1=# insert into entiers
ex1-# values ('1234');
ERROR: Load of file /ens/klimann/PostgreSQL/entier.o failed: Exec format error
ex1=#
what do you think it could be ?
Thanks,
Ines.
[HACKERS] ask for help !!! (emergency case)
dear all I hava 2 problems about view 1. i can't insert into view 2. i can't create view with union I try to insert into view as following create table t1 (id int,name varchar(12) check(id<=10)); create table t2 (id int,name varchar(12) check(id>10)); create view v1 as select * from t1,t2; insert into v1 values(1,'wan1'); insert into v1 values(12,'wan12'); it does not show any problem but it doen't have data in table t1 and table t2 -- if i want to distribute database into 2 database servers and i want to insert into database1.table1 when database1.table1.id <=100 and i want to insert into database2.table2 when database2.table2.id >100 How can i do that with create view .as ...union all and insert into view ,afterthat view is check condition and distrubute data into diferent database up on condition and How to configure the postgres sql server? If you have idea or example for solving this problem , pls help me thank you so much , i'm looking forward to seeing your response. Regards,
[ADMIN]
Hi, I cannot use any kind of odbc because my customers have his local m$ access db's locally then export them on .txt with tab or | separated, then put on my server trought ftp. and is working ok except that the customers are on spanish databases then a data like: --DATE-NAME-LANG-- 1/6/2000|Ferran Adrià|Castellano| when sended trought ftp on my server is converted to: --DATE-NAMELANG-- 1/6/2000|Ferran Adri\xe0|Castellano| so when imported on Postgresql with: COPY products FROM '/var/lib/postgres/iii2.txt' USING DELIMITERS '|' \g --DATE-NAME---LANG-- 1/6/2000|Ferran Adri\xe0|Castellano|NULL on the same cell, ignoring the '|' completelly on 'postmaster.init' I have: LANG=es_ES but doesnt' works... using tabulators as a separators also causes same problem... any pointers to solve this will be really apreciated the other problem is that if a m$ access database has a return carraige on a text cell the import also fails. bests from barcelona, teixi.
AW: [HACKERS] ask for help !!! (emergency case)
> I hava 2 problems about view > 1. i can't insert into view > I try to insert into view as following > create table t1 (id int,name varchar(12) check(id<=10)); > create table t2 (id int,name varchar(12) check(id>10)); > create view v1 as select * from t1,t2; This is not an updateable view in any database product. It is a cartesian product join of t1 and t2. You probably wanted: create view v1 as select * from t1 union all select * from t2; > insert into v1 values(1,'wan1'); > insert into v1 values(12,'wan12'); > > it does not show any problem but it doen't have data in table > t1 and table t2 Version 7.1 will give you an error if you don't create an appropriate insert and update rule for the view. Insert and update rules are not yet automatically created for views. Andreas
Re: [SQL] Speed of SQL statements
On Sun, Feb 18, 2001 at 03:42:43PM -0500, Kevin Quinlan allegedly wrote: > Does anyone have any performance numbers regarding SQL statements, > specifically SELECT, UPDATE, DELETE, and INSERT? For instance, on average > how long does a typical SELECT (UPDATE, DELETE, INSERT) statement take to > execute? You can use the EXPLAIN command to show the execution plan for a query. This allows you to tweak your query (and maybe your indexes) for optimal performance. | iig=# explain select id from entries where not exists (select * from etree where |siteid = id); | NOTICE: QUERY PLAN: | | Seq Scan on entries (cost=0.00..57838.91 rows=1 width=4) | SubPlan | -> Index Scan using idx_etree_siteid on etree (cost=0.00..2.04 rows=1 | width=8) | | EXPLAIN The PostgreSQL documentation has more information on the EXPLAIN command. Make sure you have a look at the VACUUM command, if you haven't already done so. In order to come up with an optimal execution plan pgsql needs information about the contents of your database. This is why you need to run VACUUM ANALYZE from time to time. It also cleans up your indexes. If pgsql is not using indexes when you think it should, run a VACUUM ANALYZE on the table and see if that makes a difference. I hope this helps, Mathijs -- "Books constitute capital." Thomas Jefferson
Re: [SQL] Speed of SQL statements
On Sun, Feb 18, 2001 at 03:42:43PM -0500, Kevin Quinlan allegedly wrote: > Does anyone have any performance numbers regarding SQL statements, > specifically SELECT, UPDATE, DELETE, and INSERT? For instance, on average > how long does a typical SELECT (UPDATE, DELETE, INSERT) statement take to > execute? Performance is not easily expressed in time, since it depends a lot on the way you've setup your database and your queries (indexes, etc). Of course, the hardware you use also influences the execution time a lot. Cheers, Mathijs -- "It is a great thing to start life with a small number of really good books which are your very own". Sir Arthur Conan Doyle (1859-1930)
Re: [SQL] insert values
[EMAIL PROTECTED] writes: > ERROR: Load of file /ens/klimann/PostgreSQL/entier.o failed: Exec format error .o? Did you convert this file into a shared library? I'd expect .so or .sl depending on platform... regards, tom lane
[SQL] sum(bool)?
hi there i'd like to add up the "true" values of a comparison like sum(a>b) it just doesn't work like this any workaround postgres 7.0 on linux thanks olaf -- soli-con Engineering Zanger Dipl.-Ing. (FH) Olaf Marc Zanger Lorrainestrasse 23 3013 Bern / Switzerland Fon: +41-31-332 9782 Mob: +41-76-572 9782 mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.soli-con.com
Re: [SQL] Can a function return a record set?
Mr. Taves, > Am I correct in concluding that I can't return a record > set from a function? > > For example, in MS SQL I would do: > > create procedure foo as > select * from yada > > I expected to be able to do the following in postgresql. > > create function foo (integer) returns (integer) as ' > begin > select * from yada; > end; ' > language 'plpgsql'; > > I am concluding that I can't. You are correct. Stored procedures that return rowsets are in the "wish list" for 7.2 or 8.0 ... but not in the current development version. Please browse the archives (about 1-2 months ago) for my suggested workaround for the time being. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco
[SQL] sum(bool)?
> "Olaf" == Olaf Zanger <[EMAIL PROTECTED]> writes: Olaf> hi there i'd like to add up the "true" values of a Olaf> comparison like Olaf> sum(a>b) Olaf> it just doesn't work like this Olaf> any workaround Try using a case statement: select sum(case when a > b then 1 else 0 end) -Dan
Re: [SQL] sum(bool)?
On Fri, 23 Feb 2001, Olaf Zanger wrote: > i'd like to add up the "true" values of a comparison like > > sum(a>b) > > it just doesn't work like this Try sum(case when a>b then 1 else 0 end) -- Tod McQuillin
Re: [SQL] sum(bool)?
Olaf Zanger writes: > i'd like to add up the "true" values of a comparison like > > sum(a>b) sum(case when a>b then 1 else 0 end) of maybe even just select count(*) from table where a>b; -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] sum(bool)?
hi there, s cool, this works streight away and took 5 min. waiting for a answer :-) thanks very much to you tod personal and the mailing list for existence. Olaf Tod McQuillin schrieb: > > On Fri, 23 Feb 2001, Olaf Zanger wrote: > > > i'd like to add up the "true" values of a comparison like > > > > sum(a>b) > > > > it just doesn't work like this > > Try > > sum(case when a>b then 1 else 0 end) > -- > Tod McQuillin -- soli-con Engineering Zanger Dipl.-Ing. (FH) Olaf Marc Zanger Lorrainestrasse 23 3013 Bern / Switzerland Fon: +41-31-332 9782 Mob: +41-76-572 9782 mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.soli-con.com
Re: [SQL] sum(bool)?
Or how about just: SELECT count(*) FROM tablename WHERE a > b; -- Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin [EMAIL PROTECTED] - [EMAIL PROTECTED] On Fri, 23 Feb 2001, Daniel Wickstrom wrote: > > "Olaf" == Olaf Zanger <[EMAIL PROTECTED]> writes: > > Olaf> hi there i'd like to add up the "true" values of a > Olaf> comparison like > > Olaf> sum(a>b) > > Olaf> it just doesn't work like this > > Olaf> any workaround > > Try using a case statement: > > select sum(case when a > b then 1 else 0 end) > > > -Dan >
Re: AW: [HACKERS] ask for help !!! (emergency case)
Zeugswetter Andreas SB <[EMAIL PROTECTED]> writes: > You probably wanted: > create view v1 as > select * from t1 > union all > select * from t2; Probably, but we don't support UNION in views before 7.1 :-( I'm not real clear on why t1 and t2 are separate tables at all in this example. Seems like making v1 be the real table, and t1 and t2 be selective views of it, would work a lot easier. regards, tom lane
[SQL] How can i escape a '+' or a '+' in a regexp ?
Hi fellows, I'm trying to the following query: select * from areas where titulo ~ '+' or titulo ~ '*' and the answer is: ERROR: regcomp failed with error repetition-operator operand invalid I have tried to escape the '+' and the '*' with a backslash, as follows: select * from areas where titulo ~ '\+' or titulo ~ '\*' but the answer is the same. If I use the LIKE operator, then I have the problem with '%' and '_' :-) As long as the values in the field can contain either '+' or '*' or '%' or '_' I need to escape these characters. How can i do it ? I'm using PostgreSQL 6.5.3 on Linux Red Hat 6.2. Thanks, Gabi :-)
Re: [SQL] How can i escape a '+' or a '+' in a regexp ?
I believe you'll need two \ characters to escape the + or *. titulo ~ '\\+' On Fri, 23 Feb 2001, Gabriel Fernandez wrote: > Hi fellows, > > I'm trying to the following query: > > select * from areas where titulo ~ '+' or titulo ~ '*' > > and the answer is: > > ERROR: regcomp failed with error repetition-operator operand invalid > > I have tried to escape the '+' and the '*' with a backslash, as > follows: > > select * from areas where titulo ~ '\+' or titulo ~ '\*' > but the answer is the same. > > If I use the LIKE operator, then I have the problem with '%' and '_' > :-) > > As long as the values in the field can contain either '+' or '*' or '%' > or '_' I need to escape these characters. How can i do it ?
[SQL] Need your help
Hi, I need your help on something. I have to write a trigger (in sybase) that does this: Everytime a record gets updated it should update a column in that record with the current date/time. I am able to do it for the whole table, but how do I make sure the update happens only for that record which is being updated? Please respond. Thanks a bunch, Jyotsna. __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
Re: [SQL] How can i escape a '+' or a '+' in a regexp ?
select field from table where field like '%\\%%' or field like '%*%'; select field from table where field ~ '.*\\*.*' or ~ '.*%.*'; Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Feb 2001, Gabriel Fernandez wrote: > Hi fellows, > > I'm trying to the following query: > > select * from areas where titulo ~ '+' or titulo ~ '*' > > and the answer is: > > ERROR: regcomp failed with error repetition-operator operand invalid > > I have tried to escape the '+' and the '*' with a backslash, as > follows: > > select * from areas where titulo ~ '\+' or titulo ~ '\*' > but the answer is the same. > > If I use the LIKE operator, then I have the problem with '%' and '_' > :-) > > As long as the values in the field can contain either '+' or '*' or '%' > or '_' I need to escape these characters. How can i do it ? > > I'm using PostgreSQL 6.5.3 on Linux Red Hat 6.2. > > Thanks, > > Gabi :-) > > > >
Re: [SQL] How can i escape a '+' or a '+' in a regexp ?
Gabriel, Two backslashes. > select * from areas where titulo ~ '\\+' or titulo ~ '\\*' Troy > > Hi fellows, > > I'm trying to the following query: > > select * from areas where titulo ~ '+' or titulo ~ '*' > > and the answer is: > > ERROR: regcomp failed with error repetition-operator operand invalid > > I have tried to escape the '+' and the '*' with a backslash, as > follows: > > select * from areas where titulo ~ '\+' or titulo ~ '\*' > but the answer is the same. > > If I use the LIKE operator, then I have the problem with '%' and '_' > :-) > > As long as the values in the field can contain either '+' or '*' or '%' > or '_' I need to escape these characters. How can i do it ? > > I'm using PostgreSQL 6.5.3 on Linux Red Hat 6.2. > > Thanks, > > Gabi :-) > > > > >
[SQL] Controlling Reuslts with Limit
Hi, I was reading through Bruce's on line . I found follwing bit unclear... "Notice that each query uses ORDER BY . Although this clause is not required, LIMIT without ORDER BY returns random rows from the query, which would be useless. " When I run a query several time I get the same results as given flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 I just want to know what exatly --"LIMIT without ORDER BY returns random rows from the query" --means Regards begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard
Re: [SQL] Need your help
e.g. Try: CREATE TABLE emp ( id int4 primary key, empname text, salary int4, last_date datetime, last_user name); CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS BEGIN update emp set last_date=''now''::timestamp where id=NEW.id; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER emp_stamp BEFORE UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Mon, 19 Feb 2001, Jyotsna Kypa wrote: > Hi, > I need your help on something. I have to write a > trigger (in sybase) that does this: Everytime a record > gets updated it should update a column in that record > with the current date/time. I am able to do it for the > whole table, but how do I make sure the update happens > only for that record which is being updated? Please > respond. > Thanks a bunch, > Jyotsna. > > > __ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail - only $35 > a year! http://personal.mail.yahoo.com/ >
Re: [SQL] Controlling Reuslts with Limit
> Hi, > I was reading through Bruce's on line . I found follwing bit unclear... > > "Notice that each query uses ORDER BY . Although this clause is not required, > LIMIT without ORDER BY returns random rows from the query, which would be > useless. " It means there is no guarantee which rows will be returned. You may get the rows you want, or you may not. Without the ORDER BY, the backend can return any five rows it wishes. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Controlling Reuslts with Limit
> Hi, > I was reading through Bruce's on line . I found follwing bit unclear... > > "Notice that each query uses ORDER BY . Although this clause is not required, > LIMIT without ORDER BY returns random rows from the query, which would be > useless. " > > When I run a query several time I get the same results as given ... > I just want to know what exatly --"LIMIT without ORDER BY returns random rows > from the query" --means I don't think it is actually random. It just that the order is not defined and other events may change the order. I believe that without an ORDER BY or other clauses that cause an index to be used that the database tends to return rows in the order stored on disk. This order tends to be the order in which rows were added. My observation is this ordering is faily stable and it seems to survive a database reload. Just don't rely on it. There is a CLUSTER command to change the physical ordering.
Re: [SQL] Controlling Reuslts with Limit
My understanding: because you return a subset instead of a single value, so between 2 select ... limit ... queries. if you delete a record(say song_id=947) then insert it again. then results are different. So for a multiple users db, you should use oder by when you use limit. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Sat, 24 Feb 2001, Najm Hashmi wrote: > Hi, > I was reading through Bruce's on line . I found follwing bit unclear... > > "Notice that each query uses ORDER BY . Although this clause is not required, > LIMIT without ORDER BY returns random rows from the query, which would be > useless. " > > When I run a query several time I get the same results as given > flipr=# select song_id from songs limit 5; > song_id > - > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > - > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > - > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > - > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > - > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > - > 945 > 946 > 947 > 948 > 949 > I just want to know what exatly --"LIMIT without ORDER BY returns random rows > from the query" --means > Regards >
Re: [SQL] Controlling Reuslts with Limit
> I don't think it is actually random. It just that the order is not defined > and other events may change the order. I believe that without an ORDER BY > or other clauses that cause an index to be used that the database tends to > return rows in the order stored on disk. This order tends to be the order > in which rows were added. My observation is this ordering is faily stable > and it seems to survive a database reload. Just don't rely on it. There is > a CLUSTER command to change the physical ordering. Yes, usually it is the heap order, but if you do "col > 12" you may get it in index order by the column indexes, or you may not, depending on the constant, the size of the table, vacuum, vacuum analyze, etc. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Controlling Reuslts with Limit
Najm Hashmi <[EMAIL PROTECTED]> writes: > I just want to know what exatly --"LIMIT without ORDER BY returns random rows > from the query" --means It means the results aren't guaranteed. It doesn't mean that the exact same query run under the exact same conditions by the exact same version of Postgres won't return the same results every time. Especially not one that's too simple to have more than one possible execution plan... regards, tom lane
Re: [SQL] Controlling Reuslts with Limit
It returns the first five rows it finds. Running the same query over again if there are no updates is safe, but if the table is updated there is the possibility it would find a different five rows. If the query would do a seq scan and you updated a row, the rows would be in a different order in the heap file and so you'd get a different ordering of rows... On Sat, 24 Feb 2001, Najm Hashmi wrote: > Hi, > I was reading through Bruce's on line . I found follwing bit unclear... > > "Notice that each query uses ORDER BY . Although this clause is not required, > LIMIT without ORDER BY returns random rows from the query, which would be > useless. " > > When I run a query several time I get the same results as given > flipr=# select song_id from songs limit 5; > song_id > - > 945 > 946 > 947 > 948 > 949 > (5 rows) > ...
[SQL] Recursive Query (need of PL/pgSQL?)
Hello everyone, I have a system (simplified for this example) with the following two tables: TABLE1 id::int8 containerId::int8 containerType::varchar(100) moreInfo::text TABLE2 id::int8 containerId::int8 containerType::varchar(100) otherInfo::text Now, the rows of TABLE2 are children of TABLE1 objects. Therefore, an entry in TABLE1 and TABLE2 looks like that: TABLE1: id containerId containerTypemoreInfo - 1 0 null 'foo' TABLE2: id containerId containerTypeotherInfo - 1 1 TABLE1 'bar' 2 1 TABLE1 'more bar' 3 2 TABLE2 're: more bar' Since in this case TABLE1's row 1 is a root object it has no containerType and containerId (it has no parent). The first 2 TABLE2 rows are sub-objects of TABLE1, therefore TABLE1 is referenced there. But Tables (objects) can also reference themselves, like it would be the case in a message board. Now my problem: - I need to figure out the object-tree for any row in the system (of course I do not only have 2 levels like in this example). The preferred output should look like this (or similar): level containerType containerId -- 1 TABLE1 1 2 TABLE2 2 3 TABLE2 3 This would be the object tree for row 3 in TABLE2. I am pretty sure one would need to use pg/PLSQL or something similar, if that is possible at all. I could solve the problem in my programming language with a recursive function, but it would be VERY expensive, since I would need to make n DB requests (n --> number of levels). If you know a better way to represent a system like that generically, let me know. I thought about making a base table with the fields (containerType, containerId) and then inherit that table, but that did not get me much further. THANKS A LOT FOR YOUR TIME IN ADVANCE!!! Regards, Stephan -- Stephan Richter CBU - Physics and Chemistry Student Web2k - Web Design/Development & Technical Project Management
[SQL] syntax prob
I am away from my server for the weekend and I need a little help. when doing updates of multiple fields there commas between the elements? I mean update table set cname = 'Bill', caddress = '2nd floor' where acode = 'AVAN'; I refer to the space between 'Bill' and caddress. if I could get to the server I would just try it. what happens if you have only 1 field to update and you add a comma, like this update table set cname = 'Bill', where acode = 'AVAN'; I am trying to concatenate an update string and I would love to not have to worry about the comma. I guess I could figure out how many things have changed then add commas... but I could also wish for it to be easier. Ted
[SQL] logging a script
Hello, I would like my psql script to log everything that it does. I set the following \set ECHO all \o foo.txt \qecho some sql, some ddl, etc... \o But foo.txt only contains DROP DROP DROP CREATE CREATE CREATE I want it to contain everything that I see on the screen, what am I missing? Thanks Ken
[SQL] bug.. ?
A person recent pointed this out to me.. seems a bit funny, because limit 1 pretty much say's it't not gonna return multiple values. jeff > This doesn't work: > > CREATE FUNCTION vuln_port(int4) RETURNS int4 AS 'SELECT port FROM > i_host_vuln WHERE vuln = $1 GROUP BY port ORDER BY count(port) DESC > LIMIT 1' LANGUAGE 'sql'; > > The result: > ERROR: function declared to return int4 returns multiple values in final > retrieve > > > I'm running 7.0.2. Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose PGP Public Key : http://bignose.hub.org/public.txt
[SQL] creating tables with different character set?
hi, i have a problem. i'm living in poland, which has its national characters. of course they work great under postgresql, but: when i use non-C locale all ~ '^xxx' and like 'xxx%' searches are not using index scan. this is paintful. for some of the tables i dont need all national characters. in fact i use only a-z0-9 characters which are subset of standard C-locale character set. so my question is. is it possible to make a table that way, that it will allow using indexing when searching for first x characters of string. at the moment the only solution i got is to make another database (with another postmaster process), but this is definetly not easy way. any other options? depesz -- hubert depesz lubaczewski http://www.depesz.pl/ najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...
[SQL] HELP: m$ access -> psql howto ?
Hi, I cannot use any kind of odbc because my customers have his local m$ access db's locally then export them on .txt with tab or | separated, then put on my server trought ftp. and is working ok except that the customers are on spanish databases then a data like: --DATE-NAME-LANG-- 1/6/2000|Ferran Adrià|Castellano| when sended trought ftp on my server is converted to: --DATE-NAMELANG-- 1/6/2000|Ferran Adri\xe0|Castellano| so when imported on Postgresql with: COPY products FROM '/var/lib/postgres/iii2.txt' USING DELIMITERS '|' \g --DATE-NAME---LANG-- 1/6/2000|Ferran Adri\xe0|Castellano|NULL on the same cell, ignoring the '|' completelly on 'postmaster.init' I have: LANG=es_ES but doesnt' works... using tabulators as a separators also causes same problem... any pointers to solve this will be really apreciated the other problem is that if a m$ access database has a return carraige on a text cell the import also fails. bests from barcelona, teixi.
[SQL] greetings
I have just joined the list a few days ago and am trying quite hard to come up to speed with pgsql but i find documentaion frustratiing. I think maybe it;s just a matter of finding things that are of the correct scope. I've been an Oracle developer for over 6 years so often I know what it is I want to do but something is just a little different. If there are others on the list that learned in Oracle then pgsql please tell me what you think are the best resources. Recently I did a google search on the key words "postgresql cursor loop" the example below is all I could come up with but it doesn't seem to work is this for an older version or am I just overlooking something simple? thanks Ken DECLARE emp_cursor CURSOR FOR SELECT Salary, Title, Start, Stop FROM Employee; OPEN emp_cursor; loop: FETCH emp_cursor INTO :salary, :start, :stop; if no-data returned then goto finished; find position in linked list to insert this information; goto loop; finished: CLOSE emp_cursor;
Re: [SQL] bug.. ?
Jeff MacDonald <[EMAIL PROTECTED]> writes: > A person recent pointed this out to me.. > seems a bit funny, because limit 1 pretty much > say's it't not gonna return multiple values. >> CREATE FUNCTION vuln_port(int4) RETURNS int4 AS 'SELECT port FROM >> i_host_vuln WHERE vuln = $1 GROUP BY port ORDER BY count(port) DESC >> LIMIT 1' LANGUAGE 'sql'; >> >> The result: >> ERROR: function declared to return int4 returns multiple values in final >> retrieve It's a combination of a bug and a poorly worded error message. It is complaining not about rows, but about the extra column needed to do the ORDER BY. The error check forgets to ignore this 'hidden' column. Fixed some time ago for 7.1... regards, tom lane
Re: [SQL] CREATE TABLE AS and ORDER BY
"Joy Chuang" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Hi, > > I tried to use CREATE TABLE AS and ORDER BY. The query is as followed: > > create table freshhr21 as > select e.studentid, u.hoursxfer > from enrollmentstatus e, undergradclass u > where e.studentid = u.studentid and e.classtd = '1' > order by u.hoursxfer > > But, it returns error message "ERROR: parser: parse error at or near > "order"". Does "create table as" support "order by" inside of it? > > I am using PostgreSQL 6.5.3. When you CREATE TABLEs you don't say anything about ordering. I should say it doesn't matter what the order is. When you SELECT from the table you can use ORDER BY.
[SQL] Re: logging a psql script
On Wed, 21 Feb 2001, Ken Kline wrote: > Hello, >I would like my psql script to log everything that it does. > I set the following > > \set ECHO all > \o foo.txt > \qecho > > some sql, some ddl, etc... > > \o > > > But foo.txt only contains > > DROP > DROP > DROP > CREATE > CREATE > CREATE > On UNIX/UNIX-like machines, use the script(1) command (man script for details). Jeff -- Errors have occurred. We won't tell you where or why. Lazy programmers. -- Hacking haiku
Re: [SQL] greetings
Ken Kline <[EMAIL PROTECTED]> writes: > I have just joined the list a few days ago and am trying quite hard > to come up to speed with pgsql but i find documentaion frustratiing. > I think maybe it;s just a matter of finding things that are of the > correct > scope. I've been an Oracle developer for over 6 years so often I > know what it is I want to do but something is just a little different. > If there are others on the list that learned in Oracle then pgsql > please tell me what you think are the best resources. > > Recently I did a google search on the key words "postgresql cursor loop" > > the example below is all I could come up with but it doesn't seem > to work is this for an older version or am I just overlooking > something simple? > > thanks > > Ken > > DECLARE emp_cursor CURSOR FOR > SELECT Salary, Title, Start, Stop > FROM Employee; > OPEN emp_cursor; > loop: > FETCH emp_cursor INTO :salary, :start, :stop; > if no-data returned then goto finished; > find position in linked list to insert this information; > goto loop; > finished: > CLOSE emp_cursor; PL/pgSQL does not support cursors. It also does not support goto. You can write the above as something like (untested): FOR emprec IN SELECT Salary, Title, Start, Stop FROM Employee LOOP IF no-data returned then EXIT; END LOOP; I have a patch adding cursors to PL/pgSQL. You can find it at http://www.airs.com/ian/pgsql-cursor.html This patch will not be in 7.1, but it may be in 7.2. However, it will not help you with the lack of goto. You shouldn't really use goto for a simple program like your example. But perhaps you are doing something more complex in your real code. Ian
Re: [SQL] greetings
Ian Lance Taylor <[EMAIL PROTECTED]> writes: > PL/pgSQL does not support cursors. It also does not support goto. The context is pretty unclear here, but perhaps he needs ecpg not plpgsql ... is this to be client- or server-side code? regards, tom lane
