Re: How to append records into a file which has serial

On Sat, Apr 08, 2006 at 10:21:14PM -0400, george young wrote:
>
> This worked for me:
>
> newschm3=> create table foo(x text,y text,a serial);
> newschm3=> insert into foo (x,y) values ('the','red');
> INSERT 0 1
> newschm3=> insert into foo (x,y) values ('rain','green');
> INSERT 0 1
> newschm3=> insert into foo (x,y) values ('in','blue');
> INSERT 0 1
> newschm3=> create temp table bar as select * from foo where y='red';
> SELECT
> newschm3=> alter table bar drop column a;
> newschm3=> insert into foo select * from bar;
> INSERT 0 1
>
> newschm3=> select * from foo;
> x | y | a
> ------+-------+----
> the | red | 1
> rain | green | 2
> in | blue | 3
> the | red | 4
>
> You don't need to know the field names "x" and "y", just the
> serial field name "a".
>
> Is this what you meant?

Thank you for your reply, George.

Yes, it sounds very promising, but:

t30 : 23:11:12 /home/james> psql --version
psql (PostgreSQL) 8.0.7
contains support for command-line editing
t30 : 23:11:16 /home/james> ssh www postgres --version
postgres (PostgreSQL) 8.0.4

t30 : 23:11:34 /home/james> psql
Welcome to psql 8.0.7, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

james=> \d client_table
Table "public.client_table"
Column | Type | Modifiers
----------------+---------------+------------------------------------------------------------------
cli_id | integer | not null default nextval('public.client_table_cli_id_seq'::text)
cli_code | character(20) |
cli_name | character(40) |
cli_bill_addr1 | character(40) |
cli_bill_addr2 | character(40) |
cli_bill_city | character(30) |
cli_bill_state | character(2) |
cli_bill_zip | character(10) |
cli_ship_addr1 | character(40) |
cli_ship_addr2 | character(40) |
cli_ship_city | character(30) |
cli_ship_state | character(2) |
cli_ship_zip | character(10) |
cli_phone | character(20) |
cli_email | character(40) |
Indexes:
"client_table_pkey" PRIMARY KEY, btree (cli_id)

james=> create temp table temp as select * from client_table where cli_code = 'old_client_code';
SELECT
james=> update temp set cli_code = 'new_client_code', cli_name = 'New But Similar Client Entity';
UPDATE 1
james=> alter table temp drop column cli_id;
ALTER TABLE
james=> INSERT INTO client_table SELECT * from temp;
ERROR: column "cli_id" is of type integer but expression is of type character
HINT: You will need to rewrite or cast the expression.
james=> \q