insert – Oracle: Inserting into table error – either duplicate table or invalid datatype

I’m trying to insert data from an external table.

INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct 
      (construct_id,
      n_term ,
      enz_name,
      c_term,
      cpp,
      mutations,
      mw_kda)
SELECT *
    FROM EXTERNAL ((
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      mw_kda NUMBER (7,3))
  
  TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL 
        ) 
    LOCATION ('CONSTRUCT.CSV')
    REJECT LIMIT UNLIMITED) ext
    
    WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
    );

But now i get this error:

Error at Command Line : 171 Column : 7
Error report -
SQL Error: ORA-00957: duplicate column name
00957. 00000 -  "duplicate column name"

Line 171 is the last line of this part

INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct 
      (construct_id,
      n_term ,
      enz_name,
      c_term,
      cpp,
      mutations,
      mw_kda)

Which is clearly not a duplicate column.

If I do:

INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct 
    (construct_id, 
    n_term, 
    enz_name, 
    c_term, 
    cpp, 
    mutations, 
    mw_kda)
SELECT *
    FROM EXTERNAL ((
      ext.construct_id NUMBER (10),
      ext.n_term VARCHAR2 (50),
      ext.enz_name VARCHAR2 (50),
      ext.c_term VARCHAR2 (50),
      ext.cpp VARCHAR2 (50),
      ext.mutations VARCHAR2 (50),
      ext.mw_kda NUMBER (7,3))
  
  TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL 
        ) 
    LOCATION ('CONSTRUCT.CSV')
    REJECT LIMIT UNLIMITED) ext
    
    WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
    );

I get

Error at Command Line : 174 Column : 10
Error report -
SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

Line 171 is ext.construct_id NUMBER (10),

mysql – Transfer rows into a new table, forcing to generate unique primary keys till its insertable

Lets say we have the following scenario…

+-----------------+-------------+---------------+
| Table           | PK          | Additional    |
+-----------------+-------------+---------------+
| playerinventory | id (int)    | amount, level |
+-----------------+-------------+---------------+
| newinventory    | id (BIGINT) | amount, level |
+-----------------+-------------+---------------+

We are in the middle of a architectural change of our game-database.
“amount, level” should get extracted from “playerinventory” and get inserted into “newinventory”.

Limitations

  • “newinventory” has no auto-increment pk, therefore we need to
    generate a unique long for each… we also can not add
    “auto-increment” to the table
  • “newinventory” could possible
    already contain those identities, so we need to check if the
    generated id already exists and if so repeat the process for the
    affected set of extracted columns till it was inserted
    sucessfully.
  • We CANT simply insert each row from
    “playerinventory” into “newinventory” with its current id… thats
    simply not possible ( if i would explain why this post would be much,
    much longer ), thats why we NEED to generate a new unique
    ID

In nearly any programming language this process would look like this

for row in playerInventory
   id = generate new ID
   while(id in newInventory) id = generate new ID
   insert into newInventory( id, row.amount, row.level )
      

How can we achieve this in MySQL/SQL ?

postgresql performance – Postgres tracking count of recent items in count table or by selecting count of recent records from history table

Which would generally be more efficient, or does it not matter?
Option 1 is incrementing a count in a table that stores counts by the unique id, and resetting the count to zero when enough time has passed for that unique id.
Option 2 is not storing a count and fetching the count of records from a history table, by the records’ unique ids and within the past so many time units.
I would add an index on the id and timestamp fields for option 2.
The history table is in the low millions of records and doesn’t grow that fast.
Is the periodic vacuuming of the counts table likely to be much worse than selecting the count from the history table using indexed fields?
It seems like it would require less operations, no insert or update with Option 2.

javascript – Rearranging data from one table to another in spreadsheets using apps script

I need some help with making my code run faster. It currently works by going through all cells in a 201×151 table. If the value in a cell is not equal to zero, then it will return the row number, the column number, and the actual value, and put it in a different table.

Here is the code:

