Thread: PK with an expression in field list

PK with an expression in field list

From
"Alejandro Michelin Salomon \( Adinet \)"
Date:
Hi:
 
I need create a PK for a table.
This PK has 3 fields.
PK is : CONSTRAINT ff_navios PRIMARY KEY ( idempresa, idarmador, UPPER( nome ) )
But seems not work.
 
I have and other way to do this ?
 
If this fields are not null and i create a unique index in place off pk, i have the same effect ?
 
Alejandro Michelin Salomon
Porto Alegre
Brasil.
 
 
 

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.0/346 - Release Date: 23/5/2006

Re: PK with an expression in field list

From
Tino Wildenhain
Date:
Alejandro Michelin Salomon ( Adinet ) wrote:
> Hi:
>
> I need create a PK for a table.
> This PK has 3 fields.
> PK is : CONSTRAINT ff_navios PRIMARY KEY ( idempresa, idarmador, UPPER(
> nome ) )
> But seems not work.

This is a useless error report ;) You need to provide the error message
you get. Usually this error message says in plain english (or even plain
spain or something ;) what the problem is anyway :-)

Ah yes, you should use (UPPER(nome)) in the place you wrote UPPER(nome)

> I have and other way to do this ?




> If this fields are not null and i create a unique index in place off pk,
> i have the same effect ?

What is the desired effect? PK is usually implemented as unique index
anyway.
...
>
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.7.0/346 - Release Date: 23/5/2006
>
>
> ------------------------------------------------------------------------
>
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.7.0/346 - Release Date: 23/5/2006
>
I'd get rid of those messages. They are useless anyway.

Regards
Tino

Re: PK with an expression in field list

From
Tom Lane
Date:
"Alejandro Michelin Salomon \( Adinet \)" <alejmsg@adinet.com.uy> writes:
> I need create a PK for a table.
> This PK has 3 fields.
> PK is : CONSTRAINT ff_navios PRIMARY KEY ( idempresa, idarmador, UPPER( nome
> ) )
> But seems not work.

You can't define a primary key on an expression.  The SQL standard says
so, and even if we wanted to generalize the standard here, we'd have a
bit of a problem with how it'd show up in the information_schema.

You can, however, create a unique index over those values and get pretty
much the same effect.

            regards, tom lane

RES: PK with an expression in field list

From
"Alejandro Michelin Salomon \( Adinet \)"
Date:
Hi :

I will create a unique index, and make this fiels not null.
Thanks for the reply.

Alejandro Michelin Salomon

-->-----Mensagem original-----
-->De: Tom Lane [mailto:tgl@sss.pgh.pa.us]
-->Enviada em: quarta-feira, 24 de maio de 2006 16:45
-->Para: Alejandro Michelin Salomon ( Adinet )
-->Cc: Pgsql-General
-->Assunto: Re: [GENERAL] PK with an expression in field list
-->
-->
-->"Alejandro Michelin Salomon \( Adinet \)"
--><alejmsg@adinet.com.uy> writes:
-->> I need create a PK for a table.
-->> This PK has 3 fields.
-->> PK is : CONSTRAINT ff_navios PRIMARY KEY ( idempresa, idarmador,
-->> UPPER( nome
-->> ) )
-->> But seems not work.
-->
-->You can't define a primary key on an expression.  The SQL
-->standard says so, and even if we wanted to generalize the
-->standard here, we'd have a bit of a problem with how it'd
-->show up in the information_schema.
-->
-->You can, however, create a unique index over those values
-->and get pretty much the same effect.
-->
-->            regards, tom lane
-->
-->
-->--
-->No virus found in this incoming message.
-->Checked by AVG Free Edition.
-->Version: 7.1.394 / Virus Database: 268.7.0/346 - Release
-->Date: 23/5/2006
-->
-->
-->--
-->No virus found in this incoming message.
-->Checked by AVG Free Edition.
-->Version: 7.1.394 / Virus Database: 268.7.0/346 - Release
-->Date: 23/5/2006
-->
-->

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.0/346 - Release Date: 23/5/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.0/346 - Release Date: 23/5/2006