PostgreSQL Discussion - database design questions

Author

database design questions

Ottavio Campana

2006-04-03, 11:27 am

Hello,

I'm designing a database and I'm having some problems, so I ask you a
suggestion.

1) The database I'm going to develop is a big list with a catalog of
items and I want to store subsets of this list representing the
available items in several places.

My idea is to create the big table with all the elements and then to
create another table, where each row holds a pair (id_item, id_place)
and thanks to this create several views, joining the two tables
and selecting the rows with a give id_place.

Do you think it's too heavy? Is there a simpler way to do it?

2) do you think it's possible in a plpgsql procedure select the name of
a table into a variable and use that variable in the query?

I mean, can I do something like

SELECT INTO table_name get_table_name();
SELECT * FROM table_name;

?

3) faq 4.11.1 says

> CREATE TABLE person (
> id SERIAL,
> name TEXT
> );
>
>is automatically translated into this:
>
> CREATE SEQUENCE person_id_seq;
> CREATE TABLE person (
> id INT4 NOT NULL DEFAULT nextval('person_id_s
eq'),
> name TEXT
> );

how can I do it with a INT8 instead of a INT4?

Thank you

--
Non c'è più forza nella normalità, c'è solo monotonia.

Tomi NA

2006-04-03, 11:27 am

On 4/3/06, Ottavio Campana <ottavio@campana.vi.it> wrote:

> 3) faq 4.11.1 says
>
>
> how can I do it with a INT8 instead of a INT4?
>
> Thank you
>

Is there a reason not to write explicitly?

CREATE SEQUENCE person_id_seq;
CREATE TABLE person (
id INT8 NOT NULL DEFAULT nextval('person_id_s
eq'),
name TEXT
);

Tomislav

Richard Broersma Jr

2006-04-03, 11:27 am

--- Tomi NA <hefest@gmail.com> wrote:

> On 4/3/06, Ottavio Campana <ottavio@campana.vi.it> wrote:
>
>
>
> Is there a reason not to write explicitly?
>
> CREATE SEQUENCE person_id_seq;
> CREATE TABLE person (
> id INT8 NOT NULL DEFAULT nextval('person_id_s
eq'),
> name TEXT
> );

you could also do:

CREATE TABLE person (
id BIGSERIAL,
name TEXT
);

Regards,

Richard

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

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

Keary Suska

2006-04-03, 11:27 am

on 4/3/06 7:38 AM, ottavio@campana.vi.it purportedly said:

> 1) The database I'm going to develop is a big list with a catalog of
> items and I want to store subsets of this list representing the
> available items in several places.
>
> My idea is to create the big table with all the elements and then to
> create another table, where each row holds a pair (id_item, id_place)
> and thanks to this create several views, joining the two tables
> and selecting the rows with a give id_place.
>
> Do you think it's too heavy? Is there a simpler way to do it?

On the surface, perhaps. Depending on your implementation details, you may
be adding unnecessary overhead. No one can really say since we don't know
what you are trying to accomplish.

> 2) do you think it's possible in a plpgsql procedure select the name of
> a table into a variable and use that variable in the query?
>
> I mean, can I do something like
>
> SELECT INTO table_name get_table_name();
> SELECT * FROM table_name;

Yes, kind of. I.e., you can probably do what you want but not with the
syntax you are showing. See SELECT INTO and EXECUTE in chapter 36 of the
online docs.

> 3) faq 4.11.1 says
>
>
> how can I do it with a INT8 instead of a INT4?

Use BIGSERIAL instead.

Best,

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"

