Adding text arrays to PostgreSQL

pickarooney

I'm trying to get to grips with postgreSQL and its use of quotes and have an issue adding text arrays to a table.

I didn't read up enough at the beginning and created tables with uppercase letters which now seemingly condemns me to using double quotes all over the place every time I want to run a query.

(parenthetically, is there any way around that apart from renaming everything in the schema?)

So the data set is a simple rugby DB with players, positions, clubs etc. and I'm currently trying to populate the positions - sorry, "Positions" - table which contains three items - ID (primary serial), Number (integer) and Name (text[])

When I run insert statements as follows it adds some of the items with quotes around them and some without.

insert into "Position" ("Number","Name") values (1,ARRAY['Loosehead Prop', 'Left Pillar']);                      
insert into "Position" ("Number","Name") values (2,ARRAY['Hooker','Front Row Centre']);   
insert into "Position" ("Number","Name") values (3,ARRAY['Tighthead Prop', 'Right Pillar']); 
insert into "Position" ("Number","Name") values (4,ARRAY['Loosehead Lock', 'Lock']);
insert into "Position" ("Number","Name") values (5,ARRAY['Tighthead Lock', 'Lock']); 
insert into "Position" ("Number","Name") values (6,ARRAY['Blindside Flanker', 'Flanker', 'Third Row Left']);  
insert into "Position" ("Number","Name") values (7,ARRAY['Openside Flanker', 'Flanker', 'Third Row Right']);
insert into "Position" ("Number","Name") values (8,ARRAY['Number 8', 'Flanker', 'Third Row Centre']);
insert into "Position" ("Number","Name") values (9,ARRAY['Scrum Half', 'Half Back']);
insert into "Position" ("Number","Name") values (10,ARRAY['Fly Half', 'Out Half', 'Stand Off', 'First Five Eighth']);
insert into "Position" ("Number","Name") values (11,ARRAY['Left Winger', 'Winger']);
insert into "Position" ("Number","Name") values (12,ARRAY['Inside Centre', 'Second Five Eighth']);
insert into "Position" ("Number","Name") values (13,ARRAY['Outside Centre', 'Centre']);
insert into "Position" ("Number","Name") values (14,ARRAY['Right Winger', 'Winger']);
insert into "Position" ("Number","Name") values (15,ARRAY['Full Back', 'Fifteen'])

the table then looks like

"{"Loosehead Prop","Left Pillar"}";1;1
"{Hooker,"Front Row Centre"}";2;2
"{"Tighthead Prop","Right Pillar"}";3;3
"{"Loosehead Lock",Lock}";4;4
"{"Tighthead Lock",Lock}";5;5
"{"Blindside Flanker",Flanker,"Third Row Left"}";6;6
"{"Openside Flanker",Flanker,"Third Row Right"}";7;7
"{"Number 8",Flanker,"Third Row Centre"}";8;8
"{"Scrum Half","Half Back"}";9;9
"{"Fly Half","Out Half","Stand Off","First Five Eighth"}";10;10
"{"Left Winger",Winger}";11;11
"{"Inside Centre","Second Five Eighth"}";12;12
"{"Outside Centre",Centre}";13;13
"{"Right Winger",Winger}";14;14
"{"Full Back",Fifteen}";15;15

Positions 4,5,6,7,11,13,14,15 have double quotes around one of the names but not the other while the rest have everything quoted.


I don't know if there's a typo staring at me or if there's some sense to the way it's being parsed?

JackieChan

pickarooney wrote: »

I'm trying to get to grips with postgreSQL and its use of quotes and have an issue adding text arrays to a table.

I didn't read up enough at the beginning and created tables with uppercase letters which now seemingly condemns me to using double quotes all over the place every time I want to run a query.

(parenthetically, is there any way around that apart from renaming everything in the schema?)

So the data set is a simple rugby DB with players, positions, clubs etc. and I'm currently trying to populate the positions - sorry, "Positions" - table which contains three items - ID (primary serial), Number (integer) and Name (text[])

When I run insert statements as follows it adds some of the items with quotes around them and some without.

insert into "Position" ("Number","Name") values (1,ARRAY['Loosehead Prop', 'Left Pillar']);                      
insert into "Position" ("Number","Name") values (2,ARRAY['Hooker','Front Row Centre']);   
insert into "Position" ("Number","Name") values (3,ARRAY['Tighthead Prop', 'Right Pillar']); 
insert into "Position" ("Number","Name") values (4,ARRAY['Loosehead Lock', 'Lock']);
insert into "Position" ("Number","Name") values (5,ARRAY['Tighthead Lock', 'Lock']); 
insert into "Position" ("Number","Name") values (6,ARRAY['Blindside Flanker', 'Flanker', 'Third Row Left']);  
insert into "Position" ("Number","Name") values (7,ARRAY['Openside Flanker', 'Flanker', 'Third Row Right']);
insert into "Position" ("Number","Name") values (8,ARRAY['Number 8', 'Flanker', 'Third Row Centre']);
insert into "Position" ("Number","Name") values (9,ARRAY['Scrum Half', 'Half Back']);
insert into "Position" ("Number","Name") values (10,ARRAY['Fly Half', 'Out Half', 'Stand Off', 'First Five Eighth']);
insert into "Position" ("Number","Name") values (11,ARRAY['Left Winger', 'Winger']);
insert into "Position" ("Number","Name") values (12,ARRAY['Inside Centre', 'Second Five Eighth']);
insert into "Position" ("Number","Name") values (13,ARRAY['Outside Centre', 'Centre']);
insert into "Position" ("Number","Name") values (14,ARRAY['Right Winger', 'Winger']);
insert into "Position" ("Number","Name") values (15,ARRAY['Full Back', 'Fifteen'])

the table then looks like

"{"Loosehead Prop","Left Pillar"}";1;1
"{Hooker,"Front Row Centre"}";2;2
"{"Tighthead Prop","Right Pillar"}";3;3
"{"Loosehead Lock",Lock}";4;4
"{"Tighthead Lock",Lock}";5;5
"{"Blindside Flanker",Flanker,"Third Row Left"}";6;6
"{"Openside Flanker",Flanker,"Third Row Right"}";7;7
"{"Number 8",Flanker,"Third Row Centre"}";8;8
"{"Scrum Half","Half Back"}";9;9
"{"Fly Half","Out Half","Stand Off","First Five Eighth"}";10;10
"{"Left Winger",Winger}";11;11
"{"Inside Centre","Second Five Eighth"}";12;12
"{"Outside Centre",Centre}";13;13
"{"Right Winger",Winger}";14;14
"{"Full Back",Fifteen}";15;15

Positions 4,5,6,7,11,13,14,15 have double quotes around one of the names but not the other while the rest have everything quoted.


I don't know if there's a typo staring at me or if there's some sense to the way it's being parsed?

The only insight I can give is that the single words have no quote, the quotes seem to be just where you have more than one word.

Personally if possible I would just rename the tables to what you want.
The thoughts of having to quote my tables would drive me demented and not something that I've ever seen much of in my SQL development(in Oracle anyway)

pickarooney

Good spot! I guess it's analogous to putting file names with spaces between quotes when passing them as command line arguments.

I found a trick for the table names, even though I don't really understand what it does.

ALTER TABLE "Club" RENAME TO Club;

then allows

select * from Club

or

select * from club

It must be the GUI (pgadmin3) that adds in the quotes. Utterly bizarre behaviour.