PostGreSQL creation question

[Draith]

hey guys,

I'm fairly new to SQL in general and PostGreSQL especially. I've been working at porting a database dump from MySQL to postgresql and have come down to a final three errors that i cannot for the life of me resolve or figure out what is wrong. I was hoping someone out there could tell me how these lines do not work in the parser.

user_newpasswd varchar(32) default NULL,

submitter varchar(60) NOT NULL default '',

submitter varchar(60) NOT NULL default '',

any clue? I'm wondering if the numbers are invalid to the datatype somehow, but i don't know how. I can't seem to find information on any sort of limitation there. The rest of the line looks perfectly good to me, but i keep receiving an error when trying to create the tables these line are in.

oy,
o Draith

[Draith]

ok...

so i do a line by line insertion of columns into the respective tables, only to find that i had misidentified the problems. :) oops. turns out the problems were much simpler, including a missing comma at the end of the previous line in two of the instances, and the third problem was that i had read the wrong line as being wrong.

for those who wish to know, there was a problem with the conversion program i used, and it did not change the datatype 'double(6,4)' to 'numeric(6,4)' so i'm happy with that now...

HOWEVER :) I am still running into some trouble. this time I can identify the exact location.

It involves the INSERTion of a row into a table. When the program comes across the value 'l' (that's L) , the program gives me this error message:

ERROR: pg_atoi: error in "l": can't parse "l"

The column where this trouble occurs reads like this:

bposition char(1) NOT NULL default '',

everything seems kosher to me (again) yet i cannot for the life of me figure out what's wrong...

help? :)
o Draith

[Kodo]

are you trying to make a bit field?

[Draith]

hey Kodo,

I don't believe it's trying to make a bitfield. I'm looking down the list of INSERTs and the values for that column are all "l" (L's) or 'r', and by the name of the table, I'm guessing that it's referring to positioning of something or other. I suppose I should be more informative :) I'll post the table, and all the inserts. First I should say that the package I mostly converted to Postgresql from mysql is the PHP-Nuke webportal. The version of php-nuke I'm using is v6.5 with PostGreSQL v7.3.1
Here're the table and Inserts (I stopped at the first insert error, since it seemed it would do the same for all of them)

CREATE TABLE tnuke_blocks (
bid SERIAL,
bkey varchar(15) NOT NULL default '',
title varchar(60) NOT NULL default '',
content text NOT NULL,
url varchar(200) NOT NULL default '',
bposition char(1) NOT NULL default '',
weight int NOT NULL default '1',
active int NOT NULL default '1',
refresh int NOT NULL default '0',
time varchar(14) NOT NULL default '0',
blanguage varchar(30) NOT NULL default '',
blockfile varchar(255) NOT NULL default '',
view int NOT NULL default '0',
PRIMARY KEY (bid)
) ;

INSERT INTO tnuke_blocks VALUES (1, '', 'Modules', '', '', 'l', 1, 1, 0, '', '', 'block-Modules.php', 0);

INSERT INTO tnuke_blocks VALUES (2, 'admin', 'Administration', '<strong><big>·</big></strong> <a href="admin.php">Administration</a><br>\r\n<strong><big>·</big></strong> <a href="admin.php?op=adminStory">NEW Story</a><br>\r\n<strong><big>·</big></strong> <a href="admin.php?op=create">Change Survey</a><br>\r\n<strong><big>·</big></strong> <a href="admin.php?op=content">Content</a><br>\r\n<strong><big>·</big></strong> <a href="admin.php?op=logout">Logout</a>', '', 'l', 2, 1, 0, '985591188', '', '', 2);

INSERT INTO tnuke_blocks VALUES (3, '', 'Who\'s Online', '', '', 'l', 3, 1, 0, '', '', 'block-Who_is_Online.php', 0);

INSERT INTO tnuke_blocks VALUES (4, '', 'Search', '', '', 'l', 4, 0, 3600, '', '', 'block-Search.php', 0);

