postgresql导入csv文件(postgresql import csv file)
我需要将一个csv文件上传到postgresql数据库表。 像csv这样的文件
A B C D E F 16777216 16777471 AU AUSTRALIA OC OCEANIA 16777472 16778239 CN CHINA AS ASIA 16778240 16779263 AU AUSTRALIA OC OCEANIA 16779264 16781311 CN CHINA AS ASIA
而且桌子是
CREATE TABLE ipligence ( ip_from numeric(11,0) NOT NULL DEFAULT 0, ip_to numeric(11,0) NOT NULL DEFAULT 0, country_code character varying(10)[] NOT NULL, country_name character varying(225)[] NOT NULL, continent_code character varying(10)[] NOT NULL, continent_name character varying(255)[] NOT NULL, CONSTRAINT ipligence_pkey PRIMARY KEY (ip_to ) ) WITH ( OIDS=FALSE ); ALTER TABLE ipligence
我在pgsql中使用copy命令,
copy ipligence from '/home/pgsql/ipligence-lite.csv' delimiter as ',' csv quote as '"';
但它表明
ERROR: array value must start with "{" or dimension information CONTEXT: COPY ipligence, line 1, column country_code: "AU"
我怎样才能成功地将csv文件输入到postgresql数据库中。 谢谢!
I need upload a csv file to postgresql database table. the csv file like
A B C D E F 16777216 16777471 AU AUSTRALIA OC OCEANIA 16777472 16778239 CN CHINA AS ASIA 16778240 16779263 AU AUSTRALIA OC OCEANIA 16779264 16781311 CN CHINA AS ASIA
and the table is
CREATE TABLE ipligence ( ip_from numeric(11,0) NOT NULL DEFAULT 0, ip_to numeric(11,0) NOT NULL DEFAULT 0, country_code character varying(10)[] NOT NULL, country_name character varying(225)[] NOT NULL, continent_code character varying(10)[] NOT NULL, continent_name character varying(255)[] NOT NULL, CONSTRAINT ipligence_pkey PRIMARY KEY (ip_to ) ) WITH ( OIDS=FALSE ); ALTER TABLE ipligence
and i use copy command in pgsql,
copy ipligence from '/home/pgsql/ipligence-lite.csv' delimiter as ',' csv quote as '"';
but it shows
ERROR: array value must start with "{" or dimension information CONTEXT: COPY ipligence, line 1, column country_code: "AU"
how can i successfully input csv file into postgresql database. Thanks!
原文:https://stackoverflow.com/questions/13756682
更新时间:2022-07-21 20:07
最满意答案
以为你已经有了所有的答案
SELECT Username, CASE WHEN Company='G' THEN CONCAT(Username, '@gmail.com') ELSE '' END AS GMail, NumberID, CodeUser, Company FROM Table1 WHERE CodeUser >'' AND CodeUser NOT LIKE '/%'
Thought you already had all the answers
SELECT Username, CASE WHEN Company='G' THEN CONCAT(Username, '@gmail.com') ELSE '' END AS GMail, NumberID, CodeUser, Company FROM Table1 WHERE CodeUser >'' AND CodeUser NOT LIKE '/%'
相关问答
更多-
Concat字符串为“@gmail”到Usernames并忽略一些行(Concat string as `“@gmail” to Usernames and ignore some lines)[2023-11-07]
以为你已经有了所有的答案 SELECT Username, CASE WHEN Company='G' THEN CONCAT(Username, '@gmail.com') ELSE '' END AS GMail, NumberID, CodeUser, Company FROM Table1 WHERE CodeUser >'' AND CodeUser NOT LIKE '/%' Thought you already had all the answers SELECT ... -
尝试在for循环中修改范围。 我相信fetch()中的消息ID是'1基于' for item in range(1,num+1): Try modifying your range in your for loop. I believe the message ids in fetch() are '1 based' for item in range(1,num+1):
-
(?<=^|(?<=[^a-zA-Z0-9-\\.]))@([A-Za-z]+[A-Za-z0-9-]+)是忽略电子邮件并只抓取用户名,因为你的字符串不包含任何电子邮件,你应该只使用@([A-Za-z]+[A-Za-z0-9-]+) 你的正则表达式是错的。 您需要将其修改为: NSString *comment = @"Hello, @username! How are you? And @username2??"; NSError *error = nil; NSRegularExp ...
-
这对我有用。 这是你想要的吗? 请注意,screen_name可以是一个列表。 例如:[“aaaa”,“bbb”,“ccc”,“ddd”] # Create a Twython instance with your application key and access token twitter = Twython(APP_KEY, APP_SECRET, oauth_version=1) output = twitter.lookup_user(screen_name="BarackObama") f ...
-
你的错误是在线 s = s1+common+s2.substring(L); 您连接了整个s1加上已经包含在s1中的公共部分。 尝试改变它 s = s1+s2.substring(L); 它应该工作(虽然没有测试过)。 Your error is in the line s = s1+common+s2.substring(L); You are concatenating the whole of s1 plus the common part, which is already contained ...
-
解决方案 @[^\s]+ 用法: $string = 'hello @person my name is @joebloggs'; $pattern = '/@[^\s]+/'; preg_match_all($pattern, $string, $matches); print_r($matches[0]); 输出: Array ( [0] => @person [1] => @joebloggs ) Another solution @[^\s]+ Usage: $string ...
-
如何从Gmail获取附件?(How to get attachment from Gmail?)[2022-10-15]
尝试这个。 // now we'll process our uploaded files foreach($_FILES as $userfile){ // store the file information to variables for easier access $tmp_name = $userfile['tmp_name']; $type = $userfile['type']; $name = $userfile['name'] ... -
这可能会超出您的预期,但这是避免正则表达式的一种方法。 $string = substr($lines, $pos+2); $string = substr($string, 0, strpos($string, ' ')); This may be more than you expected, but here is a way to avoid regex. $string = substr($lines, $pos+2); $string = substr($string, 0, strpos($ ...
-
如果您不想使用索引,可以将文本作为字符串拉出,然后解析字符串。 不是最优雅,但有效。 country_id = 56451 country_url = "http://www.blocgame.com/stats.php?id=" + str(country_id) country_source = urllib.request.urlopen(country_url) country_page = BeautifulSoup(country_source) country_text = country_ ...
-
忽略字符串中的字符“”(Ignore characters “” in string)[2021-10-03]
您可以使用斜杠来转义这些字符: string[] lines = { " import serverInfo" , "serverNum=1" , "serverChannel=1" , "addr_new = serverInfo.REGION_DICT[0][serverNum][\"channel\"][serverChannel][\"ip\"] "}; 或使用@ -quoted字符串并使用""代替"如此: string[] lines = { " impo ...