---------------------------(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

Alban Hertroys

2006-04-03, 11:27 am

Ottavio Campana wrote:
[color=darkred]
> how can I do it with a INT8 instead of a INT4?

Do you really expect that sequence to reach over 2 billion? Otherwise
I'd stick with the SERIAL, nothing wrong with that unless you're selling
electrons seperately or something like that (hmm... how much are they? I
sure could use a few extra).

--
Alban Hertroys
alban@magproductions
.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Alex Turner

2006-04-03, 1:30 pm

create table person (
id serial8,
name text
);

Alex

On 4/3/06, Alban Hertroys < alban@magproductions
.nl> wrote:
>
> Ottavio Campana wrote:
>
>
> Do you really expect that sequence to reach over 2 billion? Otherwise
> I'd stick with the SERIAL, nothing wrong with that unless you're selling
> electrons seperately or something like that (hmm... how much are they? I
> sure could use a few extra).
>
> --
> Alban Hertroys
> alban@magproductions
.nl
>
> magproductions b.v.
>
> T: ++31(0)534346874
> F: ++31(0)534346876
> M:
> I: www.magproductions.nl
> A: Postbus 416
> 7500 AK Enschede
>
> // Integrate Your World //
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql
.org so that your
> message can get through to the mailing list cleanly
>

Don Y

2006-04-03, 1:30 pm

Alban Hertroys wrote:
> Ottavio Campana wrote:
>
>
> Do you really expect that sequence to reach over 2 billion? Otherwise
> I'd stick with the SERIAL, nothing wrong with that unless you're selling

Depends on what the dynamics of his design are. I.e. if he frequently
creates and deletes "people", then he can consume a lot of "id-space"
even though there aren't many real people in the database itself.

Personally, I doubt this is the case. But, can see other applications
where this could be true. Since you can't reuse old id's, it's easier
to just use a bigger size datum than having to worry how the database
will react when/if you run out of them!

> electrons seperately or something like that (hmm... how much are they? I
> sure could use a few extra).

Three for a quark... keep the charge! [sic] :>

--don

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

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

Ottavio Campana

2006-04-04, 7:33 am

Alban Hertroys wrote:
> Ottavio Campana wrote:
>
>
>
>
>
> Do you really expect that sequence to reach over 2 billion? Otherwise
> I'd stick with the SERIAL, nothing wrong with that unless you're selling
> electrons seperately or something like that (hmm... how much are they? I
> sure could use a few extra).

I agree wih you, but I think that in the feature the could be more than
2 billions. I don't want to alter in the future the database

--
Non c'è più forza nella normalità, c'è solo monotonia.

hubert depesz lubaczewski

2006-04-04, 7:34 am

On 4/3/06, Ottavio Campana <ottavio@campana.vi.it> wrote:
>
> 1) The database I'm going to develop is a big list with a catalog of
> items and I want to store subsets of this list representing the
> available items in several places.
> My idea is to create the big table with all the elements and then to
> create another table, where each row holds a pair (id_item, id_place)
> and thanks to this create several views, joining the two tables
> and selecting the rows with a give id_place.
> Do you think it's too heavy? Is there a simpler way to do it?

sorry but i dont understand your description. could you make a small example
of this layout?

2) do you think it's possible in a plpgsql procedure select the name of
> a table into a variable and use that variable in the query?

possible, but not really good way. read about 'execute' in plpgsql.

3) faq 4.11.1 says
> how can I do it with a INT8 instead of a INT4?

use bigserial instead of serial

depesz

Ottavio Campana

2006-04-04, 9:28 am

hubert depesz lubaczewski wrote:
> 2) do you think it's possible in a plpgsql procedure select the name of
> a table into a variable and use that variable in the query?
> possible, but not really good way. read about 'execute' in plpgsql.

why isn't it good?

I mean, from my point of view is like a function accepting a pointer. In
many languages it is used.

--
Non c'è più forza nella normalità, c'è solo monotonia.

hubert depesz lubaczewski

2006-04-05, 3:27 am

On 4/4/06, Ottavio Campana <ottavio@campana.vi.it> wrote:
>
> hubert depesz lubaczewski wrote:
> name of
> why isn't it good?
> I mean, from my point of view is like a function accepting a pointer. In
> many languages it is used.
>

when coding in plpgsql you have to use dynamic queries to use this kind of
thing. this - by itself - is not bad. but it has certain performance penalty
when compared to standard queries.
i think reading plpgsql's manual about "execute" is the best one can do
about it :)

best regards

depesz

Sponsored Links