Home php c# sql c c++ javascript python java go android git linux asp.net django .net node.js ios xcode cocoa iphone mysql tomcat mongodb bash objective-c scala visual-studio apache elasticsearch jar eclipse jquery ruby-on-rails ruby rubygems android-studio spring lua sqlite emacs ubuntu perl docker swift amazon-web-services svn html ajax xml java-ee maven intellij-idea rvm macos unix css ipad postgresql css3 json windows-server vue.js typescript oracle hibernate internet-explorer github tensorflow laravel symfony redis html5 google-app-engine nginx firefox sqlalchemy lucene erlang flask vim solr webview facebook zend-framework virtualenv nosql ide twitter safari flutter bundle phonegap centos Register | Login | Edit Tags | New Questions | 繁体 | 简体


10 questions online user: 15

0
votes
answers
6 views
+10

PostgreSQL Varchar UID到Int UID,同時保留唯一性

0

說我有一個獨特的列VarChar(32)PostgreSQL Varchar UID到Int UID,同時保留唯一性

ex。 13bfa574e23848b68f1b7b5ff6d794e1

我想保留這個唯一性,同時將列轉換爲int。我想我可以將所有的字母都轉換成相應的ascii,同時保留數字和字符位置。爲此,我將使用翻譯功能。

僞代碼:select translate(uid, '[^0-9]', ascii('[^0-9]'))

我的問題是找到所有的字母在VARCHAR列原本。 我試過

select uid, substring(uid from '[^0-9]') from test_table;

但它只返回它遇到的第一個字母。使用上面的例子,我會尋找bfaebfbbffde

任何幫助表示讚賞!

+0

您無法將VarChar(32)值映射到int以保持唯一性。如果您將所有字母替換爲ascii等效字符,您將獲得至少32位數的數字,而最大整數爲2,147,483,647。 – CodeFuller

+1

