Postgresql With-Delete "Relation Does Not Exists"

PostgreSQL with-delete relation does not exists

that's because CTE in PostgreSQL works differently than CTE in SQL Server. In SQL Server CTE are like an updatable views, so you can delete from them or update them, in PostgreSQL you cannot.

you can join cte and delete, like:

with cte as (
    select
        id,
        row_number() over(partition by code, card_id, parent_id order by id desc) as rn
    from card
)
delete
from card
where id in (select id from cte where rn > 1)

On the other hand, you can write DDL statements inside CTE in PostgreSQL (see documentation) and this could be very handy. For example, you can delete all rows from card and then insert only those having row_number = 1:

with cte1 as (
    delete
    from card
    returning *
), cte2 as (
    select
        row_number() over(partition by code, card_id, parent_id order by id desc) as rn,
        *
    from cte1
)
insert into card
select <columns here>
from cte2
where rn = 1

Cannot simply use PostgreSQL table name ( relation does not exist )

From what I've read, this error means that you're not referencing the table name correctly. One common reason is that the table is defined with a mixed-case spelling, and you're trying to query it with all lower-case.

In other words, the following fails:

CREATE TABLE "SF_Bands" ( ... );

SELECT * FROM sf_bands;  -- ERROR!

Use double-quotes to delimit identifiers so you can use the specific mixed-case spelling as the table is defined.

SELECT * FROM "SF_Bands";

---

Re your comment, you can add a schema to the "search_path" so that when you reference a table name without qualifying its schema, the query will match that table name by checked each schema in order. Just like PATH in the shell or include_path in PHP, etc. You can check your current schema search path:

SHOW search_path
  "$user",public

You can change your schema search path:

SET search_path TO showfinder,public;

See also http://www.postgresql.org/docs/8.3/static/ddl-schemas.html

Postgres giving relation does not exist error for alias

In MS Sql Server it's possible to update the CTE.

In Postgresql you can link to a CTE for an update.

For example:

WITH CTE AS (
  SELECT id FROM source
  WHERE Data= '151234567890' 
   AND flag IS NULL
  ORDER BY id DESC
  FETCH FIRST 96 ROWS ONLY
) 
UPDATE source t
SET flag = 'Z'
FROM CTE c
WHERE t.id = c.id;

However, such update assumes that the id in that table is unique.

But, to mark the newest duplicates it can be simplified.

UPDATE source tgt
SET flag = 'Z'
FROM source src
WHERE src.Data = '151234567890'
  AND tgt.Data = src.Data
  AND tgt.id = src.id
  AND tgt.ctid > src.ctid
  AND tgt.flag IS NULL;

Or if you want to flag the higher amounts.

Using ROW_NUMBER could help.

WITH CTE AS (
  SELECT ctid, id, Data, Amt
  , ROW_NUMBER() OVER (PARTITION BY Data, id ORDER BY Amt) AS rn
  FROM source
  WHERE Data= '151234567890' 
   AND flag IS NULL
) 
UPDATE source t
SET flag = 'Z'
FROM CTE c
WHERE c.rn > 1
  AND t.id = c.id
  AND t.Data = c.Data
  AND t.ctid = c.ctid;

Test on db<>fiddle here

Postgresql tables exists, but getting relation does not exist when querying

You have to include the schema if isnt a public one

SELECT *
FROM <schema>."my_table"

Or you can change your default schema

SHOW search_path;
SET search_path TO my_schema;

Check your table schema here

SELECT *
FROM information_schema.columns

For example if a table is on the default schema public both this will works ok

SELECT * FROM parroquias_region
SELECT * FROM public.parroquias_region

But sectors need specify the schema

SELECT * FROM map_update.sectores_point

relation old does not exist

For the first INSERT, use something like

INSERT INTO organization_history VALUES (OLD.*);

The DELETE and the second INSERT are ill-conceived – for one, this will cause a lot of unnecessary churn in the organization table.

It would be much better to use a BEFORE trigger, add 1 to NEW.version and return NEW. This would cause the values to be adjusted before the record is written to the table.

Getting Relation does not exists for existing Postgres view in Rails

Shouldn't it be lowercase: consultants_clients_tasks?

---

Related Topics