Re: How to append records into a file which has serial - Mailing list pgsql-novice

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