[SQL] multiple PK with a non UNIQUE field

[davide]

Sorry, I 'm a beginner of postgres and I found a problem when I was 
passed from MySQL:
I have to create a referential constraint table APPALTO from a table 
OPERE that as a composed PK(cod_op,cod_com) where cod_op ISN'T unique.

in MySQL:
CREATE TABLE opere (
   cod_op int NOT NULL ,
   cod_com int NOT NULL ,
   costo int ,
   data_inizio date ,
   data_fine date ,
   tipo char(6) NOT NULL ,
   PRIMARY KEY (cod_op,cod_com)
) ;

CREATE TABLE committenti (
   cod_com int NOT NULL ,
   nome char(30) NOT NULL,
   indirizzo char(60) NOT NULL,
   CF char(16) unique,
   P_IVA char(11) unique,
   tipo char(8) NOT NULL ,
   PRIMARY KEY (cod_com)
);


CREATE TABLE appalto (
   cod_op int not null references Opere,
   cod_com int not null references Opere,
   scadenza date not null,
   importoint not null,   
   PRIMARY KEY (cod_op,cod_com)

);


But when I try to insert it:
ERROR:  number of referencing and referenced columns for foreign key 
disagree


another table connected at OPERE give instead another error:

CREATE TABLE direzione (
   CF char(16) not null references Salariati(CF),
   cod_op int not null references Opere (cod_op),
   cod_com int  not null references Opere (cod_com),
--opere_pkeyintreferences Opere,
--PRIMARY KEY (opere_pkey)
   PRIMARY KEY (CF,cod_op,cod_com)
) ;
ERROR:  there is no unique constraint matching given keys for referenced 
table "opere"


If I try to use the index "opere_pkey" (automatic created)
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"opere_pkey" for table "opere"


Why MySQL let me do these and PostgreSQL no?
There's another way?



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] multiple PK with a non UNIQUE field

[davide]

if you need a multi column fk don't use the "references" keyword on your create table, instead use the "FOREIGN KEY" 
keyword for the table, see the "create table" help.  

so for example (untested) change 


 CREATE TABLE appalto (
 cod_op int not null references Opere,
 cod_com int not null references Opere,
 scadenza date not null,
 importoint not null,   
 PRIMARY KEY (cod_op,cod_com)

 );

to
 CREATE TABLE appalto (
 cod_op int not null,
 cod_com int not null,
 scadenza date not null,
 importoint not null,   
 PRIMARY KEY (cod_op,cod_com),

 FOREIGN KEY (cod_op,cod_com) REFERENCES Opere(cod_op,cod_com)
 );





In this way it works, thanks.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] How can I simply substatue a value in a query?

[davide]

Bryan Encina wrote:


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Roy Souther
Sent: Monday, June 27, 2005 10:17 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] How can I simply substatue a value in a query?


I want to do a simple substatution of a value in a query. For example I have a 
boolean field that wil return t or f for True and False. I would like it to 
return Yes or No. I don't want to have a 2x2 table to look up the output. I 
don't want to use stored procedue.

I think there is a better way, somthing very simple but I cannot remember what 
it is.

-

I think a CASE statement is what you want.  Check the Conditional Expressions 
section of the docs at:
http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html
HTH,
Bryan Encina


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

 


select case "field"
when 'T' then 'Yes'
when 'F' then 'No'
else null end
from  ...



---(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] Question on the 8.0Beta Version

[Davide Negri]

Hello, 

i have installed postgres 8.0 beta3 as a program on
my pc on windows xp. I have read on the installation note file that postgres
server will not run with administrative permissions. I have read on your forum
that this happened because i have installed the beta version: is it true that
this happen only with the beta version? When will you plan to release the
production version? 

 

Thanks You

 

Negri Davide








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.779 / Virus Database: 526 - Release Date: 19/10/2004
 

[SQL] Log

[Davide Negri]

Hello, 

i have installed the 8.0 beta3-dev1 version of
postgres on my pc, and I want to disable log. How can I do? It is possible to
disable and don’t write all the log, or how can I write all the log in a
specific directory??

 

Thanks

 

Negri Davide








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.779 / Virus Database: 526 - Release Date: 19/10/2004