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] sum(bool)?
Olaf Zanger writes: i'd like to add up the "true" values of a comparison like sum(ab) sum(case when ab then 1 else 0 end) of maybe even just select count(*) from table where ab; -- 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/
[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] 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/
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] 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=units), cmdb(# encoded int4 check(encoded=enqued), cmdb(# insys int4 check(insys=encoded), 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: [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] 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: [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] 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: [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] 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/
[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] 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/
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] 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] 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] Looking for comments
Thomas SMETS writes: snip 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. /snip 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] 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] 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] 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] 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] Granting of permissions on tables
Saltsgaver, Scott writes: GRANT ALL on tables here to user2; 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] 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] 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/
[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] Database Transfer
Craig May writes: I have two servers running pgsql. Is there a command to transfer the databases between them? pg_dump and psql. "Back up" one database and "restore" it on the other server. Don't even think about moving files around. :) -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
[SQL] Re: [BUGS] Some problem with inet type on PostgreSQL-7.0
I can confirm your problem but there's no known fix. The truth is that the inet/cidr types have quite a number of bogosities but no one understands them well enough to undertake fixing them. Vadim Passynkov writes: Hi All, I have some problem with inet type on PostgreSQL-7.0 (FreeBSD 3.4-STABLE) Table "ipaddresses" Attribute | Type | Modifier --+-+ sysname | text| not null index| integer | not null ip_addr | inet| not null Indices: ipaddresses_ip_addr, ipaddresses_pkey (sysname, ip_addr) - PRIMARY KEY View "ipaddresses_view" Attribute | Type | Modifier --+-+-- sysname | text| index| integer | ip_addr | inet| ip_netmask | inet| View definition: SELECT ipaddresses.sysname, ipaddresses."index", ipv4_host(ipaddresses.ip_addr) AS ip_addr, ipv4_netmask(ipaddresses.ip_addr) AS ip_netmask FROM ipaddresses; ipv4_host and ipv4_netmask like original host and netmask but return inet type ( need for ORDER ) CREATE FUNCTION ipv4_host(inet) RETURNS inet AS ' BEGIN RETURN host($1); END; ' LANGUAGE 'plpgsql'; CREATE FUNCTION ipv4_netmask(inet) RETURNS inet AS ' BEGIN RETURN netmask($1); END; ' LANGUAGE 'plpgsql'; Problem select * from ipaddresses where sysname = 'switch01.tor'; sysname| index | ip_addr --+---+-- switch01.tor | 1 | 127.0/8 switch01.tor | 2 | 127.0/8 switch01.tor | 3 | 209.250.155.8/27 (2 rows) but (sysname, ip_addr) - PRIMARY KEY 127.0/8 - it's not correct output ( real 127.0.0.2/8 and 127.0.0.3/8) select * from ipaddresses_view where sysname = 'switch01.tor'; sysname| index |ip_addr| ip_netmask --+---+---+- switch01.tor | 1 | 127.0.0.2 | 255.0.0.0 switch01.tor | 2 | 127.0.0.3 | 255.0.0.0 switch01.tor | 3 | 209.250.155.8 | 255.255.255.224 (2 rows) 127.0.0.2 | 255.0.0.0 and 127.0.0.3 | 255.0.0.0 - it's correct output And of course after pg_dump and restore correct value 127.0.0.2/8 and 127.0.0.3/8 will lose and will have problem with PRIMARY KEY - (sysname, ip_addr). -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [SQL] A Question
On Fri, 26 May 2000, Sherril Mathew wrote: My one feild in the database is Date/time I want to find a range of records which exists between two dates in the same date field in the database SELECT * FROM table WHERE datetime_field between '1999-03-15' and '2000-10-23'; Also tell me how to retreive all records from database where field which is date time is null I am working on ASP and backend as ms-ACCESS SELECT * FROM table WHERE datetime_field is null; -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
