By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,081 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

Generating a SQL Server population routine

P: n/a
Has some one come up with a similar type script that could be used in a
Postgresql database?
The script below was created for a SQLServer database.
Thx,
-Martin

++++++++++++++++++++++++++++++++++++++
http://searchdatabase.techtarget.com...913717,00.html

In the early stages of application design DBA or a developer creates a data
model. Unfortunately many models work very well with a handful of rows but
fail miserably when the application grows by leaps and bounds. This is why
it is important to populate your data model with data and stress test it
prior to making it available for users. Test data doesn't have to be
perfect; indeed, you can duplicate the same record, or a few different
records, to test the performance of your queries.

This article offers a script for generating INSERT statements for every
table in your database. The script is fairly simple -- it relies on three
system tables: sysusers, sysobjects and syscolumns. It generates an INSERT
statement duplicating the top row in your table. However, it can be easily
altered to fit your needs.

SET NOCOUNT ON
DECLARE @table VARCHAR(200),
@owner VARCHAR(100),
@sql VARCHAR(2000),
@sql1 VARCHAR(2000)

DECLARE @schema TABLE (
table_name VARCHAR(200),
column_name VARCHAR(200))

INSERT @schema
SELECT c.name + '.' + a.name, b.name FROM sysobjects a INNER JOIN
syscolumns b ON b.id = a.id
AND a.type = 'u'
AND a.name <> 'dtproperties'
INNER JOIN sysusers c ON c.uid = a.uid
ORDER BY a.name, b.colid

DECLARE table_cursor CURSOR FOR

SELECT DISTINCT table_name FROM @schema

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @sql1 = ''
SELECT @sql1 = @sql1 + ', '+ column_name FROM @schema
WHERE table_name = @table

SELECT @sql1 = SUBSTRING(@sql1, 3, LEN(@sql1)-2)
SELECT @sql = 'INSERT ' + @table + '( ' + @sql1 + ' ) ' + CHAR(10) + '
SELECT TOP 1 ' + @sql1 + ' FROM ' +
@table

SELECT @sql
FETCH NEXT FROM table_cursor INTO @table
END
CLOSE table_cursor
DEALLOCATE table_cursor

In the pubs database, the output will be similar to the following:

INSERT dbo.authors( au_id, au_lname, au_fname, phone, address, city, state,
zip, contract )
SELECT TOP 1 au_id, au_lname, au_fname, phone, address, city, state, zip,
contract FROM dbo.authors

INSERT dbo.discounts( discounttype, stor_id, lowqty, highqty, discount )
SELECT TOP 1 discounttype, stor_id, lowqty, highqty, discount FROM
dbo.discounts

INSERT dbo.employee( emp_id, fname, minit, lname, job_id, job_lvl, pub_id,
hire_date )
SELECT TOP 1 emp_id, fname, minit, lname, job_id, job_lvl, pub_id,
hire_date FROM dbo.employee


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
Ma**********@dom.com wrote:
Has some one come up with a similar type script that could be used in a
Postgresql database? The script below was created for a SQLServer database.
Thx,
-Martin


I haven't. But I was wondering if a general purpose tuple-generating
function, which would be trivial to implement, might be worthwhile in
PostgreSQL or perhaps added to Joe Conway's tablefunc module.
Something like:

tuple_generator(integer)

which returns a set of numbers whose elements are the integer values
between 1 and the number supplied.

You could then create any number of pseudo-duplicates (can't violate
the candidate key, obviously) from a single-record table like so:

INSERT INTO employees (name, salary)
SELECT employees.name, employees.salary
FROM employees, tuple_generator(1000)
WHERE employees.employeeid = 1;

You could easily build a script to fill your database by querying
pg_class.relname and feeding the output to psql.

It would also be useful for handling sparse date and time data:

SELECT day_of_year,
(SELECT COALESCE(SUM(purchases.qty), 0)
FROM purchases
WHERE EXTRACT(doy FROM purchases.sale_date) = day_of_year)
FROM tuple_generator(366) AS day_of_year
ORDER BY day_of_year;

Mike Mascari
ma*****@mascari.com






---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #2

P: n/a
I think I have come across a bug in postgres 7.3.x. I noticed that when I
upgraded to 7.3 my code broke on certain sql statements. They still don't
work on the lastest stable release of 7.3.

Here is an example to illustrate my problem:

-- create the table with:
CREATE TABLE bugtest ( a int2 );

-- then do the following inserts:
insert into bugtest (a) select 1 union select 1; -- this one succeeds
insert into bugtest (a) select 1 union select '1'; -- this one also succeeds
insert into bugtest (a) select '1' union select 1; -- this one also succeeds
insert into bugtest (a) select '1' union select '1'; -- this one fails

The all succeed except the last one. It fails with the following error:

ERROR: column "a" is of type smallint but expression is of type text
You will need to rewrite or cast the expression

It seems to me that they should all succeed. At least postgres seems to
have no problem converting '<intvalue>' to <intvalue> anywhere else that I
can find. At least the last 3 inserts shoudl either all fail or all succeed
(IMHO).

