pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRemoving duplicate rows
5+ messages / 4 participants
[nested] [flat]
* Removing duplicate rows
@ 2007-02-06 22:15 Paul Lambert <paul.lambert@autoledgers.com.au>
0 siblings, 2 replies; 5+ messages in thread
From: Paul Lambert @ 2007-02-06 22:15 UTC (permalink / raw)
To: pgsql-sql
I have some tables which I need to load from flat data files that can
contain upwards of five million rows.
It's possible (owing to the structure of the system the data is
originating from) that on occasion the data extract that generates these
flat files can include two records that have the same values in the
fields that make up the primary key of the table in my Postgres
database, thus throwing an error when I attempt to load.
I'm assuming the best way to get around this is to load the data into a
temporary table with "copy from" and then do a "select distinct into" my
real table.
The question I have is the best way to set up this temporary table in my
reload script. (Having 14 servers running Postgres, each with 37
tables, I don't want to be creating temporary tables manually each time
I need to reload the databases)
As the table definitions get updated frequently, I'd like to put in my
script to create the table as a copy of the existing 'real' table. I'm
assuming "create table as" would be what I need to use, but what would I
use as the select sql - i.e. how do I use select to pull the definition
of a table.
Apologies if this is a stupid question, I'm still fairly new to Postgres
so I'm not sure what system tables are available for pulling out this
kind of information.
Thanks in advance for any assistance you can offer.
Regards,
Paul.
--
Paul Lambert
Database Administrator
AutoLedgers
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Removing duplicate rows
@ 2007-02-06 22:22 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Paul Lambert <paul.lambert@autoledgers.com.au>
1 sibling, 1 reply; 5+ messages in thread
From: Andrew Sullivan @ 2007-02-06 22:22 UTC (permalink / raw)
To: pgsql-sql
On Wed, Feb 07, 2007 at 07:15:02AM +0900, Paul Lambert wrote:
>
> I'm assuming the best way to get around this is to load the data into a
> temporary table with "copy from" and then do a "select distinct into" my
> real table.
You might find that sort|uniq at the command prompt would be better.
That said,
> script to create the table as a copy of the existing 'real' table. I'm
> assuming "create table as" would be what I need to use, but what would I
> use as the select sql - i.e. how do I use select to pull the definition
> of a table.
an easy way to do this is "CREATE TABLE name AS SELECT . . . WHERE
1=0". You get a table with no rows. (WHERE FALSE and similar
constructs all work equally well.)
A
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Removing duplicate rows
@ 2007-02-06 22:44 Paul Lambert <paul.lambert@autoledgers.com.au>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Paul Lambert @ 2007-02-06 22:44 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
Andrew Sullivan wrote:
> On Wed, Feb 07, 2007 at 07:15:02AM +0900, Paul Lambert wrote:
>> I'm assuming the best way to get around this is to load the data into a
>> temporary table with "copy from" and then do a "select distinct into" my
>> real table.
>
> You might find that sort|uniq at the command prompt would be better.
> That said,
Sort on Weenblows is a bastard to work with, and I don't believe it has
a unique option. I probably should have mentioned this was on Weenblows.
>
>> script to create the table as a copy of the existing 'real' table. I'm
>> assuming "create table as" would be what I need to use, but what would I
>> use as the select sql - i.e. how do I use select to pull the definition
>> of a table.
>
> an easy way to do this is "CREATE TABLE name AS SELECT . . . WHERE
> 1=0". You get a table with no rows. (WHERE FALSE and similar
> constructs all work equally well.)
Thanks... I figured it would be blatantly obvious.
This does the trick:
CREATE TABLE billing_code_temp AS SELECT * FROM billing_code WHERE 1=0
>
> A
>
Appreciate the help.
P.
--
Paul Lambert
Database Administrator
AutoLedgers
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Removing duplicate rows
@ 2007-02-06 22:49 Hélder M. Vieira <hmv@mail.telepac.pt>
parent: Paul Lambert <paul.lambert@autoledgers.com.au>
1 sibling, 0 replies; 5+ messages in thread
From: Hélder M. Vieira @ 2007-02-06 22:49 UTC (permalink / raw)
To: pgsql-sql
> As the table definitions get updated frequently, I'd like to put in my
> script to create the table as a copy of the existing 'real' table. I'm
> assuming "create table as" would be what I need to use, but what would I
> use as the select sql - i.e. how do I use select to pull the definition of
> a table.
The create statement allows you to copy a table structure, including or
excluding constraints.
For inclusion and exclusion effects, please read the manual info related
with the 'like' clause of the 'create table' statement.
Ex.:
create temporary table mytesttable (like mymaintable excluding constraints
excluding defaults);
Regards,
Hélder M. Vieira
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Removing duplicate rows
@ 2007-02-06 23:15 Thomas Kellerer <spam_eater@gmx.net>
parent: Paul Lambert <paul.lambert@autoledgers.com.au>
0 siblings, 0 replies; 5+ messages in thread
From: Thomas Kellerer @ 2007-02-06 23:15 UTC (permalink / raw)
To: pgsql-sql
Paul Lambert wrote on 06.02.2007 23:44:
> Sort on Weenblows is a bastard to work with, and I don't believe it has
> a unique option. I probably should have mentioned this was on Weenblows.
You can get all (or most?) of the *nix/GNU commandline tools for Windows as
well. As "native" Win32 programs that do not require a pseudo *nix (aka as
Cygwin) to run in:
http://gnuwin32.sourceforge.net
tsort and uniq are part of the coreutils package
Thomas
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-02-06 23:15 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-06 22:15 Removing duplicate rows Paul Lambert <paul.lambert@autoledgers.com.au>
2007-02-06 22:22 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-02-06 22:44 ` Paul Lambert <paul.lambert@autoledgers.com.au>
2007-02-06 23:15 ` Thomas Kellerer <spam_eater@gmx.net>
2007-02-06 22:49 ` Hélder M. Vieira <hmv@mail.telepac.pt>
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