[SQL] Table constraints and INSERT
Hello I use PostgreSQL in my program and I found a strange behavior, at least for me. I have a simple table with constraint CREATE TABLE PART ( P_PARTKEY int4 NOT NULL, P_RETAILPRICE numeric, CONSTRAINT PART_PRIMARY PRIMARY KEY (P_PARTKEY), CONSTRAINT PART_check CHECK (P_RETAILPRICE = (9 + P_PARTKEY / 10 + P_PARTKEY / 100) ); And I try to insert a row: INSERT INTO PART (P_PARTKEY,P_RETAILPRICE) VALUES(999,90109.89); but it fails: ERROR: new row for relation PART violates check constraint PART_check When you check using your head or pocket calculator then this INSERT seems to be correct. Is it some floating point mystery? Is there some trick? I'm using version 8.0.1 for Windows. With thanks, Risto ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Database OID
Hi, How can I find the database OID from simple SQL command? Thanks, kjt McAfee SCM 4.1 által ellenőrizve! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Table constraints and INSERT
On 17 maj 2006, at 08.42, Risto Tamme wrote: INSERT INTO PART (P_PARTKEY,P_RETAILPRICE) VALUES(999,90109.89); but it fails: ERROR: new row for relation PART violates check constraint PART_check The P_PARTKEY column is an integer, which means the expression P_PARTKEY/10 will yield 99, *not* 99.9. Try executing SELECT 9 + 999/10 + 999/10; from psql or some GUI-utility; the result is 90108. When you check using your head or pocket calculator then this INSERT seems to be correct. Is it some floating point mystery? Is there some trick? You must cast the integer column to a float or numeric, try: SELECT 9 + 999::numeric/10 + 999::numeric/100; In your case: CHECK (P_RETAILPRICE = (9 + P_PARTKEY::numeric / 10 + P_PARTKEY::numeric / 100) Sincerely, Niklas Johansson Phone: +46-322-108 18 Mobile: +46-708-55 86 90 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Recursive SELECT problem
Dave Page dpage@vale-housing.co.uk writes: I'm trying to write a query to determine whether or not the current login role has CREATEDB (or CREATEROLE) privileges, either directly or inherited from a parent role. Those privileges don't inherit, so I'm not sure why you need a recursive check. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Recursive SELECT problem
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 17 May 2006 15:07 To: Dave Page Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Recursive SELECT problem Dave Page dpage@vale-housing.co.uk writes: I'm trying to write a query to determine whether or not the current login role has CREATEDB (or CREATEROLE) privileges, either directly or inherited from a parent role. Those privileges don't inherit, so I'm not sure why you need a recursive check. Because I didn't realise they didn't inherit 'cos I missed the note at the bottom of the CREATE ROLE page :-) Thanks - that makes life significantly easier! Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] MySQL DB to PostgresSQL DB
Dear all, This is my first post and I will try to be short and clear in my questions and I hope you can help me with that. I have a MySQL DB and I want to export to PostgreSQL DB. My questions are; - Is posible to export MySQL DB to PostgreSQL DB? - Does PostgreSQL DB has tools to import MySQL DB? - If is posible, How do I need to build the DB in MySQL?, in order to have success in the exportation. - Do someone did it before?, exporting MySQL DB to PostgreSQL DB? - What kind of risk do we can to have in this process? - How long can take this process? Thanks in advance. Jose
[SQL] how referencing %rowtype variables without column names
I wrote a trigger and use %rowtype , the problue is I need to put the column name in a variable like Declare columnname VARCHAR(20); NOTIFYRULE NOTIFY_RULE%ROWTYPE begin columnname='TYPE' /* ONE OF NOTIFY_RULE column*/ SELECT * INTO NOTIFYRULE FROM NOTIFY_RULE WHERE STATUS=:OLD.STATUS; end; how can i use this columnname to get the column value form NOTIFYRULE without use NOTIFYRULE.TYPE ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [pgsql-www] MySQL DB to PostgresSQL DB
greetings, I don't know if pgsql-www is the right place for your questions or not, but I'll be nice and give you a few ideas on how to get your questions answered.. :) On Tue, 2006-05-16 at 00:19 +0200, Jose Apablaza wrote: Dear all, This is my first post and I will try to be short and clear in my questions and I hope you can help me with that. I have a MySQL DB and I want to export to PostgreSQL DB. My questions are; - Is posible to export MySQL DB to PostgreSQL DB? directly? without modification? not sure about that. at the very least many of the field types are different in pg vs. other databases like mysql. you'll need to run a script or do a search-and-replace to resolve that. - Does PostgreSQL DB has tools to import MySQL DB? - If is posible, How do I need to build the DB in MySQL?, in order to have success in the exportation. - Do someone did it before?, exporting MySQL DB to PostgreSQL DB? many people have converted from mysql to postgresql. - What kind of risk do we can to have in this process? depends on what exactly mysql is doing for you now. - How long can take this process? depends on the size of the database and skill at this sort of thing. also, if you are using mysql_* calls from (for example) php, you will need to convert that code. personally, I recommend PEAR::DB (assuming php is being used).. that way once you have the database imported to pg, you change the connect string and you are done. info about PEAR::DB is here: http://pear.php.net/DB ... you might also try http://pear.php.net/MDB2 , but I do not have personal experience with that module. I did a google search: http://www.google.com/search?hl=enq=mysql+to+postgresqlbtnG=Google +Search and found a number of links that might help you in your quest. if none of those links are helpful, please let me know privately. another recommendation I would make after using google and reading up on a few things is to ask about this process on irc.. #postgresql on irc.freenode.net is an *excellent* resource. Thanks in advance. Jose regards, J -- Jeff MacDonald Zoid Technologies, http://zoidtechnologies.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] table constraint + INSERT
Title: table constraint + INSERT Hello I use PostgreSQL in my program and I found a strange behavior, at least for me. I have a simple table with constraint CREATE TABLE PART ( P_PARTKEY int4 NOT NULL, P_RETAILPRICE numeric, CONSTRAINT PART_PRIMARY PRIMARY KEY (P_PARTKEY), CONSTRAINT PART_check CHECK (P_RETAILPRICE = (9 + P_PARTKEY / 10 + P_PARTKEY / 100) ); And I try to insert a row: INSERT INTO PART (P_PARTKEY,P_RETAILPRICE) VALUES(999,90109.89); but it fails: ERROR: new row for relation PART violates check constraint PART_check When you check using your head or pocket calculator then this INSERT seems to be correct. Is it some floating point mystery? Is there some trick? I'm using version 8.0.1 for Windows. With thanks, Risto
