CODEWITHSUNDEEP
postgresqlsql-update
JohnJ
JohnJ

Reputation: 7056

postgres update table based on another table

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.

Upvotes: 0

Views: 980

Answers (1)

joop
joop

Reputation: 4523

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
  ;

Upvotes: 2

Related Questions