From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | metaresolve <solvemetare(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Problems Importing table to pgadmin |
Date: | 2016-12-08 01:58:25 |
Message-ID: | CANu8FiwNiQa3o_7cf8yC2_KVpnaPViGj9Rr0s7ozRkz5O-mtGA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Dec 7, 2016 at 8:25 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> On 12/07/2016 05:19 PM, metaresolve wrote:
>
>> Uh, yeah, it was a SELECT * from cc_20161207;
>>
>> I know, it was dumb. I didn't realize it would break it or at least run
>> for
>> a while. I tend to do things in small steps, run a query, check my
>> results,
>> then tweak.
>>
>> You're right, I wouldn't want to be viewing those million. so I guess I
>> could just be doing the top 10000 rows to get a sense, then be doing my
>> aggregate group bys/etc to get my final numbers? That's how I hacked
>> Access
>> to be my data analysis program until I got to Alteryx. But I was also
>> never
>> using files of 1M then.
>>
>>
> FYI pgAdmin, if I remember correctly, has a setting that limits the
> maximum number of rows that it will fetch at a time.
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
With regard to John's comments
>Postgres, on a properly scaled and tuned database server, can handle
billions of records.
>Obviously, doing something silly like querying all billions at once will
never be fast, thats a lot of data to marshal and process.
On a similar note, your table structure is not exactly optimized for a
relational database.
I see no primary key to identify each row. In addition, the election & vote
columns
belong in a separate table that is a child of cc_20161207, since they are
repeating fields.
eg:
ALTER TABLE cc_20161207
ADD COLUMN cc_20161207_pk serial;
UPDATE cc_20161207
SET cc_20161207_pk = nextval(cc_20161207_pk_seq)
WHERE cc_20161207_pk IS NULL;
ALTER TABLE cc_20161207
ALTER COLUMN cc_20161207_pk SET NOT NULL;
ALTER TABLE cc_20161207
ADD CONSTRAINT cc_20161207 PRIMARY KEY (cc_20161207_pk);
CREATE TABLE election_data (
cc_20161207_pk bigint NOT NULL,
election_id serial NOT NULL,
election varchar(4),
vote_type varchar(1)
CONSTRAINT election_data_pk PRIMARY KEY (cc_20161207_pk, election_id)
);
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
From | Date | Subject | |
---|---|---|---|
Next Message | Metare Solve | 2016-12-08 02:21:04 | Re: Problems Importing table to pgadmin |
Previous Message | Sreekanth Palluru | 2016-12-08 01:55:39 | Fwd: ERROR invalid page header in block xxx of relation base/xxxxx/xxxxx/ |