Tag: table

postgresql – Pre Caching Index on a large table in PostgrSQL

I have a table with about 10mln rows in it with a primary key and an index defined on it:

    create table test.test_table(
        date_info date not null,
        string_data varchar(64) not null,
        data bigint
        primary key(date_info, string_data));
        create index test_table_idx 
        on test.test_table(string_data);

I have a query that makes the use of the test_table_idx:

select distinct date_info from test.test_table where string_data = 'some_val';

The issue is that first time around it could take up to 20 seconds to run the query and < 2 seconds on any subsequent runs.

Is there a way to pull load the entire index into memory rather then have DB load information on first access?

Select Into a temp table in DB2

Let me preface this question in that while I have a strong background in T-SQL but I am still just a newbie when it comes to DB2.

The root query looks roughly like this:

Select 
    Col1, Col2, Col3
From (
    Select A.Col1, B.Col2, B.Col3
    From SchemaA.TableA as A
        Inner Join SchemaA.TableB as B
            On A.Id = B.ParentId
    Where 
        A.Col2 = 'TypeValue'
    Fetch First 5 Rows Only -- This is not allowed in a union or sub select

    Union

    Select A.Col1, B.Col2, B.Col3
    From SchemaB.TableA as A
        Inner Join SchemaB.TableB as B
            On A.Id = B.ParentId
    Where 
        A.Col2 = 'TypeValue'
    Fetch First 5 Rows Only -- This is not allowed in a union or sub select
) as Hybrid

Because Fetch First X Rows is not allowed in subselects or with unions, this isn’t going to work. Since we’re limiting the output, it makes sense to limit the input.

I figure I can achieve a fake union by feeding my subselects into temp tables instead and returning that output instead.

In T-SQL, I can create a temp table on the fly with a query like this how would I do that in DB2?

Select *
Into #Temp
From TableA

How would I do that in DB2? Or would do I need to create the table before inserting data into it?

functions – Keep PODS data (meta table) after wp-ultimo (multisite) template switching

I have a multisite install and using wp-ultimo to manage the subsite duplication, or “template sites” as they call it.

One downside to this approach is that you’d lose any edits you make when you switch the template, because in reality, it’s not a template, it’s an entire subsite that will override your current subsite.

I have extended the site’s pages with PODS, added a couple of simple text fields, they’re basically custom meta fields, you get the data using get_post_meta($id, 'field_name', true).

What I’m trying to achieve is:

  1. Write a function that will retrieve this data before duplication
  2. Pass the data to the search & replace filter that fires after duplication

Relevant Available Hooks:

  • do_action (../inc/duplicate/duplicate.php line 139) ( ‘mucd_before_copy_data’, $from_site_id, $to_site_id );

  • apply_filters (../inc/class-wu-site-hooks.php line 576) (‘wu_search_and_replace_on_duplication’, $search_and_replace_settings, $from_site_id, $to_site_id);

Using search & replace filter example

I don’t know enough PHP to wrap the function properly and pass the values, I’d appreciate any help or a nudge in the right direction.

Thank you <3

sql server – Why is it important for every table to have a primary key?

So I have some tables in Sql Server that are essentially a list of sales, things like: 

ProductID 
SalesOrderID
ProductFamilyID 
ProductCost
ProductSource

and so on. In this case, none of the columns are necessarily unique, so I can’t create a primary key from any combination of them. In fact, the only constraint that I really have on the table is that I need every row in the table to be a unique combination of the columns. So I’m assuming something like a unique index would be the way to go there.

The only primary key I could add is something like an autoincrement primary key. But what would be the actual use of that, database wise? What are the possible problems with not creating a primary key for a table like this?

Update SQLITE table – Database Administrators Stack Exchange

I want to update my sqlite table and I use a request with conditionals my purpose is to set the name to the new value when the value is not null and keep the old value if the new value is null .
I would like anybody check my request to see if there is something wrong cause I’m getting errors

The request below :

UPDATE user SET name = CASE WHEN (? = NULL) THEN user.name ELSE ? END  WHERE ID=? ;

postgresql – Select from a table if conditions met in joined table

I have two tables

members

+-----------+
|id   | name|
+-----------+-
| 1   | john | 
| 2   | adam | 
| 3   | sara | 
+-----------+-

keys

+-----------+
|id   | color|
+-----------+-
| 1   | blue | 
| 2   | green| 
| 3   | red  | 
+-----------+-

and a third joined table

members_keys

+-----------+---------
| member_id | key_id |
+-----------+---------
| 1         | 1      | 
| 1         | 3      | 
| 2         | 1      | 
+-----------+--------

both rows are assigned as primary key and foreign key

What I need is to get john’s keys (keys 1 and 3 in this example)

This is what I am doing

select * from keys join members_keys on keys.id = members_keys.key_id where members_keys.member_id = 1;

from my limited testing I got the required rows, but I feel there is a better way to do it and this solution looks like wasting a lot of resources (comparing each row in keys with all rows in members_keys)

validation – How indicate conditional required field in editable table?

In a form indicating which fields are required are often indicated by an asterisk (*). We have an editable table and I indicate a required column with aesthetics in the title of the column.

The problem appears when initially no columns are required. But if the user fills one of the fields in the row, the next field in this row becomes mandatory and will not pass validation when the user will save the form. How to help the user and identify conditionally required fields?

enter image description here

enter image description here

8 – Table view: “Hide Empty Column” checkbox doesn’t seem to work

I’ve created a view, formatted as a table.

I’m trying to hide empty columns – so, in the table settings, I’ve checked the “Hide Empty Column” checkbox for most of the table columns.

However, this doesn’t seem to have any effect – these columns are still included in the rendered table, with empty <td>s.

I expect this checkbox to hide the column entirely when all data cells contain an empty value. Am I understanding this correctly?

This certainly seems to be how it worked in Drupal 7. (I’m converting an existing D7 site to D8 – and this is how the table columns were hidden on the D7 site.)

Is there anything I should check which might be preventing this from working as expected?

Thanks.