Is this a bug? Has anyone else reported it? Is there a procedure I need to
follow to report it? Has it been fixed in 7.4?

On a sidenote I am doing the weird select union thing as a way to insert
many records at once without having to execute multiple queries. I first
started doing it on SQLServer and it was much, much faster than doing
separate inserts. Is there a better way to do it in postgres? I have
looked at the copy from command but I can't find any examples of how to use
it in php or how to specify the columns / column order that you are going to
use with php. Also what characters need to be excaped if I do this
(obviously new lines and tabs). And nulls are represented by \N.

Rick Gigger
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #3

P: n/a
"Rick Gigger" <ri**@alpinenetworking.com> writes:
insert into bugtest (a) select '1' union select '1'; -- this one fails Is this a bug?


No. It's unfortunate perhaps, but it's not a bug. The UNION forces us
to make a decision about the output datatype of the UNION operation.
In your other cases the chosen datatype is integer, which can later be
cast to smallint for the insert, but in this case the chosen datatype is
text, which is not implicitly castable to smallint.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #4

P: n/a
What was it that changed in 7.3 that made this behavior change. (it worked
in 7.2)

Thanks,

Rick Gigger

----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Rick Gigger" <ri**@alpinenetworking.com>
Cc: <pg***********@postgresql.org>
Sent: Monday, October 06, 2003 1:04 PM
Subject: Re: [GENERAL] Possible bug on insert

"Rick Gigger" <ri**@alpinenetworking.com> writes:
insert into bugtest (a) select '1' union select '1'; -- this one fails

Is this a bug?


No. It's unfortunate perhaps, but it's not a bug. The UNION forces us
to make a decision about the output datatype of the UNION operation.
In your other cases the chosen datatype is integer, which can later be
cast to smallint for the insert, but in this case the chosen datatype is
text, which is not implicitly castable to smallint.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #5

P: n/a
I guess then I will switch to use COPY "tablename (fieldlist)" FROM. Will
this end up being faster anyway.

In case anyone was wondering here is a good example of how to do it in php
from:

http://us4.php.net/manual/en/function.pg-put-line.php

<?php
$conn = pg_pconnect("dbname=foo");
pg_query($conn, "create table bar (a int4, b char(16), d float8)");
pg_query($conn, "copy bar from stdin");
pg_put_line($conn, "3\thello world\t4.5\n");
pg_put_line($conn, "4\tgoodbye world\t7.11\n");
pg_put_line($conn, "\\.\n");
pg_end_copy($conn);
?>

thanks,

Rick Gigger

----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Rick Gigger" <ri**@alpinenetworking.com>
Cc: <pg***********@postgresql.org>
Sent: Monday, October 06, 2003 1:04 PM
Subject: Re: [GENERAL] Possible bug on insert

"Rick Gigger" <ri**@alpinenetworking.com> writes:
insert into bugtest (a) select '1' union select '1'; -- this one fails

Is this a bug?


No. It's unfortunate perhaps, but it's not a bug. The UNION forces us
to make a decision about the output datatype of the UNION operation.
In your other cases the chosen datatype is integer, which can later be
cast to smallint for the insert, but in this case the chosen datatype is
text, which is not implicitly castable to smallint.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #6

P: n/a
"Rick Gigger" <ri**@alpinenetworking.com> writes:
What was it that changed in 7.3 that made this behavior change. (it worked
in 7.2)


7.2 allowed implicit casts from text to smallint, I think.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #7

P: n/a
Is there any possiblity of adding an option for compatibilityes sake (or
does one already exist) to revert to the old behavior. This has currently
kept me from upgrading beyond 7.2.4 thus far in production as it will break
all of my apps. I can slowly update them but many of them don't get changed
very often and I will have to undergo a testing cycle for each of them just
to maintain compatibility with postgres > 7.2.4. This is not something I
really want to do. I would much prefer to just upgrade and have my legasy
apps work without modification or testing.

Thanks,

Rick Gigger

----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Rick Gigger" <ri**@alpinenetworking.com>
Cc: <pg***********@postgresql.org>
Sent: Monday, October 06, 2003 2:26 PM
Subject: Re: [GENERAL] Possible bug on insert

"Rick Gigger" <ri**@alpinenetworking.com> writes:
What was it that changed in 7.3 that made this behavior change. (it worked in 7.2)


7.2 allowed implicit casts from text to smallint, I think.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #8

P: n/a
Two questions:

1) how would I go about doing that
2) is there any change that doing that could break other things?

thanks,

Rick Gigger

----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Rick Gigger" <ri**@alpinenetworking.com>
Sent: Monday, October 06, 2003 2:57 PM
Subject: Re: [GENERAL] Possible bug on insert

Is there any possiblity of adding an option for compatibilityes sake (or
does one already exist) to revert to the old behavior.


If you're desperate you can mark that cast as implicit by changing its
entry in pg_cast. Not sure what side-effects you might see though.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #9

P: n/a
>>>>> "RG" == Rick Gigger <ri**@alpinenetworking.com> writes:

