Re: Table both does not and does exist! wth?

[hubert depesz lubaczewski <depesz@xxxxxxxxxx>]

On Fri, Dec 17, 2010 at 07:47:44AM -0800, Melvin Davidson wrote:
> enf=# DROP TABLE IF EXISTS xtmp_changed_ids;
> NOTICE:Â table "xtmp_changed_ids" does not exist, skipping
> DROP TABLE
> enf=# CREATE TABLE xtmp_changed_ids
> Â(
>  seq_all SERIAL NOT NULL,
> Â new_idÂÂ VARCHAR(200),
> Â idÂÂÂÂÂÂ VARCHAR(200),
> Â pinÂÂÂÂÂ VARCHAR(200),
> Â pcÂÂÂÂÂÂ VARCHAR(200),
>  site_id INTEGER,
> Â statusÂÂ INTEGER,
> Â csnÂÂÂÂÂ INTEGER,
> Â raw_seconds INTEGER,
> Â lastnameÂÂÂ VARCHAR(200),
> Â firstnameÂÂ VARCHAR(200),
> Â CONSTRAINT xtmp_changed_ids PRIMARY KEY (seq_all)
> Â ) WITH (OIDS = FALSE);
> NOTICE:Â CREATE TABLE will create implicit sequence "xtmp_changed_ids_seq_all_seq" for serial column "xtmp_changed_ids.seq_all"
> NOTICE:Â CREATE TABLE / PRIMARY KEY will create implicit index "xtmp_changed_ids" for table "xtmp_changed_ids"
> ERROR:Â relation "xtmp_changed_ids" already exists
> enf=#
> enf=# SELECT * FROM pg_class WHERE relname = 'xtmp_changed_ids';
> Ârelname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relfrozenxid | relacl | reloptions
> ---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------------+--------+------------
> (0 rows)

please don't name your constraint using the same name you named your
table.

constraint name is used to create index, and at the time of index
creation - table already exists.

just skip "CONSTRAINT xtmp_changed_ids" part, and you'll be fine.

depesz

-- 
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@xxxxxxxxxx / aim:depeszhdl / skype:depesz_hdl / gg:6749007

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general