pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate table and data types
6+ messages / 4 participants
[nested] [flat]
* create table and data types
@ 2006-02-14 21:12 Maciej Piekielniak <piechcio@isb.com.pl>
0 siblings, 2 replies; 6+ messages in thread
From: Maciej Piekielniak @ 2006-02-14 21:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: create table and data types
@ 2006-02-14 21:30 Ken Hill <ken@scottshill.com>
parent: Maciej Piekielniak <piechcio@isb.com.pl>
1 sibling, 1 reply; 6+ messages in thread
From: Ken Hill @ 2006-02-14 21:30 UTC (permalink / raw)
To: Maciej Piekielniak <piechcio@isb.com.pl>; +Cc: pgsql-sql
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'.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: create table and data types
@ 2006-02-14 22:03 Maciej Piekielniak <piechcio@isb.com.pl>
parent: Ken Hill <ken@scottshill.com>
0 siblings, 1 reply; 6+ messages in thread
From: Maciej Piekielniak @ 2006-02-14 22:03 UTC (permalink / raw)
To: Ken Hill <ken@scottshill.com>; +Cc: pgsql-sql
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:
>> 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
>> );
>>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: create table and data types
@ 2006-02-14 22:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Maciej Piekielniak <piechcio@isb.com.pl>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2006-02-14 22:06 UTC (permalink / raw)
To: Maciej Piekielniak <piechcio@isb.com.pl>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: create table and data types
@ 2006-02-14 22:42 Ken Hill <ken@scottshill.com>
parent: Maciej Piekielniak <piechcio@isb.com.pl>
0 siblings, 0 replies; 6+ messages in thread
From: Ken Hill @ 2006-02-14 22:42 UTC (permalink / raw)
To: Maciej Piekielniak <piechcio@isb.com.pl>; +Cc: pgsql-sql
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:
>
> >> 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
> >> );
> >>
>
> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: create table and data types
@ 2006-02-15 00:21 Bath, David <dave.bath@unix.net>
0 siblings, 0 replies; 6+ messages in thread
From: Bath, David @ 2006-02-15 00:21 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-02-15 00:21 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-14 21:12 create table and data types Maciej Piekielniak <piechcio@isb.com.pl>
2006-02-14 21:30 ` Ken Hill <ken@scottshill.com>
2006-02-14 22:03 ` Maciej Piekielniak <piechcio@isb.com.pl>
2006-02-14 22:42 ` Ken Hill <ken@scottshill.com>
2006-02-14 22:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-15 00:21 Re: create table and data types Bath, David <dave.bath@unix.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox