Re: pg_sample

On Tue, Oct 18, 2016 at 10:21 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 10/18/2016 06:30 PM, Patrick B wrote:
>
>>
>>
>> 2016-10-19 13:39 GMT+13:00 Michael Paquier <michael(dot)paquier(at)gmail(dot)com
>> <mailto:michael(dot)paquier(at)gmail(dot)com>>:
>>
>> On Wed, Oct 19, 2016 at 9:24 AM, Patrick B <patrickbakerbr(at)gmail(dot)com
>> <mailto:patrickbakerbr(at)gmail(dot)com>> wrote:
>> > 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.
>> >
>> > I'm using pg_sample to do that, but unfortunately it doesn't work
>> well.
>> > It doesn't get the first 100 rows. It gets random 100 rows.
>>
>> Why aren't 100 random rows enough to fulfill what you are looking for?
>> What you are trying here is to test the server with some sample data,
>> no? In this case, having the first 100 rows, or a set of random ones
>> should not matter much (never tried pg_sample to be honest).
>> --
>> Michael
>>
>>
>>
>> Actually it does matter because there is some essential data that has to
>> be in there so the code can work.
>>
>
> Well random does not know essential, it is after all random. If you want
> to test specific cases then you will need to build appropriate data sets.
>
>
> --
> 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
>

The following query should generate statements you can use to get the first
100 rows of every table.
You may need to tweak a bit as order is not guaranteed.

SELECT 'COPY ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname)
|| ' TO '''
-- || 'C:\temp\'
|| '/tmp/'
|| quote_ident(n.nspname) || '_' || quote_ident(c.relname) || '.csv' ||
''''
|| ' WITH CSV HEADER FORCE_QUOTE *;'
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'sql_%'
LIMIT 100;

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.