Postgresql: insert with nested select and NEXTVAL in sequence

Question

I have sought and not found the answer to this problem:

I insert a row using nested select but also need the uid sequence and datestamp

SQL insert

 insert into countdegreejob   (countdegreeid,jobid,uniquejobid,  id, created, updated) 

 select (cjtbdn.countdegreeid, j.id, j.uniquejobid ) NEXTVAL('hibernate_sequence'), now(), now()
 from job j 

 right join job_areasofstudy jd on j.id = jd.job_id

 inner join countjobtitlebydegreename cjtbdn on j.uniquejobid=cjtbdn.uniquejobid 

 where cjtbdn.degreename = jd.areasofstudy and jd.job_id is not NULL

I get the following error:

ERROR: syntax error at or near "(" LINE 2: ...jtbdn.countdegreeid, j.id, j.uniquejobid ) NEXTVAL('hibernat...

Any help would be much appreciated

Gordon Linoff · Accepted Answer · 2017-06-25 19:52:45Z

2

Try removing the parentheses and adding a comma:

select cjtbdn.countdegreeid, j.id, j.uniquejobid, NEXTVAL('hibernate_sequence'), now(), now()

When you enclose columns in parentheses, you are telling Postgres that you want a record format. So, these are not the same:

select 1, 2
select (1, 2)

The first returns two columns. The second returns one column which happens to be a record with two fields. I doubt any of the columns in your tables are actually records.

Wait. That insert doesn't make full sense. You are inserting 6 columns but have only 5 in the insert list. What do you really want to do?

edited Jun 25, 2017 at 19:52

answered Jun 25, 2017 at 19:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jake Over a year ago

that was a typo. there should be an "id" in there

Rahul · Accepted Answer · 2017-06-25 19:27:06Z

1

You are missing a , before NEXTVAL as below

select (cjtbdn.countdegreeid, j.id, j.uniquejobid )  NEXTVAL('hibernate_sequence'),
                                                   ^..... Here

Also those parenthesis () in select are not required

answered Jun 25, 2017 at 19:27

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

1 Comment

user330315 Over a year ago

Actually the parentheses are not only "not required", they are plain wrong. (cjtbdn.countdegreeid, j.id, j.uniquejobid) is a single column (a record with three fields). cjtbdn.countdegreeid, j.id, j.uniquejobid are three columns.

Collectives™ on Stack Overflow

Postgresql: insert with nested select and NEXTVAL in sequence

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related