Re: pg_sample

On 10/18/2016 08:15 PM, Charles Clavadetscher wrote:
> Hello
>
> On 10/19/2016 04:58 AM, Greg Sabino Mullane wrote:
>>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: RIPEMD160
>>
>>
>> Patrick B <patrickbakerbr@gmail.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.

Well there is:

https://github.com/18F/rdbms-subsetter

That still does not guarantee that the rows selected cover your test
cases though.

>
> Regards
> Charles
>
>>
>> - --
>> Greg Sabino Mullane greg@turnstep.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-----
>>
>>
>>
>>
>


--
Adrian Klaver
adrian.klaver@aklaver.com