Postgres有特殊[UUID類型。(https://www.postgresql.org/docs/current/static/datatype-uuid.html) – klin

+1

您應該使用UUID類型,而不是。 –

沙发
0
0

首先,我同意兩位評論者說你應該使用UID數據類型。

這一邊......

你的UID看起來像一個傳統的,因爲它不是字母數字,這是十六進制。如果是這樣的話,你可以使用這個解決方案的十六進制轉換爲數值:

PostgreSQL: convert hex string of a very large number to a NUMERIC

通知接受的解決方案(我的,恥辱)是不如其他解決方案上市,作爲煤礦將不適用於這個大的十六進制值。

這就是說,這是一個巨大的數字。聖煙。

取決於表中有多少條記錄和插入/更新的頻率,我會考慮一種截然不同的方法。簡而言之,我會創建另一列來存儲您的數字ID,其值將由序列確定。

如果你真的想讓它刀槍不入,你也可以創建一個交叉引用表來存儲將

  1. 重用一個ID,如果它曾經多次(我知道的UID沒有關係,但這將涵蓋記錄被錯誤刪除的情況下,再次出現,並且要保留原來的ID)
  2. 如果UID的重複(比如這是每個UID多個記錄的子表),這將覆蓋情況以及

如果沒有這些應用,你ç應該把它弄歪了一下。

該解決方案將是這個樣子:

添加一個ID列,這將是你的數字相當於UID:

alter table test_table 
add column id bigint 

創建一個序列:

CREATE SEQUENCE test_id 

創建交叉參考表(再次,對於虛擬版本不需要):

create table test_id_xref (
    uid varchar(32) not null, 
    id bigint not null, 
    constraint test_id_xref_pk primary key (uid) 
) 

然後做一次更新替代ID分配給每個UID兩種交叉引用和實際表:

insert into test_id_xref 
with uids as (
    select distinct uid 
    from test_table 
) 
select uid, nextval ('test_id') 
from uids; 

update test_table tt 
set id = x.id 
from test_id_xref x 
where tt.uid = x.uid; 

最後,未來所有刀片,創建一個觸發器來分配下一個值:

CREATE OR REPLACE FUNCTION test_table_insert_trigger() 
    RETURNS trigger AS 
$BODY$ 
BEGIN 
    select t.id 
    from test_id_xref t 
    into NEW.id 
    where t.uid = NEW.uid; 

    if NEW.id is null then 
    NEW.id := nextval('test_id'); 
    insert into test_id_xref values (NEW.uid, NEW.id); 
    end if; 

    return NEW; 
END; 
$BODY$ 
    LANGUAGE plpgsql VOLATILE 
    COST 100; 

CREATE TRIGGER insert_test_table_trigger 
    BEFORE INSERT 
    ON test_table 
    FOR EACH ROW 
    EXECUTE PROCEDURE test_table_insert_trigger(); 
+0

感謝您的幫助。我回過頭來看看數據的來源,它的確基於UUID。我使用鏈接中列出的函數(不是接受的函數)將其轉換爲整數。很棒! – james

板凳
0
0

創建一個函數替換空白包車,你不是在串需要,

CREATE FUNCTION replace_char(v_string VARCHAR(32) CHARSET utf8) RETURNS VARCHAR(32) 
    DETERMINISTIC 
BEGIN 

    DECLARE v_return_string VARCHAR(32) DEFAULT ''; 
    DECLARE v_remove_char VARCHAR(200) DEFAULT '1,2,3,4,5,6,7,8,9,0'; 
    DECLARE v_length, j INT(3) DEFAULT 0; 

    SET v_length = LENGTH(v_string); 

    WHILE(j < v_length) DO 
    IF (FIND_IN_SET(SUBSTR(v_string, (j+1), 1), v_remove_char) = 0) THEN 
     SET v_return_string = CONCAT(v_return_string, SUBSTR(v_string, (j+1), 1)); 
    END IF; 
    SET j = j+1; 
    END WHILE; 

    RETURN v_return_string; 

END$$ 

DELIMITER ; 

現在你只要東東調用這個函數在查詢

select uid, replace_char(uid) from test_table; 

它會給你字符串你需要(bfaebfbbffde

如果你只想int數,即13574238486817567941那麼改變變量的值和列數據類型在十進制(50,0)中,十進制可以存儲大數,並且有小數點0,所以它將存儲int值作爲十進制。

v_remove_char = 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z'; 
0
votes
answers
6 views
+10

改善性能postgresql查詢

-2

我有3個表格:users,postslikes。如果帖子在創建後的第一個小時內有5個以上的喜歡,則該帖子被稱爲熱帖。以下是用於查詢熱門帖子的列表。任何人都可以幫助我改進這個查詢(如何索引或重寫它)。改善性能postgresql查詢

SELECT post.id, 
     post.content, 
     user.username, 
     COUNT(like.id) 
FROM posts AS post 
    LEFT OUTER JOIN users AS user 
     ON post.user_id = user.id 
    INNER JOIN likes AS likes 
     ON post.id = likes.post_id 
     AND likes.created_at - INTERVAL '1 hour' < post.created_at 
GROUP BY post.id, user.username 
HAVING COUNT(like.id) >= 5 
ORDER BY post.created_at DESC; 
+0

@TimBiegeleisen指數:

如果規劃器選擇嵌套循環連接畢竟,它可能將查詢重寫是有用的它的工作原理。 http://prntscr.com/hidlsc –

+1

您需要爲任何人提供更多信息,以便爲您提供任何有價值的見解。例如,你的表定義,任何現有的索引(如果有的話)和'EXPLAIN'的輸出。此外,儘量不要用大寫字母給表格別名,並刪除所有雙引號(爲了便於閱讀)。 – Nicarus

+3

請** [編輯] **您的問題,併爲所討論的表格(包括所有索引)和使用**'explain(analyze,buffers)'**生成的執行計劃添加'create table'語句。 [**格式化文本**](http://stackoverflow.com/help/formatting)請,[無屏幕截圖](http://meta.stackoverflow.com/questions/285551/why-may-i-not -upload圖像-的代碼上那麼當-要價-A-問題/ 285557#285557) –

沙发
0
1

首先,除非實際上可能有不屬於用戶的帖子,否則使用內部聯接。

假設有大量的帖子和喜歡,最好的連接策略是合併連接或散列連接,PostgreSQL應該自動選擇。

對於合併連接,下面的指標可能會有所幫助:

CREATE INDEX ON posts (id); 
CREATE INDEX ON likes (post_id); 

號指數可以用哈希幫助在這種情況下加入。

... AND likes.created_at < post.created_at + INTERVAL '1 hour' 

和創建像

CREATE INDEX ON likes (post_id, created_at); 
0
votes
answers
6 views
+10

通過屬性匹配兩個表的行

1

有沒有辦法在普通PL/pgSQL中匹配實體? 比方說,我想通過使用比較三個單屬性的文本搜索查詢來識別匹配搜索一個人在客戶表:通過屬性匹配兩個表的行

SELECT id, first_name, last_name, birthdate 
    FROM customers_index 
    WHERE first_name @@ plainto_tsquery('simple','John') 
     AND last_name @@ plainto_tsquery('simple','Smith') 
     AND birthdate = '17.08.1967'; 

但這樣做對每個人是有點乏味,因爲我有一整張我想要以這種方式匹配的人。在任何「正常編程語言」的幫助下,我現在循環一個數組,直到我發現每一行的現有匹配成爲一個新表,但有沒有辦法在普通的PL/pgSQL中執行此操作?

沙发
0
1

我想在PL/PGSQL與陣列相結合作爲輸入參數,將很好地工作的功能(類似於Oracle的PL/SQL):

CREATE OR REPLACE FUNCTION customers(first_names text[], last_names text[], 
    birthdays date[]) 
    RETURNS SETOF customers_index as 
$BODY$ 
DECLARE 
    i integer; 
    elements integer; 
    rw customers_index%rowtype; 
BEGIN 

    elements := array_length (first_names); 

    for i in 1..elements loop 

    for rw in SELECT ci.* 
     FROM customers_index ci 
     WHERE ci.first_name @@ plainto_tsquery('simple', first_names[i]) 
     AND ci.last_name @@ plainto_tsquery('simple', last_names[i]) 
     AND ci.birthdate = birthdays[i] 
    loop 
     return next rw; 
    end loop; 
    end loop; 

    return; 

END; 
$BODY$ 
    LANGUAGE plpgsql VOLATILE 
    COST 100; 

和實施將是這個樣子:

select 
    id, first_name, last_name, birthdate 
from 
    customers(array['John', 'Jane'], array['Smith', 'Doe'], 
     array ['17.08.1967', '16.07.1970']) 

如果當然,假設你的數據來自其他地方,實際的實現應該看起來更清潔。

另外,我不保證這是超高效的,但它是從A到B的快速路徑,並且有一些GIN索引,或者至少是生日的索引,它可能實際上運行得很漂亮很好。

+0

謝謝,我可以根據您的建議創建一個工作解決方案。只是一個小問題:array_length()需要兩個參數,所以array_length(first_names,1)做了這個工作。由於沒有人提供解決方案,我會將您的解決方案標記爲最佳答案。 – MayaK

0
votes
answers
5 views
+10

PostgreSQL中的時間序列有很大的查詢需求

0

我期待實現時間序列數據庫,並且我已經通過數據庫的各種選項來使用,但是因爲我不是那種我選擇的知識與PostgreSQL,因爲我有點熟悉使用它與Django(特別是由於ORM)。PostgreSQL中的時間序列有很大的查詢需求

這個想法是存儲4列數據的時間序列(索引除了價格以外)。

timestamp | id | item | price 

我正在尋找添加這些每分鐘,大約1500數據點是批量插入每分鐘。一個月後,我不再需要它們具體到分鐘,每天只需要一個就足夠了(在00:00)。

我正確地認爲PostgreSQL應該做得很好嗎?這將由後端服務,並且需要非常低的延遲(300 ms往返)。

我的主要問題在於理解PostgreSQL是否能夠有效地返回數據,即使在給定需求時,例如項目範圍,開始和結束時間戳以及請求數據的時間間隔(無需返回所有內容並手動過濾)。

如果我的表包含下列數據的單個項目:

timestamp | id | item | price 
1514391000 01 foo  10 
1514391100 02 foo  20 
1514391200 03 foo  30 
.......... .. ...  .. 
1514392000 11 foo  20 
1514393000 21 foo  20 

我希望能夠請求start: 1514391000end: 1514392000step: 200,那麼我會希望得到6個結果回來了(1000, 1200,1400,1600,1800和2000)。 PostgreSQL能以高效的方式實現這種功能嗎?

我能想到的唯一的事情是插入我的時間序列時,我確保他們的值四捨五入到最接近的分鐘,然後我確切地知道要過濾的時間戳而不需要搜索數據庫。

我也想知道是否有可能搜索'最近的時間戳'爲一個給定的項目,相同的情況。所有這些似乎都可以通過巧妙的時間戳輸入來解決,但我不確定這是否可行。

+0

你評價過[Timescale DB](https://www.timescale.com/)嗎?它由Postgres構建,以時間序列數據爲主要目標。我的公司測試了它的早期版本,發現它對於時間序列查詢非常靈活,但我們的要求發生了變化,所以我們最終選擇了[Citus](https://www.citusdata.com)。 – bma

+0

很酷,我完全錯過了(正在看InfluxDB和類似的東西)。我想知道Timescale數據庫是否可以很好地與Django的ORM配合使用,我不介意額外的自定義SQL用於其他任務。我還打算使用這個數據庫來處理一些關係問題(指標之外)。 – sof2er

+0

如果Django不能很好地工作,我會感到很驚訝,因爲它是Postgresql的核心,並且爲了優化時間序列而進行了額外的工作。我最初的要求包括非時間序列報告,並且工作正常(基本上充當普通Postgres服務器)。請注意,我的測試在最大的表中只有80億行,所以我不能聲稱已經測試了幾十兆兆字節的數據。 – bma

沙发
0
0

我會建議有一個時間戳開始和時間戳結束列。然後你可以隨時找到匹配的行。

我在考慮雙表解決方案,一個用於更新的數據,一個用於舊數據。

你也應該劃分你最近的表格,也許白天。這將使您可以更有效地管理舊數據 - 一次只丟棄一天(或一週或一個月)的數據。

然後,每天(或一週或一個月),將較舊的數據彙總到要存檔的記錄中。您可以從較新的數據中刪除分區。

您可以交換存檔分區或使用視圖來合併它們。

0
votes
answers
5 views
+10

遷飛JDBC連接遷移麻煩

0


我有遷飛JDBC遷移麻煩:執行我試圖關閉數據庫連接與conn.close();方法,而且所有報表後,我收到org.postgresql.util.PSQLException: This connection has been closed.例外。當我檢查數據庫時 - 我在遷移過程中所做的所有更改都已完成,但schema_version表未更新:未添加有關遷移的項目。
更多細節在這裏:
在flyway.properties我添加了classpath:com/packageone/somepackege/database/migrationflyway.locations部分。然後,我創建了將執行遷移類:public class V1_1_1__SomeImportantMigration implements JdbcMigration並填寫migrate方式類似:即在migrate方法做
遷飛JDBC連接遷移麻煩

@Override 
     public void migrate(Connection conn) throws Exception { 
      beforeActions(conn); 
      veryImportantMigration(conn, getData(conn)); 
      afterActions(conn); 
      conn.close(); 
     } 

事情被應用,但關閉連接導致異常。
由於上述信息,我有這樣的問題:爲什麼這個連接在被我關閉之前關閉?並通過什麼?

UPDATE(堆棧跟蹤):

[ERROR] Unable to rollback transaction 
org.postgresql.util.PSQLException: This connection has been closed. 
    at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:820) 
    at org.postgresql.jdbc2.AbstractJdbc2Connection.rollback(AbstractJdbc2Connection.java:837) 
    at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:96) 
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:273) 
    at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:53) 
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:210) 
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:146) 
    at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:68) 
    at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLDbSupport.lock(PostgreSQLDbSupport.java:162) 
    at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:174) 
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:146) 
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1010) 
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:971) 
    at org.flywaydb.core.Flyway.execute(Flyway.java:1464) 
    at org.flywaydb.core.Flyway.migrate(Flyway.java:971) 
    at org.flywaydb.maven.MigrateMojo.doExecute(MigrateMojo.java:30) 
    at org.flywaydb.maven.AbstractFlywayMojo.execute(AbstractFlywayMojo.java:600) 
    at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:134) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:207) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145) 
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:116) 
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:80) 
    at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51) 
    at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:128) 
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:307) 
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:193) 
    at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:106) 
    at org.apache.maven.cli.MavenCli.execute(MavenCli.java:863) 
    at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:288) 
    at org.apache.maven.cli.MavenCli.main(MavenCli.java:199) 
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
    at java.lang.reflect.Method.invoke(Method.java:498) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356) 
