subquery with more than one column

Hello again,

In my previous databases development, I've been using always a unique
longint number for identifying each record.

In PostgreSQL I can see that it has the oid, he can do it for you.

Also I see that is an optional parameter, and after surfing the web,
I could find some people that say never use them, an another ones
that you can use it ...

Any extra advice would be perfectly before creating the database, if
it's better for now and future to use oid or simply create an id
field as a serial or something similar.

thanks,

raimon fernandez

On Tuesday 22 May 2007 03:30, Raimon Fernandez wrote:
> Hello again,
>
>
> In my previous databases development, I've been using always a unique
> longint number for identifying each record.
>
> In PostgreSQL I can see that it has the oid, he can do it for you.
>
> Also I see that is an optional parameter, and after surfing the web,
> I could find some people that say never use them, an another ones
> that you can use it ...
>
> Any extra advice would be perfectly before creating the database, if
> it's better for now and future to use oid or simply create an id
> field as a serial or something similar.

In general, the recommendation is not to use OIDs. Use serial columns (or
some other primary key) instead.

Sean

I'm trying with this approach:

create table public.articles( "id" int4 not null default nextval
('articles_id_seq'::regclass) , "referencia" varchar not null
)
WITHOUT OIDS;
ALTER table "public"."articles" OWNER TO "postgres";
ALTER table "public"."articles" SET WITHOUT CLUSTER;
alter table "public"."articles" add primary key(id);

and after some test, it works ...

thanks for the advice!

regards,

raimon

On 22/05/2007, at 11:57, Sean Davis wrote:

> On Tuesday 22 May 2007 03:30, Raimon Fernandez wrote:
>> Hello again,
>>
>>
>> In my previous databases development, I've been using always a unique
>> longint number for identifying each record.
>>
>> In PostgreSQL I can see that it has the oid, he can do it for you.
>>
>> Also I see that is an optional parameter, and after surfing the web,
>> I could find some people that say never use them, an another ones
>> that you can use it ...
>>
>> Any extra advice would be perfectly before creating the database, if
>> it's better for now and future to use oid or simply create an id
>> field as a serial or something similar.
>
> In general, the recommendation is not to use OIDs. Use serial
> columns (or
> some other primary key) instead.
>
> Sean
>

On Tuesday 22 May 2007 06:05, Raimon Fernandez wrote:
> I'm trying with this approach:
>
> create table public.articles( "id" int4 not null default nextval
> ('articles_id_seq'::regclass) , "referencia" varchar not null
> )
> WITHOUT OIDS;
> ALTER table "public"."articles" OWNER TO "postgres";
> ALTER table "public"."articles" SET WITHOUT CLUSTER;
> alter table "public"."articles" add primary key(id);

And keep in mind that you can use:

id serial

as a shorthand.

Sean

you mean this:

create table public.test(id serial, ref varchar);

I was using Navicat PostgreSQL, a GUI that does lot of things, I'll
try with the command line ...

thanks !

rai

On 22/05/2007, at 12:08, Sean Davis wrote:

> On Tuesday 22 May 2007 06:05, Raimon Fernandez wrote:
>> I'm trying with this approach:
>>
>> create table public.articles( "id" int4 not null default nextval
>> ('articles_id_seq'::regclass) , "referencia" varchar not null
>> )
>> WITHOUT OIDS;
>> ALTER table "public"."articles" OWNER TO "postgres";
>> ALTER table "public"."articles" SET WITHOUT CLUSTER;
>> alter table "public"."articles" add primary key(id);
>
> And keep in mind that you can use:
>
> id serial
>
> as a shorthand.
>
> Sean
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

Hi,

It's not possible to return in a subquery more than one column ?

SELECT id_intern, (SELECT referencia, descripcio FROM articles WHERE
id_intern = product_blister.id_product_added) FROM product_blister
WHERE id_product_source = '8';

so, I would have to add a new subquery for each column I want to
retrieve ?

like this:

SELECT id_intern, (SELECT referencia FROM articles WHERE id_intern =
product_blister.id_product_added),(SELECT descripcio FROM articles
WHERE id_intern = product_blister.id_product_added) FROM
product_blister WHERE id_product_source = '8';

all of them are from the same table ...

regards,

raimon fernandez

Raimon Fernandez <coder(at)montx(dot)com> writes:
> It's not possible to return in a subquery more than one column ?

Try it like this:

SELECT id_intern, (SELECT ROW(referencia, descripcio) FROM articles WHERE...

regards, tom lane

but I want as a separate columns, like a normal SELECT ...

this is what I get:

+---------------+
| row |
+---------------+
| (1,"ref rai") |
| (1,ref) |
+---------------+

thanks,

raimon

On 05/07/2007, at 21:30, Tom Lane wrote:

> Raimon Fernandez <coder(at)montx(dot)com> writes:
>> It's not possible to return in a subquery more than one column ?
>
> Try it like this:
>
> SELECT id_intern, (SELECT ROW(referencia, descripcio) FROM
> articles WHERE...
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Raimon Fernandez wrote:
> [..moved top-post..]
> On 05/07/2007, at 21:30, Tom Lane wrote:
>
>> Raimon Fernandez <coder(at)montx(dot)com> writes:
>>> It's not possible to return in a subquery more than one column ?
>>
>> Try it like this:
>>
>> SELECT id_intern, (SELECT ROW(referencia, descripcio) FROM articles
>> WHERE...
>>
>> regards, tom lane
>>
> but I want as a separate columns, like a normal SELECT ...
>
>
> this is what I get:
>
> +---------------+
> | row |
> +---------------+
> | (1,"ref rai") |
> | (1,ref) |
> +---------------+
>
>
> thanks,
>
>
> raimon

Is there something about the nature of the data which prevents you from
using a JOIN between these two tables?

select pb.id_intern, a.referencia, a.descripcio
from product_blister pb
join articles a on (a.id_intern = pb.id_product_added)
where pb.id_product_source = '8';

(Or using a LEFT JOIN if not every record in product_blister is required
to have a corresponding record in articles, and you want those records
in product_blister to still be returned.)

My apologies if my assumption is incorrect.

-Jon

--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/

great !!

that's what I was lookingfor ...

I think my brain was empty ...

:)

regards,

rai

On 05/07/2007, at 23:27, Jon Sime wrote:

> Raimon Fernandez wrote:
>> [..moved top-post..]
>> On 05/07/2007, at 21:30, Tom Lane wrote:
>>> Raimon Fernandez <coder(at)montx(dot)com> writes:
>>>> It's not possible to return in a subquery more than one column ?
>>>
>>> Try it like this:
>>>
>>> SELECT id_intern, (SELECT ROW(referencia, descripcio) FROM
>>> articles WHERE...
>>>
>>> regards, tom lane
>>>
> > but I want as a separate columns, like a normal SELECT ...
> >
> >
> > this is what I get:
> >
> > +---------------+
> > | row |
> > +---------------+
> > | (1,"ref rai") |
> > | (1,ref) |
> > +---------------+
> >
> >
> > thanks,
> >
> >
> > raimon
>
> Is there something about the nature of the data which prevents you
> from using a JOIN between these two tables?
>
> select pb.id_intern, a.referencia, a.descripcio
> from product_blister pb
> join articles a on (a.id_intern = pb.id_product_added)
> where pb.id_product_source = '8';
>
> (Or using a LEFT JOIN if not every record in product_blister is
> required to have a corresponding record in articles, and you want
> those records in product_blister to still be returned.)
>
> My apologies if my assumption is incorrect.
>
> -Jon
>
> --
> Senior Systems Developer
> Media Matters for America
> http://mediamatters.org/
>