Thread: CREATE TABLE AS SELECT....

CREATE TABLE AS SELECT....

From

"Arash Zaryoun"

Date:

17 September 2004, 00:57:50

Hi All,

I have a table which has 'SERIAL' datatype. When I use 'create table X
as select * from my-table' , it creates the table but without serial
datatype. I mean without implicit sequence.

test=> \d qptuser                                   Table "public.qptuser" Column   |         Type          |
            Modifiers
 
-----------+-----------------------+----------------------------------------------------------srl       | integer
       | not null default
 
nextval('public.qptuser_srl_seq'::text)login_nme | character varying(35) | not nullpassword  | character varying(30) |
notnull
 
Indexes:   "pk_qptuser" primary key, btree (srl)   "i1_qptuser_login_nme" unique, btree (login_nme)

test=> create table x as select * from qptuser;
test=> \d x 
             Table "public.a" Column   |         Type          | Modifiers
-----------+-----------------------+-----------srl       | integer               |login_nme | character varying(35)
|password | character varying(30) |
 

Can you help me on this?

Thanks,
- Arash

Re: CREATE TABLE AS SELECT....

From

Andrew Hammond

Date:

21 September 2004, 01:54:14

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Arash Zaryoun wrote:
| Hi All,
|
| I have a table which has 'SERIAL' datatype. When I use 'create table X
| as select * from my-table' , it creates the table but without serial
| datatype. I mean without implicit sequence.
|
| test=> \d qptuser
|                                     Table "public.qptuser"
|   Column   |         Type          |                        Modifiers
|
- 
-----------+-----------------------+----------------------------------------------------------
|  srl       | integer               | not null default
| nextval('public.qptuser_srl_seq'::text)
|  login_nme | character varying(35) | not null
|  password  | character varying(30) | not null
| Indexes:
|     "pk_qptuser" primary key, btree (srl)
|     "i1_qptuser_login_nme" unique, btree (login_nme)
|
| test=> create table x as select * from qptuser;
| test=> \d x
|
|               Table "public.a"
|   Column   |         Type          | Modifiers
| -----------+-----------------------+-----------
|  srl       | integer               |
|  login_nme | character varying(35) |
|  password  | character varying(30) |
|
| Can you help me on this?

I usually use CREATE TABLE ... AS SELECT ... to create temporary tables
or for reporting tables. I've never used it to create a table with the
intent of inserting new data into it. I don't really know what you're
trying to accomplish, but I'll guess that you want to set a default
value out of a sequence here. There's two ways you can do this. You can
share the same sequence as the qptuser table uses or you can create your
own sequence. To share the sequence:

ALTER TABLE a ALTER srl SET DEFAULT nextval('qptuser_srl_seq');

To create your own sequence:

CREATE SEQUENCE a_srl_seq START (SELECT srl FROM a ORDER BY srl DESC
LIMIT 1);

ALTER TABLE a ALTER slr SET DEFAULT nextval('a_srl_seq');

You'll probably want to throw some NOT NULL constraints on the table
while you're at it:

ALTER TABLE a SET srl NOT NULL;
ALTER TABLE a SET login_name NOT NULL;
ALTER TABLE a SET password NOT NULL;

I'll just assume that you're using hased passwords, and not storing them
in cleartext...

- --
Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFBT1Iagfzn5SevSpoRAl46AJ4iWqAN8LrdpxIX8PXSwyqs14ftKQCfbnTm
aui95Jq7i2zNzTTgMDS3nNY=
=ZFeW
-----END PGP SIGNATURE-----