[ERROR] Unable to restore autocommit to original value for connection 
org.postgresql.util.PSQLException: This connection has been closed. 
    at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:820) 
    at org.postgresql.jdbc2.AbstractJdbc2Connection.setAutoCommit(AbstractJdbc2Connection.java:762) 
    at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:114) 
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:273) 
    at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:53) 
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:210) 
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:146) 
    at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:68) 
    at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLDbSupport.lock(PostgreSQLDbSupport.java:162) 
    at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:174) 
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:146) 
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1010) 
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:971) 
    at org.flywaydb.core.Flyway.execute(Flyway.java:1464) 
    at org.flywaydb.core.Flyway.migrate(Flyway.java:971) 
    at org.flywaydb.maven.MigrateMojo.doExecute(MigrateMojo.java:30) 
    at org.flywaydb.maven.AbstractFlywayMojo.execute(AbstractFlywayMojo.java:600) 
    at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:134) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:207) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145) 
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:116) 
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:80) 
    at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51) 
    at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:128) 
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:307) 
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:193) 
    at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:106) 
    at org.apache.maven.cli.MavenCli.execute(MavenCli.java:863) 
    at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:288) 
    at org.apache.maven.cli.MavenCli.main(MavenCli.java:199) 
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
    at java.lang.reflect.Method.invoke(Method.java:498) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356) 
