PostgreSQL delete temp table

PHOTO

Tue Aug 17 2021 07:42:41 GMT+0000 (UTC)

Saved by @mglejser #sql

DROP TABLE __tbl_name;

Save snippets that work from anywhere online with our extensions

Available in the Chrome Web Store

Get Firefox Add-on

Get VS Code extension

Comments

More like this

PostgreSQL

@mglejser

PostgreSQL create table PostgreSQL create temp table PostgreSQL select query PostgreSQL add column to table PostgreSQL rename column in table PostgreSQL change column type PostgreSQL change user password PostgreSQL insert record into table PostgreSQL delete records PostgreSQL delete temp table PostgreSQL change date format Postgres start and stop running db PostgreSQL restart server PostgreSQL create user PostgreSQL create db Psql connect to db Psql open command line Psql open command line Psql db commands Psql get hba file path Psql show addresses being listened to Psql db commands

javascript - Import sql file in node.js and execute against PostgreSQL - Stack Overflow

function processSQLFile(fileName) {

  // Extract SQL queries from files. Assumes no ';' in the fileNames
  var queries = fs.readFileSync(fileName).toString()
    .replace(/(\r\n|\n|\r)/gm," ") // remove newlines
    .replace(/\s+/g, ' ') // excess white space
    .split(";") // split into all statements
    .map(Function.prototype.call, String.prototype.trim)
    .filter(function(el) {return el.length != 0}); // remove any empty ones

  // Execute each SQL query sequentially
  queries.forEach(function(query) {
    batch.push(function(done) {
      if (query.indexOf("COPY") === 0) { // COPY - needs special treatment
        var regexp = /COPY\ (.*)\ FROM\ (.*)\ DELIMITERS/gmi;
        var matches = regexp.exec(query);
        var table = matches[1];
        var fileName = matches[2];
        var copyString = "COPY " + table + " FROM STDIN DELIMITERS ',' CSV HEADER";
        var stream = client.copyFrom(copyString);
        stream.on('close', function () {
          done();
        });
        var csvFile = __dirname + '/' + fileName;
        var str = fs.readFileSync(csvFile);
        stream.write(str);
        stream.end();
      } else { // Other queries don't need special treatment
        client.query(query, function(result) {
          done();
        });
      }
    });
  });
}

mysql FIND_IN_SET equivalent to postgresql

select * 
from folder f
  join uploads u ON u.id = f.folderId 
where '8' = ANY (string_to_array(some_column,','))

SELECT Id_card, COUNT(Id_card)
FROM user_application_tbl
GROUP BY Id_card
HAVING COUNT(Id_card) > 1

replace pg topups with struct from flat table

create or replace table `reby-cloud.analytics_reby_v1_eu.pg_topups_json`
as

select
  id,
  created_at,
  gateway,
  gateway_transaction_id,
  stripe_charge_id,
  array(select as struct value_amount as amount, value_currency as currency) as value,
  array(select as struct original_value_amount as amount, original_value_currency as currency) as original_value,
  user_id,
  company_id,
  braintree_transaction_id
from `reby-cloud.staging_eu.pg_topups_stg`

Update Virtual column via SQL Workshop

ALTER TABLE DAYSUM
  MODIFY V_TOT_REVN NUMBER GENERATED ALWAYS AS (ROUND(NVL("RM_REVN",0)+NVL("FB_REVN",0)+NVL("RM_UPGRADES",0)+NVL("OTHER_REVN",0)+NVL("DIVE_SERV",0)+NVL("DIVE_EQUIP",0),2)) VIRTUAL

Troubleshoot high CPU usage in Amazon Redshift

https://aws.amazon.com/premiumsupport/knowledge-center/redshift-high-cpu-usage/

select max(value) by char

SELECT MAX(to_number(regexp_substr(cv.CODEMNEMONIC, '[0-9]*$')))

Default Value for column

ROUND(NVL("RM_REVN",0)+NVL("FB_REVN",0)+NVL("RM_UPGRADES",0)+NVL("OTHER_REVN",0)+NVL("DIVE_SERV",0)+NVL("DIVE_EQUIP",0),2)

Hide Items when page loads

apex.item( "P368_DATE" ).hide();

#sql #sqlserver #dbmaintenance

Find tables without PK, Clustered or Non-clustered Indexes and report number of rows

-- ==================================================================
-- sys.indexes.type 0 => heap - i.e. no Primary Key 
--					1 => clustered index
--					2 => non-clustered index
-- ==================================================================

SELECT	o.object_id
        , schemaname = OBJECT_SCHEMA_NAME(o.object_id)
		, tablename = o.NAME
		, PrimaryKey = CASE WHEN pk.object_id IS NULL THEN 'No Primary Key' ELSE '' END 
		, ClusteredIndexes = CASE WHEN ci.object_id IS NULL THEN 'No Clustered Index' ELSE '' END
		, NonClusteredIndexes = CASE WHEN nci.object_id IS NULL THEN 'No Non-Clustered Index' ELSE ''  END
		, [RowCount] = (SELECT SUM(p.Rows) from sys.partitions AS p where p.object_id=o.object_id) / (1+COUNT(ci.object_id)+COUNT(nci.object_id))
		, [IndexCount] = COUNT(ci.object_id)+COUNT(nci.object_id)
FROM sys.objects o
LEFT OUTER JOIN sys.indexes nci ON nci.object_id = o.object_id and nci.type=2
LEFT OUTER JOIN sys.indexes ci ON ci.object_id = o.object_id and ci.type=1
LEFT OUTER JOIN sys.indexes pk ON o.object_id = pk.object_id AND pk.is_primary_key = 1
WHERE o.Type='U' AND ((pk.object_id IS NULL) OR (ci.object_id IS NULL) OR (nci.object_id IS NULL))

GROUP BY o.object_id,
         OBJECT_SCHEMA_NAME(o.object_id), 
         o.NAME, 
		 CASE WHEN pk.object_id IS NULL THEN 'No Primary Key' ELSE '' END, 
		 CASE WHEN nci.object_id IS NULL THEN 'No Non-Clustered Index' ELSE '' END,
		 CASE WHEN ci.object_id IS NULL THEN 'No Clustered Index' ELSE '' END

ORDER BY TableName ASC

#sql #sqlserver #dbmaintenance

sql server - list of tables without indexes in sql 2008

SELECT 
     schemaname = OBJECT_SCHEMA_NAME(o.object_id)
    ,tablename = o.NAME
FROM sys.objects o
INNER JOIN sys.indexes i ON i.OBJECT_ID = o.OBJECT_ID
-- tables that are heaps without any nonclustered indexes
WHERE (
        o.type = 'U'
        AND o.OBJECT_ID NOT IN (
            SELECT OBJECT_ID
            FROM sys.indexes
            WHERE index_id > 0
            )
        )
        --    OR
        -- table that have a clustered index without any nonclustered indexes
        --(o.type='U' 
        --        AND o.OBJECT_ID NOT IN (
        --    SELECT OBJECT_ID 
        --        FROM sys.indexes 
        --        WHERE index_id>1))

Optimization Con Cleanup v2

from dataclasses import replace
from typing import final
from ams.db import select_data, insert_df, sql_execute
import pandas as pd
import numpy as np
import datetime
import os

from sqlalchemy import false

connection_string = os.environ['DB_A']

# Read from database and save file

# def my_ceil(a, precision=0):
#     return np.true_divide(np.ceil(a * 10**precision), 10**precision)

# def my_floor(a, precision=0):
#     return np.true_divide(np.floor(a * 10**precision), 10**precision)

saveme_df = select_data(connection_string, """SELECT * FROM ams_axioma_weights_rebal WHERE RUN_DATE = '30-JUN-22' AND ACCT_TYPE = 'Constrained'""")
sizecheck_df = select_data(connection_string, """SELECT * FROM ams_axioma_weights_rebal WHERE RUN_DATE = '30-JUN-22' AND ACCT_TYPE = 'Constrained'""")
# checkme_df = select_data(connection_string, """SELECT * FROM ams_axioma_weights_rebal WHERE RUN_DATE = '31-MAY-22' AND ACCT_TYPE = 'Constrained'""")
saveme_df.to_csv('C:/Users/cvanwert/Documents/ams_axioma_weights_rebal.csv', index=False)
# saveme_df = pd.read_csv('C:/Users/cvanwert/Documents/ams_axioma_weights_rebal.csv')

