postgresql将所有列插入表中

huangapple go评论65阅读模式
英文:

postgresql INSERT INTO all columns from a table

问题

我正在尝试编写一个从表中移除重复项的方法,而不需要知道表的详细信息以实现通用性(即,它应该适用于任何表)。

我正在使用来自这里(最后一个方法)通过 psycopg2 的以下方法:

CREATE TABLE tempTable (LIKE "{table}");
INSERT INTO tempTable(*)
  SELECT
    DISTINCT ON ("{column}") *
    FROM "{table}";
DROP TABLE "{table}";
ALTER TABLE tempTable
  RENAME TO "{table}";
DROP table tempTable

问题在于你无法在 INSERT INTO 命令中使用 * 来获取所有列。我们需要指定要插入的所有列的列表,这对于控制可能是有益的,但对于我的目的来说并不理想。

我知道可以获取表的列名列表:

SELECT column_name FROM information_schema.columns WHERE table_name = "{table}";

但我无法将其直接替换 * 作为子查询。

对这个8年前的问题的回答表明,这在SQL中可能是不可能的。这可能仍然是真实情况,或者可能已经发生改变。

现在是否可以做到这一点?

英文:

I am trying to write a method that removes duplicates from tables, without having to know the details of the table for generality (i.e., it should run on any table).

I am using the following method from here (last method) through psycopg2:

CREATE TABLE tempTable (LIKE "{table}");
INSERT INTO tempTable(*)
  SELECT
    DISTINCT ON ("{column}") *
    FROM "{table}";
DROP TABLE "{table}";
ALTER TABLE tempTable
  RENAME TO "{table}";
DROP table tempTable

The problem is that you can't use * to get all columns inside the INSERT INTO command. We are expected to specify a list of all the columns to insert, which is probably good for control, but is bad for my purpose here.

I know I can get the list of column names for the table with

SELECT column_name FROM information_schema.columns WHERE table_name = "{table}"

but I can't just plug that as a subquery in place of the *.

Answers to this 8 year old question state that it's just not possible in SQL. That might still be true, or it might not be true.

Can this be done now?

答案1

得分: 2

你的链接指向MySQL数据库,在PostgreSQL中可以完全有效地使用insert without the column list

INSERT INTO tempTable 
  SELECT
    DISTINCT ON (a) *
    FROM t;

这并不是最佳实践(因为如果列顺序突然不符合预期,可能会导致问题),但对于你的用例,目标表是通过从源表使用like创建的,这将起作用。

两个额外的要点:

  • 你可能想在选择不同的时候添加order by以启用确定性结果(哪些重复的行将被删除,哪些保留)。

  • 最终的DROP table tempTable放错地方了,因为这个表在之前的语句中已经被重命名,所以不再存在。

英文:

Your link points to MySQLdatabase, in PostgreSQL is perfectly valid to use insert without the column list.

INSERT INTO tempTable 
  SELECT
    DISTINCT ON (a) *
    FROM t;

It is not the best practice (as this may cause problems if the column order is sudenly not as expected), but for your use case where the target table is created with like from the source table will work.

Two additional points

  • You may want to add order by in the select distinct to enable a deterministic outcome (which duplicated row(s) will be removed and which preserve).

  • the final DROP table tempTable is misplaced as this table does not exists anymore as beeing renamed in the previous statement...

huangapple
  • 本文由 发表于 2023年4月6日 19:02:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/75948762.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定