[SQL] except on nulls?
I had the following weird experience. Hopefuly iy's my ignorance on the matter (but I sort of think this worked before...) On PostgreSQL 7.0.2, I have an database with two tables, holding different versions of the data set (some rows missing in one of the tables). Example is: CREATE TABLE "test1" ( "f1" text, "f2" date, "f3" "_text", "f4" int4, "f5" character varying(16) ); Then table test2 with the same definition. SELECT f1 FROM test2 EXCEPT SELECT f1 from test1; produced empty result, although test2 contained few more rows. Closer examination showed that both tables contained an 'null' row, with all fields null. After removing both rows, the query would return the proper results. Another query, SELECT * from test2 EXCEPT SELECT * from test1; failed too, giving the following error: ERROR: Unable to identify an operator '<>' for types '_text' and '_text' You will have to retype this query using an explicit cast Any idea why this is happening? Daniel
Re: [SQL] HELP! ... pg_locale ???
Sandis Jerics <[EMAIL PROTECTED]> writes: > As result, now all queries, written inside the php code on multiply lines, > returns the following: > ERROR: parser: parse error at or near " " At a guess, you're having trouble with newline representations (Unix convention is \n only, DOS/Windows convention is \r\n, and then there's Macintosh which likes \r only). We've been working to change Postgres to accept all of these choices, but depending on which version of which tool you are using, you may need to toe the Unix line faithfully. The above message looks a lot like something spitting up on a stray \r. Dunno what your admin did to make the problem appear where you hadn't had it before... regards, tom lane
Re: [SQL] renaming columns... danger?
Subject:[SQL] renaming columns... danger? > I just discovered that doing an alter table ... alter > column (to rename a column) does not do a complete > rename throughout the database. > shouldn't rename update any index and key definitions? > I'm very frightened right now, because I'm rather > dependent upon my database right now. I don't like > the thought that my database is corrupt at the schema > level. > Yes, I believe the same is true about trigger definitions and suchlike. In short - to do a rename on column I do a pg_dumpall and change all references of the name by hand :*((( Btw, is there a way to see what triggers are defined for particular field? Or how to drop triggers, which (by default) are unnamed?
Re: [SQL] except on nulls?
Daniel Kalchev <[EMAIL PROTECTED]> writes: > [ EXCEPT behaves oddly in the presence of NULLs ] Yup, it does, because it's implemented like NOT IN, and NOT IN on a set containing nulls can never return 'true', only 'false' or 'unknown'. For example, 1 NOT IN (1,2,NULL) is clearly FALSE. But 3 NOT IN (1,2,NULL) is not clearly either true or false --- the null is effectively "I don't know what this value is", and so it's unknown whether 3 is equal to it or not. The SQL92 spec mandates that this NOT IN result be 'unknown' (NULL), which is then treated like 'false' by EXCEPT. Net result: nulls in EXCEPT's right-hand set cause its output set to be empty. While this behavior is all according to spec for IN/NOT IN, it's *not* according to spec for EXCEPT, because the spec defines UNION/INTERSECT/ EXCEPT in terms of a different concept, of rows being "distinct" or "not distinct". NULLs are distinct from non-NULLs and so a null row behaves the way you'd expect. UNION/INTERSECT/EXCEPT are reimplemented for 7.1 in a way that behaves according to spec. There's no simple patch for 7.0.* unfortunately. > (but I sort of think this worked before...) Could be. Before 7.0, IN/NOT IN were not up to spec on NULL handling either, so EXCEPT probably worked differently in this case then. > ERROR: Unable to identify an operator '<>' for types '_text' and '_text' > You will have to retype this query using an explicit cast There are no comparison operators for array types ... regards, tom lane
[SQL] benchmarks
hi all, lately at work there has been a debate over mysql versus postgres im just looking for independent benchmarks i personally love postgres at work they like mysql currently we are investigating other possible db solutions and they are looking at oracle, i think we could save a lot of dollarsz if we decided to go to postgres i was wondering if anyone can share links to any current independent benchmarks as i would like some real data on these or at the very least give me a how to so i can do my own testing! thanks!
[SQL] .
Hi all, I tried:SELECT * FROM DB1.MY_TABLE;from DB2 and it did not like the syntax. I assume that you cannot select across database/schemas.Is this correct? Shiva
Re: [SQL] benchmarks
> im just looking for independent benchmarks Even if there exist independent benchmarks, there are none being applicable to real life. > i personally love postgres > at work they like mysql > > currently we are investigating other possible db solutions Uh? You _love_ this and they _like_ that? Since when, I dareask, personal preferences are considered when chosing a product to trust corporate data to? How 'bout using just what's the right tool? > or at the very least give me a how to so i can do my own testing! Implement your system in MySQL. Then re-do it from scratch in PG. Compare. If you still have time and funds, make your choice. -- contaminated fish and microchips huge supertankers on Arabian trips oily propaganda from the leaders' lips all about the future there's people over here, people over there everybody's looking for a little more air crossing all the borders just to take their share planning for the future Rainbow, Difficult to Cure
Re: [SQL] renaming columns... danger?
Just tested this on latest devel. version, and there does seem to be a
problem.
[]$ psql test
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test=# select version();
version
PostgreSQL 7.1devel on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
test=# create table a ( aa serial primary key );
NOTICE: CREATE TABLE will create implicit sequence 'a_aa_seq' for
SERIAL column 'a.aa'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey'
for table 'a'
CREATE
test=# alter TABLE a RENAME aa to new_aa;
ALTER
[]$ pg_dump test
--
-- Selected TOC Entries:
--
\connect - gaf
--
-- TOC Entry ID 2 (OID 20352)
--
-- Name: "a_aa_seq" Type: SEQUENCE Owner: gaf
--
CREATE SEQUENCE "a_aa_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;
--
-- TOC Entry ID 4 (OID 20370)
--
-- Name: a Type: TABLE Owner: gaf
--
CREATE TABLE "a" (
"new_aa" integer DEFAULT nextval('"a_aa_seq"'::text) NOT NULL,
PRIMARY KEY ("aa")
);
--
-- Data for TOC Entry ID 5 (OID 20370) TABLE DATA a
--
-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'a';
COPY "a" FROM stdin;
\.
-- Enable triggers
BEGIN TRANSACTION;
CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C,
"pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" ~* 'a' GROUP
BY 1;
UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr"
TMP WHERE "pg_class"."relname" = TMP."tmp_relname";
DROP TABLE "tr";
COMMIT TRANSACTION;
--
-- TOC Entry ID 3 (OID 20352)
--
-- Name: "a_aa_seq" Type: SEQUENCE SET Owner:
--
SELECT setval ('"a_aa_seq"', 1, 'f');
Michael Teter wrote:
> hi.
>
> I just discovered that doing an alter table ... alter
> column (to rename a column) does not do a complete
> rename throughout the database.
>
> for example, say you have table a, with columns b and
> c. b is your primary key.
>
> now rename b to new_b. if you do a dump of the schema
> after you rename, you'll find that you can't reload
> that schema because at the bottom of the definition of
> table a you have PRIMARY KEY ("b").
>
> shouldn't rename update any index and key definitions?
>
> also, and this may actually the source of the problem,
> while scanning my full (schema and data) dump, I
> noticed that the contents of table pga_layout also had
> the old values of columns that I have renamed.
>
> I'm very frightened right now, because I'm rather
> dependent upon my database right now. I don't like
> the thought that my database is corrupt at the schema
> level.
>
> michael
>
> __
> Do You Yahoo!?
> Yahoo! Messenger - Talk while you surf! It's FREE.
> http://im.yahoo.com/
--
> Poorly planned software requires a genius to write it
> and a hero to use it.
Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED])
Software Engineer Universal Computer Services
Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa
Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein
Fax (+27)(11)339-3421Johannesburg, South Africa