function rearranger() {
  
  var range = SpreadsheetApp.getActiveSheet().getRange('A2:C10000');
  
  var ratings = SpreadsheetApp.getActiveSheet().getRange('TopChampionsVSUser!A1:EU201');
  
  var iterationNumber = SpreadsheetApp.getActiveSheet().getRange('I1:I2').getValues();
  
  var tr = SpreadsheetApp.getActiveSheet().getRange('L1').getCell(1, 1).getValue();
  
  var r = iterationNumber(0);
  var c = iterationNumber(1);
  
  for(r; r < 3000; r++){
    
    SpreadsheetApp.getActiveSheet().getRange('I1').getCell(1, 1).setValue(r);
    
    if(c >= 151){
      c = 2;
    }
    
    for(c; c < 152; c++){
      
      SpreadsheetApp.getActiveSheet().getRange('I2').getCell(1, 1).setValue(c);
      
      var currentCellValue = ratings.getCell(r, c).getValue();
      
      if(currentCellValue != 0){
        
        range.getCell(tr, 1).setValue(r-1);
        range.getCell(tr, 2).setValue(ratings.getCell(1, c).getValue());
        range.getCell(tr, 3).setValue(currentCellValue);
        
        tr++;
        
        SpreadsheetApp.getActiveSheet().getRange('L1').getCell(1, 1).setValue(tr);
        
      }
    } 
  }
}

Do you see something you think can be improved or made more efficient?

Thanks in advance

postgresql – Do I need to ANALYZE table after DROP INDEX?

In our postgres db we historically have many similar indexes (e.g. is_deleted boolean) that have quite bad selectivity value. Worse than that, sometimes query planner breaks and postgres starts to use some of them instead of much more efficient indexes so we need to call ANALYZE manually to fix performance, so we decided to delete most of them.

Do we need to call ANALYZE for each table after deleting index?

According to the answer in Is it necessary to ANALYZE a table after an index has been created? postgres collects stats about actual values in table (for simple indexes) without index-related info but I couldn’t find proof in docs.

oracle – Why am i getting “missing right parenthesis” error when i try to LOG ERRORS when loading from an external table?

I’ve successfully created an error logging table

BEGIN
    DBMS_ERRLOG.create_error_log(
    dml_table_name  => 'enzyme',
    skip_unsupported => TRUE);
END;
/

desc ERR$_ENZYME;
Name            Null? Type           
--------------- ----- -------------- 
ORA_ERR_NUMBER$       NUMBER         
ORA_ERR_MESG$         VARCHAR2(2000) 
ORA_ERR_ROWID$        UROWID         
ORA_ERR_OPTYP$        VARCHAR2(2)    
ORA_ERR_TAG$          VARCHAR2(2000) 
ENZ_NAME              VARCHAR2(4000) 

But i get an error when I try to run this query:

insert /*+ ignore_row_on_dupkey_index ( enzyme ( enz_name ) ) */
into enzyme
SELECT enz_name FROM EXTERNAL ((
  construct_id NUMBER(10),
  n_term VARCHAR2 (50),
  enz_name VARCHAR2 (3),
  c_term VARCHAR2 (50),
  cpp VARCHAR2 (50),
  mutations VARCHAR2 (50),
  mw_kda NUMBER (7, 3))

    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        skip 1
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL 
        ) 
    LOCATION ('CONSTRUCT.CSV')
    LOG ERRORS INTO ERR$_ENZYME ('INSERT') REJECT LIMIT UNLIMITED) ext
    where not exists (
        select * from enzyme e
        where e.enz_name = ext.enz_name
    );
Error at Command Line : 79 Column : 5
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:

Line 79 is the LOG ERRORS INTO line.

If i delete the LOG ERRORS INTO ERR$_ENZYME ('INSERT') part, this command functions perfectly.

java – Libgdx – TextButtons added to Table – clicklistener not working

I am having issues trying to implement a menu in a previously created game. I am pretty new to Libgdx and not sure how everything works just yet. I am adding a menu to an already created game scene using a table and text buttons.

My goal:

  1. Click button which shows table full of buttons
  2. Click specific table
    button and do something with them
  3. Click away from the table area
    then table/buttons will disappear

I’ve got it all working except connecting the buttons to clicklisteners. I can only think of the buttons not actually being on the top surface where you can click them.

