Обсуждение: Populate Table From Two Other Tables
I want to combine columns in two tables and use the exported resulting
table for statistical analyses. The SQL script is:
INSERT INTO waterchem (site, sampdate, param, quant, ceneq1, low, high,
stream, basin)
SELECT c.site, c.sampdate, c.param, c.quant, c.ceneq1, c.low, c.high,
s.stream, s.basin
FROM chemistry as c, sites as s
WHERE c.site == s.siteid;
The problem is that both c.site and s.siteid are of type VARCHAR(16) and
postgres tells me,
ERROR: operator does not exist: character varying == character varying
and provides the hint to add explicit type casts. Since the string length of
site/siteid varies I don't see how to cast both to a working type.
Please suggest how I can populate this table while avoiding the operator
error.
Rich
Rich -- > I want to combine columns in two tables and use the exported resulting >table for statistical analyses. The SQL script is: > >INSERT INTO waterchem (site, sampdate, param, quant, ceneq1, low, high, > stream, basin) > SELECT c.site, c.sampdate, c.param, c.quant, c.ceneq1, c.low, c.high, > s.stream, s.basin > FROM chemistry as c, sites as s > WHERE c.site == s.siteid; > Try a single equals sign, e.g. WHERE c.site = s.siteid > The problem is that both c.site and s.siteid are of type VARCHAR(16) and >postgres tells me, > >ERROR: operator does not exist: character varying == character varying > >and provides the hint to add explicit type casts. Since the string length of >site/siteid varies I don't see how to cast both to a working type. > > Please suggest how I can populate this table while avoiding the operator >error. > HTH, Greg Williamson
On Tue, 5 Jun 2012, Greg Williamson wrote: > Try a single equals sign, e.g. WHERE c.site = s.siteid Greg, I'm surprised: that worked! I thought the WHERE clause was looking for equivalency, not an assignment. There's another problem now that will be more difficult to fix. Postgres tells me, 'psql:populate-waterchem.sql:6: ERROR: duplicate key value violates unique constraint "waterchem_pkey"'. But, when I SELECT rows based on the primary key only a single row is returned. I think the most parsimonious solution is to use an awk script to add the stream and basin columns to the chemistry.txt file. That also eliminates having to export the combined table to a text file. Thanks, Rich
SQL does not distinguish between equality and assignment (mainly because SQL is declarative and thus there is never anything to actually assign to). IOW, there is not "assignment" operator and "=" means "equality". As for the duplicate key I would first check to see if you are inserting into a non-empty table and that one or more of your newly inserted records conflicts with existing records on the waterchem table. The easiest way would be to insert into a staging table and then join the staging and live tables together on the PK and see if any records are returned. David J. > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Rich Shepard > Sent: Tuesday, June 05, 2012 7:28 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Populate Table From Two Other Tables > > On Tue, 5 Jun 2012, Greg Williamson wrote: > > > Try a single equals sign, e.g. WHERE c.site = s.siteid > > Greg, > > I'm surprised: that worked! I thought the WHERE clause was looking for > equivalency, not an assignment. > > There's another problem now that will be more difficult to fix. Postgres tells > me, 'psql:populate-waterchem.sql:6: ERROR: duplicate key value violates > unique constraint "waterchem_pkey"'. But, when I SELECT rows based on > the primary key only a single row is returned. > > I think the most parsimonious solution is to use an awk script to add the > stream and basin columns to the chemistry.txt file. That also eliminates > having to export the combined table to a text file. > > Thanks, > > Rich > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On Tue, 5 Jun 2012, David Johnston wrote: > As for the duplicate key I would first check to see if you are inserting > into a non-empty table and that one or more of your newly inserted records > conflicts with existing records on the waterchem table. The easiest way > would be to insert into a staging table and then join the staging and live > tables together on the PK and see if any records are returned. David, Since the two separate tables have no duplicates I find it difficult to understand how combining the two generates a duplicate row. In any case, I always drop the table before running the script after correcting errors so there should not be a previous row with the same values. Puzzling, Thanks, Rich
On 06/05/2012 04:49 PM, Rich Shepard wrote: > On Tue, 5 Jun 2012, David Johnston wrote: > >> As for the duplicate key I would first check to see if you are inserting >> into a non-empty table and that one or more of your newly inserted >> records >> conflicts with existing records on the waterchem table. The easiest way >> would be to insert into a staging table and then join the staging and >> live >> tables together on the PK and see if any records are returned. > > David, > > Since the two separate tables have no duplicates I find it difficult to > understand how combining the two generates a duplicate row. > > In any case, I always drop the table before running the script after > correcting errors so there should not be a previous row with the same > values. Puzzling, Information that would be helpful: The schema of the waterchem table In particular, what is unique constraint "waterchem_pkey" a constraint on? > > Thanks, > > Rich > > -- Adrian Klaver adrian.klaver@gmail.com
Rich Shepard wrote:
> I'm surprised: that worked! I thought the WHERE clause was looking for
> equivalency, not an assignment.
SQL generally uses "=" to mean equality test, but sometimes it also uses "=" to
mean assignment; it depends on the context; eg, in an UPDATE statement it can
have both meanings:
update mytbl
set foo = 3
where bar = 5;
Though procedural SQL also uses ":=" to mean assignment.
Thinking that "=" could only ever mean assignment is rather short-sighted; while
many programming languages do that, many more don't.
-- Darren Duncan