PostgreSQL

Anon

I need some help...

I created a table with postgresql. Is there any way we can alter the table data type without losing the data?

Example:
What I did was:

create table tablename (name text, age int);

Now I want the name to be an array of names which do this:

names text[]

is there anyway I can alter the data type after I created the table?

Please help.

Chris

Anon

Postgresql's handling of arrays is still a bit clunky, and you might find it easier to serialize a php array and push that into the database as a single entry.

Well, I played around with using insert into and select into, but postgresql doesn't seem to support conversion into / out of array format.

Your best bet is to dump the table and edit it by hand or write a php script to put it into the proper format then reload it.

%> pg_dump dbname -t tablename >table.sql
(edit the file here)
%> psql dbname -e <table.sql

But my experience with arrays with postgresql has led me to believe you're generally better off with the serialize option (it leaves your text as text, with some extra seperators, so you can still do a like '%nugget%' comparison to find fields with a name in them.

Anon

I really need to change the data type from the table instead of doing the serial. It's a very complicated subject for me to explain. Anyway, if you or anyone who knows how to change the data type of a column AFTER you created the table. Please Please show me.
Thanks,
Chris

Anon

So, how does the data currently look when you do a pg_dump -t tablename on it?

If it's single entries and you just want to transform them into arrayed entries, then it's pretty easy to munge the data to work with PHP or some other language outside the postgresql engine.

In my test tables I built, part of a non-array text table looks like this:

COPY "test" FROM stdin;
abc
123
steve
.

And arrayed ones look like this:

COPY "test2" FROM stdin;
{"mark","bill","sam"}
.

So, do you need to combine names from the first table somehow, or just put one name into each arrayed value?

And are you sure that a joined table wouldn't be a better design decision for maintainability and transportability?

Anon

Ok, let me be more specific this time. Let me give you a very small table:

Attribute | Type | Modifier

Ok, assume that the table above is the origional one. Now, I want that table look like the one below:

Attribute | Type | Modifier

I want the "pets" field and the "hobbies" field to be the array of text instead of text. Is there a way to change this? Please remember that, the original already has A LOT of data in there that I don't want to mess them up or loose them. If there is a way we can ALTER only the type without changing anything else, please let me know.

Thanks,

Chris

Anon

From my testing, I'm pretty sure you're gonna need to do a pg_dump, then write a php or perl script to reformat the data into arrayed format. Just build a target table with the format you want, pg_dump the old table, edit the sql file created by the pg_dump so that it imports into the new table and is in arrayed format (you can pg_dump a row of test data from the new table to see what format it needs to be in.)

Does that make sense? If not, let me know, and I'll try to write some sample code to help you out.

Anon

Yes, please help to write a very simple one. Let me tell you what I am dealing with, and then tell me what I have to do.

Like I said before, the table is a single text file for that field. Now I want it to be the array. Let me give you the example of what I have right now:

name | monthly_payment

...then when I have the table like above, I have to add
the monthly payment for each person like so:

chris paid = 22.30
joe paid = 8.10
anna paid = 30.00

Ok , let's talk about what I really need:

name | monthly_payment

{chris} | 12.30
{chris, anna} | 10.00
{joe, anna} | 8.10
{anna} | 11.90

The reason for me to change the table because sometime the person have the same monthly payment and I have to put them on the same row. Note that, I will never have more than 2 people have the same payment, only 2 people have the same payment at the time. What about the result? It should be the same result like the previous result:

chris paid = 22.30
joe paid = 8.10
anna paid = 30.00

Anyway, why do I need something like this? Well, let's say it's requried. If you can help me to write something like that, please help. Thank you for your consideration and your help.

Chris