[ERROR] Unable to release Flyway advisory lock 
org.postgresql.util.PSQLException: This connection has been closed. 
    at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:820) 
    at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:275) 
    at org.postgresql.jdbc2.AbstractJdbc2Connection.prepareStatement(AbstractJdbc2Connection.java:293) 
    at org.flywaydb.core.internal.dbsupport.JdbcTemplate.prepareStatement(JdbcTemplate.java:319) 
    at org.flywaydb.core.internal.dbsupport.JdbcTemplate.execute(JdbcTemplate.java:247) 
    at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:81) 
    at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLDbSupport.lock(PostgreSQLDbSupport.java:162) 
    at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:174) 
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:146) 
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1010) 
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:971) 
    at org.flywaydb.core.Flyway.execute(Flyway.java:1464) 
    at org.flywaydb.core.Flyway.migrate(Flyway.java:971) 
    at org.flywaydb.maven.MigrateMojo.doExecute(MigrateMojo.java:30) 
    at org.flywaydb.maven.AbstractFlywayMojo.execute(AbstractFlywayMojo.java:600) 
    at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:134) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:207) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145) 
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:116) 
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:80) 
    at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51) 
    at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:128) 
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:307) 
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:193) 
    at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:106) 
    at org.apache.maven.cli.MavenCli.execute(MavenCli.java:863) 
    at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:288) 
    at org.apache.maven.cli.MavenCli.main(MavenCli.java:199) 
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
    at java.lang.reflect.Method.invoke(Method.java:498) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356) 
