0

I used this query:

select *
into airbnb_parcels
from pcpao_res 
natural join AIRBNB_PARCELS;

and got:

Error: relation "airbnb_parcels" already exits.

enter image description hereIn DBeaver I used the following query:

SELECT * FROM pcpao_res NATURAL JOIN airbnb_parcels;

But the resulting data only appears in the "output" window below the query panel, not the actual table. I've saved the project, committed the query, etc. but it won't update the table.

It's a large dataset so in the screenshots I opened up a filter on the same column in both the query window and the table window to show how values are present in the former but not the latter.

What am I doing wrong?enter image description here

closed as off-topic by Colin 't Hart, mustaccio, Max Vernon, Evan Carroll, Michael Green Jan 14 at 18:54

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Colin 't Hart, Max Vernon, Michael Green
If this question can be reworded to fit the rules in the help center, please edit the question.

  • What do you mean with "not the actual table"? You run a SELECT query. DId you expect the table(s) to be updated? – ypercubeᵀᴹ Jan 13 at 22:49
  • RE: yper-crazhat-cube... yeah I guess. SELECT INTO and INSERT INTO are giving me "table already exists". I know this is super basic but I don't understand why previous SELECT INTO queries moved data into the table but now I'm getting "table already exists" errors. – gulfy Jan 13 at 23:00
  • Well, you don't show us any SELECT INTO or INSERT. You only showed a SELECT. – ypercubeᵀᴹ Jan 13 at 23:03
  • updated my question to include another screenshot – gulfy Jan 13 at 23:05
  • Please post the code as text, not images and screenshots. – ypercubeᵀᴹ Jan 13 at 23:06
1

Let me suggest to read carefully the Postgres docs:


SELECT INTO

SELECT INTO — define a new table from the results of a query

Description

SELECT INTO creates a new table and fills it with data computed by a query. The data is not returned to the client, as it is with a normal SELECT. The new table's columns have the names and data types associated with the output columns of the SELECT.

Notes

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.


That is:

  • SELECT INTO creates a new table, the new table must not exists previously.

  • INSERT INTO requires an existent table.

Example:

CREATE TABLE t1 (id int, name text);
INSERT INTO t1 VALUES
(1, '1111'),(2, '2222'),(3, '3333');


-- Correct, t2 doesn't exists.

SELECT id, name INTO t2 FROM t1;


SELECT id, name FROM t2;

id | name
-: | :---
 1 | 1111
 2 | 2222
 3 | 3333
CREATE TABLE t3 (id int, name text);

-- Error, t3 already exists
SELECT id, name INTO t3 FROM t1;

ERROR: relation "t3" already exists

-- Error, t4 doesn't exists
INSERT INTO t4
SELECT id, name FROM t1;

ERROR: relation "t4" does not exist LINE 1: INSERT INTO t4 ^

-- Correct, t3 already exists
INSERT INTO t3
SELECT id, name FROM t1;

SELECT * FROM t3;

id | name
-: | :---
 1 | 1111
 2 | 2222
 3 | 3333

db<>fiddle here

  • Thanks, I've since read through the documentation and have resolved the issue. – gulfy Jan 15 at 4:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.