Thread: How to append records into a file which has serial unique IDs?

How to append records into a file which has serial unique IDs?

From
James Long
Date:
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 -----

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

From
george young
Date:
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)

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

From
Michael Glaesemann
Date:
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




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

From
James Long
Date:
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


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

From
James Long
Date:
> 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.


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

From
george young
Date:
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)