Jak zmienić pozycję kolumny w tabeli bazy danych PostgreSQL?


Odpowiedzi:


107

Zmień pozycję kolumny ” na Wiki PostgreSQL mówi:

PostgreSQL obecnie definiuje kolejność kolumn na podstawie attnumkolumny pg_attributetabeli. Jedynym sposobem zmiany kolejności kolumn jest ponowne utworzenie tabeli lub dodanie kolumn i obracanie danych, aż osiągniesz żądany układ.

To dość słabe, ale w ich obronie, w standardowym języku SQL, również nie ma rozwiązania do zmiany pozycji kolumny. Marki baz danych, które obsługują zmianę pozycji porządkowej kolumny, definiują rozszerzenie składni SQL.

Przychodzi mi do głowy jeszcze jeden pomysł: możesz zdefiniować a, VIEWktóry określa kolejność kolumn, tak jak lubisz, bez zmiany fizycznego położenia kolumny w tabeli bazowej.


3
@Dana: Myślę, że to właśnie oznacza „odtworzenie tabeli” w artykule wiki.
Bill Karwin

2
'zrzuć bazę danych' :: świetny sposób na uszkodzenie danych. stąd efekt „wyczyszczenia ogromnej bazy danych”.
Kent Fredric

31
@Kent: Wątpliwe, twórcy postgres przeszli długą drogę, aby zapewnić spójność danych i to z pewnością dotyczyłoby pg_dump. W końcu jaki jest sens robienia kopii zapasowych db, jeśli przywracanie jest zepsute?
Dana the Sane

@Dana, tak, ale dodajesz 1) duży proces zrzutu, a następnie upuszczasz, a następnie masz duże, czasochłonne obciążenie. Jeśli masz poważnie ogromną bazę danych, która jest zwykle bywa z 37 kolumn, będziesz mieć ryzyka z dysku IO zadławienia.
Kent Fredric

@DanatheSane to są bardzo stare komentarze, ale to, co mówił Bill, jest zasadniczo tym samym, zamiast robić pełny zrzut bazy danych, po prostu zrzucasz tabelę (i zależności), zabijasz oryginał, a następnie odtwarzasz. Nie ma potrzeby przełączania całej bazy danych w tryb offline ani tracenia czasu na ponowne tworzenie czegoś, na co nie ma to wpływu. Z drugiej strony, jeśli istnieje wiele zależności, stwierdziłem, że wykonanie pełnego zrzutu (jak zasugerowałeś) jest znacznie łatwiejsze.
vol7ron

38

W PostgreSQL podczas dodawania pola zostanie dodane na końcu tabeli. Jeśli wtedy musimy wstawić w określonej pozycji

alter table tablename rename to oldtable;
create table tablename (column defs go here);
insert into tablename (col1, col2, col3) select col1, col2, col3 from oldtable;

4
Wow, to świetnie, jestem pewien, że to powinna być zaakceptowana odpowiedź!
Tommaso Thea Cioni

Tak, to naprawdę pomocne, wielkie dzięki!
Acuna

Możesz również skopiować bieżącą tabelę do nowej starej tabeli w następujący sposób: CREATE TABLE oldtable AS SELECT * FROM tablename;
Ryan

29

Ten post jest stary i prawdopodobnie rozwiązany, ale miałem ten sam problem. Rozwiązałem to, tworząc widok oryginalnej tabeli określający nową kolejność kolumn.

Stąd mogłem użyć widoku lub utworzyć nową tabelę z widoku.

    UTWÓRZ WIDOK original_tab_vw AS
    SELECT a.col1, a.col3, a.col4, a.col2
    FROM original_tab a
    GDZIE a.col1 NIE JEST NULL - lub cokolwiek
    SELECT * INTO new_table FROM original_tab_vw

Zmień nazwę lub usuń oryginalną tabelę i ustaw nazwę nowej tabeli na starą.


2
Wydaje się, że to całkiem rozsądne rozwiązanie. Szkoda, że ​​nie ma żadnych narzędzi GUI do automatyzacji tej procedury.
Pytania dotyczące Quolonel

4
dobre rozwiązanie, ale kopiowane są tylko typy danych (brak klucza podstawowego itp.)
Regisz

1
Alternatywnie - po prostu użyj widoku z uporządkowanymi kolumnami takimi, jakimi są. Powinien nastąpić minimalny spadek wydajności.
pscl

1
To najlepsze rozwiązanie.
Nikolay Shindarov

23

Jedna, aczkolwiek niezręczna opcja zmiany kolejności kolumn, gdy kolejność kolumn musi zostać bezwzględnie zmieniona, a klucze obce są w użyciu, to najpierw zrzucić całą bazę danych z danymi, a następnie zrzucić tylko schemat ( pg_dump -s databasename > databasename_schema.sql). Następnie edytuj plik schematu, aby zmienić układ kolumn zgodnie z potrzebami, a następnie ponownie utwórz bazę danych ze schematu, a na koniec przywróć dane do nowo utworzonej bazy danych.


