[SQL] Multiple Databases
Is it possible to create a view using tables from two different postgresql servers? Thanks Peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] grouping and first()
Hi, just wondering if anyone can tell me what replaces the first function in access. Thanks PEter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Multiple Databases
thanks for your help guys. Peter "Joe Conway" <[EMAIL PROTECTED]> wrote in message news:3DB6D01C.9000601@;joeconway.com... > Josh Berkus wrote: > >>Is it possible to create a view using tables from two different > >>postgresql > >>servers? > > > > No. > > > > It isn't possible with plain old SQL, but it is possible (albeit ugly) using > contrib/dblink in PostgreSQL 7.2.x. See README.dblink for documentation and > examples. > > In PostgreSQL 7.3 (in beta testing now) the syntax and usability of > contrib/dblink is greatly improved. > > Joe > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] subscrip out of range
i am getting this error when iimport data using the pgaccess client from a txt file. I will import some records then crash out. Any IDeas? has it got anything to do with the primary key on the destination table and if so how do you temporaryily disable it Thanks for any hellp PEter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] multiple databases
I need to create a recordset in vb which combines a view/table from 2 different databases. Is this possible? in mssql you could go dbo.databasename.tablename. Can it be done in vb code? Thanks for any help Peter ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] postgresql and java2
First sorry for the late reply, I only get this list from home, and just got email working again. I was working on ARRAY and STRUCT for 7.0, but it a snag. I couldn't get a grips with what the spec meant, and couldn't work out how the SQL3 ARRAY type relates to our arrays. From the documentation I have (JDBC API Tutorial and Reference, Second Edition) it mentions things like CREATE TYPE statements, etc. I'm not sure if we could implement them purely within JDBC/Java. Peter -- Peter T Mount [EMAIL PROTECTED], [EMAIL PROTECTED]Homepage: http://www.retep.org.uk Contact details: http://petermount.comPostgreSQL JDBC: http://www.retep.org.uk/postgres/Java PDF Generator: http://www.retep.org.uk/pdf/ - Original Message - From: JavierG To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] Sent: Wednesday, August 02, 2000 9:20 PM Subject: [SQL] postgresql and java2 Hi: Does anybody knows if the JDBC driver maps the new SQL 3 types (ARRAY and STRUCT) with the proprietary array and user-defined types of PostgreSQL? To put it another way: when I access an array (for instance) in PostgreSQL through the JDBC driver, does the driver returns a SQL 3 ARRAY (java.sql.Array) or a Java Object (java.lang.Object)? I’m sorry if this sounds “newbie”, but this is the first time I use an ORDMS… Thanks in advance J.
Re: [SQL] Web Hosts (off-topic)
At work, were using dedicated-servers.co.uk for our new sites. They use Redhat 6.2, but I compiled both php4 and PostgreSQL 7.0.2 and it's running smoothly. -- Peter T Mount [EMAIL PROTECTED] http://www.retep.org.uk PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/ Java PDF Generator http://www.retep.org.uk/pdf/ On Thu, 14 Sep 2000, Tim Quinlan wrote: > The Hermit Hacker can ignore this (thanks). > > I'm looking for a web host that provides Postgres, and runs on *nix or > *BSD. Does anyone have any positive, un-solicited testimonials (or > visa-versa any horror stories)? If you have a story to tell me, but you > don't feel that it is appropriate to post to the group, please don't > e-mail the group, just e-mail me. > > Any feedback is welcome. >
Re: [SQL] installing pgaccess
Keith Wong writes: > Anybody know how to compile pgaccess from postgres source files? > It must be a configure option, but I can't find it. --with-tcl --with-x -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] transactions surrounding extension functions
Forest Wilkinson writes: > Does this mean that when I call a function I wrote, which is composed > of several queries, each of those queries will be executed in its own > transaction? No > Or, will the statement containing the function call be executed in its > own transaction, thereby including all the queries composing my > function in that same transaction? Yes -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] invoke an external shell script from a function
Chau, Artemis writes:
> Does anyone know how to invoke an external shell script from a function when
> it is called from a sql statement?
If it's a C function, use system("file").
--
Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Transaction isolation level for plpgsql
Keith Wong writes: > Does anybody know the transaction isolation level default when a plpgsql > stored procedure is called? > Is it possible to set it? Or is this controlled by SPI? It's whatever the transaction that called the function was set to. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] Re: [GENERAL] All function parameters become NULL if one is?
Joel Burton writes: > I have a function that takes two text parameters. If both of these > are non-null, everything works fine. However, if one of these two > parameters is null and one is not, the function receives both as null. Worse, no matter what the function does, the return value is also null. > Is this intentional? Is there a way around this? Wait for 7.1. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Object features of pg
On Tue, 3 Oct 2000, Michael Ansley wrote: > I've done the following: > > create table address (address varchar(50), postcode varchar(9)); > create table client(id integer, name varchar(30), address address); > > Now, how the hell do I get information into the address field of client? > There appears to be very little in the manual dealing with PGs object > features. Any pointers to places in the manual, or direct instructions > would be gratefully accepted. Something like: select id,name,a.address,postcode from client c, address a where c.address=a.oid; Probably neater ways of doing it... Peter -- Peter T Mount [EMAIL PROTECTED] http://www.retep.org.uk PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/ Java PDF Generator http://www.retep.org.uk/pdf/
RE: [SQL] Object features of pg
On Tue, 3 Oct 2000, Michael Ansley wrote:
> Hi, Peter,
>
> I suspected as much of the querying, but I can't even get data into the
> tables. I tried this:
>
> insert into client values ('Michael');
> insert into address values ('11 Windsor Close, 'RH16 4QR');
> INSERT 18935 1
> update client set address = 18935 where name = 'Michael';
> ERROR: Attribute 'address' is of type 'address' but expression is of type
> 'int4
> '
> You will need to rewrite or cast the expression
>
> So that didn't work, and I tried casting the oid, but that didn't seem to
> work either. Any ideas?
Hmmm, try:
update client set address = 18935::oid where name = 'Michael';
Peter
--
Peter T Mount [EMAIL PROTECTED] http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/
Re: [SQL] createuser
Craig May writes: > createuser -adq username > > returns > > invalid option adq createuser -a -d -q -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Granting of permissions on tables
Saltsgaver, Scott writes: > GRANT ALL on to ; > > after running the command I lost permissions to the tables once I exited > psql. I had to run psql as the postgres user to correct the situation. > > Is this a bug or desired behavior? It's a bug. Fixed for 7.1. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Loading JDBC Driver
On Thu, 5 Oct 2000, Jos Hernndez Zavala wrote:
> Hello.
>
> I am trying to load a jdbc driver for postgres, only I
> want to make it from an Applet. For an application I
> used the form:
>
> Class.forName ("postgresql.Driver");
>
> Which the correct form would be to be able to make it?
For 6.x drivers you are correct. For 7.x and later it's
Class.forName("org.postgresql.Driver");
PS: For applications, you have two other methods available. One supplying
the driver in the command line, ie:
java -Djdbc.driver=org.postgresql.Driver my.application
The other is using Manifest in your application's jar file, which works
well under Windows.
Peter
--
Peter T Mount [EMAIL PROTECTED] http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/
Re: [SQL] Standard syntax?
Franz J Fortuny writes: > select col1, > case when col2 = true then > col3 > else > col4 > end as colw, > colM > where etc. > is this a "standard" or ANSI 9X compatible syntax Yes. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] JDBC Performance
On Mon, 16 Oct 2000, Josh Berkus wrote: > Mr. May, > > For discussions of JDBC, please subscribe to the pgsql-interfaces > list. You will find many JDBC users on that list. > > -Josh Berkus > > P.S. PGSQL folks, is there any way we can clarify this on the web page? > The JDBC users seem to keep ending up on this list. I think it needs to be mentioned somewhere on the postgresql site. I've had this on my site for the last 3 years ;-) Also, I only get the sql & general lists here at home (retep.org.uk), but get interfaces at work as well so I can sometimes answer it faster if it's posted to interfaces. Peter -- Peter T Mount [EMAIL PROTECTED] http://www.retep.org.uk PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/ Java PDF Generator http://www.retep.org.uk/pdf/
Re: [SQL] problem with select where like ']'
Joseph Shraibman writes: > The only way I can find is to do a search on something, and select to > search in mailing lists. Then after the search returns click on a link, > and trucate the url to http://www.postgresql.org/mhonarc/ When in doubt, use geocrawler.com. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Alternate Database Locations
Brian C. Doyle writes: > Okay I am still doing something wrong here > > I set PGDATA2=/home/user1/database > export PGDATA2 > > then I start postmaster > postmaster -d PGDATA2 -i -p 5431 & postmaster -D $PGDATA -i -p 5431 >logfile 2>&1 and i get > > Can't create pid file: /usr/local/pgsql/data/postmaster.pid > Is another postmaster (pid: 10686) running? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Synonyms
Carl Shelbourne writes: > * Is there a way of creating synonyms of tables within one database in another > database in Postgres similar to the Informix CREATE SYNONYM? Nope. > * If not and related to the post on 2000-10-28 by Sivagami, is there a way, > within a select I can make a query from mulitple databases. ( the usual > DB.Table or DB:table or DB@table dont seem to work) Nope. It's not really planned either in case you wanted to ask. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Postgres 7.0.X and arrow keys
Antti Linno writes: > I installed new Mandrake 7.2 and was eager to try new postgres. So I > installed it and used pgsql. What surprised me was that the arrow keys > wouldn't work anymore as history, instead I get those ascii codes. You need to install the readline-devel RPM. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] plperl
Jie Liang writes: > "../../../src/Makefile.global", line 304: Need an operator > make: fatal errors encountered -- cannot continue > su-2.04# > > what I need to do? Use GNU make. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Decimal vs.Numeric vs. Int & type for OID
Nikhil G. Daddikar writes: > It is unclear to me what the real difference is and when to use what. > Any pointers would be greatly appreaciated. Integers are integers (uh...) Numeric is for exact fixed-point decimal numbers. Monetary amounts are a good example. Decimal is equivalent to numeric. The oid type should only be used as a foreign key to an oid column. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Documentation for CASE
Albert REINER writes: > I think that the documentation for CASE is not very clear: Maybe you will like this better (from upcoming 7.1): http://www.postgresql.org/devel-corner/docs/postgres/functions-conditional.htm -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] trying to pattern match to a value contained in a column
Beth Gatewood writes: > So-If I had a table where I had LONG_NAME and ABBR as attributes. > > I want something like > > SELECT whatever FROM my_table WHERE long_name LIKE '%[the value of ABBR > in that row]%'; SELECT whatever FROM my_table a, my_table b WHERE a.long_name like (b.abbr || '%'); -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] MD5 use in PL/Perl
Marc Rassbach writes:
> Attempting to xlate to PL/Perl
>
> settle=# create function md5out3(varchar) returns varchar(32) as '
> settle'# use MD5;
> settle'# my $mdval = new MD5;
> settle'# my $result ;
> settle'# my $out;
> settle'# $mdval->add($_[0]);
> settle'# $result = $mdval->digest();
> settle'# $out= unpack("H*" , $result );
> settle'# return $out;'
> settle-# LANGUAGE 'plperl';
> CREATE
> settle=# select md5out3('fred');
> ERROR: creation of function failed : require trapped by operation mask at
> (eval 6) line 2.
You can't use external modules ("use", "require") for security reasons.
FWIW, if I were to write an MD5 function then I'd take one of the
implementations floating around (mhash, Kerberos, OpenSSL, RFC) and make a
C function wrapper around it.
Incidentally, someone has already done this for the upcoming 7.1 release,
but since the function call interface has changed the back port won't be
trivial.
--
Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Rules
Peeter Smitt writes: > CREATE RULE update_rule AS ON UPDATE TO table DO INSTEAD > SELECT fun1(new); > > Thing is that backend gives this error. > ERROR: parser: parse error at or near ")" Try double quoting "new", since it's a reserved word. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Looking for comments
Thomas SMETS writes: > > ISBN's have a checkdigit; it would be sensible to provide a > function to be used in a CHECK constraint to ensure that the > ISBN is valid. > > > OK I'll start searching for it but haven't implemented yet In the PostgreSQL distribution there's a directory contrib/isbn_issn that defines a couple of data types that might help you. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] psql -f option
Graham Vickrage writes: > The documentation says -f enables some nice features such as error messages > with line numbers. It seems to me that this is half true i.e. it shows me > error messages, its doesn't however give me the associated line number in > the script. Works here: $ psql -f /etc/sysctl.conf psql:/etc/sysctl.conf:8: ERROR: parser: parse error at or near "#" It looks to me like you are using an older psql, perhaps from a previous installation. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Casting
Thomas SMETS writes: > I'm removing charaters from a String which should be numbers. > I then want to make calculations on these numbers (calculate the ISBN > number). (You might want to look into contrib/isbn_issn for an isbn type.) > Do I have to cast the char into int's before I can do the calulations. Depends on the calculation. I'd just try to see if it works. When in doubt add casts. > Also I looked in the User manual but could not find the modulo function > where is it ? 5 % 4 mod(5, 4) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] how to select a time frame on timestamp rows.
[EMAIL PROTECTED] writes: > today i was trying to perform a query on a database using a time stamp > field, i need to get all records which belong to year 2000, month 11, > is there any other way to doit, or is this the pgsql way? , actually > i'm using a query like this: > select User_Name from tbacct where acct_timestamp like '2000-11%' group > by User_Name; select user_name from tbacct where extract(month from acct_timestamp) = 11 ... (SQL compliant) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] single byte unsigned integer datatype
Joe Conway writes: > I was looking for a datatype to represent a single byte unsigned integer. > The closest thing I can find looking through the online manual is a one byte > char. Are there any side-effects of using a char datatype for this purpose? Yes, it won't store single byte unsigned integers, only single byte signed characters. > Is there a better datatype to use? smallint with a check constraint -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] Re: [INTERFACES] outer join in PostgreSql
Mauricio Hipp Werner writes: > I need help, which is the symbol used in postgreSql to carry out the outer > join. > > in oracle the is used (+) > in sybase the is used * and > in postgreSql? No symbol, just words. http://www.postgresql.org/devel-corner/docs/postgres/sql-select.htm -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] select returns no line
Attila Kevei writes: > goodwill=>select * from users where user_login='test'; > user_id|user_login|user_passwd|user_exp > ---+--+---+ > (0 rows) > > goodwill=> select * from users where user_id=4; > user_id|user_login|user_passwd |user_exp > ---+--+-+ > 4|test |0PDv7a2EESjZo| > (1 row) You probably started the postmaster with two different LC_COLLATE (locale sort order) settings. The setting that was active when you ran initdb must be kept when you run the postmaster. > goodwill=> update users set user_login=user_login where user_id=4; > UPDATE > 1 > > goodwill=>select * from users where user_login='test'; > user_id|user_login|user_passwd |user_exp > ---+--+-+ > 4|test |0PDv7a2EESjZo| > (1 row) Yep, the update will fix the corrupted index (at least to the extent that this particular case now works). -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] wrong query plan in 7.1beta3
Kovacs Zoltan writes: > There seems to be an optimizer problem in 7.1beta3. The query you can see > below worked fast in 7.0.2 but in 7.1beta3 is rather slow. The problem is > that an 'index scan' has been changed to a 'seq scan'. Details: > Subquery Scan sd_user_grant (cost=38.68..38.85 rows=1 width=61) > -> Aggregate (cost=38.68..38.85 rows=1 width=61) > -> Group (cost=38.68..38.73 rows=10 width=61) > -> Sort (cost=38.68..38.68 rows=10 width=61) > -> Nested Loop (cost=0.00..38.51 rows=10 width=61) > -> Seq Scan on pg_shadow (cost=0.00..1.01 rows=1 >width=32) > -> Seq Scan on sd_grant (cost=0.00..20.00 rows=1000 >width=29) You haven't VACUUM ANALYZE'd the sd_grant table. Therefore the row estimate is way off (1000 vs 6) and thus a sequential scan is (correctly) thought to be faster. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] binary operators
Frederic Metoz writes: > I am looking for the binary AND and OR ... SHIFT as well. > Do they exist for postgresql ? in 7.1 -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Suggestion for psql: --file -
Albert REINER writes: > wouldn't it be a good idea (and if it is, I am not sure what list to > post it to) to have psql's option -f / --file take "-" for stdin, as > many programs do? Seems reasonable. > P.S: BTW, the man page (7.0.2) of psql is not very clear: it took me > some experimentation to find out that you have to do "\set VARIABLE" > interactively or give "--set VARIABLE=" to set a variable that does > not take a value. Suggested new wording? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] 7.0.2-docs: textpos -> strpos
Albert REINER writes: > in the 7.0.2-docs I find the function textpos: > However, in psql it seems one has to use strpos: textpos() was removed from the 7.1 documentation. position() is the SQL function, I think strpos() is from Oracle. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Suggestion for psql: --file -
Albert REINER writes: > > Suggested new wording? > > What about: Works for me. Thanks. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] SQL Join - MySQL/PostgreSQL difference?
Brice Ruth writes: > SELECT > a.Number, > a.Code, > a.Text > FROM > b, > a > WHERE > (b.Id = a.Id) AND > (VersionId = 'key1') AND > (Category = 'key2') AND > (b.d_Id = 'key3') > ORDER BY > a.Number; > > (my apologies: I had to 'mangle' the table/column names because of NDA) > > So my question is this: would this query operate differently in MySQL > than in PostgreSQL? The reason I ask is that this query in MySQL > returns results, yet in PostgreSQL it does not. Without showing the tables and the data in it, it's fairly hard to tell. I think MySQL does case insensitive string comparisons; check that. > I read a post about PostgreSQL not supporting outer joins, but I don't > have enough experience with SQL to determine if this is such a query > or not. Please advise. This is not an outer join. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] PL/pgsql EXECUTE 'SELECT INTO ...'
Tom Lane writes: > I am inclined to keep our options open by forbidding EXECUTE 'SELECT > INTO ...' for now. That's more than a tad annoying, because that leaves > no useful way to do a dynamically-built SELECT, but if we don't forbid > it I think we'll regret it later. You can always use CREATE TABLE AS. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Wierd postgres Problem
Najm Hashmi writes: > cmdb=# create table media_received ( > cmdb(# comp_id not null, > cmdb(# dept_id not null, ^ Those two fields should have a data type. Same in your other examples. > cmdb(# date_rec timestamp default 'now', > cmdb(# units int4 default 0, > cmdb(# media_type varchar(64), > cmdb(# enqued int4 check (enqued= cmdb(# encoded int4 check(encoded= cmdb(# insys int4 check(insys= cmdb(# constraint media_rec_pk primary key(comp_id,dept_id,date_rec), > cmdb(# constraint media_dept_fk foreign key(dept_id) references department, > cmdb(# constraint media_comp_fk foreign key(comp_id) references company > cmdb(# ); > ERROR: parser: parse error at or near "not" -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] combining
Frank Morton writes: > These two queries get me what I want from each table: > > select unique id from table1 where lastname='morton'; > select unique id from table2 where ((type = "pie") and (content = 'apple)); > > What is the best way to combine these into one query? select table1.id from table1, table2 where table1.id = table2.id and lastname = 'morton' and type='pie' and content = 'apple'; Or in 7.1, optionally: select id from table1 natural join table2 where lastname = 'morton' and type ='pie' and content = 'apple'; -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] PL/PGSQL Cook Book
mark proctor writes: > I've been trawling through the mailist lists and I notice there was > talk back in 1999 abouta PLPGSQL Cook Book - did anything come of > this? If no one is maintaining something like this and people think > its a good idea I think we should have another crack at it. I'd be > happy to maintain something like this and put it up on the web, > although I'm only a newbie and would rely upon user contribution. Start writing, and send it to [EMAIL PROTECTED] It doesn't have to be a great work of literature; incremental work is just fine. >How can I create Tree structures? >Are recursive functions supported? >Whats are the advanteds disadvantes of the different scipting langues PL/PGSQL, >PL/Perl, PL/Tcl? >How do variable scopes work in PL/PGSQL? >Can I output variables from a function to the command line for debugging purposes? >How to debug PL/PGSQL? >Various examples for each of the statements All valid questions. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] COPY isn't working right for me
Jeff S. writes: > create table member ( > member_id serial not null, > fname varchar(25) not null, > lname varchar(25) not null, > member_since date not null, > Primary Key (member_id) > ); > My member.txt file looks like this: > > Joe Smith 2000/01/14 > Frank Jones 2000/06/21 > Mike Davis 2000/09/24 > > Here's the copy command I use: > > COPY member FROM '/tmp/member.txt'; > > But I'm getting the following error: > ERROR: copy: line 1, pg_atoi: error in "Joe": can't > parse "Joe" > > I'm assuming this has to do with the member_id with > type serial. How do I import into this without having > to add the OID's to each of the rows in the text file? You can't, using COPY. You'll have to preprocess your file, either into INSERT statements, are prepend your own id's. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] cannot create sequence
Olaf Marc Zanger writes: > as it seems postgresql 7.0 has trouble to create > > ver_id_seq Define "trouble". -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] Re: [ADMIN] TPCH questions
Victor Muntes Mutero writes: > select * from (select * from supplier); (for example) No subselects in FROM before 7.1. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Contributing Documentation to PG
Roberto Mello writes: > I finally came around to writing some extra documentation for PL/PgSQL > (and maybe PL/Tcl) and how to do PL/SQL-to-PL/PgSQL porting. I saw this on your site -- very nice. Can you give use the source code, so we can integrate it? > I downloaded the documentation sources, intending to use its nice > configure/make scheme but I can't find the configure script as described > in the current docs. Where can I find it? The same configure script that you use when you build the PostgreSQL software itself, the one in the top level directory. > I know I can just use the Makefiles with a little tweaking, but I am > wondering if I am missing something here. No tweaking should be required, if you have your SGML catalogs set up correctly. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
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] Weird NOT IN effect with NULL values
Frank Joerdens writes: > When doing a subselect with NOT IN, as in > > SELECT name > >FROM customer > WHERE customer_id NOT IN ( > SELECT customer_id > >FROM salesorder > ); > > (from Bruce Momjian's book) > > I get no rows if the result column returned by the subselect > contains NULL values. It works as expected if I remove the NULL values > >from the result set. Is this behaviour correct and if so, why? It is correct. customer_id NOT IN (value1, value2, value3, ...) (which is what the subselect would essentially resolve to) is equivalent to NOT (customer_id = value1 OR customer_id = value2 OR customer_id = value3 ...) Say value2 is NULL. Then we have NOT (customer_id = value1 OR customer_id = NULL OR customer_id = value3 ...) NOT (customer_id = value1 OR NULL OR customer_id = value3 ...) NOT (NULL) NULL which means FALSE in a WHERE condition, so no rows are returned. Note that 'xxx = NULL' is different from 'xxx IS NULL'. Also note that NULL is not the same as FALSE in general. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] SQL copy from csv with explicit field ordering
Terry Fielder writes: > Is there somewhere that I can either enable the first line of CSV as > header names > > OR > > Can I explicitly define my import field ordering from within the select > statement? No and no. You will have to preprocess your file. Something like this will probably do: #! /bin/sh IFS=, cat "inputfile" | \ while read COL1 COL2 COL3; do echo "$COL3,$COL2,$COL1" >> "outputfile" done -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: [GENERAL] Date question
Boulat Khakimov writes: > Does anyone know how to get date in format '-MM-DD' of a date one > year from now. > So for example today is '2001-03-06' I need to get date 12 months from > now > which will be '2002-03-06' in todays case... > > In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that > doesnt work in PG. How about CURRENT_DATE + INTERVAL '12 months'? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] cannot get CREATE TABLE AS to work
Creager, Robert S writes: > psql -d tassiv -c "\ > create table observationsII ( \ > ra float8 not null, \ > decl float8 not null, \ > mag float8 not null, \ > smag float8 not null, \ > obs_id serial, \ > file_id int4 references files on delete cascade, \ > star_id int4 references comp_loc on delete set null default null ) \ > AS select o.ra, o.decl, o.mag, o.smag, o.file_id from > observations o" > > ERROR: parser: parse error at or near "AS" Either you have an AS clause, or a column list, not both. > And the next question, should this really be taking 3 hours to insert 315446 > records? I noticed the disk is basically idle during the few times when I > watched. Would this be because of the index created on obs_id? Quite likely. Also make sure you wrap the inserts into a BEGIN/COMMIT block. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] recompiling to use gnu readline?
Andrew Perrin writes: > I inadvertantly compiled pg 7.0.3 without gnu readline installed, so I now > don't have access to lots of the nice command-line utilities it would have > conveyed. Is there any way to add in the functionality now, or do I need > to recompile? Need to recompile. > If I need to recompile, how can I do so without wiping out > existing data? Just don't wipe it out. You can use the new executable with existing data. In fact you only need to recompile psql (src/bin/psql) after re-configuring, not the postmaster. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PL/PgSQL and NULL
Andrew Perrin writes: > I'm trying to write what should be a simple function that returns the > minimim of two integers. The complication is that when one of the two > integers is NULL, it should return the other; and when both are NULL, it > should return NULL. Functions involving NULLs don't work well before version 7.1. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Fwd: Silly question about numbering of rows?
Ken Corey writes: > It sounds easiest to me to just punt and number the rows as they are returned > in my calling application... The rows are automatically numbered. In libpq, you'd use PQgetvalue(result, rownumber, fieldnumber). Other interfaces have similar functions. If the interface has a "fetch"-like approach, it seems trivial to keep a counter. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PL/pgSQL "compilation error"
Bruce Momjian writes: > There is a PL/PgSQL HowTo that we are trying to integrate into the docs > for 7.1. Anyone have a URL or status on that? It's not a PL/pgSQL Howto, it's just a documentation how to port from Oracle's PL/SQL. The status is "done". The author expressed interest in more work on the PL/pgSQL documentation; we'll see what comes of it. http://www.postgresql.org/devel-corner/docs/postgres/plpgsql-porting.html -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Postgres & XML
Najm Hashmi writes: > Hi all, I was just wondering if there is way or some sort of utility to > incorporate XML in postgrres. This question is about as generic as "Is there a way to incorporate ASCII in Postgres?" Depending on what you want you might find Cocoon useful. See <http://xml.apache.org/cocoon/index.html>. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Toggle a Bit type ie not field
Here's the SP that toggles a Bit datatype. How would I make it comething like ??? update pcpmanufacturers set def = not (def) where ManufID = @ManufID -- create Procedure pcp_manufToggle @ManufID int as Set nocount on declare @S as bit select @s = def from pcpManufacturers where ManufID = @ManufID if (@s = 1) begin update pcpManufacturers set def=0 where manufID = @ManufID end else begin update pcpmanufacturers set def=1 where manufid = @manufID end TIA Pete ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Serials.
Grant writes: > (1) Why is a sequence limited to 2147483647, it seems very small? Because that's what a four-byte signed integer takes. No one has stepped forward to implement 8-byte sequence counters, yet. > (2) If I reset the sequence, then try another insert. It will not insert > anything until it cycles through all sequences and finds an unused one. It > will give the following error each time it tries to insert a row with a > used sequence: > > PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique > index releases_pkey Why did you reset the sequence in the first place? You should probably set it back to where it was (using setval()). Sequences simply return incrementing numbers, they don't fill holes or have any constraint-avoiding logic. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] triggered data change violation
Tom Lane writes: > Cedar Cox <[EMAIL PROTECTED]> writes: > > Added note: The trigger is a BEFORE trigger. > > AFAIK the "triggered data change" message comes out of the AFTER trigger > code. You sure you don't have any AFTER triggers on the table? Perhaps > ones added implicitly by a foreign-key constraint? A "triggered data change violation" happens everytime you change twice within a transaction a value (column) that is part of a foreign key constraint (don't recall exactly which part). This error shouldn't really happen, but I recall there were some implementation and definition problems with deferred constraints. ...FAQ alert... -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Help
Mohamed ebrahim writes: > I am a user postgresql. I want to update a table > automatically when we reach monthend. i.e i want to > update some table on 31 of every month automatically > without any user attention. Use a cron job. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] paging
A. Van Hook writes: > #!/usr/bin/expect > > set query [lindex $argv 0] > set query [lindex $argv 1] > > spawn psql imdb -U hook -n > expect "Password: "; > send "$passwd\n "; > expect "imdb=# "; > send "$query;\r"; > expect "imdb=# "; > send "\q;\r"; > > The problem is that long query returns invoke the paging option so you > can scroll a page a time. Paging doesn't work properly within the > script and even if it did , I would have to disable it in order to > redirect the output to a file or a pipe. Try psql -P pager to turn the pager off. Also you can probably use export PGPASSWORD=xyz psql ... to skip the password probe. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Calling Java from psql (was Re: [SQL] requesting help)
Mathijs Brands writes: > Has anybody ever tried calling Java code from a pgsql trigger written > in C? Shouldn't this be possible using JNI? I have, and given the current Java implementations it's a desaster. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Replace into...?
Diehl, Jeffrey writes: > I'm in the process of migrating a database and application suite from mysql > to postgresql. The problem is that I've used > mysql's "replace into..." quite frequently... begin transaction; update ... insert ... commit; or some permutation thereof. If you need to do a lot of this, writing a PL/pgSQL function might be worthwhile. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: Calling Java from psql (was Re: [SQL] requesting help)
At 10:33 26/03/01 +0200, Mathijs Brands wrote: >Has anybody ever tried calling Java code from a pgsql trigger written >in C? Shouldn't this be possible using JNI? This was discussed recently. >I'm not exactly a Java expert myself, but this is the way PHP allows >you to call Java code from your PHP scripts. It may be something to look at when things quieten down a bit. I'm rushed of my feet at the moment (busy at work, long hours etc) but once 7.1 is finally out of the door, I may get the chance to start looking at JNI (& my employers don't mind me spending some time working on postgres when I'm not on other projects either which is nice ;-) ) PS: the JDBC list is the best place for java based emails - I rarely read sql ;-) Peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Calling Java from psql (was Re: [SQL] requesting help)
At 21:37 26/03/01 +0200, Mathijs Brands wrote: >On Mon, Mar 26, 2001 at 07:00:43PM +0200, Peter Eisentraut allegedly wrote: > > Mathijs Brands writes: > > > > > Has anybody ever tried calling Java code from a pgsql trigger written > > > in C? Shouldn't this be possible using JNI? > > > > I have, and given the current Java implementations it's a desaster. > >That bad eh? Well, I must admit I couldn't get the PHP-Java coupling to >work stable either :( Not having looked at the PHP-Java link, are they working as one process or is it some IPC type link? PeterM ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Calling Java from psql (was Re: [SQL] requesting help)
A James Lewis writes: > Before I go investigating this, is it possible to trigger an arbitrary > program from the SQL, say a shell script? At the lowest level, you can generally do anything a C program can do. Writing the equivalent of system() in SQL should be rather trivial. You can also take a look at my little toy project PL/sh: http://www.postgresql.org/~petere/plsh.html -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Calling Java from psql (was Re: [SQL] requesting help)
Tom Lane writes: > I'm generally pretty suspicious of any system design that requires > calling outside programs from an SQL function. The problem is that > this fundamentally breaks transactional semantics: if the transaction > is rolled back after the function call, its effects inside the database > disappear ... but there's no way to roll back whatever the outside > program did. Now you have a consistency problem. The trick here is to organize your outside programs into fairly atomic chunks and do conservative error logging. The need to synchronize the non-database world with the database is definitely real. But usually a regular function call near the end of the transaction block is much more appropriate than a trigger function. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 'Include' function in SQL scripts
Alessio Bragadini writes: > I was wondering if PostgreSQL supports some kind of #include between SQL > script files, to split a long script in different files. If you're using the psql client then \i filename can be used. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Max Size of a text field
Koen Antonissen writes: > I just recieved this error: > 'Warning: PostgreSQL query failed: ERROR: Tuple is too big: size 13872, > max size 8140 ' > > Is there anyting I can do about that other than tell my users just not > typing so much text into the field? Option 1: increase BLCKSZ in src/include/config.h Option 1.5: use lztext (7.0* only) Option 2: upgrade to 7.1 -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] primary key scans in sequence
bernd writes: > hey i have the following table def (834.000 rows, vaccum analyze'd): > dl_online=# \d mitglied > Table "mitglied" >Attribute| Type | Modifier > +--+ > mitgliedid | bigint | not null > dlnummer | varchar(30) | > vorname| varchar(50) | > zuname | varchar(50) | not null > geburtsdatum | varchar(20) | > aktiv | boolean | not null default 't'::bool > strasse| varchar(255) | > plz| varchar(25) | > ort| varchar(255) | > telefon| varchar(255) | > eintrittsdatum | varchar(20) | > geschlechtid | bigint | not null default 3 > treuelevelid | bigint | not null default 1 > clubmitglied | boolean | not null default 'f'::bool > bemerkungen| text | > mid| bigint | > > Indices: mitglied_dlnummer_idx, [on dlnummer] > mitglied_pkey [on mitgliedid] > > 2) get one row over the primatry key (mitgliedid): > dl_online=# explain select * from mitglied where mitgliedid = 833228; > NOTICE: QUERY PLAN: > Seq Scan on mitglied (cost=0.00..18674.74 rows=1 width=154) > EXPLAIN This is because the system is not smart enough to match up a bigint = integer comparison with an index scan on a bigint column. Quoting the number, '833228', should fool it sufficiently to make this work. Incidentally, it seems unlikely that you need to use bigint for membership ids, unless you plan on more than 2*10^9 members. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] performance of functions - or rather lack of it
We are building a postgresql based backend database for our 'hosting provisioning' system. In a vain attempt to add some, what I thought, simple performance tweaks, I thought I would try putting some of the larger and more straighforward queries into functions. For everything else the same, the functions are on the whole slower. Should they be ? The whole thing is being driver through perl DBI. This may be contributory. Anyhow, the original query: SELECT COUNT(mb.instance) FROM domain dm, mail ms, mailbox mb WHERE dm.enabled = true and dm.existent = true and dm.available = true AND ms.enabled = true and ms.existent = true and ms.available = true AND mb.enabled = true and mb.existent = true and mb.available = true AND dm.parent = ms.parent AND mb.parent = ms.serviceid AND dm.instance = $q_domain AND mb.instance = $q_local_part; where $q_XXX are quoted perl scalars. The function is then: CREATE FUNCTION mail_is_mailbox(text, text) RETURNS int4 AS ' SELECT COUNT(mb.instance) FROM domain dm, mail ms, mailbox mb WHERE dm.enabled = true and dm.existent = true and dm.available = true AND ms.enabled = true and ms.existent = true and ms.available = true AND mb.enabled = true and mb.existent = true and mb.available = true AND dm.parent = ms.parent AND mb.parent = ms.serviceid AND dm.instance = $2 AND mb.instance = $1;' LANGUAGE 'sql'; SELECT mail_is_mailbox($q_local_part, $q_domain); Running both these 1000 times from a remote (same subnet 100BaseTX) client with the same query results in time for the function typically 20 - 25% more than the bare query. 22 vs 16 seconds for example. I would have thought that not sending the long SQL across the wire 1000 times would have saved some time even without any potential query optimisations by pre-parsing the SQL ? rgds, -- Peter Galbavy Knowledge Matters Ltd. http://www.knowledge.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] performance of functions - or rather lack of it
BTW The service is 7.0.2 and the client 7.1RC1 and the OSes are OpenBSD/i386 2.8-stable. On Wed, Apr 04, 2001 at 11:12:34AM +0100, Peter Galbavy wrote: > We are building a postgresql based backend database for our 'hosting > provisioning' system. In a vain attempt to add some, what I thought, simple > performance tweaks, I thought I would try putting some of the larger and > more straighforward queries into functions. For everything else the same, > the functions are on the whole slower. Should they be ? -- Peter Galbavy Knowledge Matters Ltd http://www.knowledge.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] performance of functions - or rather lack of it
On Wed, Apr 04, 2001 at 01:01:15PM -0400, Tom Lane wrote: > Without knowing which PG version you're using, what plans you're > getting, or even whether you've VACUUM ANALYZEd lately, it's difficult > to say more than that. I followed that up quickly - server 7.0.2, client 7.1RC1. VACUUMed etc prior to the test. OTOH, since the tests were done multiple times directly after the other, the underlying infrastructure should be the same. BTW I deleted your paragraph above, but I agree about the parameters and the constant stuff. I will retry with a fixed-value function just for the completeness of it. > Unless your TCP connection is running across tin cans and string, > the transfer time for the query text is negligible ... Fair point. I am not really in the 100Mb networking work in my heart ... :-) -- Peter Galbavy Knowledge Matters Ltd http://www.knowledge.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] please help
Loïc Bourgeois writes: > What is the equivalent of the oracle request: SELECT ... FOR UPDATE > NOWAIT, under PostGreSQL I don't know Oracle, but there doesn't seem to be such a command in PostgreSQL. If the table is already locked, the SELECT FOR UPDATE has to wait. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] how to do this join ?
[EMAIL PROTECTED] writes: > Hello there > > I have another SQL question. Please see the example : > > select *, > (select a_typ from auftrag where a_nr=z_a_nr) as typ, > (select a_t_definition_d from auftrags_typ where a_t_code=typ) as text > from zylinder select zylinder.*, auftrag.a_typ (select a_t_definition_d from auftrags_typ where a_t_code = auftrag.a_typ) from zylinder, auftrag where auftrag.a_nr = zylinder.z_a_nr; or, using 7.1, maybe something like select zylinder.*, auftrag.a_typ, auftrags_typ.a_t_definition from (zylinder join auftrag on a_nr = z_a_nr) left join auftrags_typ on a_t_code = a_typ Other variations are possible, depending on the referential contraints you have between the tables. > > I have three tables that I need data from. I'd like to use the to > temporary store the kind of auftrag and then use it to get the > definition (clear text) from another table. > > The query returns that typ is not known . -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Casting numeric to text
Hans-Jürgen Schönig writes: > Is there any possibility to cast numeric to text in Postgres 7.0.3? > > shop=# select cast(price as text) from products; > ERROR: Cannot cast type 'numeric' to 'text' Use the to_char() function. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] open and closed paths ...
Hans-Jürgen Schönig writes: > I am using PostgreSQL 7.0.3. I have tried the isopen() function but it > doesn't seem to work (or it is not fully implemented yet). > In my opinion the third record is a closed path but the isopen() > functions return f. For no good reason apart from ancient tradition, paths enclosed in parentheses, like ((3,1),(2,8),(10,4)), are implicitly closed. To make an open path brackets should be used, like [(3,1),(2,8),(10,4)]. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Casting numeric to text
Cedar Cox writes: > When would one want to use cast()? What is the difference between cast > and :: ? After a quick look in the documentation I couldn't find > anything.. cast() is SQL, :: is traditional Postgres. :: may go away in the distant future to make room for the SQL feature that is supposed to use ::. (It escapes me at the moment what that was.) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] please help
Cedar Cox writes: > It would be somewhat (very) useful to have something like this. We were > toying with the idea of making some sort of system to figure out if a > table is locked or not. This will probably introduce race conditions unless done very carefully. In theory you need a second level of locks to protect the information you obtained regarding the "real" locks. I'm not saying it's impossible, but 20 years ago people were writing Ph.D. theses about these sort of things. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: open and closed paths ...
Hans-Jürgen Schönig writes:
> The only problem left is the correct syntax of the command when using [].
> I've tried some versions but it did not work.
> Maybe Tom can include an example into the docs.
>
> Hans
>
>
> shop=# INSERT INTO temppath(fieldname) VALUES '((1,3), (4,12))';
> ERROR: parser: parse error at or near "'"
Should be VALUES ('((1,3), (4,12))'); The outer parentheses belong to the
INSERT command, the quotes delimit the data literal, whatever is inside
the quotes is the data type's business.
> shop=# INSERT INTO temppath(fieldname) VALUES ((1,3), (4,12));
> ERROR: parser: parse error at or near ","
> shop=# INSERT INTO temppath(fieldname) VALUES ('(1,3), (4,12)');
> INSERT 51947 1
> shop=# INSERT INTO temppath(fieldname) VALUES ['(1,3), (4,12)'];
> ERROR: parser: parse error at or near "["
> shop=# INSERT INTO temppath(fieldname) VALUES '[(1,3), (4,12)]';
> ERROR: parser: parse error at or near "'"
> shop=# INSERT INTO temppath(fieldname) VALUES '[(1,3), (4,12)]'::path;
> ERROR: parser: parse error at or near "'"
> shop=# INSERT INTO temppath(fieldname) VALUES ['(1,3), (4,12)']::path;
> ERROR: parser: parse error at or near "["
--
Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Casting numeric to text
Ross J. Reedstrom writes: > FYI, I can't find an occurance of '::' that's not part of '::=' in either > SQL1992.txt or the ansi-iso-[sql]-1999.txt files I've got. SQL 1999 6.12 ::= [ ] That syntax even makes sense... -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: select substr???
Tim Johnson writes: > Hi, > > I have postgres 6.x (where x is something). > > I have the following list of data > > data > > ABC* > ABC > ABC- > ABC+ > ABC > QWE~ > ASD+ > ASD# > KLASDHK- > KLASDHK+ > KLASDHK > KLASDHK* > > > what I want to do is 'select distinct(data) [ignoring non alphanumeric > characters] order by data' Write a function that strips off the suffixes and do 'select distinct voodoo(data) ...'. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Making SELECT COUNT(seed) FROM fast
Gerald Gutierrez writes: > I have a table with about 5 million rows in it. I need to be able to get the > exact number of rows in the table at runtime. So I tried the following: > > xxx=> explain select count(seed) from mytable; > NOTICE: QUERY PLAN: > > Aggregate (cost=103152.27..103152.27 rows=1 width=4) > -> Seq Scan on mytable(cost=0.00..89756.42 rows=5358342 width=4) > Actually executing this query takes about 2 minutes on a P3-800MHz machine > with 512MB of RAM. > > I have an index on the seed table, and I have done VACUUM ANALYZE on the > table after inserting the rows. Is there any way I can get this to be fast? For a count of all rows you necessarily need to visit all rows (at least in this implementation), so an index is of no use. For a sequential scan with little computation involved this really comes down to pure hardware speed. You might be able to speed it up a little by using count(*) instead. Note that there's a semantic difference, because count(seed) doesn't count the rows where seed is null, which is probably not what you intended anyway. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] DROP TABLE in transaction
David Olbersen writes: > I was wondering if anybody could explain to me why I can't roll back dropping a > table. Because DROP TABLE removes the table file on disk, and you can't roll back that. Actually, in 7.1 you can. ;-) > I would think that of all the events that should be rollback-able, > dropping a table would be the first on the list. Naah. Insert and update are first. ;-) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] enumerating rows
Kovacs Zoltan writes: > Maybe a trivial question, maybe it's foreign from SQL, I'dont know... > How to add a column which stands for the row number in each row of the > result? E.g.: > > row_no | column1 | column2 | ... > ---+-+-+ ... > 1 | datum11 | datum12 | ... > 2 | datum21 | datum22 | ... >... | ... | ... | ... > > I didn't find anything in the docs. Your client can do that. When you fetch the results you have to have some sort of loop anyway, so you have (or keep) a counter there. There's no use of the server generating this information, because the numbering is implied by the order in which the rows are sent. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] BOOLEAN data type?
Josh Berkus writes: > That the following query: > > SELECT detail_id FROM order_detials > WHERE order_usq = 7703 AND detail_required; > > ... should be equivalent to: > > SELECT detail_id FROM order_detials > WHERE order_usq = 7703 AND detail_required = TRUE; > > However, in testing (7.1 RC2), the query turned out to mean this: > > SELECT detail_id FROM order_detials > WHERE order_usq = 7703 AND detail_required IS NOT NULL; No way. You're doing something wrong. How about showing the data that makes you believe this? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Using Random Sequence as Key
Bernardo de Barros Franco writes:
> I wanted to index a table by a random key. Exemplifying, when a insert is
> made, the id value is automatically filled with a random number between
> 1 and 9.
=> create table test (id int default random() * 8 + 1, content text);
CREATE
=> insert into test (content) values ('hi');
INSERT 36163 1
=> insert into test (content) values ('there');
INSERT 36164 1
=> insert into test (content) values ('blah');
INSERT 36165 1
=> select * from test;
id | content
---+-
61616 | hi
72605 | there
83469 | blah
(3 rows)
Verifying the default expression for correct boundaries is left as an
exercise. ;-)
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Using Random Sequence as Key
Bernardo de Barros Franco writes:
> But my only question would be, in the example quoted would id be really the
> table index and is it unique?
Making unique *and* random numbers is a pretty complex (and slow) thing to
do in general. Consider redesigning your application.
>
> Thank you
>
> Quoting:
> >Bernardo de Barros Franco writes:
> >
> > > I wanted to index a table by a random key. Exemplifying, when a insert
> >is
> > > made, the id value is automatically filled with a random number between
> > > 1 and 9.
> >
> >=> create table test (id int default random() * 8 + 1, content
> >text);
> >CREATE
> >=> insert into test (content) values ('hi');
> >INSERT 36163 1
> >=> insert into test (content) values ('there');
> >INSERT 36164 1
> >=> insert into test (content) values ('blah');
> >INSERT 36165 1
> >=> select * from test;
> > id | content
> >---+-
> > 61616 | hi
> > 72605 | there
> > 83469 | blah
> >(3 rows)
> _____
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
>
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] function to format floats as money?
Mark Stosberg writes: > I'm curious to know if there is a function available in Postgres 7.0.3 > (or 7.1) that will format a float style number as "money". I understand > that the money type is going away in the future, so using a float type > that is sometimes formatted like money seems like a good alternative. So > ideally, I'm looking for a solution that won't go away when the money type > does. :) Thanks! to_char() for formatting. numeric for storage. Using floats for monetary amounts is not only an extremely bad idea because of the inexactness of storage and arithmetic, it might even be illegal if you're using it for official purposes. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] is this proper sql?
clayton cottingham writes: > hi one of the developers here at work say this should work > insert into detail (det_id,det_mas_id,det_date,det_amt) values > (0,0,now(),'0'), (1,1,now(),'1'); This is real SQL (except for the now() function) but it isn't supported in PostgreSQL yet. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] RI permission problem
Kyle writes: > Is there a way to get this to work without granting update to table b? Update to 7.1. > Tom, I understand someone was working on setuid functions. Is that a > long way off? It would be nifty if triggers could execute with the > privileges of the user that created them rather than the user who is > executing them. This would help greatly in closing some security holes > like this we are dealing with. It wouldn't be hard to implement, but there were some disputes about the particular method of how to do it. Maybe 7.2. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] RI permission problem
Kyle writes: > Peter Eisentraut wrote: > > > Kyle writes: > > > > > Is there a way to get this to work without granting update to table b? > > > > Update to 7.1.] > > I'm on 7.1. Should an RI trigger under 7.1 run as the DBA or as the current > user? Okay, we missed a few cases. Try the attached patch. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter Patch ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SET SEQSCAN TO OFF - error
Hans-Jürgen Schönig writes: > I have the command below like it is described in the documentation > (http://www.archonet.com/pgdocs/force-index.html) but it doens't work. > > SET SEQSCAN TO OFF; > ERROR: 'seqscan' is not a valid option name > > Does anybody know why? Because 'seqscan' is not a valid option name. Maybe you mean enable_seqscan. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] simulate union in subselect
Wei Weng writes: > I know this is not do-able, but is there any way to simulate the > following in Postgresql 7.1? > > select id from > (select id, recv_date as date from table1 > union > select id, send_date as date from table2) AS subtable > order by date; I haven't actually tried whether this does not work, but ISTM that you could simply do select id, recv_date as date from table1 union select id, send_date as date from table2 order by 2; and ignore the second column when processing the result. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Using Transaction Blocks w/ SELECT
[EMAIL PROTECTED] writes: > Goinging throught the libpq docs, I noticed that in all of the examples involving > select statements, transaction blocks are used. I see why this is necessary for > write operations, but I don't see the need in read operations that don't commit. Am > I missing something? Any help appreciated. It's done to get consistent data snapshots across several commands. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] '13 months ago'::reltime
Christopher Sawtell writes: > On Friday 04 May 2001 06:32, you wrote: > > Type reltime is old and deprecated. Don't use it. > > What should we be using instead? interval -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
