Adding new column to existing table

[mamoon]

hi all,
Question:
i have a table in a database of 6 columns and 630 rows.i want to add one new column to this table and also update this column starting from row 1 to row 630.

System information:
1.Linux ac8d7f39.ipt.ao l.com 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005 i686 i686 i386 GNU/Linux
2. PostgreSQL 7.4.6

My trial:
1. i added one column with ALTER TABLE command

ALTER TABLE only maintable add column id_local text;

it worked well .i checked by select command.
2. then i copied data into that column by COPY FROM command

COPY maintable (id_local) from 'unix path name of source file' with delimiter as '\n';

the data is copied from file BUT the problem is that it didnt add the data from very 1st row, rather it added data into that column after row 630.
finally row must be 630, BUT it came to be 1260.
please solve or suggest suitable command .
i hope i put my problem explicitly.
with regard
mamoon

[michaelb]

Using the COPY command you can only add rows to your existing table, what you need is the UPDATE command.

[mamoon]

Using the COPY command you can only add rows to your existing table, what you need is the UPDATE command.

thanks michaelb,
actually my problem is different. i wanted to update whole column (630 rows) in one turn which were vaccant earlier. moreover other columns are already filled.
anyway i solved in other way. i manipulated source file and introduced new column with suitable data filled then i run COPY FROM command to already truncated fresh table.it worked.

please tell me about \d command, when i execute \d at database prompt it gives error like this:

ERROR:  relation "pg_catalog.pg_user" does not exist

thanks
mamoon

[michaelb]

... i wanted to update whole column (630 rows) in one turn ...

- you could accomplish this with UPDATE

... when i execute \d at database prompt it gives error like this:
ERROR: relation "pg_catalog.pg_ user" does not exist

You may have a corrupted database.
Try to execute these commands and post results
[CODE=sql]
-- let's see the db version and platform
select version();
-- this may give an error
VACUUM ANALYZE;
-- if no errors reported run the next command
VACUUM FULL;
[/CODE]

[mamoon]

- you could accomplish this with UPDATE



You may have a corrupted database.
Try to execute these commands and post results
[CODE=sql]
-- let's see the db version and platform
select version();
-- this may give an error
VACUUM ANALYZE;
-- if no errors reported run the next command
VACUUM FULL;
[/CODE]

hi,
results of ur suggested SQL code:

1.select version();

version
----------------------------------------------------------------------------------------------------------------------------- PostgreSQL 7.4.6 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.3 20041125 (Red Hat 3.4.3-6.EL4)
(1 row)

2. VACUUM ANALYZE;

VACUUM

3. VACUUM FULL;

VACUUM

no error i have seen on the prompt. what next to do.
waiting for reply
with regard
mamoon

[michaelb]

Are you're running with SELinux enabled? If so, this is a known problem.
Due to some issues in early releases of the SELinux policy for Postgres some steps of initdb may fail due to permissions problems.
As a result your database is missing some system views and maybe more (pg_user is a view on the system table pg_shadow), although generally database seems operational, you can create tables and run some basic operations.

Try to upgrade to a more recent version of Postgres (I think the latest release is 8.2)

[mamoon]

Are you're running with SELinux enabled? If so, this is a known problem.
Due to some issues in early releases of the SELinux policy for Postgres some steps of initdb may fail due to permissions problems.
As a result your database is missing some system views and maybe more (pg_user is a view on the system table pg_shadow), although generally database seems operational, you can create tables and run some basic operations.

Try to upgrade to a more recent version of Postgres (I think the latest release is 8.2)

hi
i am not running SELinux. Instead i am running Red Hat Enterprise Linux.
i think something wrong with initdb. when i run initdb from postgres user prompt
it didnt show that initdb failed.i used the following command

initdb -D /var/lib/pgsql/data

then following appeared on the terminal
[HTML]The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.

initdb: directory "/var/lib/pgsql/data" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/var/lib/pgsql/data" or run initdb with an argument other than
"/var/lib/pgsql/data".[/HTML]

i remeber this directly already existed and containing some file before doing initdb. then i run

postmaster -D /var/lib/pgsql/data

now in data directory a file postmaster.pid appeared containig PID.
i can create table, insert everything and do some jobs. but \d and some commands are not being executed.
if possible suggest where is the problem? either in installing postgres or somewhere else.
coordial thanks
mamoon

[michaelb]

hi
i am not running SELinux. Instead i am running Red Hat Enterprise Linux.

This does not mean that you don't have SELinux enabled.
Check if you have file /etc/selinux/config
If it exists and has the line
SELINUX=enforci ng
then you may want to change it to:
SELINUX=disable d
I'm not sure whether this change requires reboot to take effect.

The initdb error you experienced is understandable, you cannot install database in a non-empty directory.
The problem with SELinux policy will not fail initdb, it seems to have worked, but as I noted you'll be missing some system views, like pg_user.

[mamoon]

This does not mean that you don't have SELinux enabled.
Check if you have file /etc/selinux/config
If it exists and has the line
SELINUX=enforci ng
then you may want to change it to:
SELINUX=disable d
I'm not sure whether this change requires reboot to take effect.

The initdb error you experienced is understandable, you cannot install database in a non-empty directory.
The problem with SELinux policy will not fail initdb, it seems to have worked, but as I noted you'll be missing some system views, like pg_user.

ohhh great,
thanks alot to you really thanks.
1.i have emptied data directory and simultaneously set selinux=disable d.
2. after that i applied initdb to empty data directory.
3. well worked
4. then i started postmaster

postmaster -D /var/lib/pgsql/data

it worked well.postmaster is running in foreground.
now \d is operational.
5. last but not least problem is that when i started postmaster in background with following command:

postmaster -D /var/lib/pgsql/data > logfile 2>&1 &

it gave error :

bash-3.00$ bash: logfile: Permission denied

how can i run it in background?
thanks to solve my \d problem.
bye

[michaelb]

... when i started postmaster in background with following command:

postmaster -D /var/lib/pgsql/data > logfile 2>&1 &

it gave error :

bash-3.00$ bash: logfile: Permission denied

how can i run it in background?

I guess the logfile already existed and
a) by some reason you don't have a write permission to it (ls -l logfile would show it all)
or
b) some shells do not allow redirecting output to existing file - see noclobber for details.

You can try >> logfile which will append new data to existing file,
but I would recommend using the pg_ctl wrapper script.

See these links for more info

http://www.postgresql.org/docs/8.2/static/server-start.html

http://www.postgresql.org/docs/8.2/static/app-pg-ctl.html