INSERT INTO tnuke_blocks VALUES (5, '', 'Languages', '', '', 'l', 5, 1, 3600, '', '', 'block-Languages.php', 0);

INSERT INTO tnuke_blocks VALUES (6, '', 'Random Headlines', '', '', 'l', 6, 0, 3600, '', '', 'block-Random_Headlines.php', 0);

INSERT INTO tnuke_blocks VALUES (7, '', 'Amazon', '', '', 'l', 7, 1, 3600, '', '', 'block-Amazon.php', 0);

INSERT INTO tnuke_blocks VALUES (8, 'userbox', 'User\'s Custom Box', '', '', 'r', 1, 1, 0, '', '', '', 1);

INSERT INTO tnuke_blocks VALUES (9, '', 'Categories Menu', '', '', 'r', 2, 0, 0, '', '', 'block-Categories.php', 0);

INSERT INTO tnuke_blocks VALUES (10, '', 'Survey', '', '', 'r', 3, 1, 3600, '', '', 'block-Survey.php', 0);

INSERT INTO tnuke_blocks VALUES (11, '', 'Login', '', '', 'r', 4, 1, 3600, '', '', 'block-Login.php', 3);

INSERT INTO tnuke_blocks VALUES (12, '', 'Big Story of Today', '', '', 'r', 5, 1, 3600, '', '', 'block-Big_Story_of_Today.php', 0);

INSERT INTO tnuke_blocks VALUES (13, '', 'Old Articles', '', '', 'r', 6, 1, 3600, '', '', 'block-Old_Articles.php', 0);

INSERT INTO tnuke_blocks VALUES (14, '', 'Information', '<br><center><font class="content">\r\n<a href="http://phpnuke.org"><img src="images/powered/phpnuke.gif" border="0" alt="Powered by PHP-Nuke" title="Powered by PHP-Nuke" width="88" height="31"></a>\r\n<br><br>\r\n<a href="http://validator.w3.org/check/referer"><img src="images/html401.gif" width="88" height="31" alt="Valid HTML 4.01!" title="Valid HTML 4.01!" border="0"></a>\r\n<br><br>\r\n<a href="http://jigsaw.w3.org/css-validator"><img src="images/css.gif" width="88" height="31" alt="Valid CSS!" title="Valid CSS!" border="0"></a></font></center><br>', '', 'r', 7, 1, 0, '', '', '', 0);

[Kodo]

damnit.. now I have to learn another db system.. shit.

I don't have an answer for you. The syntax is different than MSSQL.

[Draith]

heh,

well postgres is pretty fun. I'm willing to bet, however that mssql isn't *vastly* different from postgres - would you be able to translate the appropriate lines, roughly, and figure what would be wrong with it in relation to mssql? that's part of the way i'm going at it - if i can't figure out what's wrong in the postgres, than maybe i can find the answer in a separate system.

ciao!
o Draith

[Draith]

hrm. well changing the bposition column's datatype from char(1) to varchar(1) seemed to do the trick.

do you suppose this might have an ill effect on the positioning of left and right that the column was (i assume) designed for?

[Draith]

oyoyoy! I'm posting like crazy. So that all may learn from my errors, I've come across the real reason that the INSERT wasn't working with char(1).

It's because the order in which you add columns to a table, is the order the columns are represented, and the INSERT command I was using, assumed the columns to be in a certain order, and so it was inputing 'l' (again, that's 'L') into the wrong column. I dropped the table and rewrote it in the proper order (although i bet there was an easier solution) and all works kosher once again.

thanks for you help though Kodo.

[Kodo]

I didn't take the time to look at your insert statements for ordering.. as you've discovered, it must be in the same order. :D

[Fat Tech]

Is the programming software freeware?? I wish to learn how to work with PHP and ASP.

[Kodo]

PHP is free and ASP3 comes with Windows 2000/XP.
MySQL is free and I believe postrge is as well.