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?