saveme_df['PERCENTILE'] = saveme_df.PERCENTILE.astype(float)
saveme_df['PERCENTILE_PRE'] = saveme_df['PERCENTILE'].apply(lambda x: np.true_divide(np.floor(x * 10**4), 10**4))
saveme_df['PERCENTILE_FIX'] = saveme_df['PERCENTILE_PRE'].apply(lambda x: np.true_divide(np.ceil(x * 10**2), 10**2))
# saveme_df[saveme_df['PERCENTILE'] == 1.1].PERCENTILE_FIX.unique()
saveme_df.loc[saveme_df.PERCENTILE == 1.1, 'PERCENTILE_FIX'] = 1.1
saveme_df.loc[saveme_df.PERCENTILE == 1.09, 'PERCENTILE_FIX'] = 1.09
saveme_df.loc[saveme_df.PERCENTILE == 1.08, 'PERCENTILE_FIX'] = 1.08
saveme_df.loc[saveme_df.PERCENTILE == 1.07, 'PERCENTILE_FIX'] = 1.07
saveme_df.loc[saveme_df.PERCENTILE == 1.06, 'PERCENTILE_FIX'] = 1.06
saveme_df.loc[saveme_df.PERCENTILE == 1.05, 'PERCENTILE_FIX'] = 1.05
saveme_df.loc[saveme_df.PERCENTILE == 1.04, 'PERCENTILE_FIX'] = 1.04
saveme_df.loc[saveme_df.PERCENTILE == 1.03, 'PERCENTILE_FIX'] = 1.03
saveme_df.loc[saveme_df.PERCENTILE == 1.02, 'PERCENTILE_FIX'] = 1.02
saveme_df.loc[saveme_df.PERCENTILE == 1.01, 'PERCENTILE_FIX'] = 1.01
saveme_df.loc[saveme_df.PERCENTILE == 1., 'PERCENTILE_FIX'] = 1.
saveme_df.loc[saveme_df.PERCENTILE == 0.99, 'PERCENTILE_FIX'] = 0.99
saveme_df.loc[saveme_df.PERCENTILE == 0.98, 'PERCENTILE_FIX'] = 0.98
saveme_df.loc[saveme_df.PERCENTILE == 0.97, 'PERCENTILE_FIX'] = 0.97
saveme_df.loc[saveme_df.PERCENTILE == 0.96, 'PERCENTILE_FIX'] = 0.96
saveme_df.loc[saveme_df.PERCENTILE == 0.95, 'PERCENTILE_FIX'] = 0.95
saveme_df.loc[saveme_df.PERCENTILE == 0.94, 'PERCENTILE_FIX'] = 0.94
saveme_df.loc[saveme_df.PERCENTILE == 0.93, 'PERCENTILE_FIX'] = 0.93
saveme_df.loc[saveme_df.PERCENTILE == 0.92, 'PERCENTILE_FIX'] = 0.92
saveme_df.loc[saveme_df.PERCENTILE == 0.91, 'PERCENTILE_FIX'] = 0.91
saveme_df.loc[saveme_df.PERCENTILE == 0.9, 'PERCENTILE_FIX'] = 0.9
# saveme_df.loc[(saveme_df.PERCENTILE == 0.96015) & (saveme_df.MODEL_CODE == 'E2GR'), 'PERCENTILE_FIX'] = 0.96
saveme_df['PERCENTILE_TEMP'] = saveme_df['PERCENTILE_FIX'].apply(lambda x: x*100).astype(int)
saveme_df['ACCT_ID_FIX'] = 'CON' + saveme_df['PERCENTILE_TEMP'].astype(str)

dupe_check_df = pd.concat(g for _, g in saveme_df.groupby(["MODEL_CODE","ACCT_ID_FIX","ASSET_CLASS"]) if len(g) > 1)
Index_array = dupe_check_df.index
saveme_df.loc[Index_array, 'PERCENTILE_FIX'] = saveme_df.loc[Index_array, 'PERCENTILE_PRE'].apply(lambda x: np.true_divide(np.floor(x * 10**2), 10**2))
saveme_df['PERCENTILE_TEMP'] = saveme_df['PERCENTILE_FIX'].apply(lambda x: x*100).astype(int)
saveme_df['ACCT_ID_FIX'] = 'CON' + saveme_df['PERCENTILE_TEMP'].astype(str)
dupe_check_df = pd.concat(g for _, g in saveme_df.groupby(["MODEL_CODE","ACCT_ID_FIX","ASSET_CLASS"]) if len(g) > 1)

# saveme_df.loc[(saveme_df.PERCENTILE == dupe_check_df.PERCENTILE) & (saveme_df.MODEL_CODE == dupe_check_df.MODEL_CODE), 'PERCENTILE_FIX'] = saveme_df['PERCENTILE_PRE'].apply(lambda x: np.true_divide(np.floor(x * 10**2), 10**2))
# dupe_check_df.to_csv('C:/Users/cvanwert/Documents/dupes_ams_axioma_weights_rebal.csv')

saveme_df = saveme_df.drop(columns=['PERCENTILE', 'ACCT_ID'])
saveme_df= saveme_df.rename(columns={"PERCENTILE_FIX":"PERCENTILE", "ACCT_ID_FIX":"ACCT_ID"})
saveme_df = saveme_df.drop(columns=['PERCENTILE_TEMP'])
saveme_df = saveme_df.drop(columns=['PERCENTILE_PRE'])

saveme_df = saveme_df[['RUN_DATE', 'MODEL_CODE', 'ACCT_TYPE', 'ACCT_ID', 'PERCENTILE', 'ASSET_CLASS', 'WEIGHT']]

saveme_df['ACCT_ID'] = saveme_df.ACCT_ID.astype(str)
#Insert into database
saveme_df['PERCENTILE'] = saveme_df.PERCENTILE.astype(float)
saveme_df['WEIGHT'] = saveme_df.WEIGHT.astype(float)
saveme_df['RUN_DATE'] = pd.to_datetime(saveme_df.RUN_DATE)


# Delete Raw Returns Run
sql_execute(connection_string, f"""DELETE FROM ams_axioma_weights_rebal WHERE RUN_DATE = '30-JUN-22' AND ACCT_TYPE = 'Constrained'""")
insert_df(connection_string,saveme_df,'ams_axioma_weights_rebal')

# Stats to match

statssaveme_df = select_data(connection_string, """SELECT * FROM AMS_AXIOMA_STATS_REBAL WHERE RUN_DATE = '30-JUN-22' AND ACCT_TYPE = 'Constrained'""")
# statssaveme_df = pd.read_csv('C:/Users/cvanwert/Documents/ams_axioma_stats_rebal.csv')
statssaveme_df.to_csv('C:/Users/cvanwert/Documents/ams_axioma_stats_rebal.csv', index=False)

statssaveme_df['PERCENTILE'] = statssaveme_df.PERCENTILE.astype(float)
statssaveme_df['PERCENTILE_PRE'] = statssaveme_df['PERCENTILE'].apply(lambda x: np.true_divide(np.floor(x * 10**4), 10**4))
statssaveme_df['PERCENTILE_FIX'] = statssaveme_df['PERCENTILE_PRE'].apply(lambda x: np.true_divide(np.ceil(x * 10**2), 10**2))
statssaveme_df[statssaveme_df['PERCENTILE'] == 1.1].PERCENTILE_FIX.unique()
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.1, 'PERCENTILE_FIX'] = 1.1
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.1, 'PERCENTILE_FIX'] = 1.1
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.09, 'PERCENTILE_FIX'] = 1.09
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.08, 'PERCENTILE_FIX'] = 1.08
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.07, 'PERCENTILE_FIX'] = 1.07
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.06, 'PERCENTILE_FIX'] = 1.06
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.05, 'PERCENTILE_FIX'] = 1.05
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.04, 'PERCENTILE_FIX'] = 1.04
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.03, 'PERCENTILE_FIX'] = 1.03
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.02, 'PERCENTILE_FIX'] = 1.02
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1.01, 'PERCENTILE_FIX'] = 1.01
statssaveme_df.loc[statssaveme_df.PERCENTILE == 1., 'PERCENTILE_FIX'] = 1.
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.99, 'PERCENTILE_FIX'] = 0.99
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.98, 'PERCENTILE_FIX'] = 0.98
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.97, 'PERCENTILE_FIX'] = 0.97
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.96, 'PERCENTILE_FIX'] = 0.96
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.95, 'PERCENTILE_FIX'] = 0.95
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.94, 'PERCENTILE_FIX'] = 0.94
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.93, 'PERCENTILE_FIX'] = 0.93
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.92, 'PERCENTILE_FIX'] = 0.92
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.91, 'PERCENTILE_FIX'] = 0.91
statssaveme_df.loc[statssaveme_df.PERCENTILE == 0.9, 'PERCENTILE_FIX'] = 0.9
# statssaveme_df.loc[(statssaveme_df.PERCENTILE == 0.96015) & (statssaveme_df.MODEL_CODE == 'E2GR'), 'PERCENTILE_FIX'] = 0.96
statssaveme_df['PERCENTILE_TEMP'] = statssaveme_df['PERCENTILE_FIX'].apply(lambda x: x*100).astype(int)
statssaveme_df['ACCT_ID_FIX'] = 'CON' + statssaveme_df['PERCENTILE_TEMP'].astype(str)
# statssaveme_df.loc[(statssaveme_df.PERCENTILE == 0.92164) & (statssaveme_df.MODEL_CODE == 'UKBAL'), 'ACCT_ID_FIX'] = 'CON0.92164'

