Re: pg_sample

Hello

On 10/19/2016 04:58 AM, Greg Sabino Mullane wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
> Patrick B <patrickbakerbr(at)gmail(dot)com> writes:
> ...
>> However, this new database test server doesn't need to have all the data. I
>> would like to have only the first 100 rows(example) of each table in my
>> database.
> ...
>
> This should do what you ask.
>
> If the order does not matter, leave out the ORDER BY.
>
> This assumes everything of interest is in the public schema.
>
> $ createdb testdb
> $ pg_dump realdb --schema-only | psql -q testdb
> $ psql realdb
>
> psql> \o dump.some.rows.sh
> psql> select format($$psql realdb -c 'COPY (select * from %I order by 1 limit %s) TO STDOUT' | psql testdb -c 'COPY %I FROM STDIN' $$, table_name, 100, table_name)
> from information_schema.tables where table_schema = 'public' and table_type = 'BASE TABLE';
> psql> \q
>
> $ sh dump.some.rows.sh

I may be overseeing something, but what about dependencies between
tables, sequencies, indexes, etc.? I guess that if one takes the first
100 rows of a table referenced by another table, there is no guarantee
that in the first 100 rows of the referencing table there will not be
some foreign key that does not exist.

Regards
Charles

>
> - --
> Greg Sabino Mullane greg(at)turnstep(dot)com
> End Point Corporation http://www.endpoint.com/
> PGP Key: 0x14964AC8 201610182256
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
> -----BEGIN PGP SIGNATURE-----
>
> iEYEAREDAAYFAlgG4NkACgkQvJuQZxSWSsge4ACePhBOBtBFnGNxXt5qpY7X+w3o
> d04AoKTzAgxcaqy8qfIE0LPuzG9x0KIU
> =sS+m
> -----END PGP SIGNATURE-----
>
>
>
>

--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+-----------------------+
| ____ ______ ___ |
| / )/ \/ \ |
| ( / __ _\ ) |
| \ (/ o) ( o) ) |
| \_ (_ ) \ ) _/ |
| \ /\_/ \)/ |
| \/ <//| |\\> |
| _| | |
| \|_/ |
| |
| PostgreSQL 1996-2016 |
| 20 Years of Success |
| |
+-----------------------+