{"action":"created","issue":{"url":"https://api.github.com/repos/jackc/pgx/issues/182","repository_url":"https://api.github.com/repos/jackc/pgx","labels_url":"https://api.github.com/repos/jackc/pgx/issues/182/labels{/name}","comments_url":"https://api.github.com/repos/jackc/pgx/issues/182/comments","events_url":"https://api.github.com/repos/jackc/pgx/issues/182/events","html_url":"https://github.com/jackc/pgx/issues/182","id":177587731,"number":182,"title":"int64 but show error pg:int4","user":{"login":"justintv90","id":2010010,"avatar_url":"https://avatars.githubusercontent.com/u/2010010?v=3","gravatar_id":"","url":"https://api.github.com/users/justintv90","html_url":"https://github.com/justintv90","followers_url":"https://api.github.com/users/justintv90/followers","following_url":"https://api.github.com/users/justintv90/following{/other_user}","gists_url":"https://api.github.com/users/justintv90/gists{/gist_id}","starred_url":"https://api.github.com/users/justintv90/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/justintv90/subscriptions","organizations_url":"https://api.github.com/users/justintv90/orgs","repos_url":"https://api.github.com/users/justintv90/repos","events_url":"https://api.github.com/users/justintv90/events{/privacy}","received_events_url":"https://api.github.com/users/justintv90/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":7,"created_at":"2016-09-17T16:09:19Z","updated_at":"2016-09-24T17:08:33Z","closed_at":null,"body":"I have this struct\r\n\r\ntype User struct {\r\n       ID int64 json:\"id\"\r\n       .....\r\n}\r\n\r\ntype Project struct {\r\n      ID int64\r\n      UserID int64 // References to user_id above\r\n...\r\n}\r\n\r\nWhen i use query 'SELECT * FROM projects WHERE user_id = $1) it showed error\r\n\r\n`1341396671631721500 is greater than max pg:int4`\r\n\r\nHow to fix it, please help. Thanks"},"comment":{"url":"https://api.github.com/repos/jackc/pgx/issues/comments/249375872","html_url":"https://github.com/jackc/pgx/issues/182#issuecomment-249375872","issue_url":"https://api.github.com/repos/jackc/pgx/issues/182","id":249375872,"user":{"login":"jackc","id":94130,"avatar_url":"https://avatars.githubusercontent.com/u/94130?v=3","gravatar_id":"","url":"https://api.github.com/users/jackc","html_url":"https://github.com/jackc","followers_url":"https://api.github.com/users/jackc/followers","following_url":"https://api.github.com/users/jackc/following{/other_user}","gists_url":"https://api.github.com/users/jackc/gists{/gist_id}","starred_url":"https://api.github.com/users/jackc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jackc/subscriptions","organizations_url":"https://api.github.com/users/jackc/orgs","repos_url":"https://api.github.com/users/jackc/repos","events_url":"https://api.github.com/users/jackc/events{/privacy}","received_events_url":"https://api.github.com/users/jackc/received_events","type":"User","site_admin":false},"created_at":"2016-09-24T17:08:33Z","updated_at":"2016-09-24T17:08:33Z","body":"I had to change the data structure a bit to get it to run. The references between `midway_1` and `shard_1` schemas didn't work. So I changed all `midway_1` to `shard_1`. I then created a user and project with the ID you gave in the original example.\r\n\r\n```sql\r\nCREATE SCHEMA shard_1;\r\nCREATE SEQUENCE shard_1.global_id_sequence;\r\n\r\n\r\nCREATE OR REPLACE FUNCTION shard_1.id_generator(OUT result bigint) AS $$\r\nDECLARE\r\nour_epoch bigint := 1314220021721;\r\nseq_id bigint;\r\nnow_millis bigint;\r\n\r\nshard_id int := 1;\r\nBEGIN\r\nSELECT nextval('shard_1.global_id_sequence') % 1024 INTO seq_id;\r\n\r\nSELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;\r\nresult := (now_millis - our_epoch) << 23;\r\nresult := result | (shard_id << 10);\r\nresult := result | (seq_id);\r\nEND;\r\n$$ LANGUAGE PLPGSQL;\r\n\r\n\r\n\r\nCREATE TABLE shard_1.users(\r\nid bigint not null PRIMARY KEY default shard_1.id_generator(),\r\ndata jsonb not null,\r\ncreated_at timestamptz not null default now(),\r\nupdated_at timestamptz not null default now()\r\n);\r\n\r\nCREATE TABLE shard_1.projects(\r\nid bigint not null PRIMARY KEY default shard_1.id_generator(),\r\nuser_id bigint not null REFERENCES shard_1.users(id) ON DELETE CASCADE,\r\ndata jsonb,\r\ncreated_at timestamptz not null default now(),\r\nupdated_at timestamptz not null default now()\r\n);\r\n\r\ninsert into shard_1.users(id, data) values(1341396671631721500, '{}');\r\ninsert into shard_1.projects(user_id) values(1341396671631721500);\r\n```\r\n\r\nThen I ran the following test program:\r\n\r\n```go\r\npackage main\r\n\r\nimport (\r\n\t\"github.com/jackc/pgx\"\r\n\t\"log\"\r\n)\r\n\r\nvar conn *pgx.Conn\r\n\r\nfunc main() {\r\n\tconfig, err := pgx.ParseEnvLibpq()\r\n\tif err != nil {\r\n\t\tlog.Fatalln(\"Unable to extract config from env:\", err)\r\n\t}\r\n\r\n\tconn, err = pgx.Connect(config)\r\n\tif err != nil {\r\n\t\tlog.Fatalln(\"Unable to connection to database:\", err)\r\n\t}\r\n\r\n\tvar projectID int64\r\n\terr = conn.QueryRow(\"SELECT id FROM shard_1.projects WHERE user_id = $1\", int64(1341396671631721500)).Scan(&projectID)\r\n\tif err != nil {\r\n\t\tlog.Fatalln(\"Query failed:\", err)\r\n\t}\r\n\r\n\tlog.Println(\"Succeeded:\", projectID)\r\n}\r\n```\r\n\r\nIt works for me.\r\n\r\n```\r\njack@edi:~/dev/go/src/github.com/jackc/pgx-182$ PGHOST=/var/run/postgresql PGDATABASE=pgx182 go run main.go \r\n2016/09/24 12:05:22 Succeeded: 1346509728796115969\r\n```\r\n\r\nYour sample code uses schemas, but the queries do not explicitly select a schema. Is it possible that there is a `projects` or `users` table earlier in the search path that is not the one you are expecting. Perhaps a table that has an `int` instead of `bigint`...\r\n"}}
{"actor":{"display_login":"jackc"}}