Re: [SQL] table constraint + INSERT
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?
Postgres is likely doing integer arithmetic:
test=# select 9+999/10+999/100;
?column?
--
90108
(1 row)
So you have to cast your check constraint to numeric types:
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::numeric / 10
);
However if this would be your real SQL Schema I'd recommend using a
view to calculate the R_RETAILPRICE column:
CREATE TABLE PART
(
P_PARTKEY int4 NOT NULL,
CONSTRAINT PART_PRIMARY PRIMARY KEY (P_PARTKEY)
);
create view PARTV as
select P_PARTKEY, 9 + P_PARTKEY::numeric / 10 + P_PARTKEY::numeric / 100 as
from PART;
--
---> Dirk Jagdmann
> http://cubic.org/~doj
-> http://llg.cubic.org
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] table constraint + INSERT
Too bad, some code got truncated... 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::numeric / 10 + P_PARTKEY::numeric / 100 ); and the second code should read: create view PARTV as select P_PARTKEY, 9 + P_PARTKEY::numeric / 10 + P_PARTKEY::numeric / 100 as P_RETAILPRICE from PART; -- ---> Dirk Jagdmann > http://cubic.org/~doj -> http://llg.cubic.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about SQL Control Structure(if then, for loop)
If you're control is that simple, you can write similar statements in pure SQL: RDM=# for i in 1 .. 10 loopRDM-# select "test"RDM-# end loop;ERROR: syntax error at or near "for" at character 1LINE 1: for i in 1 .. 10 loop SELECT 'test' FROM RDM=# if exits ( select * from testtable)RDM-# thenRDM-# select "TEST" RDM-# ;ERROR: syntax error at or near "if" at character 1LINE 1: if exits ( select * from testtable)^ SELECT 'test' FROM testtable LIMIT 1;Without knowing about what you want to do, I can't guarantee that that will suffice and/or be efficient. If it gets much more complicated, you might have to go to some procedural language (PL/PGSQL, PL/Perl, etc). Just remember that SQL is set-based, not procedural. -Mike
Re: [SQL] Question about SQL Control Structure(if then, for loop)
SELECT 'test' FROM SELECT 'test' FROM generate_series(1,10);Sorry, thought faster than I could type :)-Mike
Re: [SQL] [JDBC] error : could not access status of transaction
Hi, Pavan, Pavan Kumar wrote: > could any body tell me what does following error message means > i get this error when i perform select operation on a table. > Error Message : > > could not access status of transaction 265224192 > DETAIL: could not open file "pg_clog/00FC": No such file or directory This sounds like a broken PostgreSQL data directory, not a jdbc problem. You should check whether you can access the table via psql or other non-jdbc tools, and if not, ask on PostgreSQL SQL List or pgsql-general. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: 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
[SQL] Encryption functions
Hi,Are there any encryption functions that can be used in SQL inserts and selects directly ? For example like "select encryptin_function('test_to_be_encrypted'), "Thanks and regards,-Thusitha
Re: [SQL] Encryption functions
On Thu, May 18, 2006 at 04:21:19AM -0700, Thusitha Kodikara wrote:
> Are there any encryption functions that can be used in
> SQL inserts and selects directly? For example like
> "select encryptin_function('test_to_be_encrypted'), "
See the contrib/pgcrypto module. It has functions like digest()
for making SHA1, MD5, and other digests; hmac() for making Hashed
Message Authentication Codes; and encrypt()/encrypt_iv() and
decrypt()/decrypt_iv() for doing encryption and decryption. Since
8.1 pgcrypto also has functions for doing OpenPGP symmetric and
public-key encryption.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Encryption functions
On Thu, 18 May 2006 06:44:55 -0600
Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Thu, May 18, 2006 at 04:21:19AM -0700, Thusitha Kodikara wrote:
> > Are there any encryption functions that can be used in
> > SQL inserts and selects directly? For example like
> > "select encryptin_function('test_to_be_encrypted'), "
>
> See the contrib/pgcrypto module. It has functions like digest()
> for making SHA1, MD5, and other digests; hmac() for making Hashed
> Message Authentication Codes; and encrypt()/encrypt_iv() and
> decrypt()/decrypt_iv() for doing encryption and decryption. Since
> 8.1 pgcrypto also has functions for doing OpenPGP symmetric and
> public-key encryption.
If your requirements are simpler check out the genpass module. It is a
DES3 encrypted type. You can do things like "SELECT * FROM table WHERE
passw = 'hello'" and it will find passwords that are entered as 'hello'
even though they are stored encrypted. Example:
darcy=# select 'hello'::chkpass;
chkpass
:v1L3NdWy0OHlQ
(1 row)
darcy=# select ':v1L3NdWy0OHlQ'::chkpass = 'hello';
?column?
--
t
(1 row)
darcy=# select ':v1L3NdWy0OHlQ'::chkpass = 'nothello';
?column?
--
f
(1 row)
Note that the leading colon says that the string is already encrypted.
This allows dump and restore to work correctly.
--
D'Arcy J.M. Cain | Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Encryption functions
On Thu, 18 May 2006 09:13:39 -0400 "D'Arcy J.M. Cain" wrote: > If your requirements are simpler check out the genpass module. It is a Sorry, chkpass module. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Add column and specify the column position in a table
Guillaume LELARGE wrote: > Emi Lu a écrit : > > I am trying to insert one column to a specific position in a table. > > > > In mysql, I can do: > > . create table test(id varchar(3), name varchar(12)); > > . alter table test add column givename varchar(12) after id; > > > > > > I am looking for similar things in postgresql to add a new column to the > > correct position in a table. > > There's no similar thing in PostgreSQL. You have to duplicate the table > to do it. ... which is the same thing MySQL does, only you must do it explicitely. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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
[SQL] Constraint question
I have the following table: create table onp_crm_businessfield_company( businessfield_id integer not null references onp_crm_businessfield(id), company_id integer not null references onp_crm_relation(id), is_preferred boolean, UNIQUE(businessfield_id, company_id) ); I want a constraint on "is_preffered" so that it's only allowed to be set once pr. businessfield_id pr. company so that only one businessfield can be preferred for a company. Does anyone have an idea how to enforce this? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ ---(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] MySQL DB to PostgresSQL DB
Jose Apablaza wrote: - 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? Yes, it's doable, but it's not as straighforward as mysqldump -someflags mydbname | psql -someotherflags pgdbname I suggest you start by checking the resources/articles in the MySQL section of http://www.postgresql.org/docs/techdocs.3 (which I'm glad to say is much better organized and comprehensive than when I had to do it). FWIW, I converted using CSV as the intermediate format, and the dates and timestamps were the trickiest, which required the use of "staging" tables (with a textual representation of the columns). An interesting side effect was discovering data inconsistencies in the MySQL database since as part of the conversion I implemented foreign key constraints under PostgreSQL (which were missing in the former). Joe ---(end of broadcast)--- TIP 1: 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] Add column and specify the column position in a table
2006/5/18, Alvaro Herrera <[EMAIL PROTECTED]>: Guillaume LELARGE wrote: > Emi Lu a écrit : > > I am trying to insert one column to a specific position in a table. > > > > In mysql, I can do: > > . create table test(id varchar(3), name varchar(12)); > > . alter table test add column givename varchar(12) after id; > > > > > > I am looking for similar things in postgresql to add a new column to the > > correct position in a table. > > There's no similar thing in PostgreSQL. You have to duplicate the table > to do it. ... which is the same thing MySQL does, only you must do it explicitely. Do you mean that, using "alter table test add column" with the "after" option, MySQL creates a new table, populates it with the old table data and finally drops the old table ? I mean, there's the same performance problem with big tables ? -- Guillaume. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Add column and specify the column position in a table
On Thu, May 18, 2006 at 05:43:19PM +0200, Guillaume Lelarge wrote: > Do you mean that, using "alter table test add column" with the "after" > option, MySQL creates a new table, populates it with the old table > data and finally drops the old table ? I mean, there's the same > performance problem with big tables ? MySQL does that for a great deal of its DDL. Yes, the performance is awful for this on big tables. The reason I didn't answer the OP's question, though, is that I can't think of a legitimate reason to do this anyway. The physical layout of the columns should not be of concern to the developer, who should be naming the columns anyway. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Constraint question
Hi, Andreas, Andreas Joseph Krogh wrote: > create table onp_crm_businessfield_company( > businessfield_id integer not null references onp_crm_businessfield(id), > company_id integer not null references onp_crm_relation(id), > is_preferred boolean, > UNIQUE(businessfield_id, company_id) > ); > I want a constraint on "is_preffered" so that it's only allowed to be set > once > pr. businessfield_id pr. company so that only one businessfield can be > preferred for a company. Does anyone have an idea how to enforce this? CREATE UNIQUE INDEX foo ON onp_crm_businessfield_company(company_id) WHERE is_prefferred; HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] Add column and specify the column position in a table
2006/5/18, Andrew Sullivan <[EMAIL PROTECTED]>: On Thu, May 18, 2006 at 05:43:19PM +0200, Guillaume Lelarge wrote: > Do you mean that, using "alter table test add column" with the "after" > option, MySQL creates a new table, populates it with the old table > data and finally drops the old table ? I mean, there's the same > performance problem with big tables ? MySQL does that for a great deal of its DDL. Yes, the performance is awful for this on big tables. The reason I didn't answer the OP's question, though, is that I can't think of a legitimate reason to do this anyway. The physical layout of the columns should not be of concern to the developer, who should be naming the columns anyway. I totally agree on the physical layout. Just being curious :) -- Guillaume. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] help..postgresql mulyiple return values
On 5/12/06, Michael Joseph Tan <[EMAIL PROTECTED]> wrote: hi, im new in postgresql, generally new in databases. im trying to make a function using PGAdminIII which returns several types, example, my query is: "select count(id) as requests, organization from connection_requests group by organization" id is of type int8, organization is of type varchar(50). basically the query would return coun(id), and a varchar(50) which is organization. i really dont know what to put on the return type. what would be the best solution? If you are in 8.1 you can follow this example from the documentation. http://www.postgresql.org/docs/8.1/interactive/plpgsql-declarations.html CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ BEGIN sum := x + y; prod := x * y; END; $$ LANGUAGE plpgsql; Then run it like: SELECT sum, prod FROM sum_n_product(1,2); ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Encryption functions
Thanks Michael, I'll check that module.Michael Fuhr <[EMAIL PROTECTED]> wrote: On Thu, May 18, 2006 at 04:21:19AM -0700, Thusitha Kodikara wrote:> Are there any encryption functions that can be used in> SQL inserts and selects directly? For example like> "select encryptin_function('test_to_be_encrypted'), "See the contrib/pgcrypto module. It has functions like digest()for making SHA1, MD5, and other digests; hmac() for making HashedMessage Authentication Codes; and encrypt()/encrypt_iv() anddecrypt()/decrypt_iv() for doing encryption and decryption. Since8.1 pgcrypto also has functions for doing OpenPGP symmetric andpublic-key encryption.-- Michael Fuhr---(end of broadcast)---TIP 5: don't forget to
increase your free space map settings
Re: [SQL] Encryption functions
Thanks for the guidance .-Thusitha"D'Arcy J.M. Cain" wrote: On Thu, 18 May 2006 06:44:55 -0600Michael Fuhr <[EMAIL PROTECTED]> wrote:> On Thu, May 18, 2006 at 04:21:19AM -0700, Thusitha Kodikara wrote:> > Are there any encryption functions that can be used in> > SQL inserts and selects directly? For example like> > "select encryptin_function('test_to_be_encrypted'), "> > See the contrib/pgcrypto module. It has functions like digest()> for making SHA1, MD5, and other digests; hmac() for making Hashed> Message Authentication Codes; and encrypt()/encrypt_iv() and> decrypt()/decrypt_iv() for doing encryption and decryption. Since> 8.1 pgcrypto also has functions for doing OpenPGP symmetric and> public-key
encryption.If your requirements are simpler check out the genpass module. It is aDES3 encrypted type. You can do things like "SELECT * FROM table WHEREpassw = 'hello'" and it will find passwords that are entered as 'hello'even though they are stored encrypted. Example:darcy=# select 'hello'::chkpass;chkpass :v1L3NdWy0OHlQ(1 row)darcy=# select ':v1L3NdWy0OHlQ'::chkpass = 'hello'; ?column?-- t(1 row)darcy=# select ':v1L3NdWy0OHlQ'::chkpass = 'nothello'; ?column?-- f(1 row)Note that the leading colon says that the string is already encrypted.This allows dump and restore to work correctly.-- D'Arcy J.M. Cain | Democracy is three wolveshttp://www.druid.net/darcy/| and a sheep voting on+1 416 425 1212 (DoD#0082)(eNTP) | what's for
dinner.---(end of broadcast)---TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
