January 7, 2022 at 4:21 pm
I'm trying to clone a table in sql with partial data but with all structure (index, etc). This is the code I'm trying to execute:
CREATE TABLE "new table" (LIKE "old table")
INSERT INTO "new table"
SELECT *
FROM "old table"
WHERE "date column" > ('now'::text::date - '1 year'::interval)
The error message says INSERT is wrong. When I execute only with LIKE it works, but when I try to input the data it doesn't.
Furthermore, the WHERE function is also correctly working.
Also, when I use CREATE TABLE "" AS it works and brings only the data without the structure. When I use LIKE it works and brings the structure without data. But, when I try to bring both data and structure it doesn't work and that is my question.
January 7, 2022 at 5:32 pm
My advice - post the error message. I have a feeling it doesn't say the literal words "INSERT is wrong".
Now, I am also not 100% familiar with PostgreSQL, but I THINK it expects statement terminators. So when PostgreSQL goes to parse that, it is seeing it all as 1 single command, not 2 commands - one to create the table and one to do the INSERT.
Add a ; to the end of your create table and (likely) the end of your WHERE and it MAY stop complaining.
If the above is incorrect (which it VERY well could be as I am not a PostgreSQL DBA and have only dabbled in PostgreSQL VERY lightly), post the full error message and someone is more likely to help.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply