add custom table?

[zubs1aa]

THis is a followup to an extended discussion on how to manually enter results in the gen discussion... andit's warped into an SQL problem for me at this point.

Here is what I want to do: I am tired of manually entering R?A expenses for stars so I want to automate it.

I have the data
I have 90% of the SQL if I were to try this right away as I can do all the linkages in access, but I can't update the mastre PT DB.

1) so, I want to add a table to the PT3 DB
2) I'll update this table periodically with the R/A data I want
3) then I'll just go into PGAdmin and run a sql query that joins my table to the tourney_holdem_results table and have it do an update on the R/A fields (yes I realize i do this at my own peril

but, I don't know how to create an additional tablespace out there...and assuming I can.. that it doesnt get blasted everytime there might be a new database.

THoughts?

[kraada]

As I said in the other thread, the documentation for CREATE TABLE is here - you can create a new table in a PT3 database and it won't be touched by PT3 as PT3 won't know it's there.

You'd need to do this for every new database you build/use, though.

[zubs1aa]

As I said in the other thread, the documentation for CREATE TABLE is here - you can create a new table in a PT3 database and it won't be touched by PT3 as PT3 won't know it's there.

You'd need to do this for every new database you build/use, though.

OK, i got the table created. Then i needed to populate it- the guide suggested the COPY command.

so, I saved a text file and tried it.

COPY starsra FROM 'C:\Documents and Settings\*****\Desktop\starsdata.txt';

This got me the following:

WARNING: nonstandard use of escape in a string literal
LINE 1: COPY starsra FROM 'C:\Documents and Settings\...
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
ERROR: could not open file "C:Documents and Setting*****Desktopstarsdata.txt" for reading: No such file or directory

********** Error **********

ERROR: could not open file "C:Documents and Settings******Desktopstarsdata.txt" for reading: No such file or directory
SQL state: 58P01


so per the help i went to "\\"

COPY starsra FROM 'C:\\Documents and Settings\\*****\\Desktop\\starsdata.txt';

which erred as follows

WARNING: nonstandard use of \\ in a string literal
LINE 1: COPY starsra FROM 'C:\\Documents and Settings\\*****\\D...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
ERROR: could not open file "C:\Documents and Settings\******\Desktop\starsdata.txt" for reading: Permission denied

********** Error **********

ERROR: could not open file "C:\Documents and Settings\*******\Desktop\starsdata.txt" for reading: Permission denied
SQL state: 42501


How do i get it to read the text file. ??

[kraada]

What do you have in that file that you're copying in?

Also you might want to try putting it somewhere not on the desktop (say directly in C:\), as there are sometimes odd permissions on the desktop (and you're getting permission denied errors).

[zubs1aa]

What do you have in that file that you're copying in?

Also you might want to try putting it somewhere not on the desktop (say directly in C:\), as there are sometimes odd permissions on the desktop (and you're getting permission denied errors).

OK so here is the create command:

Code: Select all

CREATE TEMPORARY TABLE StarsRA (
   id_tourney      integer,
   ID_player      integer,
   TnyNum         integer,
   NumReb         smallint,
   RebAmt         numeric(11,2),
   NumAdd         smallint,
   AddAmt         numeric(11,2)
);


The data file has 7 tab-delimited columns matching that file def. I left the headers out of the file as I don't want to import them

Code: Select all

7922   258459   256039962   5   10   1   2
7927   258459   254822235   1   10   1   10
7933   258459   303010874   1   10   1   10
7934   258459   303010924   5   15   1   3

[zubs1aa]

blarg that isnt it either.

I moved the file to the G:\ directory

1) WARNING: nonstandard use of escape in a string literal
LINE 1: COPY starsra FROM 'G:\starsdata.txt';
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
ERROR: could not open file "G:starsdata.txt" for reading: No such file or directory

********** Error **********

ERROR: could not open file "G:starsdata.txt" for reading: No such file or directory
SQL state: 58P01

so it doesn't like my syntax.

2) WARNING: nonstandard use of \\ in a string literal
LINE 1: COPY starsra FROM 'G:\\starsdata.txt';
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
Query returned successfully: 4 rows affected, 16 ms execution time.

<<<<<<<<<<<hmmm appears this was a warning, but the data actually did load where it was supposed to!

G:\ > desktop evidently...

[zubs1aa]

arg i hate knowing 90% of what I need lol.

In trying to load th edat ain it wasn't working os i decided to start again since it is a temporary table. Stopped the service, restarted and back to PGadmin.

-- Executing query:
CREATE TEMPORARY TABLE StarsRA (
id_tourney integer,
ID_player integer,
TnyNum integer,
NumReb smallint,
RebAmt numeric(11,2),
NumAdd smallint,
AddAmt numeric(11,2)
);


Query returned successfully with no result in 141 ms.


SO that worked... but this time I can't find the table anywhere....

[kraada]

I think temporary tables are really very temporary - I'm not sure if it survives past a transaction block.

You may need to specify the delimiter in the COPY command? I haven't ever used COPY so this is a bit new to me. Some useful documentation is here and here though. It looks like '\t' will indicate tab delimiting.

[zubs1aa]

I got it!

the table is loaded depending on where you had clicked in the structure prior to lading the table. So if i want to make sure its in the PT3 space, I need to click the top layer of the tables group within the active db/schemas/public/tables.

Then create the table
then load the data
then run the update code.

I got it to work once... fingers crossed I can replicate :)

----------

BUT in trying to do it for all my data and making sure it's from A-Z it bombs out. WTF? lol

1) unfortunately, starting from scratch, this time it puts the table in pg_temp3 tables, though that may not be a problem.

2) However, the data load bombed out again using the exact same friggin syntax as before with the g drive. Only this time it was a hard error and no rows were loaded. AARG. If the data loads my update command works fine. why is it so hard to just load up a text file?

Also, I'm using copy as it is the most straightforward method. my syntax is otherwise fine, it knows about the tab delimiters. the problem is it can find the filepath correctly. POS! :)

WARNING: nonstandard use of \\ in a string literal
LINE 1: COPY starsra FROM 'G:\\starsdata.txt';
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
ERROR: invalid input syntax for integer: ""
CONTEXT: COPY starsra, line 4, column numadd: ""

********** Error **********

ERROR: invalid input syntax for integer: ""
SQL state: 22P02
Context: COPY starsra, line 4, column numadd: ""

This is the exact same string I used to load it before too.

[kraada]

What's the data on the fourth line of the file?