+1

你說他們是部分完成的。你有沒有例外?你能提供日誌嗎? – Korgen

+2

您不應該關閉該連接,它由飛路擁有。 –

+0

@Korgen,我可以分享日誌。部分惹眼 - 這只是我的錯誤... –

沙发
0
2

你不應該調用由遷飛提供給您的連接上close()。 Flyway將其自身用於其他內務處理任務(包括更新版本表),並關閉它可防止飛路正常工作。

0
votes
answers
5 views
+10

Postgresql的不同查詢爲何同樣解釋?

-1

查詢1:Postgresql的不同查詢爲何同樣解釋?

select c_bh,c_xzdm,c_twhbm,d_tjrq 
from (
    select c_bh,c_xzdm,c_twhbm,d_tjrq 
    from t_table 
    order by d_tjrq desc 
) t1 
limit 10 

問題2:

select c_bh,c_xzdm,c_twhbm,d_tjrq 
from t_table 
order by d_tjrq desc 
limit 10 

相同的計劃:

Limit (cost=0.44..18.79 rows=10 width=59) 
    -> Index Scan using i_t_table_d_tjrq on t_table (cost=0.44..32135085.58 rows=17507700 width=59) 
+0

查詢(2)的任何輸出都是查詢(1)的有效輸出(相反不成立),因此Postgresql可以選擇爲(1)提供與(2)相同的輸出,因此執行的操作相同計劃。 –

