2

On PostgreSQL 11, I've created a new table from another, using a subquery :

CREATE TABLE table2 AS (SELECT * FROM table1)

As a result, table2 is created, but each column is in read-only mode within the PGadmin interface.

--EDIT--

To answer to Vérace's comment, i add screenshots of my tables (I use PGadmin). As you can see, Table2's columns have a locker in their name. I can update a value with an SQL Query, but not with PGadmin interface. So, it's a PGadmin problem... !

TABLE 1 Table1

TABLE 2 enter image description here

7
  • 1
    What makes you say that table2 is read-only? Once you've created table2, it is totally independent of table1! À+...
    – Vérace
    Apr 6, 2021 at 17:25
  • 1
    All I can think of is that there's some PGAdmin4 setting that does this? Or maybe you have some strange setting in one of your .conf files? Can you issue this command using the psql CLI tool? CREATE TABLE table3 AS SELECT * FROM table1;. Then check to see if you can INSERT/DELETE/UPDATE? What you describe in your question is not normal behaviour! Or maybe there's a permissions issue - same user for issuing the command as for looking at the table in PGAdmin4?
    – Vérace
    Apr 6, 2021 at 18:10
  • Creating the table with psql makes no difference : I can't edit the table within PGadmin interface, but I can with a SQL update query. It should be something soft-locked in PGadmin... It could be a permission issue : the database is on a distant server, and 2 users have rights on the table (+ postgres). I'm connected as postgres, so I should have all the rights
    – Cupain
    Apr 6, 2021 at 18:30
  • 1
    I don't use pgAdmin, but from other similar questions I recall that pgAdmin requires the table to have a primary key in order to be "editable" - but you can always change the table's data using DML statements manually.
    – user1822
    Apr 6, 2021 at 18:38
  • 1
    If you created table2 with the code shown in your question, it will not have a primary key
    – user1822
    Apr 6, 2021 at 20:38

1 Answer 1

3

When you create a table by using following query

CREATE TABLE table2 AS (SELECT * FROM table1)

table2 is created but the sequences and constraints are not copied from table1. Due to this reason columns became read only mode. You can insert the data inside the read only columns by an insert statement, but not by UI of PgAdmin4. By creating constraints, mainly primary key, you can change read only mode to editable columns and then you can edit the columns by UI of PgAdmin4.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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