Another Currval Problem

[Nlaivlys]

I am fairly new to PostgreSQL. I am in the process of converting a MySQL script to postgresql. The mysql script includes a line that reads:
INSERT INTO OVERLAY VALUES
(NULL,LAST_INSERT_ID(),'Grey Standard','basemap',1,'png','y',-1,0,NOW(),NOW()),
(NULL,LAST_INSERT_ID(),'Brown Standard','basemap1',1,'png','y',-1,0,NOW(),NOW());

I am trying to figure out how to get this exact function in the pstgresql db. So far I have this:
CREATE SEQUENCE OVERLAY_SEQ;

CREATE TABLE OVERLAY (
OVERLAY_ID SERIAL DEFAULT nextval('OVERLAY_SEQ'),
PACKAGE_ID SMALLINT NOT NULL,
NAME VARCHAR(64) NOT NULL,
Z_INDEX SMALLINT NOT NULL,
FILE_EXTENSION CHAR(3) NOT NULL,
PRIMARY KEY OVERLAY_ID,
FOREIGN KEY (PACKAGE_ID) REFERENCES PACKAGE PACKAGE_ID)
);

I am trying to use the insert command:
INSERT INTO OVERLAY VALUES
(DEFAULT,currval('OVERLAY_SEQ'),'Grey Standard','basemap',1,'png','y',-1,0,NOW(),NOW()),
(DEFAULT,currval('OVERLAY_SEQ'),'Brown Standard','basemap1',1,'png','y',-1,0,NOW(),NOW())
;

And of course - it's not working. I receive this error message:
psql:create_database.sql:31: ERROR: syntax error at or near "nextval" at character 59

Can anyone help me? I have done a million searches on Google (postgresql currval(), postgresql last_insert_id(), etc.) and still have been unable to come up with a solution. Any help or direction is much appreciated! Thanks, Syl

 

[ceco]

in postgresql you cannot insert two rows in one insert you should do it with two

(it still cat be done in one query, when separate the two inserts with ; )

also it looks strange that you insert the sequence value in other field PACKAGE_ID!! why?

and actually the number of fields you try to insert doesn't have anything to do with the fields in the table (there are no date fields and etc.)

 

[Nlaivlys]

I'm not too sure how to use the sequence identifier. As for the date fields - I simply cut out some of the declarations in the overlay table. I cannot seem to get the auto increment field to work. Is there anything else I can do to fix that auto increment field?

 

[Nlaivlys]

The good news is, I have figured out what the problem was with the above query - I was using the wrong field as the auto increment. However, once one problem is solved, another pops up. I am looking into how to perform an insert into a table using a select statement. Here are the 3 queries I am trying to perform:
INSERT INTO WX_PREFERENCE SELECT DEFAULT,WX_USER.USER_ID,DEFAULT_PREFERENCE.X_POINT,DEFAULT_PREFERENCE.Y_POINT,DEFAULT_PREFERENCE.SCALE,NOW(),NOW() FROM WX_USER,DEFAULT_PREFERENCE;

INSERT INTO PACKAGE_PREFERENCE SELECT DEFAULT,WX_PREFERENCE.PREFERENCE_ID,DEFAULT_PACKAGE_PREFERENCE.PACKAGE_ID,DEFAULT_PACKAGE_PREFERENCE.VISIBLE,DEFAULT_PACKAGE_PREFERENCE.TREE_STATE_VIEW FROM WX_PREFERENCE,DEFAULT_PACKAGE_PREFERENCE;

INSERT INTO OVERLAY_PREFERENCE
SELECT DEFAULT,PACKAGE_PREFERENCE.PACKAGE_PREFERENCE_ID,DEFAULT_OVERLAY_PREFERENCE.OVERLAY_ID,DEFAULT_OVERLAY_PREFERENCE.VISIBLE
FROM PACKAGE_PREFERENCE,DEFAULT_OVERLAY_PREFERENCE
WHERE PACKAGE_PREFERENCE.PACKAGE_ID = DEFAULT_OVERLAY_PREFERENCE.PACKAGE_ID;

Does anyone see what is wrong with my syntax. I get these errors:
ERROR: syntax error at or near "," at character 34
ERROR: syntax error at or near "," at character 34
ERROR: syntax error at or near "DEFAULT" at character 34
ERROR: syntax error at or near "DEFAULT" at character 39
ERROR: syntax error at or near "DEFAULT" at character 41

Nothing else is mentioned though.

 

[ceco]

you cannot use DEFAULT like that try using something like

insert into table_name (list_of_values, comma_separated) values select blah, blah2 .... from ....

 

[Nlaivlys]

Thank you - it worked by listing the values and leaving off the default incrementer.