pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCREATE TABLE AS SELECT....
4+ messages / 4 participants
[nested] [flat]
* CREATE TABLE AS SELECT....
@ 2004-09-16 20:57 Arash Zaryoun <Arash_Zaryoun@CBC.CA>
2004-09-20 21:56 ` Re: CREATE TABLE AS SELECT.... Andrew Hammond <ahammond@ca.afilias.info>
0 siblings, 1 reply; 4+ messages in thread
From: Arash Zaryoun @ 2004-09-16 20:57 UTC (permalink / raw)
To: pgsql-sql
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?
Thanks,
- Arash
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE TABLE AS SELECT....
2004-09-16 20:57 CREATE TABLE AS SELECT.... Arash Zaryoun <Arash_Zaryoun@CBC.CA>
@ 2004-09-20 21:56 ` Andrew Hammond <ahammond@ca.afilias.info>
0 siblings, 0 replies; 4+ messages in thread
From: Andrew Hammond @ 2004-09-20 21:56 UTC (permalink / raw)
To: Arash Zaryoun <Arash_Zaryoun@CBC.CA>; +Cc: pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 4+ messages in thread
* CREATE TABLE AS SELECT
@ 2005-07-29 17:09 Jeff Boes <jeff@endpoint.com>
2005-07-29 18:53 ` Re: CREATE TABLE AS SELECT daq <daq@ugyvitelszolgaltato.hu>
0 siblings, 1 reply; 4+ messages in thread
From: Jeff Boes @ 2005-07-29 17:09 UTC (permalink / raw)
To: pgsql-sql
Offered up because I have no explanation, and curiosity overwhelms me:
I was attempting to create a table from a SELECT statement against
another table:
create table foo
as select
a,
f(b)
from xxx;
The function f() attempts to make a unique value based on its argument
(it's actually a "username" constructor, making "jboes" out of "Jeff
Boes"). The odd thing is that function f() also looks into the table
"foo" to see if the value it's constructing is truly unique; if it is
not, it tacks on a "1", "2", etc. until it gets a unique value.
The odd behavior is as follows: with a "CREATE TABLE ... AS SELECT"
statement, the function never found duplicate values, so I ended up with
f(a) = f(a') = f(a''), etc. I tried defining the function as STABLE,
then VOLATILE, without success. But if I changed to create the table
first, and then do "INSERT INTO ... SELECT", the function worked properly.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE TABLE AS SELECT
2005-07-29 17:09 CREATE TABLE AS SELECT Jeff Boes <jeff@endpoint.com>
@ 2005-07-29 18:53 ` daq <daq@ugyvitelszolgaltato.hu>
0 siblings, 0 replies; 4+ messages in thread
From: daq @ 2005-07-29 18:53 UTC (permalink / raw)
To: Jeff Boes <jeff@endpoint.com>; +Cc: pgsql-sql
JB> Offered up because I have no explanation, and curiosity overwhelms me:
JB> I was attempting to create a table from a SELECT statement against
JB> another table:
JB> create table foo
JB> as select
JB> a,
JB> f(b)
JB> from xxx;
In this command table foo populated after the select statement
sucessfully finished. The function always runs on an empty foo table.
JB> The function f() attempts to make a unique value based on its argument
JB> (it's actually a "username" constructor, making "jboes" out of "Jeff
JB> Boes"). The odd thing is that function f() also looks into the table
JB> "foo" to see if the value it's constructing is truly unique; if it is
JB> not, it tacks on a "1", "2", etc. until it gets a unique value.
JB> The odd behavior is as follows: with a "CREATE TABLE ... AS SELECT"
JB> statement, the function never found duplicate values, so I ended up with
JB> f(a) = f(a') = f(a''), etc. I tried defining the function as STABLE,
JB> then VOLATILE, without success. But if I changed to create the table
JB> first, and then do "INSERT INTO ... SELECT", the function worked properly.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-07-29 18:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-16 20:57 CREATE TABLE AS SELECT.... Arash Zaryoun <Arash_Zaryoun@CBC.CA>
2004-09-20 21:56 ` Andrew Hammond <ahammond@ca.afilias.info>
2005-07-29 17:09 CREATE TABLE AS SELECT Jeff Boes <jeff@endpoint.com>
2005-07-29 18:53 ` Re: CREATE TABLE AS SELECT daq <daq@ugyvitelszolgaltato.hu>
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