1
Dlaczego głos przeciw? Jak wskazuje zaakceptowana odpowiedź, cytując Wiki PostgreSQL: »Jedynym sposobem zmiany kolejności kolumn jest odtworzenie tabeli lub dodanie kolumn i obracanie danych, aż osiągniesz żądany układ.» To rozwiązanie nie jest optymalne (żadna z tych odpowiedzi nie jest związana z brakiem wbudowanej operacji do wykonania zadania), ale zapewnia sposób na zmianę układu kolumn w tabeli.
Ville

1
Powtarzam, że nie ma dobrych sposobów, aby to zrobić, a to, co zarysowałem powyżej, jest właściwym sposobem zmiany kolejności kolumn, jeśli absolutnie trzeba je zmienić. Jeśli zignorujesz moją odpowiedź, skomentuj, dlaczego uważasz, że jest to niedopuszczalne rozwiązanie.
Ville

3
W rzeczywistości jest to bardzo dobre rozwiązanie, gdy tabela jest kluczem obcym w innych tabelach. Wszystkie inne rozwiązania nie działają w tym scenariuszu. Na koniec użyj pg_dump --column-inserts -s
nazwa


5

Otwórz tabelę w PGAdmin iw okienku SQL na dole skopiuj instrukcję SQL Create Table. Następnie otwórz Narzędzie do wysyłania zapytań i wklej. Jeśli tabela zawiera dane, zmień nazwę tabeli na „nowa_nazwa”, jeśli nie, usuń komentarz „-” w wierszu Drop Table. Edytuj sekwencję kolumn zgodnie z wymaganiami. Zwróć uwagę na brakujący / zbędny przecinek w ostatniej kolumnie na wypadek, gdybyś go przeniósł. Wykonaj nowe polecenie SQL Create Table. Odśwież i ... voilà.

W przypadku pustych tabel na etapie projektowania ta metoda jest dość praktyczna.

W przypadku, gdy tabela zawiera dane, musimy również zmienić kolejność kolumn danych. To proste: użyj, INSERTaby zaimportować starą tabelę do nowej wersji za pomocą:

INSERT INTO new ( c2, c3, c1 ) SELECT * from old;

... gdzie c2, c3, c1są kolumny c1, c2, c3starej tabeli w swoich nowych stanowiskach. Pamiętaj, że w tym przypadku musisz użyć „nowej” nazwy dla edytowanej „starej” tabeli, w przeciwnym razie utracisz swoje dane . W przypadku, gdy nazwy kolumn są liczne, długie i / lub złożone, użyj tej samej metody, co powyżej, aby skopiować nową strukturę tabeli do edytora tekstu i utworzyć tam nową listę kolumn przed skopiowaniem jej do INSERTinstrukcji.

Po sprawdzeniu, że wszystko jest w porządku, DROPużyj starej tabeli i zmień nazwę „nową” na „starą” ALTER TABLE new RENAME TO old;i gotowe.


1

Pracowałem nad zmianą kolejności wielu tabel i nie chciałem ciągle pisać tych samych zapytań, więc stworzyłem skrypt, który zrobi to wszystko za mnie. Zasadniczo to:

  1. Pobiera kod SQL tworzenia tabeli z pg_dump
  2. Pobiera wszystkie dostępne kolumny ze zrzutu
  3. Umieszcza kolumny w żądanej kolejności
  4. Modyfikuje oryginalne pg_dumpzapytanie, aby utworzyć tabelę o zmienionej kolejności z danymi
  5. Upuszcza stary stół
  6. Zmienia nazwę nowej tabeli, aby pasowała do starej tabeli

Można go użyć, uruchamiając następujące proste polecenie:

./reorder.py -n schema -d database table \
    first_col second_col ... penultimate_col ultimate_col --migrate

Drukuje sql, abyś mógł go zweryfikować i przetestować, to był duży powód, dla którego go oparłem pg_dump. Możesz znaleźć repozytorium github tutaj .


0

Używam Django i wymaga kolumny id w każdej tabeli, jeśli nie chcesz mieć bólu głowy. Niestety byłem nieostrożny i moja tabela bp.geo_location_vague nie zawierała tego pola. Parafowałem małą sztuczkę. Krok 1:

CREATE VIEW bp.geo_location_vague_vw AS
    SELECT 
        a.id, -- I change order of id column here. 
        a.in_date,
        etc
    FROM bp.geo_location_vague a

Krok 2: (bez tworzenia tabeli - tabela utworzy się automatycznie!)

SELECT * into bp.geo_location_vague_cp2 FROM bp.geo_location_vague_vw

Krok 3:

CREATE SEQUENCE bp.tbl_tbl_id_seq;
ALTER TABLE bp.geo_location_vague_cp2 ALTER COLUMN id SET DEFAULT nextval('tbl_tbl_id_seq');
ALTER SEQUENCE bp.tbl_tbl_id_seq OWNED BY bp.geo_location_vague_cp2.id;
SELECT setval('tbl_tbl_id_seq', COALESCE(max(id), 0)) FROM bp.geo_location_vague_cp2;

Ponieważ potrzebuję dużego pseudotypu seryjnego w tabeli. Po SELECT * w pg utworzy insetad bigserial typu bigint.

Krok 4: Teraz możemy usunąć widok, usunąć tabelę źródłową i zmienić nazwę nowej tabeli na starą nazwę. Sztuczka zakończyła się sukcesem.


Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.