+0

當然,他們使用相同的計劃,因爲他們正在做同樣的事情。 –

+1

SQL是***聲明式***而非***勢在必行***。這意味着SQL沒有執行,它是一種允許你表達問題空間的語言,數據庫引擎從那個問題聲明中產生一個計劃*(它選擇自己的方式來解決它,通常使用成本估計)* 。在你的例子中,兩個SQL語句都是相同的問題,所以可以用同樣的方法解決。 – MatBailie

沙发
0
0

在第一種情況時,PostgreSQL 「變平」子查詢,以便它可以被優化更好。結果等同於第二個查詢。

如果您想避免這種情況,請將優化屏障OFFSET 0放入子查詢中。

0
votes
answers
5 views
+10

可視化的數據 - 營收趨勢每月有缺失的客戶數據

0

我對如何處理數據,以克服一定的一個月中繼客戶的收入,因爲他們開始業務丟失客戶數據的概念問題(這是一個關鍵點) 。我總是需要採取的最後報告,並只顯示它,否則我我增加了一倍的數據。 我展示每月收入的趨勢,問題是,如果客戶不告訴我們在給定月份的收入,以下月收入將不包括從上月該客戶的收入。所以基本上,我試圖找出如何,即使沒有收入已經報道,本月會延長顧客在接下來的幾個月的收入。我試圖在圖表中將其可視化。可視化的數據 - 營收趨勢每月有缺失的客戶數據

這是在數據庫中的數據結構的例子:

date |  customer | revenues 
08/2017  A   100 
08/2017  B   125 
09/2017  A   200 
09/2017  B   200 
10/2017  A   450 
10/2017  B   220 
10/2017  C   180 
11/2017  A   700 
11/2017  C   250 

當前結果:

08/2017 - total 225 - A 100, B 125 
09/2017 - total 400 - A 200, B 200 
10/2017 - total 850 - A 450, B 220, C 180 
11/2017 - total 880 - A 700, C 180 

通緝結果:

08/2017 - total 225 - A 100, B 125 
09/2017 - total 400 - A 200, B 200 
10/2017 - total 850 - A 450, B 220, C 180 
11/2017 - total 1170 - A 700, B 220, C 180 

我不知道我應該我試圖使用PostgreSQL或使用可視化工具。

我怎麼能自動模擬它(無論是在查看或其他方法),使應用的數據每個月包含了所有客戶的最新收入(攜帶向前,如果數據沒有爲客戶當月存在)。

沙发
0
0

您可以創建與PostgreSQL的這樣一個查詢視圖。

SELECT 
     date_t, 
     COALESCE(sum_a,0) + COALESCE(sum_b,0) + COALESCE(sum_c,0) Total, 
     SUM_A, 
     SUM_B, 
     SUM_C 
