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

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(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007