RG> very often and I will have to undergo a testing cycle for each of them just
RG> to maintain compatibility with postgres > 7.2.4. This is not something I
RG> really want to do. I would much prefer to just upgrade and have my legasy
RG> apps work without modification or testing.

You can't have progress that way. To be fair, it *is* a major version
number change, and if you don't test your apps across major version
changes of *anything* you don't need to be in this business. No
offense ;-)

Anyhow, Bruce posted a patch to the 7.3 line to allow this behavior
back, as it was necessary for some apps. However, don't count on it
for 7.4...

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: kh***@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #10

P: n/a
In article <3F**************@mascari.com>,
Mike Mascari <ma*****@mascari.com> writes:
Ma**********@dom.com wrote:
Has some one come up with a similar type script that could be used in a
Postgresql database? The script below was created for a SQLServer database.
Thx,
-Martin
I haven't. But I was wondering if a general purpose tuple-generating
function, which would be trivial to implement, might be worthwhile in
PostgreSQL or perhaps added to Joe Conway's tablefunc module.
Something like: tuple_generator(integer) which returns a set of numbers whose elements are the integer values
between 1 and the number supplied.


How about this?

CREATE OR REPLACE FUNCTION enum (INT) RETURNS SETOF INT AS '
DECLARE
numvals ALIAS FOR $1;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION enum (INT, INT) RETURNS SETOF INT AS '
DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT minval + currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION enum (INT, INT, INT) RETURNS SETOF INT AS '
DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
maxval ALIAS FOR $3;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval % (maxval - minval + 1) + minval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

Usage: SELECT * FROM enum (numvals [, minval [, maxval]])
Returns numvals consecutive numbers, beginning with 0 or minval
Wraps around to minval if maxval is reached
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #11

P: n/a
In article <3F**************@mascari.com>,
Mike Mascari <ma*****@mascari.com> writes:
Ma**********@dom.com wrote:
Has some one come up with a similar type script that could be used
in a Postgresql database? The script below was created for a SQLServer database.
Thx,
-Martin

I haven't. But I was wondering if a general purpose tuple-generating
function, which would be trivial to implement, might be worthwhile in
PostgreSQL or perhaps added to Joe Conway's tablefunc module.
Something like:

tuple_generator(integer)

which returns a set of numbers whose elements are the integer values
between 1 and the number supplied.


How about this?

CREATE OR REPLACE FUNCTION enum (INT) RETURNS SETOF INT AS '
DECLARE
numvals ALIAS FOR $1;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION enum (INT, INT) RETURNS SETOF INT AS '
DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT minval + currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION enum (INT, INT, INT) RETURNS SETOF INT AS
' DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
maxval ALIAS FOR $3;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval % (maxval - minval + 1) + minval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

Usage: SELECT * FROM enum (numvals [, minval [, maxval]])
Returns numvals consecutive numbers, beginning with 0 or minval
Wraps around to minval if maxval is reached


Or a little different, with the over-loaded functions relying on the
original:

CREATE OR REPLACE FUNCTION public.enum(int4) RETURNS SETOF int4 AS '
DECLARE
numvals ALIAS FOR $1;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION public.enum(int4, int4) RETURNS SETOF int4 AS '
DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
currval RECORD;
BEGIN
FOR currval IN SELECT minval + enum AS enum FROM enum(numvals) LOOP
RETURN NEXT currval.enum;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION public.enum(int4, int4, int4) RETURNS SETOF
int4 AS ' DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
maxval ALIAS FOR $3;
currval RECORD;
/*
From: Harald Fuchs
Date: Wed, October 8, 2003 5:53
To: pg***********@postgresql.org

tuple_generator(integer)

which returns a set of numbers whose elements are the integer values
between 1 and the number supplied.

Usage: SELECT * FROM enum (numvals [, minval [, maxval]])
Returns numvals consecutive numbers, beginning with 0 or minval
Wraps around to minval if maxval is reached
*/

BEGIN
FOR currval IN SELECT * FROM enum(numvals, minval) LOOP
RETURN NEXT currval.enum % maxval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' VOLATILE;
But, as interesting as these look, what would you actually use them for?
~Berend Tober


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #12

P: n/a
In article <64591.66.212.203.144.1065659357.squirrel@$HOSTNAM E>,
<bt****@seaworthysys.com> writes:
Or a little different, with the over-loaded functions relying on the
original: CREATE OR REPLACE FUNCTION public.enum(int4) RETURNS SETOF int4 AS '
DECLARE
numvals ALIAS FOR $1;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' VOLATILE;
Why VOLATILE? Shouldn't that be IMMUTABLE?
(Sorry, but I'm a PostgreSQL newbie.)
But, as interesting as these look, what would you actually use them for?


SELECT extract (month FROM sdate) AS month,
count (*) AS monthly_sales
FROM sales
GROUP BY month
ORDER BY month;

gives you the monthly sales, but what if you would like a result row
also for months with nothing sold?

SELECT enum, count (sdate) AS monthly_sales
FROM enum (12, 1)
LEFT JOIN sales ON enum = extract (month FROM sdate)
GROUP BY enum
ORDER BY enum;

This would do the trick. Is there a more elegant solution?
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.