如何选择除一个表之外的表的所有列

My code looks like:

我的代码看起来像：

 CREATE TABLE tableC AS
 (SELECT tableA.*,
  ST_Intersection (B.geom, A.geom) as geom2  -- generate geom
 FROM tableB, tableA
 JOIN tableB
 ON ST_Intersects (A.geom, b.geom)
 WHERE test.id = 2);

Now It is working but I have two columns geom and geom2! Inside geom column I will have the new geometry based on the intersection. So how can I select tableA except the geom column?

现在它正在工作，但我有两个列geom和geom2！在geom列内部，我将根据交点得到新的几何体。那么我怎样才能选择除了geom列之外的tableA？

2 个解决方案

#1

Create the table with all the columns and after that drop the geom column and rename the new one:

创建包含所有列的表，然后删除geom列并重命名新列：

CREATE TABLE tableC AS
SELECT
    tableA.*,
    ST_Intersection (B.geom, A.geom) as geom2  -- generate geom
FROM
    tableA inner JOIN tableB ON ST_Intersects (A.geom, b.geom)
WHERE test.id = 2
;

alter table tableC drop column geom;
alter table tableC rename column geom2 to geom;

#2

The only way you would be able to do this would be to generate a dynamic SQL statement based on the columns within the table that excludes those you don't want. Obviously this will be a lot more effort than simply adding in all the column names.

您能够做到这一点的唯一方法是根据表中的列排除动态SQL语句，这些列排除了您不想要的列。显然，这比简单地添加所有列名要多得多。

There are also a lot of very good reasons to never include a select * in a production environment, given how picky SQL often is on the number and format of columns that are returned. By using select * you open yourself up to a changing query result in the future that could potentially break things.

考虑到返回的列的数量和格式经常出现挑剔的SQL，因此在生产环境中永远不会包含select *也有很多很好的理由。通过使用select *，您可以在将来打开一个可能会破坏事物的更改查询结果。

If you have a LOT of columns and you simply don't want to manually type them all out, run the query below for your table and then format the result so you can copy/paste into your script:

如果你有很多列而你根本不想手动输入它们，请为你的表运行下面的查询，然后格式化结果，这样你就可以复制/粘贴到你的脚本中：

SELECT *
FROM information_schema.columns
WHERE table_schema = 'your_schema'
  AND table_name   = 'your_table'

#1