1
vote
1answer
62 views

Add NOT NULL constraint to large table without table scan

Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table ...
2
votes
3answers
345 views

Change column type from varchar to text in all tables at once

I am using Postgres. I would like to change column type in all tables where column name is "description" from varchar(255) to text. If anyone knows that I would be very glad for your help.
1
vote
1answer
191 views

PostgreSQL - how to rename CamelCase column?

When I tried to rename a CamelCase column like this: ALTER TABLE mytable RENAME COLUMN camelCaseColumn TO camel_case_column; I get the following error: ERROR: column "camelcasecolumn" does not ...
0
votes
1answer
170 views

use function arguments or variable in postgres function

I want to set the default value for the column building and floor in my table points in the below function called test(b,f) Create or replace function test(b text, f text) returns void as $$ Begin ...
0
votes
1answer
72 views

Altering a parent table in Postgresql 8.4 breaks child table defaults

The problem: In Postgresql, if table temp_person_two inherits fromtemp_person, default column values on the child table are ignored if the parent table is altered. How to replicate: First, create ...
1
vote
3answers
237 views

Altering same table across multiple databases

I have multiple databases on the same instance and I am in the process of updating a table schema that must propagate across all the databases. I am not sure I know what the right procedure for this ...
8
votes
5answers
5k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
5
votes
5answers
2k views

Altering table schema takes too much time

I am using PostgreSQL database. I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much ...
4
votes
1answer
2k views

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
2
votes
1answer
964 views

Rotate a table in PostgreSQL

Problem description I need to rotate (with DROP and CREATE) a table which is heavily used by other clients. At present, I have a program which replaces (DROP + CREATE) this table. Sometimes, just ...
4
votes
3answers
5k views

How to change the datatype of a column from integer to money?

I am attempting to convert a PostgreSQL table column from integer to money, but I am receiving the error: cannot cast type MyColumn to money I have tried these two statements, but just haven't ...
1
vote
1answer
885 views

How can I alter the type of a money-column to decimal in PostgreSQL?

I have a table in PostgreSQL where one column "vat" has type money but I would like to alter it to decimal instead. How can I do it? I tried with: alter table my_table alter column vat type ...