Section of code:
Shortened for brevity; class implements InputProcessor

    protected MenuComponent(GameContext context, SeaBattleScene owner) {
        super(context, owner);
        this.context = context;
    }

    @Override
    public void create() {
        batch = new SpriteBatch();
        this.stage = new Stage();
        Gdx.input.setInputProcessor(stage);
        menuUp = new Texture("assets/ui/disengage.png");
        menuDown = new Texture("assets/ui/disengagePressed.png");
        menuWindow = createMenuWindow();
        menuWindow.setVisible(false);
        stage.addActor(menuWindow);
        stage.getRoot().setPosition(65,150);
    }

    @Override
    public void update() {
    }

    @Override
    public void render() {
        batch.begin();
        batch.draw((menuButtonIsDown)?menuDown:menuUp, MENU_buttonX, MENU_buttonY);
        batch.end();
        stage.act();
        stage.draw();
    }
    private Table createMenuWindow() {
        //Table View
        Table table = new Table();
        table.setPosition(675,160);
        //Button View
        SpriteDrawable upFormat=new SpriteDrawable(new Sprite(createRoundPixMap(Color.BLACK,100, 30,10)));
        SpriteDrawable downFormat=new SpriteDrawable(new Sprite(createRoundPixMap(Color.BLACK,100, 30,10)));
        SpriteDrawable checkedFormat=new SpriteDrawable(new Sprite(createRoundPixMap(Color.BLACK,100, 30,10)));
        TextButtonStyle buttonStyle=new TextButtonStyle(upFormat, downFormat, checkedFormat, new BitmapFont());
        //Individual Buttons
        TextButton lobbyButton = new TextButton("Back to Lobby",buttonStyle);
        lobbyButton.addListener( new ClickListener() {              
            public void clicked(InputEvent event, float x, float y) {
                System.out.println("Clicked");
            };
        });
        TextButton mapButton = new TextButton("Show Maps",buttonStyle);
        table.add(lobbyButton).pad(3,0,3,0).row();
        table.add(mapButton).pad(3,0,3,0).row();
        return table;
    }

    
    public static Texture createRoundPixMap(Color color, int width,
            int height, int cornerRadius) {
        Pixmap pixmap = new Pixmap(width, height, Pixmap.Format.Alpha);
        pixmap.setBlending(Pixmap.Blending.None);
        pixmap.setColor(color.r,color.g,color.b,0.4f);
        pixmap.fillCircle(cornerRadius, cornerRadius, cornerRadius);
        pixmap.fillCircle(width - cornerRadius, cornerRadius, cornerRadius);
        pixmap.fillCircle(width - cornerRadius, height - cornerRadius, cornerRadius);
        pixmap.fillCircle(cornerRadius, height - cornerRadius, cornerRadius);
        pixmap.fillRectangle(0, cornerRadius, width, height - (cornerRadius * 2));
        pixmap.fillRectangle(cornerRadius, 0, width - (cornerRadius * 2), height);
        Texture texture = new Texture(pixmap);
        pixmap.dispose();
        return texture;
    }

Can someone expain or show me what I am doing wrong, so I can fix it to where I can use the buttons?

This is basically what it looks like:
Menu

postgresql – Delete rows on a table with cascading foreign keys

First, it depends on how your foreign keys are declared. Assuming tables like:

CREATE TABLE parent
( pid ... not null primary key
, ...
);

CREATE TABLE child
( ...
, pid ... not null
    references parent (pid)
        on delete <action>
        on update ...
...
);

action can be any of:

  • NO ACTION
    Produce an error indicating that the deletion or update would create a foreign
    key constraint violation. If the constraint is deferred, this error will be
    produced at constraint check time if there still exist any referencing rows.
    This is the default action.

  • RESTRICT
    Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable.

  • CASCADE
    Delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the referenced column, respectively.

  • SET NULL
    Set the referencing column(s) to null.

  • SET DEFAULT
    Set the referencing column(s) to their default values.

See https://www.postgresql.org/docs/9.2/sql-createtable.html

If your foreign keys are declared as “on delete cascade” it is – in theory – sufficient to delete the root node. In practice, there may be physical limitations that restrict the total number of rows that can be deleted in one transaction.

If you want to experiment with the different actions you can use Fiddle. 9.5 is the oldest one available. If you are still on 9.2, consider upgrading to something more modern.

dataframe – reading text table into R

I know if I copy the content of the object block and paste it onto a text editor (e.g., notepad), then I can read the block.txt file as a table in R like so: read.table('block.txt', header = TRUE).

But is there a way to directly (without first creating a block.txt file) read the block itself in R?

block <- "Item1 Item2 Item3 
             22    52    16 
             42    33    24 
             44     8    19 
             52    47    18 
             45    43    34 
             37    32    39"

filter – adding an item to filtered table

Many of the approaches others have mentioned make sense. It’s also important to make it clear to the user that he or she has filters applied. If you filter the list, then leave the product and come back to it later, it’s very easy to forget that the list is filtered and thus be confused when you add something and it doesn’t appear in the table. I would do this combined with showing a toast success message with a link, as suggested above. The toast, or maybe a separate message located where the user is more likely to read it, could also explicitly mention that the new item is hidden because filters are applied.

Personally, I think it just doesn’t make sense to show the new item in the table when the filter is applied – you want to let the user remain in control, and he or she applied the filter for a reason.

Whatever you decide, you should consider testing it with a few users to see how it works for them.