Postgres Pro
Downloads
Home   >   mailing lists

Thread: CREATE TABLE AS ...

CREATE TABLE AS ...

From
Raphael Bauduin
Date:
Hi,

In a data model change, I wanted to use the "create table as select"
feature. However, I want that table to have a primary key defined, and
thus a field defined as NOT NULL. I didn't find a way to add a NOT NULL
constraint on a column of an existing table. If there isn't, what's the
best way to go? A COPY?

Thanks.

Raph

Re: CREATE TABLE AS ...

From
Artur Pietruk
Date:
On Tue, Jul 23, 2002 at 03:48:07PM +0200, Raphael Bauduin wrote:
> Hi,

    Hello,

> In a data model change, I wanted to use the "create table as select"
> feature. However, I want that table to have a primary key defined, and
> thus a field defined as NOT NULL. I didn't find a way to add a NOT NULL
> constraint on a column of an existing table. If there isn't, what's the
> best way to go? A COPY?

    Yes, it looks that you cannot add NOT NULL constraint. But check if
this could work for you:

== 8< ===
ALTER TABLE your_table ADD CONSTRAINT my_constr1 CHECK (your_column IS NOT NULL UNIQUE);
ALTER TABLE your_table ADD CONSTRAINT my_constr2 UNIQUE (your_column);
== 8< ===

    It is not marked as "Primary Key", but it does the same job: checks
that column is UNIQUE and NOT NULL.

    Best regards,
--
--- Artur Pietruk, arturp@plukwa.net

Re: CREATE TABLE AS ...

From
Bruno Wolff III
Date:
On Tue, Jul 23, 2002 at 15:48:07 +0200,
  Raphael Bauduin <raphael@be.easynet.net> wrote:
> Hi,
>
> In a data model change, I wanted to use the "create table as select"
> feature. However, I want that table to have a primary key defined, and
> thus a field defined as NOT NULL. I didn't find a way to add a NOT NULL
> constraint on a column of an existing table. If there isn't, what's the
> best way to go? A COPY?

Another option is to use create table and insert select. The create can
then set up the table exactly like you need. And then you can copy over
data using insert selecting from the source table.

Re: CREATE TABLE AS ...

From
Raphael Bauduin
Date:
Hi,

Thanks for the tips, but although the result is the same as a primary
key constraint, I'm not sure it will fit my needs as I want to use the
primary key in a foreign key constraint in another table. I made it with
a copy and it works fine.

Raph

On Tue, Jul 23, 2002 at 04:40:32PM +0200, Artur Pietruk wrote:
> On Tue, Jul 23, 2002 at 03:48:07PM +0200, Raphael Bauduin wrote:
> > Hi,
>
>     Hello,
>
> > In a data model change, I wanted to use the "create table as select"
> > feature. However, I want that table to have a primary key defined, and
> > thus a field defined as NOT NULL. I didn't find a way to add a NOT NULL
> > constraint on a column of an existing table. If there isn't, what's the
> > best way to go? A COPY?
>
>     Yes, it looks that you cannot add NOT NULL constraint. But check if
> this could work for you:
>
> == 8< ===
> ALTER TABLE your_table ADD CONSTRAINT my_constr1 CHECK (your_column IS NOT NULL UNIQUE);
> ALTER TABLE your_table ADD CONSTRAINT my_constr2 UNIQUE (your_column);
> == 8< ===
>
>     It is not marked as "Primary Key", but it does the same job: checks
> that column is UNIQUE and NOT NULL.
>
>     Best regards,
> --
> --- Artur Pietruk, arturp@plukwa.net

Re: CREATE TABLE AS ...

From
Mathieu Arnold
Date:

--On mardi 23 juillet 2002 15:48 +0200 Raphael Bauduin
<raphael@be.easynet.net> wrote:

> Hi,
>
> In a data model change, I wanted to use the "create table as select"
> feature. However, I want that table to have a primary key defined, and
> thus a field defined as NOT NULL. I didn't find a way to add a NOT NULL
> constraint on a column of an existing table. If there isn't, what's the
> best way to go? A COPY?

why not just :
create table new_table (new fields with all you need);
insert into new_table <the same select as you had in your create as>;

--
Mathieu Arnold