Re: BUG #18607: UNION ALL discards all foreign key relations + indexes - Mailing list pgsql-bugs
From | hubert depesz lubaczewski |
---|---|
Subject | Re: BUG #18607: UNION ALL discards all foreign key relations + indexes |
Date | |
Msg-id | Zt_4_jOQNLSLVFtf@depesz.com Whole thread Raw |
In response to | BUG #18607: UNION ALL discards all foreign key relations + indexes (PG Bug reporting form <noreply@postgresql.org>) |
List | pgsql-bugs |
On Tue, Sep 10, 2024 at 12:52:21PM +0530, Sanskar Agrawal wrote: > Please find the below DDLS: > > CREATE TABLE test_users ( > user_id SERIAL PRIMARY KEY, > username VARCHAR(50) NOT NULL, > email VARCHAR(100) NOT NULL, > created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP > ); > -- Add an index on the user_id column > CREATE INDEX idx_user_id ON test_users (user_id); > -- Add an index on the email column > CREATE INDEX idx_email ON test_users (email); > INSERT INTO test_users (username, email) VALUES > ('user1', 'user1@example.com'), > ('user2', 'user2@example.com'); > SELECT * FROM temp_users WHERE email = 'user1@example.com' > UNION ALL > SELECT * FROM temp_users WHERE email = 'user2@example.com'; First, please note that your queries run with error: #v+ =$ psql -aX -f z.sql CREATE TABLE test_users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE -- Add an index on the user_id column CREATE INDEX idx_user_id ON test_users (user_id); CREATE INDEX -- Add an index on the email column CREATE INDEX idx_email ON test_users (email); CREATE INDEX INSERT INTO test_users (username, email) VALUES ('user1', 'user1@example.com'), ('user2', 'user2@example.com'); INSERT 0 2 SELECT * FROM temp_users WHERE email = 'user1@example.com' UNION ALL SELECT * FROM temp_users WHERE email = 'user2@example.com'; psql:z.sql:16: ERROR: relation "temp_users" does not exist LINE 1: SELECT * FROM temp_users WHERE email = 'user1@example.com' ^ #v- You meant test_users, but had temp_users. Fixed that, re-ran: #v+ =$ psql -aX -f z.sql CREATE TABLE test_users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE -- Add an index on the user_id column CREATE INDEX idx_user_id ON test_users (user_id); CREATE INDEX -- Add an index on the email column CREATE INDEX idx_email ON test_users (email); CREATE INDEX INSERT INTO test_users (username, email) VALUES ('user1', 'user1@example.com'), ('user2', 'user2@example.com'); INSERT 0 2 SELECT * FROM test_users WHERE email = 'user1@example.com' UNION ALL SELECT * FROM test_users WHERE email = 'user2@example.com'; user_id | username | email | created_at ---------+----------+-------------------+--------------------------- 1 | user1 | user1@example.com | 2024-09-10 09:43:05.72212 2 | user2 | user2@example.com | 2024-09-10 09:43:05.72212 (2 rows) #v- So, the queries work. Now, let's see the table: #v+ =$ psql -aX <<< '\d test_users' \d test_users Table "public.test_users" Column | Type | Collation | Nullable | Default ------------+-----------------------------+-----------+----------+--------------------------------------------- user_id | integer | | not null | nextval('test_users_user_id_seq'::regclass) username | character varying(50) | | not null | email | character varying(100) | | not null | created_at | timestamp without time zone | | | CURRENT_TIMESTAMP Indexes: "test_users_pkey" PRIMARY KEY, btree (user_id) "idx_email" btree (email) "idx_user_id" btree (user_id) #v- The index is there. So nothing got dropped. Can you explain what you're seeing, preferably using tested queries that show the problem? Best regards, depesz
pgsql-bugs by date: