By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,908 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Adding new column to existing table

P: 18
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.aol.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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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
May 18 '07 #1
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 534
Using the COPY command you can only add rows to your existing table, what you need is the UPDATE command.
May 18 '07 #2

P: 18
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:
Expand|Select|Wrap|Line Numbers
  1. ERROR:  relation "pg_catalog.pg_user" does not exist
  2.  
thanks
mamoon
May 19 '07 #3

Expert 100+
P: 534
... 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
Expand|Select|Wrap|Line Numbers
  1. -- let's see the db version and platform
  2. select version();
  3. -- this may give an error
  4. VACUUM ANALYZE;
  5. -- if no errors reported run the next command
  6. VACUUM FULL;
  7.  
May 19 '07 #4

P: 18
- you could accomplish this with UPDATE



You may have a corrupted database.
Try to execute these commands and post results
Expand|Select|Wrap|Line Numbers
  1. -- let's see the db version and platform
  2. select version();
  3. -- this may give an error
  4. VACUUM ANALYZE;
  5. -- if no errors reported run the next command
  6. VACUUM FULL;
  7.  
hi,
results of ur suggested SQL code:
Expand|Select|Wrap|Line Numbers
  1. 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)
Expand|Select|Wrap|Line Numbers
  1. 2. VACUUM ANALYZE;
VACUUM
Expand|Select|Wrap|Line Numbers
  1. 3. VACUUM FULL;
VACUUM

no error i have seen on the prompt. what next to do.
waiting for reply
with regard
mamoon
May 21 '07 #5

Expert 100+
P: 534
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)
May 22 '07 #6

P: 18
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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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
May 22 '07 #7

Expert 100+
P: 534
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=enforcing
then you may want to change it to:
SELINUX=disabled
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.
May 22 '07 #8

P: 18
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=enforcing
then you may want to change it to:
SELINUX=disabled
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=disabled.
2. after that i applied initdb to empty data directory.
3. well worked
4. then i started postmaster
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. postmaster -D /var/lib/pgsql/data > logfile 2>&1 &
it gave error :
Expand|Select|Wrap|Line Numbers
  1. bash-3.00$ bash: logfile: Permission denied
how can i run it in background?
thanks to solve my \d problem.
bye
May 22 '07 #9

Expert 100+
P: 534
... when i started postmaster in background with following command:
Expand|Select|Wrap|Line Numbers
  1. postmaster -D /var/lib/pgsql/data > logfile 2>&1 &
it gave error :
Expand|Select|Wrap|Line Numbers
  1. 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/s...ver-start.html
http://www.postgresql.org/docs/8.2/s...pp-pg-ctl.html
May 22 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.