postgres update table based on another table

Question

I am relatively new to postgres (I am a django user - use pgsql via the orm), and I am trying to figure out a way to insert content into a specfic column - but so far, am not having any luck. So, I first have a database dzmodel_uf with two columns: id (which is the PK) and content - both of which are populated (say 50 entries).

Now, I would like to create another table, which references (foreign keys) to id of dzmodel_uf. So, I do the following:

--INITIALIZATION
CREATE TABLE MyNewTable(id integer REFERENCES dzmodel_uf (id));
ALTER TABLE ONLY FullTextSearch ADD CONSTRAINT mynewtable_pkey PRIMARY KEY (id);

which works fine. Now, I create a column on my MyNewTable table like so:

ALTER TABLE MyNewTable ADD COLUMN content_tsv_gin tsvector;

..which also works fine. Finally, I would like to add the content from dzmodel_uf - column content like so:

UPDATE MyNewTable SET content_tsv_gin = to_tsvector('public.wtf', dzmodel_uf(content) )

.. but this FAILS and says that column content does not exist..

In a nutshell, I am not sure how I can reference values from another table.

If the new table is still empty you should INSERT, not UPDATE. — joop
– joop, Commented Dec 16, 2014 at 17:05
@joop: I tried this. When I do this - PGsql does not seem to print out any error messages - but I can see that the table is empty (MyNewTable) wheras dzmodel_uf(content) has around 50 entries! — JohnJ
– JohnJ, Commented Dec 16, 2014 at 17:09
What is your question about. The UPDATE statement or the table design? It's best to ask one clearly defined question. But first you need to learn the basics yourself. — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 16, 2014 at 17:11
@JohnJ If you update an empty table, the update can only affect zero rows. Elementary, dear Watson... — joop
– joop, Commented Dec 16, 2014 at 17:13

joop · Accepted Answer · 2014-12-16 17:16:58Z

2

I hope I understood the question (it is rather fuzzy).There are no rows in the target table, so you have to add them. You need INSERT, not UPDATE :

INSERT INTO  MyNewTable (id,content_tsv_gin)
SELECT dzu.id, to_tsvector( public.wtf, dzu.content )
FROM dzmodel_uf dzu
  ;

answered Dec 16, 2014 at 17:16

joop

4,5431 gold badge18 silver badges26 bronze badges

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

1 Comment

JohnJ Over a year ago

I should have known better - I needed an INSERT and not an UPDATE - Quite stupid of me - Sorry, I am not a real programmer - just trying!

Collectives™ on Stack Overflow

postgres update table based on another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related