Обсуждение: How to append records into a file which has serial unique IDs?
Please pardon any duplication, but the previous two posts do not appear to have made it to the list. ----- Forwarded message ----- Subject: How to append records into a file which has serial unique IDs? To: pgsql-novice@postgresql.org Date: Wed, 5 Apr 2006 00:37:48 -0700 (PDT) X-Mailer: ELM [version 2.5 PL8] Suppose I have a table "foo" with columns a b and c. A is a serial unique key. What's a good way to copy a record to a temp table, and then append it back in as a new record, automatically assigning a new serial key? It appears that: create temp table bar as select (*) from foo; insert into foo (*) select (*) from bar; isn't the way to do it. If I explicitly name fields in the CREATE, INSERT and SELECT clauses (so as to exclude field a), then I have to know and maintain a list of each and every field and this becomes a maintenance headache any time the column names change in foo. I could live with a solution that required me to know just the name of the one field I want to exclude, such as create temp table temp as select (* except a) from foo; insert into foo (* except a) select (* except a) from bar; I have done a fair amount of old-school DOS database stuff, but am rather new to SQL, so if this is a simple question, then that's why I'm asking on the novice list! :) But surely, there must be a nice general solution to this, since it would seem to happen in a *large* number of master/transaction situations, where a ledger of transactions is posted, and then the posted transactions are archived into a history file, before clearing the batch to make way for the next batch. Suppose both the batch file and the history file use unique serial IDs, and each table's IDs are numbered separately -- one has to be able to append all fields except the serial from a number of rows, while simultaneously generating new serials for each of the rows appended.... Once I figure out how, I would create a php subroutine with parameterized table names and field name(s) to exclude on insert, and then be able to easily append serialized records with no other knowledge of the underlying structure of the specific table being operated upon. I just can't find a clean way to do it. Suggestions are appreciated. Thanks, and regards from Portland, Jim Long ----- End forwarded message -----
On Sat, 8 Apr 2006 08:09:32 -0700
James Long <pgsql-novice@museum.rain.com> threw this fish to the penguins:
> Please pardon any duplication, but the previous two posts do not
> appear to have made it to the list.
>
> ----- Forwarded message -----
>
> Subject: How to append records into a file which has serial unique IDs?
> To: pgsql-novice@postgresql.org
> Date: Wed, 5 Apr 2006 00:37:48 -0700 (PDT)
> X-Mailer: ELM [version 2.5 PL8]
>
> Suppose I have a table "foo" with columns a b and c. A is a serial unique key.
>
> What's a good way to copy a record to a temp table, and then append it
> back in as a new record, automatically assigning a new serial key?
>
> It appears that:
>
> create temp table bar as select (*) from foo;
> insert into foo (*) select (*) from bar;
>
> isn't the way to do it.
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?
[BTW, it's always good practice to include the exact version of postgres
and your platform in questions, to save extra round-trips like "Are you
using version 8.1.2.3 of postgres? It has a bug that makes your
fingernails turn green...".]
-- George Young
> If I explicitly name fields in the CREATE, INSERT and SELECT
> clauses (so as to exclude field a), then I have to know and
> maintain a list of each and every field and this becomes a
> maintenance headache any time the column names change in foo.
>
> I could live with a solution that required me to know just
> the name of the one field I want to exclude, such as
>
> create temp table temp as select (* except a) from foo;
> insert into foo (* except a) select (* except a) from bar;
>
> I have done a fair amount of old-school DOS database stuff, but
> am rather new to SQL, so if this is a simple question, then that's
> why I'm asking on the novice list! :)
>
> But surely, there must be a nice general solution to this, since
> it would seem to happen in a *large* number of master/transaction
> situations, where a ledger of transactions is posted, and then the
> posted transactions are archived into a history file, before clearing
> the batch to make way for the next batch. Suppose both the batch
> file and the history file use unique serial IDs, and each table's
> IDs are numbered separately -- one has to be able to append all fields
> except the serial from a number of rows, while simultaneously
> generating new serials for each of the rows appended....
>
> Once I figure out how, I would create a php subroutine with
> parameterized table names and field name(s) to exclude on insert, and
> then be able to easily append serialized records with no other
> knowledge of the underlying structure of the specific table being
> operated upon.
>
> I just can't find a clean way to do it. Suggestions are appreciated.
>
>
> Thanks, and regards from Portland,
>
> Jim Long
>
>
> ----- End forwarded message -----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
On Apr 9, 2006, at 11:21 , george young wrote: > "Are you using version 8.1.2.3 of postgres? It has a bug that > makes your > fingernails turn green...".] Please note that PostgreSQL 8.1.2.3 has not yet been released, and that particular bug is fixed in HEAD. (Thanks, Tom!) Michael Glaesemann grzm myrealbox com
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
> This worked for me: > > newschm3=> create table foo(x text,y text,a serial); I think it just dawned on me -- I have to put the serial ID at the end of the table structure, don't I? So that the fields correspond one-to-one, until the temp runs out of columns where the master table has the SERIAL field.
On Sat, 8 Apr 2006 23:27:07 -0700
James Long <james_mapson@umpquanet.com> threw this fish to the penguins:
> > This worked for me:
> >
> > newschm3=> create table foo(x text,y text,a serial);
>
> I think it just dawned on me -- I have to put the serial ID at
> the end of the table structure, don't I? So that the fields
> correspond one-to-one, until the temp runs out of columns
> where the master table has the SERIAL field.
Here's a somewhat cleaner method (and faster if that is an issue):
newschm3=> create temp table bar as select * from foo where y='red';
SELECT
-- Now find the proper name of the sequence underlying column 'a':
newschm3=> \d+ foo
Table "public.foo"
Column | Type | Modifiers | Description
--------+---------+-------------------------------------------------+-------------
x | text | |
y | text | |
a | integer | not null default nextval('foo_a_seq'::regclass) |
-- It's *almost* always safe to assume serial column 'a' of table
-- 'foo' uses foo_a_seq but it's possible for it to be something else,
-- e.g. if the table or column has been renamed, or if the
-- table name+column name is very long.
newschm3=> update bar set a=nextval('foo_a_seq') from foo;
UPDATE 1
newschm3=> select * from foo;
x | y | a
------+-------+---
the | red | 1
rain | green | 2
in | blue | 3
the | red | 4
[see http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html]
-- George Young
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)