FROM 
     (SELECT 
       date_t, 
       CASE 
         WHEN LAG(sum_A,1) OVER (ORDER BY date_t) IS NOT NULL 
           AND sum_A IS NULL 
         THEN LAG(sum_A,1) OVER (ORDER BY date_t) 
         ELSE sum_A 
       END SUM_A, 
       CASE 
         WHEN LAG(sum_B,1) OVER (ORDER BY date_t) IS NOT NULL 
           AND sum_B IS NULL 
         THEN LAG(sum_B,1) OVER (ORDER BY date_t) 
         ELSE sum_B 
       END SUM_B, 
       CASE 
         WHEN LAG(sum_C,1) OVER (ORDER BY date_t) IS NOT NULL 
           AND sum_C IS NULL 
         THEN LAG(sum_C,1) OVER (ORDER BY date_t) 
         ELSE sum_C 
       END SUM_C 
     FROM 
       (select 
         date_t, 
         SUM (CASE 
           WHEN customer = 'A' 
           THEN revenues 
         END) sum_A, 
         SUM (CASE 
           WHEN customer = 'B' 
           THEN revenues 
         END) sum_B, 
         SUM (CASE 
           WHEN customer = 'C' 
           THEN revenues 
         END) sum_C 
       FROM 
         yourtable 
       GROUP BY 
         date_t 
       ) s1 
     )   s2 
ORDER BY 
     date_t; 

DEMO

LAG()功能檢查前一行 - 你的情況的總和。

0
votes
answers
5 views
+10

將postgreSQL連接到steam3

-1

我爲連接postgreSQLProvider和我的項目做了所有正確的事情。但在文件更改「供應商」,從「記憶」到「PostgreSQL的」後「流利」我得到了錯誤:將postgreSQL連接到steam3

Configuration error: Key hostname in Config/postgresql.json of type String required.

我必須說,我對這些東西很新。我該如何解決它?

+0

聽起來像你的配置中的'hostname'可能是空白的......試着把你的PostgreSQL數據庫的主機名放在那裏。 – Nicarus

+0

@Nicarus,我的主機名稱如下所示:「host」:「127.0.0.1」。這是正確的,對吧? –

+0

我不知道你的PostgreSQL數據庫在哪裏,但它聽起來像是在同一臺機器上。如果是這樣,請嘗試「本地主機」 – Nicarus

沙发
0
0

我只是將文件「postgresql.json」「host」更改爲「hostname」。

0
votes
answers
5 views
+10

如何通過調用Postgres函數插入記錄時提交

0

我寫了一個函數將記錄插入表person。行ID是串行(自動遞增)如何通過調用Postgres函數插入記錄時提交

CREATE OR REPLACE FUNCTION public.add_person(
    name character varying, 
    email character varying, 
    level integer, 
    company_id integer, 
    comp_list integer[]) 
    RETURNS integer as 

$BODY$ 
declare 
    p_id integer; 
begin 
    insert into person (name, email, level, company_id, comp_list, status, insert_ts) values ($1, $2, $3, $4, $5, 'Y', now()) 
    returning person.person_id into p_id; 
    return p_id; 
end 
$BODY$ LANGUAGE 'plpgsql' 

如果我運行SQL select * from add_person('xxx', '[email protected]', 1, 3, '{1,2}')這個功能,它成功地插入一條記錄。但是,當我使用SQLAlchemy在Python中調用此函數時,無法插入記錄。

engine = create_engine(URL(**settings.DATABASE)) 
session = scoped_session(sessionmaker(bind=engine)) 
email = '[email protected]' 
company = 1 
level = 3 
comp_list = '1,2' 
args = "'', '" + email + "', " + str(company) + ", " + str(level) + ", '{" + comp_list + "}'" 
statement = "select * from add_person({})".format(args) 
session.execute(statement) 

在Python中構造的語句與我在postgres中運行的命令完全相同。但它並沒有像表中那樣插入記錄。根本沒有錯誤信息。 session,engine配置正確,因爲所有其他select查詢工作。

我也注意到,即使記錄不能使用python代碼插入。主鍵的順序確實增加了。因爲當我在postgres中再次運行函數時,行ID跳過了。

如果我用SQLAlchemy會話進行插入而沒有提交,行爲是一樣的。

def add_person(name, email, company, level, comp_list): 
    current_ts = datetime.datetime.now() 
    p = Person(name = name, 
       email = email, 
       company_id = company, 
       level = level, 
       comp_list = comp_list, 
       status = 'Y', 
       insert_ts = current_ts) 
    session.add(p) 
    session.flush() 
    return p.person_id 

如果我運行上述方法的Python,person_id增量但沒有記錄被插入。只有當我將session.flush更改爲以下時,記錄才能正確插入。