dupe_check_df = pd.concat(g for _, g in statssaveme_df.groupby(["MODEL_CODE","ACCT_ID_FIX","STAT_NAME"]) if len(g) > 1)
Index_array = dupe_check_df.index
statssaveme_df.loc[Index_array, 'PERCENTILE_FIX'] = statssaveme_df.loc[Index_array, 'PERCENTILE_PRE'].apply(lambda x: np.true_divide(np.floor(x * 10**2), 10**2))
statssaveme_df['PERCENTILE_TEMP'] = statssaveme_df['PERCENTILE_FIX'].apply(lambda x: x*100).astype(int)
statssaveme_df['ACCT_ID_FIX'] = 'CON' + statssaveme_df['PERCENTILE_TEMP'].astype(str)
dupe_check_df = pd.concat(g for _, g in statssaveme_df.groupby(["MODEL_CODE","ACCT_ID_FIX","STAT_NAME"]) if len(g) > 1)

statssaveme_df = statssaveme_df.drop(columns=['PERCENTILE', 'ACCT_ID'])
statssaveme_df= statssaveme_df.rename(columns={"PERCENTILE_FIX":"PERCENTILE", "ACCT_ID_FIX":"ACCT_ID"})
statssaveme_df = statssaveme_df.drop(columns=['PERCENTILE_TEMP'])
statssaveme_df = statssaveme_df.drop(columns=['PERCENTILE_PRE'])
# statssaveme_df = statssaveme_df[['RUN_DATE', 'MODEL_CODE', 'ACCT_TYPE', 'ACCT_ID', 'PERCENTILE', 'ASSET_CLASS', 'WEIGHT']]

statssaveme_df['ACCT_ID'] = statssaveme_df.ACCT_ID.astype(str)
statssaveme_df['PERCENTILE'] = statssaveme_df.PERCENTILE.astype(float)
statssaveme_df['VALUE'] = statssaveme_df.VALUE.astype(float)
statssaveme_df['RUN_DATE'] = pd.to_datetime(statssaveme_df.RUN_DATE)

# statssaveme_df = statssaveme_df.drop(columns=['Unnamed: 0'])

sql_execute(connection_string, f"""DELETE FROM AMS_AXIOMA_STATS_REBAL WHERE RUN_DATE = '30-JUN-22' AND ACCT_TYPE = 'Constrained'""")
insert_df(connection_string,statssaveme_df,'ams_axioma_stats_rebal')

sql - pg_ctl: no database directory specified and environment variable PGDATA unset - Stack Overflow

 set $PGDATA = [directory location here]

mysql - ERROR 1067 (42000): Invalid default value for 'created_at' - Stack Overflow

set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

create or replace table `reby-cloud.reby_marketing_eu.user_360` as 
select
  created_at,
  meta_user_id,
  type,
  concat(
    'amount:',if(amount is null,'na',cast(amount as string)),'; ',
    'minutes:',if(minutes is null,'na',cast(minutes as string)),'; ',
    'service_area:',if(service_area is null, 'na',service_area),'; ',
    'vehicle_type:',if(vehicle_type is null, 'na',vehicle_type),'; ',
    'app_used:',if(app_promoting_co is null, 'na',app_promoting_co),'; ',
    'vehicle_co:',if(owner_co is null,'na',owner_co),'; ',
    'topup_through:',if(topup_trough_co is null, 'na',topup_trough_co)
  ) as details
from `reby-cloud.analytics_reby_v1_eu.py_ridestatus_combined` pyrsc
where type not in ('vehicle_reservation')

union all

SELECT
  timestamp(created_at) as created_at,
  user_id as meta_user_id,
  'cx-conversation' as type,
  concat (
    'conversation_id:',conversation_id,'; ',
    'conact_reason1:',if(cantact_reason_level1 is null, 'na',cantact_reason_level1),'; ',
    'conact_reason2:',if(cantact_reason_level2 is null, 'na',cantact_reason_level2)
  ) as details
FROM `reby-cloud.kustomer_eu.conversation_messages_combination` 

union all

select
  created_at,
  user_id as meta_user_id,
  'user-created' as type,
  concat ('company:',co.name) as details
from `reby-cloud.reby_marketing_eu.pg_company_user` cu
  left join `reby-cloud.analytics_reby_v1_eu.pg_company` co on cu.company_id = co.id

Browse more snippets >>