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