session.add(p) 
    session.commit() 
    session.refresh(p) 
    print(p.person_id) 
    return p.person_id 

調用plsql函數時提交插入操作的正確方法是什麼?

+1

嘗試'conn.autocommit = True'請參閱[事務控制。](http://initd.org/psycopg/docs/usage.html#transactions-control) – klin

+0

請*不要使用字符串格式*來傳遞值查詢。這就是佔位符/綁定對象的用途。閱讀http://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-textual-sql查看「how」和https://xkcd.com/327/查看「why」。 –

+1

這裏甚至不需要文本SQL。函數泛型允許你執行'func.add_person('',email,company,level,comp_list.split(','))。select())'。請注意,您可以並應該將comp_list作爲一個列表。 –

沙发
0
1

除非您有autocommit連接設置爲True,否則您需要調用會話的commit()函數。

文檔:http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it


的原因,你的person_id增加,即使沒有提交,是因爲它使用的是序列 - 無論你創建了明確,或者爲您創建當你定義的列類型SERIAL (或類似)。該序列會在有或沒有提交的情況下遞增,並且不依賴於在表中成功插入記錄。

請注意,只涉及SELECT的語句不需要調用提交。

0
votes
answers
5 views
+10

2單個表中的函數依賴關係

2

我正在嘗試基於這兩個函數依賴關係創建一個模式。2單個表中的函數依賴關係

A - > B,C,d

乙 - > d

我試圖建立一個單一的表既包括這些的FD與以下約束的:

  • 一對元組(B,D)可以重複,但遵循FD的定義(每當有兩個B值相同,則D值也相同)。

    CREATE TABLE one(
        B INT PRIMARY KEY, 
        D INT NOT NULL, 
        UNIQUE (B, D) 
    ); 
    
    CREATE TABLE two(
        A INT PRIMARY KEY, 
        B INT NOT NULL, 
        C INT NOT NULL, 
        D INT NOT NULL, 
        FOREIGN KEY(B, D) REFERENCES one(B, D) 
    ); 
    

    我只是想知道如果有一個方法可以讓我在短短一個表結合這2桌,而無需使用TRIGGERS:現在

,我在2個表如下實施本在Postgresql中?

編輯:

示例數據:

enter image description here

+2

你說的'在短短一個table'結合這兩個表是什麼意思?你能舉出你想要的例子嗎,最好包括例子數據? *(我相信我理解你描述的約束條件,我只是不明白最後一點...)* – MatBailie

+0

@MatBailie我的意思是,不是創建2個單獨的表,我只是想創建一個包含所有這些屬性的表。我用一個例子編輯了這篇文章。 –

+0

好吧,我想你的意思是'我只是想創建一個包含所有這些屬性的表格,並且強制執行所有這些約束而不需要第二個表格...... – MatBailie

沙发
0
1

我必須學習它自己,但我認爲這是你想要的...

CREATE EXTENSION btree_gist; 

CREATE TABLE fd (
    a INT PRIMARY KEY, 
    b INT NOT NULL, 
    c INT NOT NULL, 
    d INT NOT NULL, 
    EXCLUDE USING gist (b WITH =, d WITH <>) 
); 

一個排除約束互相檢查所有行,就像UNIQUE約束檢查所有行。但它更廣泛。

如果所有支票(b WITH =, d WITH <>)都是TRUE則該行被拒絕。

(相反地,這意味着行是唯一可以接受的,如果至少一個檢查是FALSE。)

因此,如果兩行具有相同的b但不同dINSERTUPDATE正被運行將失敗。

http://dbfiddle.uk/?rdbms=postgres_10&fiddle=5fc308eaaedef4d3d2232ec3d70f3de6

+0

這就是我正在尋找的。可以肯定的是,在Postgresql中只使用外鍵/唯一約束或檢查約束是不可能的? –

+1

@jeeva_v如果你只想要一張桌子,那就不行了......你已經展示瞭如何用外鍵來做......唯一的選擇是觸發器等,它們都有競爭條件問題。 – MatBailie

+1

另外,請注意'EXCLUDE' ***實際上是一種'CHECK'約束。只是一個非常靈活的。 – MatBailie