PostgreSQL SQL - create table and data types

Author

create table and data types

Maciej Piekielniak

2006-02-14, 8:25 pm

Hello pgsql-sql,

Is anybody know how create field in a new table with data type accuiring from a field in other table?
For example:

create table new_table
(
name other_table.name%TYPE
);

--
Best regards,
Maciej mailto:piechcio@isb.com.pl

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

Ken Hill

2006-02-14, 8:25 pm

On Tue, 2006-02-14 at 22:12 +0100, Maciej Piekielniak wrote:

> Hello pgsql-sql,
>
> Is anybody know how create field in a new table with data type accuiring from a field in other table?
> For example:
>
> create table new_table
> (
> name other_table.name%TYPE
> );
>

Have you tried inheritance from one table to the new table?

CREATE TABLE new_table (new_column)
INHERITS (old_table)

All columns in 'old_table' will be inclueded in 'new_table' plus the
column 'new_column'.

Maciej Piekielniak

2006-02-14, 8:25 pm

Hello Ken,

Tuesday, February 14, 2006, 10:30:34 PM, you wrote:
KH> On Tue, 2006-02-14 at 22:12 +0100, Maciej Piekielniak wrote:
[color=darkred]

KH> Have you tried inheritance from one table to the new table?

KH> CREATE TABLE new_table (new_column)
KH> INHERITS (old_table)

KH> All columns in 'old_table' will be inclueded in 'new_table' plus the
KH> column 'new_column'.

yes, but i don't need all colums, i need only the same data type for only
some fields.

for example
create table new table
(
name other_table.name%TYPE,
mynewfield VARCHAR(100),
mynewfield2 VARCHAR(100)
);

--
Best regards,
Maciej

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

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

Tom Lane

2006-02-14, 8:25 pm

Maciej Piekielniak <piechcio@isb.com.pl> writes:
> Is anybody know how create field in a new table with data type accuiring from a field in other table?

Sorry, the %TYPE syntax only works in function declarations at the
moment.

It could possibly be made to work in table declarations --- there are
syntactic conflicts with allowing it in general, but I'm not sure that
objection applies to table declarations. But it's not there today.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Ken Hill

2006-02-14, 8:25 pm

On Tue, 2006-02-14 at 23:03 +0100, Maciej Piekielniak wrote:

> Hello Ken,
>
> Tuesday, February 14, 2006, 10:30:34 PM, you wrote:
> KH> On Tue, 2006-02-14 at 22:12 +0100, Maciej Piekielniak wrote:
>
>
> KH> Have you tried inheritance from one table to the new table?
>
> KH> CREATE TABLE new_table (new_column)
> KH> INHERITS (old_table)
>
> KH> All columns in 'old_table' will be inclueded in 'new_table' plus the
> KH> column 'new_column'.
>
> yes, but i don't need all colums, i need only the same data type for only
> some fields.
>
> for example
> create table new table
> (
> name other_table.name%TYPE,
> mynewfield VARCHAR(100),
> mynewfield2 VARCHAR(100)
> );
>

Have you tried restructuring the table with CREATE TABLE AS...? Try
this:

CREATE TABLE new_table
(id, mynewfield, mynewfield2)
AS SELECT id FROM old_table);

This should create a new table ('new_table') with the data-type for
'old_table' for the id column.

Bath, David

2006-02-14, 8:25 pm

On Tue, 2006-02-14 at 22:12 +0100, Maciej Piekielniak wrote: (snipped)
>Is anybody know how create field in a new table with data type accuiring
>from a field in other table?
>For example:
> create table new_table ( name other_table.name%TYPE);

On Wed, 15 Feb 2006 09:42, Ken Hill wrote: (snipped)
> Have you tried restructuring the table with CREATE TABLE AS...?
> Try this:
> CREATE TABLE new_table
> _ _ _(id, mynewfield, mynewfield2)
> _ _ _AS SELECT id FROM old_table);

I use a similar technique, but add a "WHERE 1=0" so the new table is
empty. _Of course, more than a single table can be referenced in the
FROM clause.
--
David T. Bath
dave.bath@unix.net

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

Sponsored Links