Ashraf Hassanein
asked on
How to convert this query to postgres
I have the following mysql command, how can I change to postgres:
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp."ID" = pm.post_id WHERE wp."ID" IS NULL;SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The original query is actually a left join that tells you to delete all records from left which have no counterparts in right.
Not sure about mysql approach but it's important that the predicate "null = null" in postgresql is not true. It's NULL and as such, left join on a NULL field is simply not possible. Therefore I'd say what you're looking for is:
- "NOT EXISTS" is usually faster than "NOT IN"
- you can't use alias (e.g. pm) for the table name to delete from but you can use alias in the subquery.
Not sure about mysql approach but it's important that the predicate "null = null" in postgresql is not true. It's NULL and as such, left join on a NULL field is simply not possible. Therefore I'd say what you're looking for is:
DELETE FROM wp_postmeta WHERE NOT EXISTS
(SELECT 1 FROM wp_posts wp WHERE wp.id = wp_postmeta.id);- "NOT EXISTS" is usually faster than "NOT IN"
- you can't use alias (e.g. pm) for the table name to delete from but you can use alias in the subquery.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your support, I think that it would be better to make a select first to see the content, but when I am trying to do :
I got error:
Although the ID is column in such table:
SELECT COUNT(*) FROM wp_postmeta WHERE post_id IS NULL OR post_id NOT IN (SELECT ID FROM wp_posts);I got error:
ERROR: column "id" does not exist
LINE 1: ...a WHERE post_id IS NULL OR post_id NOT IN (SELECT ID FROM ha...Although the ID is column in such table:
\d+ wp_posts;
Table "public.wp_posts"
Column | Type | Modifiers | Storage | Description
-----------------------+-----------------------------+-------------------------------------------------------------+----------+-------------
ID | bigint | not null default nextval(('has_posts_seq'::text)::regclass) | plain |
post_author | bigint | not null default 0::bigint | plain |
post_date | timestamp without time zone | not null default now() | plain |
post_date_gmt | timestamp without time zone | not null default timezone('gmt'::text, now()) | plain |
post_content | text | not null | extended |
post_title | text | not null | extended |
post_excerpt | text | not null | extended |
post_status | character varying(20) | not null default 'publish'::character varying | extended |
comment_status | character varying(20) | not null default 'open'::character varying | extended |
ping_status | character varying(20) | not null default 'open'::character varying | extended |
post_password | character varying(20) | not null default ''::character varying | extended |
post_name | character varying(200) | not null default ''::character varying | extended |
to_ping | text | not null | extended |
pinged | text | not null | extended |
post_modified | timestamp without time zone | not null default now() | plain |
post_modified_gmt | timestamp without time zone | not null default timezone('gmt'::text, now()) | plain |
post_content_filtered | text | not null | extended |
post_parent | bigint | not null default 0::bigint | plain |
guid | character varying(255) | not null default ''::character varying | extended |
menu_order | integer | not null default 0 | plain |
post_type | character varying(20) | not null default 'post'::character varying | extended |
post_mime_type | character varying(100) | not null default ''::character varying | extended |
comment_count | bigint | not null default 0::bigint | plain |
Indexes:
"wp_posts_pkey" PRIMARY KEY, btree ("ID")
"wp_posts_post_author" btree (post_author)
"wp_posts_post_name" btree (post_name)
"wp_posts_post_parent" btree (post_parent)
"wp_posts_type_status_date" btree (post_type, post_status, post_date, "ID")
Has OIDs: no
The ID is reserved word so you'll probably need to use quoted form:
SELECT COUNT(*) FROM wp_postmeta WHERE post_id IS NULL OR post_id NOT IN (SELECT "ID" FROM wp_posts);
More info: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
SELECT COUNT(*) FROM wp_postmeta WHERE post_id IS NULL OR post_id NOT IN (SELECT "ID" FROM wp_posts);
More info: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks I managed to execute the query successfully finally
PostgreSQL has a non ANSI standard extension USING clause for DELETE so the equivalent syntax would be ...
DELETE FROM wp_postmeta AS pm USING wp_posts AS wp
WHERE pm.post_id = wp."ID" AND wp."ID" is null;
This is the same as
delete from wp_postmeta where post_id is null;
except there would have to be entries in wp_posts table where wp_posts.id = NULL
It is always better to catch this on input using table column NOT NULL criteria.